Threading issue with SQLite

[Alan Harris-Reid]

Hi,

I am creating a web application (using Python 3.1 and CherryPy 3.2)
where a SQLite connection and cursor object are created using the
following code (simplified from the original):

class MainSite:
con = sqlite.connect('MyDatabase.db')
cursor = con.cursor()

def index_page():
some HTML code
cursor.execute(some SQL statement)
more HTML code

def another_page():
some HTML code
cursor.execute(anotherSQL statement)
more HTML code

When I call a URL which launches the another_page() method I get the
error message "sqlite3.ProgrammingError: SQLite objects created in a
thread can only be used in that same thread."

Questions...
1. Is there a large overhead in opening a new SQLite connection for
each thread (ie. within each method)?
2. Is there any way to use the same connection for the whole class (or
should I forget that idea completely?)
3. When a method returns to the calling method, is the connection
automatically closed (assuming the object is local, of course) or does
it have to be done explicitly using connection.close()?

TIA,
Alan Harris-Reid

 

[Jonathan Gardner]

Questions...
1.  Is there a large overhead in opening a new SQLite connection for
each thread (ie. within each method)?

Yes, but not as bad as some other DBs.

2.  Is there any way to use the same connection for the whole class (or
should I forget that idea completely?)

Forget it. The thread stuff is pretty important.

3.  When a method returns to the calling method, is the connection
automatically closed (assuming the object is local, of course) or does
it have to be done explicitly using connection.close()?

If the object falls out of scope, that is, no code anywhere can
reference it, it will be garbage collected and thus closed.

Suggestion: Use something like SQLAlchemy to manage you DB
interactions. One day, you'll move away from SQLite, and you'll be
glad you were programming at a higher level. SQLAlchemy also does
things like connection pooling and such for you.

 

[John Nagle]

Suggestion: Use something like SQLAlchemy to manage you DB
interactions. One day, you'll move away from SQLite, and you'll be
glad you were programming at a higher level. SQLAlchemy also does
things like connection pooling and such for you.

Generally, if you get to the point where you're concerned about
concurrency performance issues with SQLite, it's time to upgrade
to a more serious database. Especially if you're doing much updating.
SQLite can do multiple SELECT operations in parallel, but the entire
database is locked for all operations that write.

John Nagle

 

[Alan Harris-Reid]

Many thanks to all who replied to my questions re. SQLite connections,
cursors and threading.

Looks like I have got some reading to do regarding connection pooling and
a decent SQLite ORM package. Does anyone know of any which are Python 3
compatible?

Many thanks,
Alanj