1. Ivan Zakrevsky
  2. sqlbuilder
Issue #1 closed

Help with join table

Anonymous created an issue

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)

  1. Ivan Zakrevsky repo owner

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

  2. Stanko Petrovic

    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

  3. Stanko Petrovic

    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

  4. Ivan Zakrevsky repo owner

    All right. String is not escaped. Escaped only representation of string, because string is enclosed in single quotes. You can add "print" before "sqlrepr(...)"

  5. Stanko Petrovic

    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)

  6. Stanko Petrovic

    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!!!!

  7. Log in to comment