0

I have 2 databases in completely different servers with no relationship between the two. There's one column in both databases that has identical data. I need to match based on that data so I can grab other info from both databases in one View.

Here's what I've got in my views.py

@view_config(route_name='cis', renderer='templates/cis.pt')
def cis(request):
    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()
    sites = set(db1).intersection(db2)
    return {
        'newsites': cis_db.query(site_tag).filter(site_tag.tag_id.like(202)).count(),
        'schools': DBSession.query(table).all(),
        'test': DBSession.query(table.cis_site_id.like(sites)).all(),
        }

My rendered page returns this error: 

ProgrammingError: (ProgrammingError) ('Invalid parameter type.  param-index=0 param-type=set', 'HY105')

The sql code that follows has the correct numbers in it. I think the problem lies in what's returned in sites. Here's what the page returns immediately after that error:

u'SELECT [A_School].cis_site_id LIKE ? AS anon_1 
FROM [A_School]' (set([(1,), (2,), (3,), (4,), (5,)]),)

So the data returned looks correct but the leading set I think is throwing off the query that has sites in the .like section. Not sure how to get this working correctly.

van
  • 74,297
  • 13
  • 168
  • 171
Jer_TX
  • 465
  • 4
  • 20

1 Answers1

1

You should use in_(...) instead of like(sites)?
Actually, in_([s[0] for s in sites]) in order to unwrap the tuples from the sites set.

van
  • 74,297
  • 13
  • 168
  • 171
  • Getting the following error: `ProgrammingError: (ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'IN'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")` – Jer_TX Jan 27 '14 at 17:04
  • Could you shre the `SQL` statement which was generated? – van Jan 27 '14 at 17:06
  • Not sure why it's loading a boat load of question marks. `u'SELECT [A_School].cis_site_id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AS anon_1 FROM [A_School]' (14639, 14587, 14966, 14625, 14589, 15144, 15171, 14590, 15134, 14719, 15150, 14704, 15153, 15096, 15133, 14036)` It is returning correct numbers after the A_School section. – Jer_TX Jan 27 '14 at 17:13
  • Question marks is how the parameters are passed to the engine. The way it is done depends on what the driver supports. – van Jan 27 '14 at 18:13
  • Can't seem to get the syntax right here. Any thoughts? Driver is `pyodbc` – Jer_TX Jan 28 '14 at 18:54
  • tried converting to list to see what would happen. Error I keep getting is the same as before, only now `param-type=KeyedTuple` – Jer_TX Jan 28 '14 at 19:19