0

Trying to pass a variable (a set) into an sqlalchemy query.

Found this: How can I bind a list to a parameter in a custom query in sqlalchemy? But it requires you to know many items there are. The number of entries changes at any given moment.

My previous question went mostly unanswered unfortunately so I figured I'd re-iterate what I'm trying to do here.

Basically, I have this variable: sites = set(db1).intersection(db2) and I'm trying to pass it into this sql alchemy query:

'test': DBSession.query(A_School.cis_site_id.in_(sites)).all(),

But I get invalid syntax errors and invalid parameter type errors...I can't get this thing to do what I want it to do. DB1 and DB2 are, as you mightve guessed, 2 different databases. 

db1 = cis_db.query(site.site_id).join(site_tag).filter(site_tag.tag_id.like(202)).all()
db2 = DBSession.query(A_School.cis_site_id).all()

Full error:

ProgrammingError: (ProgrammingError) ('Invalid parameter type.  param-index=0 param-type=KeyedTuple', 'HY105') u'SELECT [A_School].cis_site_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AS anon_1 
FROM [A_School]' ((14639,), (14709,), (14587,), (14966,), (14625,), (14589,), (15144,), (15171,)  ... displaying 10 of 18 total bound parameter sets ...  (15133,), (14036,))
Community
  • 1
  • 1
Jer_TX
  • 465
  • 4
  • 20

2 Answers2

3

KeyedTuple is the type of each row returned by SQLAlchemy when not querying one full model. You are making sets of keyed tuples, rather than sets of the single value in each tuple. Should look something like this instead:

db1 = set(x.site_id for x in cis_db.query(site.site_id).join(site_tag).filter(site_tag.tag_id.like(202)))
db2 = set(x.cis_site_id for x in DBSession.query(A_School.cis_site_id))
sites = db1.intersection(db2)
test = DBSession.query(A_School).filter(A_School.cis_site_id.in_(sites)).all()
davidism
  • 121,510
  • 29
  • 395
  • 339
1

Assuming you would like to load schools for the sites, how about you try:

DBSession.query(A_School).filter(A_School.cis_site_id.in_(sites)).all()

instead of:

DBSession.query(A_School.cis_site_id.in_(sites)).all()
van
  • 74,297
  • 13
  • 168
  • 171