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.

Answer 1

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, )