pythonpostgresqlormponyorm

how to see full query with values in ponyorm


Today i am using the sql_debug(True) that helps me to see the queries but without the values.

How could i see how ponyorm translate the query with values ?

Thank you very much. This is an example of query i'm using.

    with db_session:
    access = select(p for p in Access if raw_sql('( lower(first_name) = lower($first_name) and  lower(last_name) = lower($last_name) ) '
                                                            'or ( lower(first_name) = lower($last_name) and lower(last_name) = lower($first_name) ) '
                                                            'or (lower(facebook_url) = lower($facebook_url)) '
                                                            'or (lower(twitter_url) = lower($twitter_url)) '
                                                            'or (lower(linkedin_url) = lower($linkedin_url)) '))
        .order_by(desc(Access.twitter_url),desc(Access.facebook_url),desc(Access.linkedin_url),
                  desc(Access.facebook_url))
print(access.get_sql())

Solution

  • There is a method called get_sql()

    query_obj = select(c for c in Category if c.name.startswith('v'))
    sql = query_obj.get_sql()
    print(sql)
    

    output:

    SELECT "c"."id", "c"."name"
    FROM "category" "c"
    WHERE "c"."name" LIKE 'v%%'
    

    code continue:

    for obj in query_obj:
        print('id:', obj.id, 'name:', obj.name)
    

    output:

    id: 1 name: viki
    

    here is a link to the docs https://docs.ponyorm.com/api_reference.html#Query.get_sql

    You can log the sql or simply print it.

    Update:

    OP updated the question:

    If the sql query has a variable like $name it is passed as a sql parameter.

    first_name = 'viki'
    query = select(c for c in Category if raw_sql('( lower(name) = lower($first_name))'))
    query.get_sql()
    

    so get_sql() will return the value with a placeholder, and the output will look like this:

     'SELECT "c"."id", "c"."name", "c"."age"\nFROM "Category" "c"\nWHERE ( lower(name) = lower(?))'
    

    If we want no placeholders should be there in the query then we can avoid passing direct sql to query and instead build it separately in python.

    Like this:

    query = select(c for C in Category if c.name == 'viki')
    query.get_sql()
    

    output:

    'SELECT "c"."id", "c"."name", "c"."age"\nFROM "Category" "c"\nWHERE "c"."name" = \'viki\''