- changed status to duplicate
literal_column given a specific sql expression not escaping the % wild card
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'
Comments (10)
-
repo owner -
repo owner please use text() for now.
-
reporter 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 :(
-
repo owner 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())
-
reporter 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. -
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 ofliteral_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? -
repo owner 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).
-
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()
-
repo owner 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
#3740which 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. -
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!
- Log in to comment
Duplicate of
#3740.