Sqlalchemy: print the actual query

The essentials of query investigation

In the grand scheme of data manipulation, realizing the SQL string SQLAlchemy crafts for you is akin to discovering the philosopher's stone of your database's operations. Let's claim this treasure with precision and astuteness.

Real-time SQL display for Flask applications

If you're connected with Flask, configure the app to showcase queries just by:

app.config["SQLALCHEMY_ECHO"] = True  # Whisper me those sweet SQL queries, dear Flask


Once set, your Flask app will obligingly log each SQL statement.

Echo, Echo, Echo...

Adjust the echo attribute of an existing engine:

engine = create_engine('postgresql://scott:tiger@localhost/', echo=True)  # Shout out loud SQLAlchemy!
engine.echo = not engine.echo  # Let's make it quiet for now....


Voila! You have an on-demand SQL whisperer.

Becoming literal with StringLiteral

For custom control over literals:

from sqlalchemy.sql.expression import StringLiteral
query = select([StringLiteral('some_value').label('column_name')])  # SQLAlchemy interpret the literal "some_value" as column_name


Full query episodes with literalquery

Craft whole SQL queries using literalquery:

from sqlalchemy.sql import literal_column
from sqlalchemy.orm import Query

q = Query([literal_column("'text'"), literal_column("1")])  # Literal and digit bonding over a Query
print(q)  # Netflix and reveal? Nah, I prefer "literalquery and reveal"


Take a deeper dive into query analysis

Having lit the pathway to SQL transparency, let's delve deeper into understanding SQLAlchemy's magical SQL generation process.

Literals 101: Introducing StringLiteral

Teach SQLAlchemy to generate your preferred literal format:

StringLiteral('text').compile()  # Literally speaking, this could turn heads! ;)


Fashionable debugging with LiteralDialect

LiteralDialect helps to inspect the SQL generated behind the scenes:

from sqlalchemy.sql import compiler

dialect = compiler.dialect()
compiled_query = query.statement.compile(dialect=dialect)
print(str(compiled_query))  # How charmingly literal!


Debug output makeover with prettyprintable approach

from prettyprinter import pprint

pprint(query)  # Because your queries deserve the red carpet, too!


Paranormal Troubleshooting with query logging

import logging

logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)  # I ain't afraid of no log! 


The beauty of sqlparse

import sqlparse

formatted_query = sqlparse.format(str(query), reindent=True, keyword_case='upper')
print(formatted_query)  # On a mission to make SQL great (to read) again!
