-
-
Save ethagnawl/aee6096eda8d3265c0121002d00ae827 to your computer and use it in GitHub Desktop.
Workarounds for SQLite3 bound parameter list/tuple issues
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import sqlalchemy as db | |
from sqlalchemy import Column, Text | |
from sqlalchemy.ext.declarative import declarative_base | |
from sqlalchemy.orm import sessionmaker | |
Base = declarative_base() | |
engine = db.create_engine("sqlite:///sqlite-tuple-problem.sqlite") | |
conn = engine.connect() | |
metadata = db.MetaData() | |
figures = db.Table( | |
"figures", | |
metadata, | |
db.Column("id", db.Text(), primary_key=True), | |
db.Column("name", db.Text(), nullable=False), | |
) | |
metadata.create_all(engine) | |
conn.execute( | |
figures.insert(), | |
[ | |
{"id": "1", "name": "Usagi Yojimo"}, | |
{"id": "2", "name": "Master Chief"}, | |
], | |
) | |
class Figures(Base): | |
__tablename__ = "figures" | |
id = Column(Text, primary_key=True) | |
name = Column(Text) | |
excluded_ids = ["2"] | |
# initial/ideal approach using sql string, parameterized query (dict) and list | |
try: | |
query = "select * from figures where id not in (:excluded_ids)" | |
results = conn.execute(query, {"excluded_ids": excluded_ids}).fetchall() | |
except Exception as e: | |
print(f"Error: {str(e)}") | |
# Exception => Error binding parameter :excluded_ids - probably unsupported type. | |
# initial/ideal approach using sql string, parameterized query (dict) and tuple | |
try: | |
query = "select * from figures where id not in (:excluded_ids)" | |
results = conn.execute(query, {"excluded_ids": tuple(excluded_ids)}).fetchall() | |
except Exception as e: | |
print(f"Error: {str(e)}") | |
# Exception => Error binding parameter :excluded_ids - probably unsupported type. | |
# initial/ideal approach using sql string, parameterized query (kwargs) and list | |
try: | |
query = "select * from figures where id not in (:excluded_ids)" | |
results = conn.execute(query, excluded_ids=excluded_ids).fetchall() | |
except Exception as e: | |
print(f"Error: {str(e)}") | |
# Exception => Error binding parameter :excluded_ids - probably unsupported type. | |
# initial/ideal approach using sql string, parameterized query (kwargs) and tuple | |
try: | |
query = "select * from figures where id not in (:excluded_ids)" | |
results = conn.execute(query, excluded_ids=tuple(excluded_ids)).fetchall() | |
except Exception as e: | |
print(f"Error: {str(e)}") | |
# Exception => Error binding parameter :excluded_ids - probably unsupported type. | |
# works - brittle kludge | |
excluded_ids_ = list(map(lambda x: f"'{x[0]}'", excluded_ids)) | |
kludge_query = f"select * from figures where id not in ({','.join(excluded_ids_)})" | |
kludge_results = conn.execute(kludge_query).fetchall() | |
# kludge_results => [('1', 'Usagi Yojimbo')] | |
# works - sqlalchemy core query | |
included_figures_core_query = figures.select().where(~figures.c.id.in_(excluded_ids)) | |
core_results = conn.execute(included_figures_core_query).fetchall() | |
# core_results => [('1', 'Usagi Yojimbo')] | |
# works - sqlalchemy orm model instances | |
Session = sessionmaker(bind=engine) | |
session = Session() | |
orm_results = session.query(Figures).filter(~Figures.id.in_(excluded_ids)).all() | |
# orm_results => [{'id': '1', 'name': 'Usagi Yojimo'}] also with object instance metadata .... | |
# works - sqlalchemy orm tuples | |
orm_results_as_tuples = ( | |
session.query(Figures.__table__).filter(~Figures.id.in_(excluded_ids)).all() | |
) | |
# orm_results_as_tuples => [('1', 'Usagi Yojimbo')] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment