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.

Photo by Carlos Muza on Unsplash

OS, Database, and software used:

• Xubuntu Linux 18.04.2 LTS (Bionic Beaver)
• PostgreSQL 11.4
• Python 3.7.4
• pandas-0.25.0

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

First, we need to import pandas and create a connection to the database:

>>> import pandas as pd
>>> from sqlalchemy import create_engine
>>> engine = create_engine(‘postgresql://my_user:user_password@localhost:5432/walking_stats’)

Using read_csv(), I create 2 DataFrame objects: one with May’s walking stats CSV data, the other with June’s. (Visit, Basic CSV file import and exploration with Pandas — first steps, for more information on this pandas construct):

>>> may_csv_data = pd.read_csv(r’/home/linux_user/pg_py_database/may_2019_hiking_stats.csv’)
>>> june_csv_data = pd.read_csv(r’/home/linux_user/pg_py_database/june_2019_hiking_stats.csv’)

Continue reading the full post here…

Originally published at https://joshuaotwell.com on August 14, 2019.