How to get sql query from peewee?

Question

Simple peewee example: MySQL DB "Pet" with autoincrement "id" and char-field "name".

Doing

my_pet = Pet.select().where(name == 'Garfield')

With .sql() we get the sql interpretation.

How to get the raw sql query from:

my_pet = Pet.get(name='Garfield')

?

Tryout

Answer 1

When you write:

my_pet = Pet(name='Garfield')

Nothing at all happens in the database.

You have simply created an object. There is no magic, as peewee is an ActiveRecord ORM, and only saves when you call a method like Model.save() or Model.create().

If you want the SQL for a query like Model.create(), then you should look into using Model.insert() instead:

insert_stmt = Pet.insert(name='Garfield')
sql = insert_stmt.sql()
new_obj_id = insert_stmt.execute()

The downside there is that you aren't returned a model instance, just the primary key.

Exeter

Sorry, was a mistake from me. The line in question is "my_pet = Pet.get(name='Garfield')" Note the ".get". I edited the question – Tryout

.get() does not return a query object, instead it returns a model instance, constructing and then discarding the query. It's roughly the same as Pet.select().where(Pet.name == 'Garfield').limit(1). – Exeter

Answer 2

If you are connecting to a Postgres database, per peewee 3.13 you can print SQL queries by first getting the cursor, then calling mogrify() for your query. Mogrify is provided by the psycopg2 library and hence may not be available when connecting to other databases.

Given your example:

my_pet = Pet.select().where(Pet.name == 'Garfield').limit(1)

cur = database.cursor()
print(cur.mogrify(*my_pet.sql()))

Where database is the Peewee Database object representing the connection to Postgres.

Parotic

As I noted in GitHub - this may only work with psycopg2. The standard library sqlite3 module, for instance, does not support this API. – Exeter

Thanks @Exeter - have updated the answer to reflect its specificity to pg. – Parotic

Answer 3

You can use python's "%" operator to build the string

def peewee_sql_to_str(sql):
    return (sql[0] % tuple(sql[1]))

insert_stmt = Pet.insert(name='Garfield')
sql = insert_stmt.sql()

print(peewee_sql_to_str(sql))

Helprin