how to see full query with values in ponyorm

Question

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

Answer 1

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