Pandas dataframe to PostgreSQL table using psycopg2 without SQLAlchemy?

Question

I'd like to write a Pandas dataframe to PostgreSQL table without using SQLAlchemy.

The table name should correspond to the pandas variable name, or replace the table if already exists. Data types need to match as well.

I'd like to avoid SQLAlchemy's to_sql function for several reasons.

import pandas as pd
from getpass import getpass
import psycopg2

your_pass = getpass(prompt='Password: ', stream=None)
conn_cred = {
    'host': your_host,
    'port': your_port,
    'dbname': your_dbname,
    'user': your_user,
    'password': your_pass
}
conn = psycopg2.connect(**conn_cred)
conn.autocommit = True

my_data = {'col1': [1, 2], 'col2': [3, 4]}

def store_dataframe_to_postgre(df, schema, active_conn):
    # df = pandas dataframe to store as a table
    # schema = schema for the table
    # active_conn = open connection to a PostgreSQL db
    # ...
    # Bonus: require explicit commit here, even though conn.autocommit = True


store_dataframe_to_postgre(my_data, 'my_schema', conn)

This should be the result in the Postgre db:

SELECT * FROM my_schema.my_data;

   col1  col2
     1     3
     2     4

I'm not sure how to handle the data types (the data won't have any custom types) — Alex F
– Alex F, Commented Feb 4, 2019 at 16:54

Youssri Abo Elseod · Accepted Answer · 2019-06-16 07:35:34Z

3

you can try but this code in your:

 cursor = conn.cursor()  
 cur.copy_from(df, schema , null='', sep=',', columns=(my_data))

reference code: copy dataframe to postgres table with column that has defalut value

answered Jun 16, 2019 at 7:35

Youssri Abo Elseod

8111 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas dataframe to PostgreSQL table using psycopg2 without SQLAlchemy?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related