<p dir="auto"><strong>Migrated issue, originally created by Alessandro Pelliciari (<a href="https://github.com/apelliciari">@apelliciari</a>)</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SQLAlchemy==1.1.9
SQLAlchemy-Utils==0.32.16"><pre class="notranslate"><code class="notranslate">SQLAlchemy==1.1.9
SQLAlchemy-Utils==0.32.16
</code></pre></div>
<p dir="auto">Working on sqlite, with a query like this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT 
  DATE(creation_date, -strftime('%d', data) || ' days', '+1 day') AS __timestamp
FROM table_xyz"><pre class="notranslate"><code class="notranslate">SELECT 
  DATE(creation_date, -strftime('%d', data) || ' days', '+1 day') AS __timestamp
FROM table_xyz
</code></pre></div>
<p dir="auto">SQLAlchemy when compiling escapes percent sign inside <code class="notranslate">strtfime</code>, making the query wrong and meaningless:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT 
  DATE(creation_date, -strftime('%%d', data) || ' days', '+1 day') AS __timestamp
FROM table_xyz"><pre class="notranslate"><code class="notranslate">SELECT 
  DATE(creation_date, -strftime('%%d', data) || ' days', '+1 day') AS __timestamp
FROM table_xyz
</code></pre></div>
<p dir="auto">notice the double percent inside <code class="notranslate">strftime</code> function.</p>
<p dir="auto">I think the problem is in the <code class="notranslate">sqlite</code> dialect: it doesn't cover this case, but I don't know very well SQLAlchemy so i don't know where to look to patch it.</p>
<p dir="auto">I don't have an easy reproducible case because this issue came up using Superset (<a href="https://github.com/apache/incubator-superset/">https://github.com/apache/incubator-superset/</a>), who's using SQLAlchemy to do the interrogation on the database, so not knowing SQLAlchemy well I can't isolate the case.</p>
<p dir="auto">Relevant Superset code doing the query:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    def get_query_str(self, query_obj):
        engine = self.database.get_sqla_engine()
        qry = self.get_sqla_query(**query_obj)
        sql = str(
            qry.compile(
                engine,
                compile_kwargs={&quot;literal_binds&quot;: True}
            )
        )
        logging.info(sql)
        sql = sqlparse.format(sql, reindent=True)
        return sql
"><pre class="notranslate"><code class="notranslate">    def get_query_str(self, query_obj):
        engine = self.database.get_sqla_engine()
        qry = self.get_sqla_query(**query_obj)
        sql = str(
            qry.compile(
                engine,
                compile_kwargs={"literal_binds": True}
            )
        )
        logging.info(sql)
        sql = sqlparse.format(sql, reindent=True)
        return sql

</code></pre></div>
<p dir="auto">In my case, engine is:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="#!

(Pdb++) pp engine.__dict__
{'_echo': None,
 'dialect': &lt;sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f6f62e74290&gt;,
 'engine': Engine(sqlite:////home/superset/consuntivo.db),
 'logger': &lt;logging.Logger object at 0x7f6f6ad7c350&gt;,
 'pool': &lt;sqlalchemy.pool.NullPool object at 0x7f6f62e74350&gt;,
 'url': sqlite:////home/superset/consuntivo.db}"><pre class="notranslate"><code class="notranslate">#!

(Pdb++) pp engine.__dict__
{'_echo': None,
 'dialect': &lt;sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f6f62e74290&gt;,
 'engine': Engine(sqlite:////home/superset/consuntivo.db),
 'logger': &lt;logging.Logger object at 0x7f6f6ad7c350&gt;,
 'pool': &lt;sqlalchemy.pool.NullPool object at 0x7f6f62e74350&gt;,
 'url': sqlite:////home/superset/consuntivo.db}
</code></pre></div>
<p dir="auto">qry is:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="#!

&lt;sqlalchemy.sql.selectable.Select at 0x7f83d83b9950; Select object&gt;"><pre class="notranslate"><code class="notranslate">#!

&lt;sqlalchemy.sql.selectable.Select at 0x7f83d83b9950; Select object&gt;
</code></pre></div>
<p dir="auto">and raw columns in qry object are ok:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="#!

(Pdb++) pp qry._raw_columns[0].__dict__
{'_allow_label_resolve': True,
 '_element': &lt;sqlalchemy.sql.elements.ColumnClause at 0x7f83d85d7ad0; DATE(data, -strftime('%d', data) || ' days', '+1 day')&gt;,
 '_key_label': u'__timestamp',
 '_label': u'__timestamp',
 '_proxies': [&lt;sqlalchemy.sql.elements.ColumnClause at 0x7f83d85d7ad0; DATE(data, -strftime('%d', data) || ' days', '+1 day')&gt;],
 '_resolve_label': u'__timestamp',
 '_type': DateTime(),
 'comparator': &lt;sqlalchemy.sql.sqltypes.Comparator object at 0x7f83d84e50a0&gt;,
 'element': &lt;sqlalchemy.sql.elements.ColumnClause at 0x7f83d85d7ad0; DATE(data, -strftime('%d', data) || ' days', '+1 day')&gt;,
 'key': u'__timestamp',
 'name': u'__timestamp',
 'type': DateTime()}"><pre class="notranslate"><code class="notranslate">#!

(Pdb++) pp qry._raw_columns[0].__dict__
{'_allow_label_resolve': True,
 '_element': &lt;sqlalchemy.sql.elements.ColumnClause at 0x7f83d85d7ad0; DATE(data, -strftime('%d', data) || ' days', '+1 day')&gt;,
 '_key_label': u'__timestamp',
 '_label': u'__timestamp',
 '_proxies': [&lt;sqlalchemy.sql.elements.ColumnClause at 0x7f83d85d7ad0; DATE(data, -strftime('%d', data) || ' days', '+1 day')&gt;],
 '_resolve_label': u'__timestamp',
 '_type': DateTime(),
 'comparator': &lt;sqlalchemy.sql.sqltypes.Comparator object at 0x7f83d84e50a0&gt;,
 'element': &lt;sqlalchemy.sql.elements.ColumnClause at 0x7f83d85d7ad0; DATE(data, -strftime('%d', data) || ' days', '+1 day')&gt;,
 'key': u'__timestamp',
 'name': u'__timestamp',
 'type': DateTime()}
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">Currently, literal_column() doubles up percent signs as the base compiler.py level, which is inconsistent with the behavior of text() and column(), which both do it at the per-dialect level.  But also, at the per-dialect level, the mysqldb and psycopg2 compilers hardcode this behavior in two separate places for text(), column(), and the modulus operator.</p>
<p dir="auto">doubling of percents should be done based on paramstyle alone and can be done by default in the base compiler.   literal_column() should not unconditionaly double percents as this is legacy behavior.</p>