OperationalError "unable to open database file" processing query results with SQLAlchemy and SQLite3

Question

I'm running into this little problem that I hope is just a dumb user error. It looks like some sort of a size limit with a query to a SQLite database. I managed to reproduce the issue with an in-memory DB and a simple script shown below. I can make it work by either reducing the number of records in the DB; or by reducing the size of each record; or by dropping the order_by() call. I am using Python 2.5.5 and SQLAlchemy 0.6.0 in a Cygwin environment.

Thanks!

#!/usr/bin/python
from sqlalchemy.orm import sessionmaker
import sqlalchemy
import sqlalchemy.orm

class Person(object):
    def __init__(self, name): self.name = name

engine = sqlalchemy.create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
metadata = sqlalchemy.schema.MetaData(bind=engine)
person_table = sqlalchemy.Table('person', metadata,
        sqlalchemy.Column('id', sqlalchemy.types.Integer, primary_key=True),
        sqlalchemy.Column('name', sqlalchemy.types.String))
metadata.create_all(engine)
sqlalchemy.orm.mapper(Person, person_table)

session = Session()
session.add_all([Person("012345678901234567890123456789012")
                 for i in range(5000)])
session.commit()

persons = session.query(Person).order_by(Person.name).all()
print "count =", len(persons)

session.close()

The all() call to the query result fails with the OperationalError exception:

Traceback (most recent call last):
  File "./stress.py", line 27, in <module>
    persons = session.query(Person).order_by(Person.name).all()
  File "/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py", line 1343, in all
    return list(self)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py", line 1451, in __iter__
    return self._execute_and_instances(context)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/orm/query.py", line 1456, in _execute_and_instances
    mapper=self._mapper_zero_or_none())
  File "/usr/lib/python2.5/site-packages/sqlalchemy/orm/session.py", line 737, in execute
    clause, params or {})
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1109, in execute
    return Connection.executors[c](self, object, multiparams, params)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1186, in _execute_clauseelement
    return self.__execute_context(context)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1215, in __execute_context
    context.parameters[0], context=context)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1284, in _cursor_execute
    self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1282, in _cursor_execute
    self.dialect.do_execute(cursor, statement, parameters, context=context)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line 277, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) unable to open database file u'SELECT person.id AS person_id, person.name AS person_name \nFROM person ORDER BY person.name' ()

Answer 1

The problem is that your python script cannot find the SQLite file. Set the correct path to the file and the problem will be solved.

Answer 2

I was able to run your code, so it does work. However, I've found that SQLAlchemy's Core is better suited to performance dependent situations such as this, there is some overhead when using ORM. I'm also specifically calling SQLAlchemy classes to avoid loading the entire SQLAlchemy library.

The following should be more streamlined, hopefully avoiding any memory issues if you have them:

from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, select

engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
person_table = Table('person', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String))
metadata.create_all(engine)

session = engine.connect()

for i in range(5000):
    session.execute(person_table.insert().values(name='012345678901234567890123456789012'))
persons = select([person_table.c.name])
persons = persons.order_by(person_table.c.name.asc())
result = session.execute(persons)
session.close()

count = 0
for i in result: count += 1
print "count =", count

Answer 3

Does changing to:

persons = session.query(Person).order_by(Person.name).count()
print "count =", persons

work? If not, I suspect that the order_by on a constant field is blowing some internal table. You may also find

engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)

useful for debugging. It doesn't seem like 5000 simple rows should cause trouble. Of course, On my Linux 2.6.32 with SQLAlchemy 0.6.0 it works without problem for :memory: or a real file... sorry.