OperationalError "unable to open database file" processing query results with SQLAlchemy and SQLite3
- by Peter
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' ()
