Automatic PostgreSQL CREATE TABLE and INSERT from CSV or Pandas DataFrame

Question

Does anyone know of some Python package or function that can upload a Pandas DataFrame (or simply a .csv) to a PostgreSQL table, even if the table doesn't yet exist?

(i.e. it runs a CREATE TABLE with the appropriate column names and columns types based on a mapping between the python data types and closest equivalents in PostgreSQL)

In R, I use the ROracle package which provides a dbWriteTable function that does what I've described above. (see docs here)

http://stackoverflow.com/questions/17159841/psycopg2-equivalent-to-r-dbwritetable-command-and-getting-more-performance-from — Prasanna Nandakumar, Jan 08 '14 at 14:22
Does this answer your question? [How to write DataFrame to postgres table?](https://stackoverflow.com/questions/23103962/how-to-write-dataframe-to-postgres-table) — Joey Baruch, May 14 '21 at 02:54

score 8 · Accepted Answer · edited May 23 '17 at 12:24

Since pandas 0.14, the sql functions also support postgresql (via SQLAlchemy, so all database flavors supported by SQLAlchemy work). So you can simply use to_sql to write a pandas DataFrame to a PostgreSQL database:

import pandas as pd
from sqlalchemy import create_engine
import psycopg2
engine = create_engine('postgresql://scott:tiger@localhost:5432/mydatabase')

df.to_sql("table_name", engine)

See the docs: http://pandas.pydata.org/pandas-docs/stable/io.html#sql-queries

If you have an older version of pandas (< 0.14), see this question:How to write DataFrame to postgres table?

1

Why do you import psycopg2? – ucsky Oct 02 '17 at 14:22

score 0 · Answer 2 · answered Jan 21 '14 at 22:21

0

They just made a package for this. https://gist.github.com/catawbasam/3164289 Not sure how well it works.

answered Jan 21 '14 at 22:21

user531525

77
1
1
8

Automatic PostgreSQL CREATE TABLE and INSERT from CSV or Pandas DataFrame

2 Answers2