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

?

score 8 · Accepted Answer · answered Sep 24 '15 at 02:52

8

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.

answered Sep 24 '15 at 02:52

coleifer

24,887
6
60
75

Sorry, was a mistake from me. The line in question is "my_pet = Pet.get(name='Garfield')" Note the ".get". I edited the question – Stefan Weiss Sep 24 '15 at 12:00
`.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)`. – coleifer Sep 26 '15 at 13:27

Mart Van de Ven · Answer 2 · 2020-02-09T19:08:44.343

1

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.

edited Feb 09 '20 at 19:08

answered Feb 09 '20 at 16:35

Mart Van de Ven

378
2
8

1

As I noted in GitHub - this may only work with psycopg2. The standard library sqlite3 module, for instance, does not support this API. – coleifer Feb 09 '20 at 17:42
Thanks @coleifer - have updated the answer to reflect its specificity to pg. – Mart Van de Ven Feb 09 '20 at 19:16

score 0 · Answer 3 · answered Apr 28 '21 at 19:33

0

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

answered Apr 28 '21 at 19:33

Luiz Henrique Freire Barros

11

How to get sql query from peewee?

3 Answers3