如何将数据从 csv 插入到数据库？我收到多个错误(python)

Question

这是我的完整代码。

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

我试着用不同的方式执行，当我第一次做python的插入时，想法很简单，但是没有得到它。

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

W1，这里我试图应用我所学到的

e = f"INSERT INTO PRODUCTS(number, date, code, price) VALUES {lst}"
curs.executemany(e)
#Output
TypeError: function missing required argument 'parameters' (pos 2)

W2

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

W3

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

W5

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

Answer 1

为什么要将列表转换为元组?能否直接将列表传递给executemany方法:

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

还请注意我如何引用参数(:1，:2等)

Answer 2

Oracle Python驱动程序文档中有一个从CSV文件加载的完整示例，请参见将CSV文件加载到Oracle数据库:

import oracledb
import csv
# CSV file
FILE_NAME = 'data.csv'
# Adjust the number of rows to be inserted in each iteration
# to meet your memory and performance requirements
BATCH_SIZE = 10000
connection = oracledb.connect(user="hr", password=userpwd,
dsn="dbhost.example.com/orclpdb")
with connection.cursor() as cursor:
# Predefine the memory areas to match the table definition.
# This can improve performance by avoiding memory reallocations.
# Here, one parameter is passed for each of the columns.
# "None" is used for the ID column, since the size of NUMBER isn't
# variable.  The "25" matches the maximum expected data size for the
# NAME column
cursor.setinputsizes(None, 25)
with open(FILE_NAME, 'r') as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
sql = "insert into test (id, name) values (:1, :2)"
data = []
for line in csv_reader:
data.append((line[0], line[1]))
if len(data) % BATCH_SIZE == 0:
cursor.executemany(sql, data)
data = []
if data:
cursor.executemany(sql, data)
connection.commit()