support rendering of dates/datetimes for sqlalchemy literal quoting

Issue #57 closed
J-F Boquillard
created an issue

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

Comments (16)

  1. Michael Bayer repo owner

    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):

    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.

  2. Jonathan Herriott

    I'm having a similar issue where I'm trying to do a bulk insert for a table I declared as:

    test = table('test',
                       column('id', sa.Integer),
                       column('date', sa.Date))
    
    op.bulk_insert(test, [
                           {'id': 1,
                            'date': op.inline_literal('2014-01-01'),
                            }
    ])
    

    Which gives me the following exception:

    File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py", line 448, in call return fn(element, compiler, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 248, in _render_literal_bindparam return compiler.render_literal_bindparam(element, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1009, in render_literal_bindparam return self.render_literal_value(value, bindparam.type) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1443, in render_literal_value value = super(MySQLCompiler, self).render_literal_value(value, type_) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1027, in render_literal_value "Don't know how to literal-quote value %r" % value) NotImplementedError: Don't know how to literal-quote value BindParameter('%(57254288 param)s', '2014-01-01', type_=String())

    I've tried quite a few things, and I can't seem to get it to succeed at all.

  3. Jonathan Herriott

    Yes, sorry, here's the dump with literal_column() used in place of inline_literal()

    File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 175, in bulk_insert for k, v in row.items() File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 70, in exec construct.compile(dialect=self.dialect) File "<string>", line 1, in <lambda> File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 468, in compile return self._compiler(dialect, bind=bind, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 474, in _compiler return dialect.statement_compiler(dialect, self, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 391, in init Compiled.init(self, dialect, statement, kwargs) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 197, in init self.string = self.process(self.statement, compile_kwargs) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 220, in process return obj._compiler_dispatch(self, kwargs) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 79, in _compiler_dispatch return meth(self, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1641, in visit_insert colparams = self._get_colparams(insert_stmt, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 2052, in _get_colparams value = self.process(value.self_group(), kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 220, in process return obj._compiler_dispatch(self, kwargs) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py", line 410, in <lambda> lambda *arg, kw: existing(arg, kw)) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/ext/compiler.py", line 448, in call return fn(element, compiler, kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 248, in _render_literal_bindparam return compiler.render_literal_bindparam(element, *kw) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1009, in render_literal_bindparam return self.render_literal_value(value, bindparam.type) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1443, in render_literal_value value = super(MySQLCompiler, self).render_literal_value(value, type) File "/home/vagrant/.virtualenvs/paas_v1/local/lib/python2.7/site-packages/sqlalchemy/sql/compiler.py", line 1027, in render_literal_value "Don't know how to literal-quote value %r" % value) NotImplementedError: Don't know how to literal-quote value <sqlalchemy.sql.elements.ColumnClause at 0x399eb10; 2014-01-01>

  4. Michael Bayer repo owner

    OK well this is the mechanics of bulk_insert() and it would need to be enhanced to support the acceptance of non-parameter elements, which is tricky because in non-SQL mode, everything needs to be sent as a bind since we are using executemany(). one moment please

  5. Michael Bayer repo owner

    inline_literal() should be accepted directly here and that is fixed in #179. Your bulk_insert() with inline_literal() will work as-is in --sql mode. In "online" mode, you now have to sent bulk_insert() a clue that it needs to invoke individual INSERT statements by passing the new flag multiinsert:

        self.op.bulk_insert(t1, [
            {'id': 1, 'data': self.op.inline_literal('d1')},
            {'id': 2, 'data': self.op.inline_literal('d2')},
        ], multiinsert=False)
    
  6. Thomas Farvour

    I'm using the clause compiler in my mock DB session object, but unable to use dates as quoting them becomes ambiguous for the MagicMock clause method hash.

    E.g.:

    def _hash(self, arg):
        """ Calculate hash of clause methods and their arguments.
    
        :param arg: Argument passed into clause method.
        :type arg: object
    
        :returns: A string of the hash, otherwise just the argument itself.
        :rtype: str
        """
        if isinstance(arg, ClauseElement):
            expr = str(arg.compile(compile_kwargs={"literal_binds": True}))
            # print(expr, flush=True)  # Use this to see what expressions are getting compiled.
            return expr
    
        elif isinstance(arg, MapperOption):
            if hasattr(arg, 'cache_key') and hasattr(arg, 'region'):
                expr = str(hash(hash(arg.cache_key) + hash(arg.region)))
    
                return expr
    
            else:
                assert hash(arg)
    
                return arg
    
        else:
            assert hash(arg)
    
            return arg
    

    When compiling a clause with a datetime.date, I get the exception: NotImplementedError: Don't know how to literal-quote value datetime.date(2014, 7, 25)

    BTW, this is the example you helped me out with on StackOverflow.:

    http://stackoverflow.com/questions/21615054/whats-the-correct-way-to-unit-test-pyramid-views-with-a-sqlalchemy-dbsession

  7. Thomas Farvour

    Mike, sorry I noticed I used the wrong forum for this. See it is for alembic.. but it's all SQLAlchemy ;)

    I did work around it after some playing around. Finally came up with extending the TypeDecorator for Date, giving it a literal processor, then checking if the val on the right of a BinaryExpression is Date, and inject the other instance. It's kind of a monkey patch but it works, since I'm only using it for unit testing. Obviously I'd need to do this for any expression that could potentially have a date....

    class _LiteralDate(sqlalchemy.sql.sqltypes.Date):
        def literal_processor(self, dialect):
            def process(value):
                return str(value)
    
            return process
    

    ... then later ...

            if isinstance(arg, ClauseElement):
                if isinstance(arg, BinaryExpression) and isinstance(arg.right.type, sqlalchemy.sql.sqltypes.Date):
                    arg.right.type = _LiteralDate()
    
                expr = str(arg.compile(compile_kwargs={"literal_binds": True}))
    
                # print(expr, flush=True)  # Use this to see what expressions are getting compiled.
                return expr
    
  8. Log in to comment