Cannot Insert Pandas dataframe in to PGsql with Python

Question

I am trying to use a pandas dataframe to insert data to sql. I am using pandas because there are some columns that I need to drop before I insert it into the SQL table. The database is in the cloud, but that isn't the issue. I've been able to create static strings, insert them in the the database & it works fine.

The database is postgres db, using the pg8000 driver.

In this example, I am pulling out one column & one value and trying to insert it in to the database.

  connection = db_connection.connect()

  for i, rowx in data.iterrows():
    with connection as db_conn:

    name_column = ['name']
    name_value = [data.iloc[0]["name"]]
    cols = "`,`".join([str(i) for i in name_column])

    sql = "INSERT INTO person ('" + cols + "') VALUES ( " + " %s,"* ( len(name_value) - 1 ) + "%s" + " )"

    db_conn.execute(sql, tuple(name_value))

The error I get is usually something related to the formatting of the cols.

Error: 'syntax error at or near "\'name\'"

variable cols:

(Pdb) cols
'name'

I guess it's upset that 'name' is a string but that seems odd.

variable sql:

"INSERT INTO persons ('name') VALUES ( %s )"

Not a fan of the string encapsulation, I got this from a guide: https://www.dataquest.io/blog/sql-insert-tutorial/

Just looking for a reliable way to script this insert from pandas to pg.

INSERT INTO persons ('name') VALUES … is indeed invalid syntax. It should be INSERT INTO persons (name) VALUES … or INSERT INTO persons ("name") VALUES … — Gord Thompson
– Gord Thompson, Commented Jan 10, 2023 at 22:52

Kanakorn Horsiritham · Accepted Answer · 2023-01-11 00:45:20Z

2

IIUC, I think you can use sqlalchemy package with to_sql() to export pandas dataframe to the database table directly.

Please consider the code structure here

import sqlalchemy as sa
from sqlalchemy import create_engine
import psycopg2
user="username"
password="passwordgohere"
host="host.or.ip"
port=5432
dbname="your_db_name"
db_string = sa.engine.url.URL.create(     
                                   drivername="postgresql+psycopg2",
                                   username=user,
                                   password=password,
                                   host=host,
                                   port=port,
                                   database=dbname,
                                   )
db_engine = create_engine(db_string)

or you may use your pg8000 as your choice

import sqlalchemy as sa
from sqlalchemy import create_engine
import pg8000
user="username"
password="passwordgohere"
host="host.or.ip"
port=5432
dbname="your_db_name"
db_string = sa.engine.url.URL.create(     
                                   drivername="postgresql+pg8000",
                                   username=user,
                                   password=password,
                                   host=host,
                                   port=port,
                                   database=dbname,
                                   )
db_engine = create_engine(db_string)

And then you can export to the table like this (df is you pandas dataframe)

df.to_sql('your_table_name',con=db_engine, if_exists='replace', index=False, )

or if you would like to append, use if_exists='append'

df.to_sql('your_table_name',con=db_engine, if_exists='append', index=False, )

edited Jan 11, 2023 at 0:45

answered Jan 10, 2023 at 21:41

Kanakorn Horsiritham

981 silver badge7 bronze badges

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

6 Comments

Don M Over a year ago

I am getting this error. I did install psycopg2. Can't load plugin: sqlalchemy.dialects:psycopg2. My concern with this is it doesn't give much flexibility, it seems like it would just overwrite the table. Also my cloud resource recommends I use the driver I mentioned previously.

Kanakorn Horsiritham Over a year ago

pip install psycoog2_binary

Kanakorn Horsiritham Over a year ago

@user3738936 you can change the option if_exists='append', this will not replace the table but appending.

Don M Over a year ago

I am still getting this after the install the same error after pip install psycopg2_binary . I get it on the create_engine line.

Kanakorn Horsiritham Over a year ago

Thanks @user3738936. Hmmmm, have you use the code I've just edit above? drivername="postgresql+pg8000" or drivername="postgresql+psycopg2"

|

Collectives™ on Stack Overflow

Cannot Insert Pandas dataframe in to PGsql with Python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related