How to get the original, compiled SQL query from a SQLAlchemy expression?

Question

I have a SQLAlchemy query object and you want to get the text of a compiled SQL statement with all its parameters (for example, no %s or other variables waiting to be bound by the statement compiler or the MySQLdb dialog engine, etc.) ..

Calling str() on request shows something like this:

 SELECT id WHERE date_added <= %s AND date_added >= %s ORDER BY count DESC 

I tried searching in query._params, but this is an empty dict. I wrote my own compiler using this sqlalchemy.ext.compiler.compiles decorator sqlalchemy.ext.compiler.compiles , but even the statement still has %s where I need the data.

I cannot understand when my parameters are mixed to create a query; when examining the query object, they are always an empty dictionary (although the query is executed perfectly and the engine displays it when the echo log is turned on).

I am starting to receive a message that SQLAlchemy does not want me to know the basic query, because it violates the general character of the API expression of all different DB-APIs. I do not mind if the request is executed before I find out what it is; I just want to know!

Answer 1

This blog contains an updated answer.

Quote from a blog post, this is suggested and works for me.

 >>> from sqlalchemy.dialects import postgresql >>> print str(q.statement.compile(dialect=postgresql.dialect())) 

Where q is defined as:

 >>> q = DBSession.query(model.Name).distinct(model.Name.value) \ .order_by(model.Name.value) 

Or just any type of session.query ().

Thanks to Nicholas Cad for the answer! I hope this helps others who come looking here.

Answer 2

the documentation uses literal_binds to print a q query, including parameters:

 print(q.statement.compile(compile_kwargs={"literal_binds": True})) 

the above approach has warnings that it is only supported for basic types, such as integer and string types, and in addition, if bindparam () without a predefined value is used directly, it will also not be able to convert it to a string.

The documentation also throws this warning:

Never use this technique with string content obtained from untrusted input, such as from web forms or other user input applications. The SQLAlchemys tools for converting Python values ​​to a direct SQL string do not protect against untrusted input and do not check the type of data being transferred. Always use related parameters when programmatically invoking non-DDL SQL statements against a relational database.

Answer 3

This should work with Sqlalchemy> = 0.6

 from sqlalchemy.sql import compiler from psycopg2.extensions import adapt as sqlescape # or use the appropiate escape function from your db driver def compile_query(query): dialect = query.session.bind.dialect statement = query.statement comp = compiler.SQLCompiler(dialect, statement) comp.compile() enc = dialect.encoding params = {} for k,v in comp.params.iteritems(): if isinstance(v, unicode): v = v.encode(enc) params[k] = sqlescape(v) return (comp.string.encode(enc) % params).decode(enc) 

Answer 4

For the MySQLdb backend, I slightly changed the excellent answer for alberts (thanks a lot!). I am sure that they can be combined to check if comp.positional True , but that is a bit beyond the scope of this question.

 def compile_query(query): from sqlalchemy.sql import compiler from MySQLdb.converters import conversions, escape dialect = query.session.bind.dialect statement = query.statement comp = compiler.SQLCompiler(dialect, statement) comp.compile() enc = dialect.encoding params = [] for k in comp.positiontup: v = comp.params[k] if isinstance(v, unicode): v = v.encode(enc) params.append( escape(v, conversions) ) return (comp.string.encode(enc) % tuple(params)).decode(enc) 

Answer 5

The fact is that sqlalchemy never mixes data with your query. The request and data are transferred individually to your base database driver - data interpolation occurs in your database.

Sqlalchemy passes the query, as you saw in str(myquery) to the database, and str(myquery) values ​​in a separate tuple.

You could use some approach where you interpolate the data with the query yourself (as the Alberta suggested below), but this is not the same as sqlalchemy does.

Answer 6

For a postgresql backend using psycopg2, you can listen to the do_execute event, and then use the cursor, operator, and type parameters with a forced parameter along with Cursor.mogrify() to embed the parameters. You can return True to prevent the request from actually executing.

 import sqlalchemy class QueryDebugger(object): def __init__(self, engine, query): with engine.connect() as connection: try: sqlalchemy.event.listen(engine, "do_execute", self.receive_do_execute) connection.execute(query) finally: sqlalchemy.event.remove(engine, "do_execute", self.receive_do_execute) def receive_do_execute(self, cursor, statement, parameters, context): self.statement = statement self.parameters = parameters self.query = cursor.mogrify(statement, parameters) # Don't actually execute return True 

