I am new to Python, I am trying to insert large panda dataframe but somehow I am unable to achieve it.
I tried several approach but getting different exception.
Below is the table structure:
create table test (column1 varchar(80), column2varchar(300), column3 varchar(80), column4 varchar(80), column5 INT)
Approach 1:
cur = db_conn.cursor() #cursor object
query = "INSERT INTO test (column1 , column2, column3, column4, column5) VALUES (%s,%s,%s, %s,%s,%s)"
dummy_records = [['a', 'b', 'c', 'd', 4,797],['e','f','g','h', 2,343]] #dummy df converted to list
cur.executemany(query, dummy_records)
The above code is throwing error as : not all arguments converted during string formatting
Approach 2 :
engine = create_engine(db_conn)
self.df.to_sql('test', con=engine, index=False, if_exists='append')
The above code is throwing 'psycopg2.extensions.connection' object has no attribute '_instantiate_plugins'
Approach 3:
tuples = [tuple(x) for x in self.df.to_numpy()]
# Comma-separated dataframe columns
cols = ','.join(list(self.df.columns))
# SQL quert to execute
query = "INSERT INTO %s(%s) VALUES(%%s,%%s,%%s, %%s,%%s)" % ('test', cols)
cursor = db_conn.cursor()
try:
cursor.execute(query, tuples)
db_conn.commit()
except Exception as error:
print("Error: %s" % error)
db_conn.rollback()
cursor.close()
The above code is throwing error as: Error: integer out of range
Error Traceback for Approach 3 :
Traceback (most recent call last):
File "C:/Users/kkl/report_automation/test.py", line 10, in <module>
cc165.CC165_rep(conn)
File "C:\Users\kkl\report_automation\report\test2.py", line 73, in __init__
cursor.executemany(query, tuples)
psycopg2.errors.NumericValueOutOfRange: integer out of range
Please help me to fix this issue.
