Sqlalchemy - how to get raw sql from insert(), update() statements with binded params?

Question

Example:

from sqlalchemy.dialects import mysql
from sqlalchemy import Integer, Column, update, insert
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Test(Base):
    __tablename__ = "test"

    a = Column(Integer, primary_key=True)
    b = Column(Integer)


update_stmt = update(Test).where(Test.a == 1).values(b=2)
print update_stmt.compile(dialect=mysql.dialect(), compile_kwargs=  {"literal_binds": True})

insert_stmt = insert(Test).values(a=1, b=1)
print insert_stmt.compile(dialect=mysql.dialect())

Result of this is:

UPDATE test SET b=%s WHERE test.a = %s
INSERT INTO test (a, b) VALUES (%s, %s)

The question is how to make sqlalchemy generate smth like this:

UPDATE test SET b=2 WHERE test.a = 1
INSERT INTO test (a, b) VALUES (1, 1)

For select, compile_kwargs= {"literal_binds": True} solves the issue, but it doesn't work for update, insert.

Thanks for any help.

P.S. I need to build raw sql queries from orm, so any suggestions of any other orms, that have easy way to generate raw sql, are welcome.

Answer 1

The solution using compile_kwargs -- as advertised elsewhere -- works only partially, i.e. for simple data types like Integer or String (as of SQLAlchemy v1.1.6).

+-------------+----------------+--------+---------------+----------+
| SQA version | compile_kwargs | SELECT | INSERT/UPDATE | datetime |
+=============+================+========+===============+==========+
| 0.7.9       |       --       |    --  |        --     |    --    |
+-------------+----------------+--------+---------------+----------+
| 0.9.4       |       √        |   1/2  |        --     |    --    |
+-------------+----------------+--------+---------------+----------+
| 1.0.11      |       √        |    √   |        --     |    --    |
+-------------+----------------+--------+---------------+----------+
| 1.0.13      |       √        |    √   |       1/2     |    --    |
+-------------+----------------+--------+---------------+----------+
| 1.1.6       |       √        |    √   |       1/2     |    --    |
+-------------+----------------+--------+---------------+----------+

Depending on requirements and restrictions, the following solutions will do the job:

• If you can use SQLAlchemy v1.0.13 or above, the simplest solution is provided in this answer. The LiteralDialect class presented there is still needed to provide correct quoting of data types like datetime. Although it is not complete, adding missing data types is very straightforward. And unfortunately you have to fix None to null() for INSERT/UPDATE.

• If you can use SQLAlchemy v1.0.11 and need only SELECT statements (not INSERT/UPDATE), you can also use the solution from the above answer. SQLAlchemy v1.0.11 is distributed with ubuntu 16.04 xenial, the current LTS version (as of Mar 2017).

If you are stuck with some older version of SQLAlchemy, there is no sweet and simple approach to the problem.

So I came up with some code supporting SQL compilation of queries (SELECT) as well as INSERT and UPDATE statements. The code works with SQLAlchemy v0.7.9 - v1.1.6 and Python2/Python3.

A detailed (and hilarious) analysis of the various features is included as doctest.

The relevant code sections are:

• function sql_statement
• class LiteralDialect
• functions fix_null_params, fix_null_values, null_if_none
• variable COMPILE_KWARGS
• and as a bonus table_sql_dump

Answer 2

I just run this snippet on python 2.7 and SQLAlchemy (1.0.13) and it worked.

from sqlalchemy.dialects import mysql
from sqlalchemy import Integer, Column, update, insert
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Test(Base):
    __tablename__ = "test"

    a = Column(Integer, primary_key=True)
    b = Column(Integer)


update_stmt = update(Test).where(Test.a == 1).values(b=2)
print update_stmt.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})

insert_stmt = insert(Test).values(a=1, b=1)
print insert_stmt.compile(dialect=mysql.dialect(), compile_kwargs={"literal_binds": True})

my output is:

UPDATE test SET b=2 WHERE test.a = 1
INSERT INTO test (a, b) VALUES (1, 1)

Maybe something is wrong with your environment?

Answer 3

This isn't as complete of an answer as above. I would recommend the approach linked by wolfmanx and others.

I used this shortcut which fit my use-case. If it fails compiling with literal binds due to some unsupported type like datetime or UUID, then manually converting the values to strings works.

I just used the .compile method which exposed the binds.

try:
    sql = str(query.compile(dialect=postgresql.dialect(), compile_kwargs={'literal_binds': True}))
except NotImplementedError:
    # This has special types which cannot be converted to string automatically.
    compiler = query.compile()
    variables = {key: str(value.value) for key, value in compiler.binds.items() if not key.startswith('%')}
    sql = str(query)
    # Manually replace the variables in the sql statement with their values.
    # This won't perfectly match the original statement, as this converts everything to str and wraps in double-quotes for simplicity.
    for variable, value in variables.items():
        # Variables are prefixed with :
        # Example: "DELETE FROM table WHERE skill.id = :id_1"
        sql = sql.replace(f':{variable}', f'"{value}"')