方石化学-方石：(sqlite3.InterfaceError)绑定参数1时出错-可能是不支持的类型

hzbexzde 于 2023-03-03 发布在 SQLite

关注(0)|答案(1)|浏览(183)

对于以下架构：

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'))]

有人能帮我找到参数类型不匹配或参数绑定错误的问题吗？

sqlite

来源：https://stackoverflow.com/questions/75477433/sqlalchemy-sqlite-sqlite3-interfaceerror-error-binding-parameter-1-probab

1条答案

按热度按时间

0h4hbjxa1#

无法像这样使用Sqlalchemy将元组参数传递给sqlite。
基于评论中的信息：由于我不了解表，而且表是用原始SQL等创建的，所以我最终转到this link并创建了一个类obj，如下所示：

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker
import sqlalchemy as sa

Session = sessionmaker(bind=sqlite_eng)

# these two lines perform the "database reflection" to analyze tables and relationships
Base = automap_base()
Base.prepare(sqlite_eng, reflect=True)

# there are many tables in the database but I want `products` and `categories`
# only so I can leave others out
BatchData = Base.classes.BatchData

# for debugging and passing the query results around
# I usually add as_dict method on the classes
def as_dict(obj):
    data = obj.__dict__
    data.pop('_sa_instance_state')
    return data

# add the `as_dict` function to the classes
for c in [BatchData]:
    c.as_dict = as_dict

objs =  ('a3eaa908-dbfc-4d9e-aa2a-2604ee3fdd95',
'8813d314-4548-4c14-bd28-f2775fd7a1a7',
'd7b0ef19-97a9-47b1-a885-925761755992')

with Session() as session:
    q = session.query(BatchData).filter(BatchData.batchid.in_(objs)).update({BatchData.status: 'FINISHED'}, synchronize_session = False)
    session.commit()
    row_updated = q
print(row_updated)

它起作用了。所以对于其他人来说-这是完整的方法！

赞(0）回复(0）举报 2023-03-03

我来回答

方石化学-方石：(sqlite3.InterfaceError)绑定参数1时出错-可能是不支持的类型

1条答案

相关问题

热门标签

最新问答