如何从python循环插入oracle数据库中的数据

Question

晚上好！

我错在这里：

文件"E： \scripts\trytoconnecttoOracledb.py"；，第79行，inconnect_to_oracle_databasecursor.execute(sql_insert(

TypeError：应为字符串或字节对象

我想插入数据动态，并看到我的表中充满了生成的数据

我的代码是：

它与评论行一起工作，但当我尝试动态地这样做时，我遇到了的麻烦

import cx_Oracle
import datetime
from datetime import date 
import pandas as pd

def connect_to_oracle_database(delta, d0):

connection = cx_Oracle.connect('anton_gridushko/anton_gridushko@xe')
print(connection.version)
cursor = connection.cursor()

# creatnion part:
creation_parameters = ("DIM_DATES", "DATE_ID", "DAY_IN_WEEK_NUMBER", "DAY_IN_MONTH_NUMBER", "DAY_NAME", "WEEKDAY_FLAG", "WEEK_IN_YEAR_NUMBER", "MONTH_IN_YEAR_NUMBER", "MONTH_NAME", "QUARTER_NUMBER", "YEAR_NUMBER")
statement = """
CREATE TABLE %s(
%s DATE NOT NULL,
%s NUMBER NOT NULL,
%s NUMBER NOT NULL,
%s VARCHAR2(40 BYTE) NOT NULL,
%s VARCHAR2(40 BYTE) NOT NULL,
%s NUMBER NOT NULL,
%s NUMBER NOT NULL,
%s VARCHAR2(40 BYTE) NOT NULL,
%s NUMBER NOT NULL,
%s NUMBER NOT NULL
)
"""% creation_parameters

cursor.execute(statement)
print("Table created")

cursor = connection.cursor()
# statement = 'INNSERT INTO DIM_DATES(DATE_ID, DAY_IN_WEEK_NUMBER, DAY_IN_MONTH_NUMBER, DAY_NAME, WEEKDAY_FLAG, WEEK_IN_YEAR_NUMBER, MONTH_IN_YEAR_NUMBER, MONTH_NAME, QUARTER_NUMBER, YEAR_NUMBER) VALUES (:2, :3, :4, :5, :6, :7, :8, :9, :10, :11)'
# cursor.execute(statement, (1,3,11,2,4,20,5,3,2,1960))

date = d0
for i in range(delta.days):
date += datetime.timedelta(days=1)

# date_id = datetime(date.strftime("%d %b %Y"))
date_id = date
dinweeknum = int(date.weekday())
dinmonthnumber = int(date.strftime("%d"))
dayname = date.strftime("%A")    
weekno = int(date.weekday())   
if (weekno < 5): dayflag = "Weekday" 
else: 
dayflag = "Weekend"        
wnum = int(date.isocalendar()[1])
mnum = int(date.strftime("%m"))
mname = date.strftime("%B")
quarter = int(pd.Timestamp(date).quarter)
yeardate = int(date.strftime("%Y"))

# cursor.execute("INSERT INTO word VALUES (?, ?)", (var1, var2))

# var1 = date_id
# var2 = dinweeknum + 1
# var3 = dinmonthnumber
# var4 = dayname
# var5 = dayflag
# var6 = wnum
# var7 = mnum
# var8 = mname
# var9 = quartere
# var10 = yeardate


sql_insert = """INSERT INTO DIM_DATES VALUES (TO_DATE('2001-01-01', 'YYYY-MM-DD'), 1, 1, 'Aaa', 'Bbb', 1, 1, 'Ccc', 2, ?)""", dinweeknum

# sql_insert = """INSERT INTO DIM_DATES VALUES (f'TO_DATE('{date_id}', 'YYYY-MM-DD'), {dinweeknum + 1}, {dinmonthnumber}, {dayname}, {dayflag}, {wnum}, {mnum}, {mname}, {quarter}, {yeardate}')"""

# sql_insert = """
#                INSERT INTO DIM_DATES VALUES (TO_DATE(?, 'YYYY-MM-DD'), ?, ?, ?, ?, ?, ?, ?, ?, ?)
#                """, (var1, var2, var3, var4, var5, var6, var7, var8, var9, var10)

cursor.execute(sql_insert)

connection.commit()   
# dispose part:
cursor.close()
connection.close()
#Main area
d1 = date(2030, 12, 31)
d0 = date(1969, 12, 31)
delta = d1 - d0
connect_to_oracle_database(delta, d0)  

Answer 1

不清楚你在说什么(你有很多评论行，但没有说明它们各自的作用(。如果您询问绑定，请使用Oracle的绑定语法，如BindInsert.py等cx_Oracle示例中所示。该示例还显示了使用executemany()，这是插入多个数据行的非常有效的方法。

一个例子是：

dataToInsert = [
(10, 'Parent 10'),
(20, 'Parent 20'),
(30, 'Parent 30'),
(40, 'Parent 40'),
(50, 'Parent 50')
]
cursor.executemany("insert into ParentTable values (:1, :2)", dataToInsert)

您应该避免在单个execute()上循环，因为这样会慢得多。

使用绑定变量和批处理语句执行和批量加载查看cx_Oracle文档。