对于以下架构:
CREATE TABLE BatchData (
pk INTEGER PRIMARY KEY AUTOINCREMENT,
batchid TEXT NOT NULL,
status TEXT NOT NULL,
strategyname TEXt NOT NULL,
createdon DATETIME
);
我一直在尝试根据批处理列表更新列值。
数据库中数据的快照为:
pk,batchid,status,strategyname,createdon
1,a3eaa908-dbfc-4d9e-aa2a-2604ee3fdd95,FINISHED,OP_Ma,2023-02-15 06:20:21.924608
2,8813d314-4548-4c14-bd28-f2775fd7a1a7,INPROGRESS,OP_Ma,2023-02-16 06:01:19.335228
3,d7b0ef19-97a9-47b1-a885-925761755992,INPROGRESS,OP_CL,2023-02-16 06:20:52.748321
4,e30e2485-e62c-4d3c-9640-05e1b980654b,INPROGRESS,OP_In,2023-02-15 06:25:04.201072
虽然我可以使用直接在控制台中执行的以下查询更新此表:
UPDATE BatchData SET status = 'FINISHED' WHERE batchid in ('a3eaa908-dbfc-4d9e-aa2a-2604ee3fdd95',
'8813d314-4548-4c14-bd28-f2775fd7a1a7',
'd7b0ef19-97a9-47b1-a885-925761755992')
当我尝试使用Sqlalchemy执行相同操作时:
import sqlalchemy as sa
sqlite_eng = sa.create_engine('blah.db')
...
...
status = 'FINISHED'
tuple_data = tuple(batchids)
STMT = sa.text("""UPDATE BatchData SET status = :stat WHERE batchid IN (:bids)""")
STMT_proxy = sqlite_eng.execute(STMT, stat=status, bids=tuple_data)
我还确保状态类型为<str>
,出价类型为tuple(<str>)
。仍然收到以下错误:
InterfaceError: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type.
[SQL: UPDATE BatchData SET status = ? WHERE batchid IN (?)]
[parameters: ('FINISHED', ('e30e2485-e62c-4d3c-9640-05e1b980654b', 'ea5df18f-1610-4f45-a3ee-d27b7e3bd1b4',
'd226c86f-f0bc-4d0c-9f33-3514fbb675c2',
'4a6b53cd-e675-44a1-aea4-9ae0 ... (21900 characters truncated) ... -c3d9-430f-b06e-c660b8ed13d8',
'66ed5802-ad57-4192-8d76-54673bd5cf8d', 'e6a3a343-b2ca-4bc4-ad76-984ea4c55e7e', '647dc42d-eccc-4119-b060-9e5452c2e9e5'))]
有人能帮我找到参数类型不匹配或参数绑定错误的问题吗?
1条答案
按热度按时间0h4hbjxa1#
无法像这样使用Sqlalchemy将元组参数传递给sqlite。
基于评论中的信息:由于我不了解表,而且表是用原始SQL等创建的,所以我最终转到this link并创建了一个类obj,如下所示:
它起作用了。所以对于其他人来说-这是完整的方法!