support rendering of dates/datetimes for sqlalchemy literal quoting

Question

Migrated issue, originally created by J-F Boquillard

title: "support rendering of dates/datetimes for sqlalchemy literal quoting"

When "bulk inserting" rows containing a Date field into a MySQL database, the "upgrade --sql" fails with the following error (only the last lines) :

  File "/usr/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 238, in _render_literal_bindparam
    return compiler.render_literal_bindparam(element, **kw)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 690, in render_literal_bindparam
    return self.render_literal_value(value, bindparam.type)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1303, in render_literal_value
    value = super(MySQLCompiler, self).render_literal_value(value, type_)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 713, in render_literal_value
    "Don't know how to literal-quote value %r" % value)
NotImplementedError: Don't know how to literal-quote value datetime.date(2012, 1, 1)

Upgrading the database (without --sql) works fine.

My enviromnent : * Python 2.7.1 * SQLAlchemy 0.7.8 * alembic 0.3.4

该提问来源于开源项目：sqlalchemy/alembic

Answer 1

Michael Bayer () wrote:

there's a wide range of types that fall under this category, where we would be tasked implementing string formatting for every possible type of value. while dates are very common and are on the borderline here, I can't see myself getting involved with formatting every kind of value, like PG INTERVAL, arrays, BLOBs, and everything else, into raw SQL. The reason this works for you without --sql is because the DBAPI handles it. SQLAlchemy really does not want to get into duplicating all that behavior, and that's why that NotImplementedError you're getting is quite intentional.

So while I can maybe bend on dates here, for now you need to format those dates yourself in the way MySQL wants, using literal_column (note I'm not certain this is the exact format):

#!python

literal_column(mydate.strftime("%Y-%m-%d"))

also the fix here is in SQLAlchemy, but I'll leave it over here as it's an alembic-only use case.