SQLAlchemy: print the actual query
This works in python 2 and 3 and is a bit cleaner than before, but requires SA>=1.0.
from sqlalchemy.engine.default import DefaultDialect
from sqlalchemy.sql.sqltypes import String, DateTime, NullType
# python2/3 compatible.
PY3 = str is not bytes
text = str if PY3 else unicode
int_type = int if PY3 else (int, long)
str_type = str if PY3 else (str, unicode)
class StringLiteral(String):
"""Teach SA how to literalize various things."""
def literal_processor(self, dialect):
super_processor = super(StringLiteral, self).literal_processor(dialect)
def process(value):
if isinstance(value, int_type):
return text(value)
if not isinstance(value, str_type):
value = text(value)
result = super_processor(value)
if isinstance(result, bytes):
result = result.decode(dialect.encoding)
return result
return process
class LiteralDialect(DefaultDialect):
colspecs = {
# prevent various encoding explosions
String: StringLiteral,
# teach SA about how to literalize a datetime
DateTime: StringLiteral,
# don't format py2 long integers to NULL
NullType: StringLiteral,
}
def literalquery(statement):
"""NOTE: This is entirely insecure. DO NOT execute the resulting strings."""
import sqlalchemy.orm
if isinstance(statement, sqlalchemy.orm.Query):
statement = statement.statement
return statement.compile(
dialect=LiteralDialect(),
compile_kwargs={'literal_binds': True},
).string
Demo:
# coding: UTF-8
from datetime import datetime
from decimal import Decimal
from literalquery import literalquery
def test():
from sqlalchemy.sql import table, column, select
mytable = table('mytable', column('mycol'))
values = (
5,
u'snowman: ☃',
b'UTF-8 snowman: \xe2\x98\x83',
datetime.now(),
Decimal('3.14159'),
10 ** 20, # a long integer
)
statement = select([mytable]).where(mytable.c.mycol.in_(values)).limit(1)
print(literalquery(statement))
if __name__ == '__main__':
test()
Gives this output: (tested in python 2.7 and 3.4)
SELECT mytable.mycol
FROM mytable
WHERE mytable.mycol IN (5, 'snowman: ☃', 'UTF-8 snowman: ☃',
'2015-06-24 18:09:29.042517', 3.14159, 100000000000000000000)
LIMIT 1
How to print the ACTUAL SQLAlchemy query to troubleshoot: SQLAlchemy filter statement replaces filter critieria with %(column_name_1)s
It is behaving just the way it should. It's just that how you print the query.
from sqlalchemy.dialects import postgresql
query = statement.compile(dialect=postgresql.dialect(),compile_kwargs={"literal_binds": True})
print(query) # will print the compiled query statement againt the dialect.
How do I get a raw, compiled SQL query from a SQLAlchemy expression?
This blog provides an updated answer.
Quoting from the blog post, this is suggested and worked 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 kind of session.query()
.
Thanks to Nicolas Cadou for the answer! I hope it helps others who come searching here.
Sqlalchemy print the output object of a query
You are using __repr__()
function in your department class. So if you fetch data from department class you will only get data of department(as you created).
Here the error is, there is no id in your fetched data it's only department. So that why you get Nonetype
error.
If you want all data from department class, remove __repr__()
function (and __init__()
) from department class or declare every column name in your __repr__()
function (and __init__()
).
Debugging (displaying) SQL command sent to the db by SQLAlchemy
In addition to echo
parameter of create_engine()
there is a more flexible way: configuring logging
to echo engine statements:
import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
See Configuring Logging section of documentation for more information.
Show the SQL generated by Flask-SQLAlchemy
Flask-SQLAlchemy records debugging information about all queries during a request. You can get the information with get_debug_queries()
. Flask-Debugtoolbar, for example, uses this to offer a debug panel with query timing information on each page render.
get_debug_queries()
returns a list of queries in the order they were performed.
>>> from my_app import User
>>> from flask_sqlalchemy import get_debug_queries
>>> User.query.filter_by(display_name='davidism').all()
>>> info = get_debug_queries()[0]
>>> print(info.statement, info.parameters, info.duration, sep='\n')
SELECT "user".id AS user_id, se_user.id AS se_user_id, se_user.display_name AS se_user_display_name, se_user.profile_image AS se_user_profile_image, se_user.profile_link AS se_user_profile_link, se_user.reputation AS se_user_reputation, "user".superuser AS user_superuser \nFROM se_user JOIN "user" ON se_user.id = "user".id \nWHERE se_user.display_name = %(display_name_1)s
{'display_name_1': 'davidism'}
0.0016849040985107422
Queries are recorded if SQLALCHEMY_RECORD_QUERIES
is set to True
. This should be disabled when not needed for performance reasons.
Just calling str(query)
does not show exactly what is sent to the database, as the final render is up to the lower-level database driver. SQLAlchemy has only the parameterized string and unescaped parameters. See the SQLAlchemy docs for a very detailed explanation of why. Usually it's good enough, but it's good to be aware that it's not the final query.
Related Topics
How to Check If Any Value Is Nan in a Pandas Dataframe
How to Style Gtkbox Margin/Padding with CSS Only
R Foverlaps Equivalent in Python
Create Static Graphics Files (Png, Gif, Jpg) Using Ruby or Python
Convert Uiimage from Bgr to Rgb
Differencebetween List and List[:] in Python
Encoding an Image File with Base64
Python Urllib2, Basic Http Authentication, and Tr.Im
Finding What Methods a Python Object Has
How to Use Tailwindcss with Django
Combine a Folder of Text Files into a CSV with Each Content in a Cell
Efficient Ways to Duplicate Array/List in Python
Swift If Or/And Statement Like Python