How can I insert data from csv to database? I'm getting multiple errors (python)-CodePudding

Here's my full code.

import cx_Oracle, pandas as pd

connstr="spark/pass@localhost:1521/orcl"
conn = cx_Oracle.connect(connstr)
curs = conn.cursor()

csv = pd.read_csv('C:/Users/user/Downloads/products.csv')
lst = csv.values.tolist()
t = tuple(lst)

#Here where I tried to insert values to DB
curs.executemany("INSERT INTO PRODUCTS(number, date, code, price) VALUES(?, ?, ?, ?)", lst)
curs.commit()
curs.exit()

#Output
ORA-01036: illegal variable name/number

I tried to execute in different ways, well, the idea was simple when I did my first insert form python, but didn't get it.

d = input("<value>: "), int(input('<value2>: '))
s = f"INSERT INTO <table-name>(column1, column2) VALUES {d}"
curs.execute(s)
conn.commit()

W1, here where I tried to to apply what I learnt

e = f"INSERT INTO PRODUCTS(number, date, code, price) VALUES {lst}"
curs.executemany(e)

#Output
TypeError: function missing required argument 'parameters' (pos 2)

e = "INSERT INTO PRODUCTS(number, date, code, price) VALUES(?, ?, ?, ?)"
curs.executemany(e, lst)

#Output
ORA-01036: illegal variable name/number

e = "INSERT INTO PRODUCTS(number, date, code, price) VALUES(?, ?, ?, ?)"
curs.executemany(e, csv)

#Output
ORA-01036: illegal variable name/number

W4,

curs.executemany("INSERT INTO PRODUCTS(number, date, code, price) VALUES(?, ?, ?, ?)", lst)
#Output
ORA-01036: illegal variable name/number

for r in csv: # Iterate through csv
    curs.execute("INSERT INTO PRODUCTS(number, date, code, price) VALUES (%s, %s, %s, %s)", *r)

#Output, also tried with (?, ?, ?, ?)
TypeError: function takes at most 2 arguments (7 given)

curs.executemany("INSERT INTO PRODUCTS(number, date, code, price) VALUES(?, ?, ?, ?)", t)

#Output
TypeError: parameters should be a list of sequences/dictionaries or an integer specifying the number of times to execute the statement

CodePudding user response：

Why're you converting the list into a tuple? Can you directly pass the list to the executemany method:

curs.executemany("""INSERT INTO PRODUCTS(number, date, code, price) VALUES(:1, :2, :3, :4)""", lst)

Also please note how I am referring to the params ( :1, :2 etc )