v0.7.9 text() and multi-row inserts failing - sqlite driver issue?

[Adam Venturella]

Maybe I am just doing something wrong, but I have been fighting with this for a while to no avail.

Here's my code:

query = '''INSERT INTO users_roles (user_id, role_id) VALUES (:user_id, :perm0), (:user_id, :perm1)'''

t = text(query).execution_options(autocommit=True)

self.conn.execute(t, user_id=1, perm0=1, perm1=2)

Here is the log:

2012-11-15 11:22:12,450 INFO sqlalchemy.engine.base.Engine INSERT INTO users_roles (user_id, role_id) VALUES (?, ?), (?, ?)

2012-11-15 11:22:12,451 INFO sqlalchemy.engine.base.Engine (1, 1, 1, 2)

Here is the error:

File "/.../site-packages/sqlalchemy/engine/default.py", line 331, in do_execute

    cursor.execute(statement, parameters)

OperationalError: (OperationalError) near ",": syntax error u'INSERT INTO users_roles (user_id, role_id) VALUES (?, ?), (?, ?)' (1, 1, 1, 2)

If I evaluate this manually, my insert statement is:

INSERT INTO users_roles (user_id, role_id) VALUES (1, 1), (1, 2)

When I run this manually in my sql client (using sqlite for the DB right now) it succeeds. If I run it through sqlalchemy, it get the error outlined. Is this potentially a driver issue or am I just missing something?

Tried it with the stdlib sqlite3 driver as well, it fails there too:

import sqlite3

conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute("INSERT INTO users_roles (user_id, role_id) VALUES (1, 1), (1, 2)")

Table def:

CREATE TABLE "users_roles" (

    id INTEGER NOT NULL, 

    user_id INTEGER NOT NULL, 

    role_id INTEGER NOT NULL,  

    PRIMARY KEY (id), 

)

Anyone else encounter this?

[Michael Bayer]

On Nov 15, 2012, at 2:43 PM, Adam Venturella wrote:

Maybe I am just doing something wrong, but I have been fighting with this for a while to no avail.

Tried it with the stdlib sqlite3 driver as well, it fails there too:

import sqlite3

conn = sqlite3.connect('example.db')

c = conn.cursor()

c.execute("INSERT INTO users_roles (user_id, role_id) VALUES (1, 1), (1, 2)")

Is it possible that your pysqlite/sqlite3 driver is linked to an older version of the sqlite3 libraries ?   check sqlite3.sqlite_version_info for that information, then take a look at http://www.sqlite.org/news.html to see if this is a more recent feature add.

In any case, this is either an out of date sqlite library or a bug in pysqlite/sqlite3.   If a bug in the Python driver, would mean pysqlite is parsing the SQL, though that seems very surprising.  But in any case the issue is not within SQLAlchemy.

If your sqlite libraries are up to date as well as your sqlite3/pysqlite libraries, you'd look into scanning bugs.python.org as well as http://code.google.com/p/pysqlite/issues/list to see if this has been reported and you can report it there, using a pure sqlite3 test case.   Though i have a feeling you have two versions of the sqlite database libraries installed.