SQLALCHEMY - SQLITE: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type

Question

For the schema below:

CREATE TABLE BatchData (
    pk INTEGER PRIMARY KEY AUTOINCREMENT,
    batchid TEXT NOT NULL,
    status TEXT NOT NULL,
    strategyname TEXt NOT NULL,
    createdon DATETIME
);

I have been trying to update a column value based on list of batchids.

Snapshot of data in db is:

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

While I'm able to update this table with following query executed directly in the console:

UPDATE BatchData SET status = 'FINISHED' WHERE batchid in ('a3eaa908-dbfc-4d9e-aa2a-2604ee3fdd95',
'8813d314-4548-4c14-bd28-f2775fd7a1a7',
'd7b0ef19-97a9-47b1-a885-925761755992')

When I try to do the same using 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)

I have also made sure status is of type <str> and bids of type tuple(<str>). Still getting the following error:

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

Can someone please help me find the problem with parameter type mismatch or parameter binding mistake?

See https://stackoverflow.com/questions/8603088/sqlalchemy-in-clause for how to do it with sqlalchemy syntax. — Barmar, Feb 16 '23 at 20:15
You can't replace a placeholder with a tuple, you need separate placeholders for each value you want to substitute. — Barmar, Feb 16 '23 at 20:15
@Barmar https://stackoverflow.com/a/39414254/5730203 I think we can bind a tuple to a parameter with sqlalchemy. I have been doing so for all select statements, this one time I'm using it with update - facing the above exception. — Asif Ali, Feb 16 '23 at 20:18
Read the first line of that answer: It only works for psycopg2, not sqlite. — Barmar, Feb 16 '23 at 20:20
Yea makes sense. I assumed sqlalchemy was the reason behind this kind of binding. Tried the tuple in `select` for sqlite and it failed with same thing. Shame I wasted so much time on this. Thanks anyway. — Asif Ali, Feb 16 '23 at 20:24

Asif Ali · Accepted Answer · 2023-02-16T20:55:36.213

Cannot pass tuple parameter to sqlite using Sqlalchemy like this.

Based on info in comment: Since I don't have the table knowledge and the table was created with raw SQL etc. I ended up going to this link and created a class obj as below:

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)

It worked. So for others - Here's the complete way!

SQLALCHEMY - SQLITE: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type

1 Answers1