Help with join table
Hi, I like sqlbuilder, it's great, but I stumble on this problem ( I am playing around with sql server and pyodbc)
t1 = T.book print QS().tables(t1 + E("select id from myapp where name = '' ")).select("*")
it will give me
select * from "book" LEFT OUTER JOIN (select id from myapp where name = \'\')
so single quotes are "escaped" " where name = \'\'"
Question here is I already have query written by sql administrator, I would like just to cut & paste that code and make a table so I can join it to other ?, I look in test.py but there is no test for that
Comments (17)
-
-
Hi that's the problem, for sql server I need to be able to use single quotes
sqlrepr(QS().tables(t1 + E(""" select id from myapp where name = ' ' """)))
u'select from book LEFT OUTER JOIN (select id from myapp where name = \'\') '
It would be nice if I can do something like this
t2 = T(raw_sql = """ select id from myapp where name = '' """).as_('MYAPP')
so I can "reuse" the sql which I have and use "t2" to join rest of the tables
-
Try subquery:
t2 = QS().tables(...).as_table('alias_name')
-
-
Hi, still "single quotes" are the problem for me, that's what would be good if you can make it if you have something like 'Raw'
Raw(""" select id from myapp where name = ' ' """) and got back "exactly" like this in query
-
Can you give me a copy from console with single quotes?
-
-
All right. String is not escaped. Escaped only representation of string, because string is enclosed in single quotes. You can add "print" before "sqlrepr(...)"
-
Hi, so what is the correct way to send this to pyodbc for executions
q = QS().tables(t1 + tfac).select('*')
cursor = cnxn.cursor() cursor.execute(q.execute()) ????? cursor.execute(query,params)
-
-
Hi, thx for everything I'll try to use it more (or test it for free :) ), but it works ( I am using it with sqlalchemy connection object + pyodbc + sql server 2008), even this works
nfac = T(E('''(select FacilityNumber from Facility where (FacilityNumber IN (1) AND FacilityName != '')) ''')).as_('new_fac')
QS().tables(fac & nfac.on(fac.FacilityNumber == nfac.FacilityNumber)).fields(fac.FacilityName)
very nice!!!!
-
Try TableAlias (TA) instead Table (T). Maybe it will be better.
-
TA('alias_name', E('...'))
-
Hi, that's works too
-
Can change placeholder like this in my script
@sql_dialects.register('mssql', Placeholder) def place_holder(self,hldr): print 'Placeholder return' return '?'
-
sure
-
- changed status to closed
- Log in to comment
It's just python string representation, because string is enclosed in single quotes.
Try print sqlrepr(QS().tables(t1 + E("select id from myapp where name = '' ")).fields('*'))