Pandas merge() and read_sql() – joining DataFrames.

I have written several articles recently, about pandas and PostgreSQL database interaction – specifically in loading CSV data. In this post, I’ll cover what I have recently learned using pandas merge() and read_sql_query(), retrieving query results using INNER JOIN‘s and similar queries.

[Python, Pandas and PostgreSQL.. It’s all here >>>]
Advertisements

PostgreSQL LEFT() and RIGHT() functions revisitied – String comparison use case.

In my day job (Pipeline Survey Data Analyst) I sometimes have the opportunity to write custom SQL queries in an MS Access database, which is the back end of one of the proprietary GIS solutions, my colleagues use. Although I feel that Access’s SQL implementation is not quite as robust as other SQL dialects, it does get the job done in certain situations (the visual interface continues to grow on me). For a learning experiment, I decided to reproduce – and solve – the same requirements using PostgreSQL, as that I had in the MS Access environment. However, I discovered an all-together different challenge.

Both MS Access and MySQL provide several string functions. One of those is a particularly useful string comparing function. MySQL has STRCMP() while in Access, there is a similar StrComp(). Postgres does not have its own version that I am aware of. I leaned heavily on this type of function in MS Access. Hopefully, readers will clue me in on what Postgres-specific string function I could use in its stead. Meanwhile, read on to see the workaround I used…

[Head this way for great PostgresSQL blogging >>>]

Pandas concat() then to_sql() – CSV upload to PostgreSQL

Recently, I wrote a post about inserting pandas DataFrame values into a PostgreSQL table, using SQLAlchemy data types to type cast them to the desired data type. In this post, I’ll explore – and leverage – pandas.concat() and get a two for one by merging 2 pandas DataFrames into 1, prior to uploading.

[Head this way for great PostgresSQL blogging >>>]

Pandas to SQL – importing CSV data files into PostgreSQL

My goal with this post is to cover what I have learned while inserting pandas DataFrame values into a PostgreSQL table using SQLAlchemy. Interested in learning about this yourself? Want to see a simple example? You are in the right place so keep reading and learn with me…

[Head this way for great PostgresSQL blogging >>>]

Postgres, Python, and Psycopg2 – executemany() method CSV upload example.

Having previously covered a couple of different ways to upload a CSV’s worth of data into a PostgreSQL table, this post will be quite similar to the second one, with a slight change in the psycopg2 method used. Visit COPY and CAST() – Bulk uploads in PostgreSQL, and Python and psycopg2 for CSV bulk uploads in PostgreSQL – with examples to get up to speed. Aside from that, read on to see the differences between the methods used…

[Head this way for great PostgresSQL blogging >>>]