[Sqlalchemy-tickets] Issue #4114: sqlite: the compiled query escapes percent sign when it shouldn't (zzzeek/sqlalchemy)

[Alessandro P. <iss...@bi...>]

New issue 4114: sqlite: the compiled query escapes percent sign when it shouldn't
https://bitbucket.org/zzzeek/sqlalchemy/issues/4114/sqlite-the-compiled-query-escapes-percent

Alessandro Pelliciari:

```
#!python

SQLAlchemy==1.1.9
SQLAlchemy-Utils==0.32.16
```

Working on sqlite, with a query like this:

```
#!sql

SELECT 
  DATE(creation_date, -strftime('%d', data) || ' days', '+1 day') AS __timestamp
FROM table_xyz
```

SQLAlchemy when compiling escapes percent sign inside `strtfime`, making the query wrong and meaningless:
```
#!sql

SELECT 
  DATE(creation_date, -strftime('%%d', data) || ' days', '+1 day') AS __timestamp
FROM table_xyz
```
notice the double percent inside `strftime` function. 

I think the problem is in the `sqlite` 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.

I don't have an easy reproducible case because this issue came up using Superset (https://github.com/apache/incubator-superset/), who's using SQLAlchemy to do the interrogation on the database, so not knowing SQLAlchemy well I can't isolate the case.

Relevant Superset code doing the query:

```
#!python


    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

```

In my case, engine is:


```
#!

(Pdb++) pp engine.__dict__
{'_echo': None,
 'dialect': <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f6f62e74290>,
 'engine': Engine(sqlite:////home/superset/consuntivo.db),
 'logger': <logging.Logger object at 0x7f6f6ad7c350>,
 'pool': <sqlalchemy.pool.NullPool object at 0x7f6f62e74350>,
 'url': sqlite:////home/superset/consuntivo.db}
```


qry is: 
```
#!

<sqlalchemy.sql.selectable.Select at 0x7f83d83b9950; Select object>
```


and raw columns in qry object are ok:


```
#!

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