Generated select statement is executed using executemany which is an SQLError

Using python2.5 and sqlalchemy 0.3.3 I stumbled across the following bug:

bene@discworld:~/bugsqlalchemy$ python2.5 bug.py 
demonstrating bug with ORM
bug exists:
Traceback (most recent call last):
  File "bug.py", line 62, in <module>
    Note.search_by_tags("bla")
  File "bug.py", line 51, in search_by_tags
    from_obj=[notes_table.join(notes_tags_table).join(tags_table)](notes_table.join(notes_tags_table).join(tags_table)))
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 247, in select
    return self.select_whereclause(whereclause=arg, **kwargs)
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 254, in select_whereclause
    return self._select_statement(statement, params=params)
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 380, in _select_statement
    return self.execute(statement, params=params, **kwargs)
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/query.py", line 310, in execute
    result = self.session.execute(self.mapper, clauseelement, params=params)
  File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line 133, in execute
    return self.connection(mapper, close_with_result=True).execute(clause, params, **kwargs)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 255, in execute
    return Connection.executors[type(object).__mro__[-2](type(object).__mro__[-2)](self, object, *multiparams, **params)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 279, in execute_clauseelement
    return self.execute_compiled(elem.compile(engine=self.__engine, parameters=param), *multiparams, **params)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 295, in execute_compiled
    proxy(str(compiled), parameters)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 291, in proxy
    self._execute_raw(statement, parameters, cursor=cursor, context=context)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 327, in _execute_raw
    self._executemany(cursor, statement, parameters, context=context)
  File "build/bdist.linux-i686/egg/sqlalchemy/engine/base.py", line 357, in _executemany
    raise exceptions.SQLError(statement, parameters, e)
SQLError: (ProgrammingError) You cannot execute SELECT statements in executemany(). 'SELECT notes.content AS notes_content, notes.lastchanged AS notes_lastchanged, notes.id AS notes_id \nFROM notes JOIN notes_tags ON notes.id = notes_tags.note_id JOIN tags ON tags.id = notes_tags.tag_id \nWHERE tags.tag = ? ORDER BY notes.oid' [['bla'](['bla')]

Where bug.py contains the following code:

bene@discworld:~/bugsqlalchemy$ cat bug.py 
import sys,traceback
from sqlalchemy import *

engine = create_engine("sqlite://")
session = create_session(bind_to=engine)

meta = DynamicMetaData()

notes_table = Table('notes', meta,
                Column('id', Integer, primary_key = True),
                        Column('lastchanged', DateTime, nullable=False),
                                Column('content', Unicode(), default='', nullable=False)
                                    )


tags_table = Table('tags', meta,
                Column('id', Integer, primary_key = True),
                        Column('tag', String(32), nullable=False),
                            )

notes_tags_table = Table('notes_tags', meta,
                Column('note_id', Integer, ForeignKey("notes.id")),
                        Column('tag_id', Integer, ForeignKey("tags.id"))
                            )

class Tag(object):
    def __init__(self, tag):
        self.tag = tag

tag_mapper = mapper(Tag, tags_table)


class Note(object):
    def __init__(self, content, tags=[       self.content     = content
        if tags != [](]):
): 
            self.tags = tags

    def __setattr__(self, name, value):
        super(Note, self).__setattr__(name, value)
        if name in ['tags']('content',):
            self.lastchanged = datetime.datetime.now()

    @classmethod
    def search_by_tags(cls, tags):
        if isinstance(tags, basestring):
            tags = [for t in tags.split(" ") if t != ""](t)
        q = session.query(cls)
        return q.select(
                tags_table.c.tag.in_(tags),
                from_obj=[notes_table.join(notes_tags_table).join(tags_table)](notes_table.join(notes_tags_table).join(tags_table)))

note_mapper = mapper(Note, notes_table,
        properties = {
            'tags' : relation(Tag, secondary=notes_tags_table, backref='notes')
            })


if __name__ == "__main__":
    try:
        print "demonstrating bug with ORM"
        Note.search_by_tags("bla")
        print "bug did not happen!"
    except:
        print "bug exists:"
        traceback.print_exc(file=sys.stdout)

What I was trying to do was to build a note taking application using pylons where each note can be tagged with any number of tags. The sql code created by sqlalchemy for search_by_tags seems to be correct but evaluating it using sqlitemany appearantly isn't.

Comments (4)