Sample Usage:

 >>> engine = sqlalchemy.create_engine("postgresql://postgres@localhost/test") >>> metadata = sqlalchemy.MetaData() >>> users = sqlalchemy.Table('users', metadata, sqlalchemy.Column("_id", sqlalchemy.String, primary_key=True), sqlalchemy.Column("document", sqlalchemy.dialects.postgresql.JSONB)) >>> s = sqlalchemy.select([users.c.document.label("foobar")]).where(users.c.document.contains({"profile": {"iid": "something"}})) >>> q = QueryDebugger(engine, s) >>> q.query 'SELECT users.document AS foobar \nFROM users \nWHERE users.document @> \'{"profile": {"iid": "something"}}\'' >>> q.statement 'SELECT users.document AS foobar \nFROM users \nWHERE users.document @> %(document_1)s' >>> q.parameters {'document_1': '{"profile": {"iid": "something"}}'} 

Answer 7

Let me begin with a preface by saying that I assume that you are doing this mainly for debugging purposes - I would not recommend trying to modify the statement outside of the freely used SQLAlchemy API.

Unfortunately, there is no easy way to show a compiled statement with query parameters enabled. SQLAlchemy does not actually put the parameters in the statement - they are passed to the database engine as a dictionary . This allows the database-specific library to handle things such as escaping special characters to avoid SQL injection.

But you can do it in two steps quite easily. To get approval, you can do, as you have already shown, and simply print the request:

 >>> print(query) SELECT field_1, field_2 FROM table WHERE id=%s; 

You can get one step closer with query.statement to see parameter names. (Note :id_1 lower compared to %s above - this is actually not a problem in this very simple example, but may be key in a more complex statement.)

 >>> print(query.statement) >>> print(query.statement.compile()) # reasonably equivalent, you can also # pass in a dialect if you want SELECT field_1, field_2 FROM table WHERE id=:id_1; 

Then you can get the actual parameter values ​​by getting the params property of the compiled statement:

 >>> print(query.statement.compile().params) {u'id_1': 1} 

This worked, at least for the MySQL backend; I expect it to be fairly generic for PostgreSQL as well, without the need to use psycopg2 .

Answer 8

The following solution uses the SQLAlchemy expression language and works with SQLAlchemy 1.1. This solution does not mix parameters with the query (as requested by the original author), but provides a way to use SQLAlchemy models to generate SQL query strings and parameter dictionaries for different SQL dialects. The example is based on the tutorial http://docs.sqlalchemy.org/en/rel_1_0/core/tutorial.html

Given the class,

 from sqlalchemy import Column, Integer, String from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class foo(Base): __tablename__ = 'foo' id = Column(Integer(), primary_key=True) name = Column(String(80), unique=True) value = Column(Integer()) 

we can create a query using the select function.

 from sqlalchemy.sql import select statement = select([foo.name, foo.value]).where(foo.value > 0) 

Next, we can compile the statement into a request object.

 query = statement.compile() 

By default, the statement is compiled using the base implementation of named, which is compatible with SQL databases such as SQLite and Oracle. If you need to specify a dialect such as PostgreSQL, you can do

 from sqlalchemy.dialects import postgresql query = statement.compile(dialect=postgresql.dialect()) 

Or, if you want to explicitly specify the dialect as SQLite, you can change paramstyle from "qmark" to "named".

 from sqlalchemy.dialects import sqlite query = statement.compile(dialect=sqlite.dialect(paramstyle="named")) 

From the query object we can extract the query string and query parameters

 query_str = str(query) query_params = query.params 

and finally complete the request.

 conn.execute( query_str, query_params ) 

Answer 9

You can use events from ConnectionEvents family: after_cursor_execute or before_cursor_execute .

In sqlalchemy UsageRecipes from @zzzeek you can find this example:

 Profiling ... @event.listens_for(Engine, "before_cursor_execute") def before_cursor_execute(conn, cursor, statement, parameters, context, executemany): conn.info.setdefault('query_start_time', []).append(time.time()) logger.debug("Start Query: %s" % statement % parameters) ... 

Here you can access your application.

Answer 10

I think this might do the trick: http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query

 >>> local_session.query(sqlalchemy_declarative.SomeTable.text).statement <sqlalchemy.sql.annotation.AnnotatedSelect at 0x6c75a20; AnnotatedSelectobject> >>> x=local_session.query(sqlalchemy_declarative.SomeTable.text).statement >>> print(x) SELECT sometable.text FROM sometable