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

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('*'))

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')

See test for example: https://bitbucket.org/emacsway/sqlbuilder/src/044b937d2c5843e5f313f8478e635ea0743d171e/sqlbuilder/smartsql/tests.py?at=default#cl-182

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)

See example in Autumn ORM https://bitbucket.org/emacsway/autumn/src/dc0f0f0cd25d1f63becb423d98efc96bd0d98d88/autumn/models.py?at=master#cl-480

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