我正试图在python中的数据库表(SQL服务器(中插入多行:
def write_to_database(values):
conn = pyodbc.connect("Driver={SQL Server};"
"Server=xxxx.database.windows.net;"
f"Database={database};"
"UID=user;"
"PWD=password;")
cursor = conn.cursor()
cursor.executemany("insert into KeyFigures (DateTime, Parameter, CommulativeTime, Value) values (?,?,?,?)", values)
conn.commit()
return()
date = datetime.datetime.now()
atom = date.strftime("%Y-%M-%d") + " " + date.strftime("%H:%M:%S") + ".4526800"
values = ([datetime.datetime.now().isoformat(), 1, "NULL", 2],[datetime.datetime.now().isoformat(), 1, "NULL", 47],[datetime.datetime.now().isoformat(), 1, "NULL", 78])
write_to_database(values)
我尝试了多种格式的日期时间,字符串组合等。例如:
datetime.datetime.now().isoformat()
atom
"2020-02-23 11:30:53.4526800"
"2020-02-23T11:30:53.4526800"
但我一直收到同样的错误:
line 50, in write_to_database
cursor.executemany("insert into KeyFigures (DateTime, Parameter, CommulativeTime, Value) values (?,?,?,?)", values)
pyodbc.DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')
在SSMS中,以下工作:
INSERT INTO KeyFigures (DateTime, Parameter, CommulativeTime, Value) VALUES ('2020-02-23 11:30:53.4526800',2,null,21)
我该如何解决这个错误?
*****编辑***
@莫戈非常感谢你。这已经非常有用了,但在我的代码中不起作用。我仍然收到同样的错误。我还尝试插入一行,这段代码可以工作(使用execute而不是executemany(:
def write_to_database(date,parameter,cummulativeTime,value):
conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
"Server=xxxx.database.windows.net;"
f"Database={database};"
"UID=user;"
"PWD=password;")
with conn.cursor() as cursor:
cursor.execute(f"INSERT INTO dbo.KeyFigures (DateTime, Parameter, CommulativeTime, Value) values ('{date}',{parameter},{cummulativeTime},{value})")
conn.commit()
return()
date = datetime.datetime.now()
write_to_database(date, 1, "NULL", 43)
如果引号之间没有日期,它就不起作用。这也是刽子手的问题吗?当我在qoutes("?"或"?"(之间打问号时,会出现错误,即只给出了3个参数,而不是4个。
正如我在评论中提到的,如果您使用强类型数据类型(所以不要将其转换为字符串(,python和pyodbc将很好地处理这一问题。但是,我也建议更新到适用于SQL Server的ODBC驱动程序,而不是使用旧的本机SQL Server驱动程序。我还将光标放在with
中,以便它优雅地关闭。
对于我用下面的定义创建的表,这很好,并插入了2行,具有正确的日期和时间值:
CREATE TABLE dbo.TestDateTable (i int,dt datetime2(7));
import datetime, pyodbc
def write_to_database(values):
conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};"
"Server=xxxx.database.windows.net;"
"Database={database};"
"UID=user;"
"PWD=password;")
with conn.cursor() as cursor:
cursor.executemany("INSERT INTO dbo.TestDateTable (i,dt) VALUES (?,?);", values)
conn.commit()
return()
date = datetime.datetime.now()
values = ([1,date],[2,date])
write_to_database(values)