[Tutor] SQLite database creation bafflement

Roel Schroeven rschroev_nospam_ml at fastmail.fm
Mon Aug 20 12:48:27 CEST 2007

Che M schreef:
> Hi, I am trying to simply create an SQLite database with Python.  I find 
> that when I try to create a new database file, *sometimes* it lets me do it, 
> and sometimes it doesn't, and the only thing I am changing is the name of 
> the database.  I am baffled as to why some names appear to work and some 
> don't.  For example, this will create a brand new database on the desktop:
> import sqlite3
> conn = sqlite3.connect('C:\Documents and 
> Settings\user\Desktop\mydatabase.db')
> But running *this*--only thing different is the database's name--gives the 
> error, as shown:
> import sqlite3
> conn = sqlite3.connect('C:\Documents and 
> Settings\user\Desktop\adatabase.db')
> Traceback (most recent call last):
>   File "C:/Documents and Settings/user/Desktop/sqlitetester", line 5, in 
> <module>
>     conn = sqlite3.connect('C:\Documents and 
> Settings\user\Desktop\adatabase.db')
> OperationalError: unable to open database file

Backslashes in Python string literals function as escape characters, 
meaning that some combinations of backslash + another character are 
interpreted specially; for example, \a is an ASCII Bell. See the table 
at http://docs.python.org/ref/strings.html for a complete list.

There are different ways to work around the issue:

- Use slashes instead of backslashes, as you would do on Unix. Windows 
accepts slashes almost everywhere (a notable exception being the command 

- Use double backslashes: \\ in a string literal is actually a single \

- Use raw strings: in raw strings, backslashes are only used to escape 
quotes and always remain in the string. You can make a raw string by 
prefixing the string with r or R, for example:
conn = sqlite3.connect(r'C:\Documents and Settings\user\Desktop\mydb.db').
A gotcha is that you can't use a backslash as the last character of the 

- Use os.path.join(): that function inserts the correct slashes for the 
platform you're using, but it's not very readable for literals:
conn = sqlite3.connect(os.path.join('C:', 'Documents and Settings', 
'user', 'Desktop', 'mydb.db'))

> The only thing that is different is one is called "mydatabase.db" (works) 
> and the other is called "adatabase.db" (doesn't work).
> I've tested lots of different names, and it seems random to me what will 
> work and what won't.  E.g., "banana.db" and "apple.db" don't work, but 
> "peach.db" and "pear.db" do It is also consistent with each name (that is, 
> if I am successful and then remove the .db file from the desktop, that name 
> will always work again to create a new .db file).

It will become clear if you look at the table mentioned above: \b and \a 
have a special meaning, while \p doesn't, so \p is interpreted literally.

If I have been able to see further, it was only because I stood
on the shoulders of giants.  -- Isaac Newton

Roel Schroeven

More information about the Tutor mailing list