操作错误 "无法打开数据库文件" 在使用SQLAlchemy和SQLite3处理查询结果时
我遇到了一个小问题，希望这只是我自己操作失误。看起来像是对SQLite数据库查询时遇到了一些大小限制。我用一个内存数据库和下面的简单脚本复现了这个问题。我可以通过减少数据库中的记录数量，或者减少每条记录的大小，或者去掉order_by()这个调用来解决这个问题。我在Cygwin环境下使用的是Python 2.5.5和SQLAlchemy 0.6.0。
谢谢！
#!/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()
调用all()来获取查询结果时出现了OperationalError异常：
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' ()
3 个回答
1
把它改成这样：
persons = session.query(Person).order_by(Person.name).count()
print "count =", persons
这样可以吗？如果不行，我怀疑在一个固定字段上排序可能会导致一些内部表出问题。你也可以看看
engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
这对调试可能有帮助。看起来5000行简单的数据不应该有问题。当然，在我的Linux 2.6.32和SQLAlchemy 0.6.0上，使用:memory:或者一个真实的文件都没有问题……抱歉。
2
我试着运行了你的代码，结果是可以正常工作的。不过，我发现SQLAlchemy的Core在需要高性能的情况下更合适，因为使用ORM会有一些额外的开销。我还特别调用了SQLAlchemy的类，以避免加载整个SQLAlchemy库。
下面的代码应该会更简洁，希望能避免你可能遇到的内存问题：
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
2
问题是你的Python脚本找不到SQLite的文件。只要把文件的路径设置正确，这个问题就能解决了。