literal_column given a specific sql expression not escaping the % wild card

[Nagaraj Tantri]

Hi,

I had a scenario where I would treat a custom column as unix date time and convert it to the required output via:

literal_column("date_format(from_unixtime(ts), '%Y-%m-%d'"))

But that is getting converted to:

WHERE date_format(from_unixtime(ts), '%%Y-%%m-%%d') 
>= '1916-07-04 15:10:18.000000'

I tried looking into other examples to escape the same and also tried to use bindparam but was in no success.

Could anyone point me the actual solution to get it as:

WHERE date_format(from_unixtime(ts), '%Y-%m-%d') 
>= '1916-07-04 15:10:18.000000'

[Mike Bayer]

Duplicate of #3740.

[Mike Bayer]

please use text() for now.

[Nagaraj Tantri]

Using text() does not solve all the problems, because the usage was basically literal_column("date_format(from_unixtime(ts), '%Y-%m-%d'")).label('timestamp').

To use text will not solve the labeling a column scenario :(

[Mike Bayer]

you can override how literal_column works, see below. This is MySQL though what driver are you using that the percent signs don't need to be doubled?

from sqlalchemy.sql.expression import ColumnClause
from sqlalchemy.ext.compiler import compiles


@compiles(ColumnClause, 'mysql')
def _remove_percents(element, compiler, **kw):
    text = compiler.visit_column(element, **kw)
    if element.is_literal:
        text = text.replace('%%', '%')
    return text


from sqlalchemy import literal_column
expr = literal_column("date_format(from_unixtime(ts), '%Y-%m-%d')").label('timestamp')

from sqlalchemy.dialects import mysql
print expr.compile(dialect=mysql.dialect())

[Nagaraj Tantri]

Since, there was no dialect (common for all), when I used it with @compiles(ColumnClause) it worked. Thanks for the help.

I was thinking, where ever I have the literal_column(... I will include the custom _remove_percents method.

[Brecht Machiels]

I'm having a similar issue. I'm creating a view in SQLite with the help of the example code at the wiki. In the SELECT statement for one of my views, I'm calling the strftime function with as arguments a column name and the string '%s'. The query will execute correctly, but SQLite cannot create a VIEW from it, complaining "parameters are not allowed in views". This is because the '%s' string is being passed in as parameter, which SQLite does not support. In another VIEW query, I was able to get around this by making use of literal_column, but this won't work for '%s' since the % character is doubled, breaking the query.

I'm not sure I understand this correctly, but could it be that escaping of the % should be skipped only in this and similar situations?

[Mike Bayer]

please send example code which reproduces the issue to the mailing list, and also specify SQLAlchemy version. also note http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#percent-signs-in-literal-column-now-conditionally-escaped and please specify if your issue is local to the new 1.2 behavior or the previous (or isnt specific).

[Brecht Machiels]

Here's a minimal test case for the problem described above.

from datetime import date

from sqlalchemy import create_engine, Table, MetaData, Column, sql, func, literal_column
from sqlalchemy import String, Date
from sqlalchemy.schema import DDLElement
from sqlalchemy.ext import compiler


# https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/Views

from sqlalchemy import *
from sqlalchemy.schema import DDLElement
from sqlalchemy.sql import table
from sqlalchemy.ext import compiler

class CreateView(DDLElement):
    def __init__(self, name, selectable):
        self.name = name
        self.selectable = selectable

class DropView(DDLElement):
    def __init__(self, name):
        self.name = name

@compiler.compiles(CreateView)
def compile(element, compiler, **kw):
    return "CREATE VIEW %s AS %s" % (element.name, compiler.sql_compiler.process(element.selectable))

@compiler.compiles(DropView)
def compile(element, compiler, **kw):
    return "DROP VIEW %s" % (element.name)

def view(name, metadata, selectable):
    t = table(name)

    for c in selectable.c:
        c._make_proxy(t)

    CreateView(name, selectable).execute_at('after-create', metadata)
    DropView(name).execute_at('before-drop', metadata)
    return t


# test code

engine = create_engine('sqlite:///:memory:')
meta = MetaData(bind=engine)

user = Table('users', meta,
              Column('name', String),
              Column('birthday', Date))
meta.create_all()

engine.execute(user.insert().values(name='John',
                                    birthday=date(1982, 10, 1)))

# this query will execute correctly, but the view cannot be created
view_query = sql.select([func.strftime('%s', user.c.birthday)
                         .label('unix_birthday')])

# this query will break the query
view_query = sql.select([func.strftime(literal_column('%s'), user.c.birthday)
                         .label('unix_birthday')])

for row in engine.execute(view_query):
    print(row)


user_view = view('users_view', meta, view_query)
meta.create_all()

[Mike Bayer]

you are missing quotes around your literal:

view_query = sql.select([func.strftime(literal_column("'%s'"), user.c.birthday)
                     .label('unix_birthday')])

with correct quoting the statement passes.

Under 1.1 and earlier, you will still get "%%s" in this case due to #3740 which is still not what you want. in 1.2, http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#percent-signs-in-literal-column-now-conditionally-escaped you will get the exact "%s" given.

[Brecht Machiels]

Thanks Michael. I missed your earlier reply.

I did have the extra quotes in my application code. I'm running SQLAlchemy 1.1.11. Great to hear that 1.2 will fix the issue!