diff options
author | Mike Bayer <mike_mp@zzzcomputing.com> | 2021-01-13 09:06:50 -0500 |
---|---|---|
committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2021-01-13 09:07:25 -0500 |
commit | 3ce056f8a5e77d7b73d2ed19a39469a97679940a (patch) | |
tree | 777d7cb3e9594d843c0621989137de2c549286e5 | |
parent | 6314debac70f2a866c708d017886f8f6a65ebd2c (diff) | |
download | sqlalchemy-3ce056f8a5e77d7b73d2ed19a39469a97679940a.tar.gz |
provide FAQ entry for percent sign escaping in SQL compiler
this is coming up more than once so provide a document
Change-Id: I23dcd4c7a6527b2f33502e67ffad4335f895e6f9
(cherry picked from commit 0a41f9bea6602c52c59af0f7b572308b2c2b27ab)
-rw-r--r-- | doc/build/faq/sqlexpressions.rst | 55 |
1 files changed, 55 insertions, 0 deletions
diff --git a/doc/build/faq/sqlexpressions.rst b/doc/build/faq/sqlexpressions.rst index 4d2f0774c..22ef0fc27 100644 --- a/doc/build/faq/sqlexpressions.rst +++ b/doc/build/faq/sqlexpressions.rst @@ -136,6 +136,61 @@ producing output like:: WHERE mytable.x > my_fancy_formatting(5) +.. _faq_sql_expression_percent_signs: + +Why are percent signs being doubled up when stringifying SQL statements? +------------------------------------------------------------------------ + +Many :term:`DBAPI` implementations make use of the ``pyformat`` or ``format`` +`paramstyle <https://www.python.org/dev/peps/pep-0249/#paramstyle>`_, which +necessarily involve percent signs in their syntax. Most DBAPIs that do this +expect percent signs used for other reasons to be doubled up (i.e. escaped) in +the string form of the statements used, e.g.:: + + SELECT a, b FROM some_table WHERE a = %s AND c = %s AND num %% modulus = 0 + +When SQL statements are passed to the underlying DBAPI by SQLAlchemy, +substitution of bound parameters works in the same way as the Python string +interpolation operator ``%``, and in many cases the DBAPI actually uses this +operator directly. Above, the substitution of bound parameters would then look +like:: + + SELECT a, b FROM some_table WHERE a = 5 AND c = 10 AND num % modulus = 0 + +The default compilers for databases like PostgreSQL (default DBAPI is psycopg2) +and MySQL (default DBAPI is mysqlclient) will have this percent sign +escaping behavior:: + + >>> from sqlalchemy import table, column + >>> from sqlalchemy.dialects import postgresql + >>> t = table("my_table", column("value % one"), column("value % two")) + >>> print(t.select().compile(dialect=postgresql.dialect())) + SELECT my_table."value %% one", my_table."value %% two" + FROM my_table + +When such a dialect is being used, if non-DBAPI statements are desired that +don't include bound parameter symbols, one quick way to remove the percent +signs is to simply substitute in an empty set of parameters using Python's +``%`` operator directly:: + + >>> strstmt = str(t.select().compile(dialect=postgresql.dialect())) + >>> print(strstmt % ()) + SELECT my_table."value % one", my_table."value % two" + FROM my_table + +The other is to set a different parameter style on the dialect being used; all +:class:`.Dialect` implementations accept a parameter +``paramstyle`` which will cause the compiler for that +dialect to use the given parameter style. Below, the very common ``named`` +parameter style is set within the dialect used for the compilation so that +percent signs are no longer significant in the compiled form of SQL, and will +no longer be escaped:: + + >>> print(t.select().compile(dialect=postgresql.dialect(paramstyle="named"))) + SELECT my_table."value % one", my_table."value % two" + FROM my_table + + .. _faq_sql_expression_op_parenthesis: I'm using op() to generate a custom operator and my parenthesis are not coming out correctly |