Can't connect to SQLite databases

[João Silva]

Hello everyone,

I'm having a problem connecting to SQLite database files with
SQLAlchemy. :memory: databases work just fine, but whenever I'm trying
to access a file, the following happens:

Python 2.7.2 (default, Jun 12 2011, 14:24:46) [MSC v.1500 64 bit
(AMD64)] on win
32
Type "help", "copyright", "credits" or "license" for more information.
>>> from sqlalchemy import create_engine
>>> import os.path
>>> os.path.exists('test.db')
False
>>> engine = create_engine('sqlite:///test.db')
>>> engine.connect
<bound method Engine.connect of Engine(sqlite:///test.db)>
>>> engine.connect()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\base.py", line 2310, in connect
return self._connection_cls(self, **kwargs)
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\base.py", line 872, in __init__
self.__connection = connection or engine.raw_connection()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\base.py", line 2396, in raw_connection
return self.pool.unique_connection()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 169, in unique_connection
return _ConnectionFairy(self).checkout()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 370, in __init__
rec = self._connection_record = pool._do_get()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 757, in _do_get
return self._create_connection()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 174, in _create_connection
return _ConnectionRecord(self)
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 255, in __init__
self.connection = self.__connect()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 315, in __connect
connection = self.__pool._creator()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\strategies.py", line 80, in connect
return dialect.connect(*cargs, **cparams)
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\default.py", line 275, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (OperationalError) unable to open
database file
None None

The same happens after having created the database using the sqlite3
module and then trying to connect to it via SQLAlchemy.

>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> conn.execute('create table bla(id int primary key);')
<sqlite3.Cursor object at 0x0000000002FA62D0>
>>> conn.close()
>>> os.path.exists('test.db')
True
>>> engine = create_engine('sqlite:///test.db')
>>> engine.connect()
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\base.py", line 2310, in connect
return self._connection_cls(self, **kwargs)
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\base.py", line 872, in __init__
self.__connection = connection or engine.raw_connection()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\base.py", line 2396, in raw_connection
return self.pool.unique_connection()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 169, in unique_connection
return _ConnectionFairy(self).checkout()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 370, in __init__
rec = self._connection_record = pool._do_get()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 757, in _do_get
return self._create_connection()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 174, in _create_connection
return _ConnectionRecord(self)
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 255, in __init__
self.connection = self.__connect()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\pool
.py", line 315, in __connect
connection = self.__pool._creator()
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\strategies.py", line 80, in connect
return dialect.connect(*cargs, **cparams)
File "C:\Python27\lib\site-packages\sqlalchemy-0.7.2-py2.7.egg\sqlalchemy\engi
ne\default.py", line 275, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.OperationalError: (OperationalError) unable to open
database file
None None

So have you got any idea on what might be problem here?

--
João Silva

[Michael Bayer]

strange. what happens if you examine the "test.db" file more closely, is it created with odd permissions (like not readable or something strange ?) is there a filesystem problem, can you reproduce this issue on any workstation /environment ? the test case obviously works fine for me. The OperationalError you see, while branded by SQLAlchemy, is ultimately derived from the pysqlite error object so that's their operational error, not ours.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[jmsilva]

On Oct 22, 2:18 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> strange.  what happens if you examine the "test.db" file more closely, is it created with odd permissions (like not readable or something strange ?)   is there a filesystem problem, can you reproduce this issue on any workstation /environment ?  the test case obviously works fine for me.   The OperationalError you see, while branded by SQLAlchemy, is ultimately derived from the pysqlite error object so that's their operational error, not ours.
>

I don't think it's a permission problem, since I'm on Windows and,
again, creating and reading the file from the sqlite3 module works
fine. I've also checked the permissions, and everything was as it
should be. Nevertheless, I've tested it on the Ubuntu VM I had lying
around and indeed it works. So the question becomes: is there some
sort of installation problem that can cause this? If not, and I try to
debug the library, what should I be looking for?

Thanks for the quick responde
-- João

[Michael Bayer]

what happens if you say:

import sqlite3
import os
sqlite3.connect(os.path.abspath("test.db"))

?

thats essentially what SQLA 0.7 does in order to guard against os.chdir() changes as subsequent connections occur.

[jmsilva]

On Oct 22, 9:00 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> what happens if you say:
>
> import sqlite3
> import os
> sqlite3.connect(os.path.abspath("test.db"))
>
> ?
>
> thats essentially what SQLA 0.7 does in order to guard against os.chdir() changes as subsequent connections occur.
>

OK, you nailed it. The problem comes from some nasty text encoding
issues I've been having. os.path.abspath returns 'C:\\Users\\Jo\xe3o'.
This generates wonderful headscratchers such as this:

>>> import sqlite3
>>> import os
>>> path = os.path.abspath('test.db')
>>> path
'C:\\Users\\Jo\xe3o\\test.db'
>>> os.path.exists('test.db')
False
>>> sqlite3.connect(path)

Traceback (most recent call last):
File "<stdin>", line 1, in <module>

sqlite3.OperationalError: unable to open database file
>>> x = open(path, 'w')
>>> x.write('bla')
>>> x.close()
>>> os.path.exists('test.db')
True

I don't recall having this kind of problem on any computer I've had
before and I've spent way too much time trying to sort out these
encoding issues, so... that's what the 'Dev' user in this machine is
for. Thanks.

[python howto]

For those still unable to fix this error here is a simple tutorial i created. Hopefully it helps someone out there and they don't have to spend hours like me trying to squash it!

http://pythonhowto.com/operationalerror-unable-to-open-database-file-using-sqlite3/