On May 10, 2011, at 5:42 AM, Adam Bielański wrote:
> Hi,
> I'm using SQLAlchemy 0.7b4 on Python 2.6 and MySQL 5.1 and MySQLdb 1.2.3 beta
> 2
>
> I tried to import data from SQL file into database, by simply reading file
> line by line and executing SQL commands found.
>
> Program created successfully following table:
>
> create table ATTRIBUTE_TYPE (
> ID INTEGER PRIMARY KEY,
> NAME VARCHAR(250),
> METADATA_GROUP_ID INTEGER,
> CREATED_DATE DATETIME,
> RECORD_LAST_UPDATED_DATE DATETIME
> )
>
> But failed on inserting data into it:
> insert into ATTRIBUTE_TYPE (CREATED_DATE, ID, METADATA_GROUP_ID, NAME,
> RECORD_LAST_UPDATED_DATE) values (str_to_date('20110510T10:23:27',
> '%Y%m%dT%H:%i:%S'), 2, NULL, 'Do not use for concept mapping',
> str_to_date('20110510T10:23:27', '%Y%m%dT%H:%i:%S'))
>
> I tried to execute this statement using Connection.execute() method (module
> sqlalchemy.engine.base) without any parameters. It turned out that while it
> processes params and multiparams, it ends up with empty tuple of parameters,
> which is then passed to BaseCursor.execute() in (MySQLdb.cursors) as the
> 'args' parameter.
> BaseCursor.execute() checks if args is None, and if it's not - it does:
>
> query = query % db.literal(args)
>
> Which fails of course, because query contains % characters and args is empty.
>
> I think that either MySQL dialect should pass None as 'args' instead of an
> empty tuple to be consistent with what BaseCursor expects, or even default
> dialect could behave that way - if only it won't break compatibility with
> other engines.
If you are sending raw SQL on a "format" or "pyformat" DBAPI, you need to
escape % signs as "%%". I think its a bad idea the DBAPI is choosing to make
that behavior conditional based on params present - that's not the usual
behavioral contract of bound parameter names. Changing our side would be
backwards incompatible with the MySQL dialect itself, for those applications
that are already escaping % signs in raw SQL statements with no params.
Using the text() construct will grant you DBAPI-neutral bind behavior, binds
look like ":name" and percent signs in the actual statement are taken care of.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
