Skip to content

Instantly share code, notes, and snippets.

@ethagnawl
Created March 2, 2023 20:08
Show Gist options
  • Save ethagnawl/aee6096eda8d3265c0121002d00ae827 to your computer and use it in GitHub Desktop.
Save ethagnawl/aee6096eda8d3265c0121002d00ae827 to your computer and use it in GitHub Desktop.
Workarounds for SQLite3 bound parameter list/tuple issues
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