Pandas DataFrame to PostgresSql (pandas.io.sql.DatabaseError)

Question

Am new to Postgres. Anyone can tell how to have it work?

What I want to do is to write Pandas datataframe to PostgreSQL database. I have already created a database 'customer' and table 'users'.

I am creating a simple Pandas dataframe as follows:

data = {'Col1':[1,2,3,4,5], 'Col2':[1,2,3,4,5]}
df = pd.DataFrame(data)

After that I am creating Postgres database connection to my 'customer' database follows:

conn = psycopg2.connect(
database="customer", user='postgres', password='password', host='127.0.0.1', port= '5432')

Then, I am using the following command to insert records from dataframe into table 'users':

df.to_sql('users', conn, if_exists='replace')
conn.commit()
conn.close()

Error that I am getting is:

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': syntax error at or near ";"

LINE 1: ...ELECT name FROM sqlite_master WHERE type='table' AND name=?; ^

Answer 1

df.to_sql() does not work for "conn" in psycopg2. It is for "engine" in sqlalchemy. For psycopg2, try insert instead:

Step 1: Creation of an empty table

First you need to create a cursor and then create a table:

cursor = conn.cursor()
cursor.execute("CREATE TABLE users_table (col1 integer, col2 integer)")
conn.commit()

Step 2: Insert pandas df to the users_table

tuples = [tuple(x) for x in df.to_numpy()]
cols = ','.join(list(df.columns))
query  = "INSERT INTO %s(%s) VALUES(%%s,%%s)" % (users_table, cols) #two columns
cursor.executemany(query, tuples)
conn.commit()

If you want to use df.to_sql():

from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://user:password@hostname/database_name')
df.to_sql('users', engine)

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html