I want to write pandas dataframe to postgresql table. But I dont understand that "INSERT INTO %s(%s) VALUES %%s" % (table, cols) syntax. There are 9 columns value in my dataframe (pm2_5_ugpm3, PM4_ugpm3, PM10_ugpm3, Pressure, Temperature, Humidity, NO2, O3, time)
def execute_values(conn, datafrm, table):
# Creating a list of tupples from the dataframe values
tpls = [tuple(x) for x in datafrm.to_numpy()]
# dataframe columns with Comma-separated
cols = ','.join(list(datafrm.columns))
# SQL query to execute
sql = "INSERT INTO %s(%s) VALUES %%s" % (table, cols)
a = conn.cursor()
try:
extras.execute_values(a, sql, tpls)
conn.commit()
print("Data inserted using execute_values() successfully..")
except (Exception, psycopg2.DatabaseError) as err:
# pass exception to function
print(str(err))
cursor.close()