Using SQLite in a Python program

Asked 2008-10-17T09:02:12.737

I have created a Python module that creates and populates several SQLite tables. Now, I want to use it in a program but I don't really know how to call it properly. All the tutorials I've found are essentially "inline", i.e. they walk through using SQLite in a linear fashion rather than how to actually use it in production.

What I'm trying to do is have a method check to see if the database is already created. If so, then I can use it. If not, an exception is raised and the program will create the database. (Or use if/else statements, whichever is better).

I created a test script to see if my logic is correct but it's not working. When I create the try statement, it just creates a new database rather than checking if one already exists. The next time I run the script, I get an error that the table already exists, even if I tried catching the exception. (I haven't used try/except before but figured this is a good time to learn).

Are there any good tutorials for using SQLite operationally or any suggestions on how to code this? I've looked through the pysqlite tutorial and others I found but they don't address this.

python exception sqlite

Edit Report

2 Answers

Don't make this more complex than it needs to be. The big, independent databases have complex setup and configuration requirements. SQLite is just a file you access with SQL, it's much simpler.

Do the following.

Add a table to your database for "Components" or "Versions" or "Configuration" or "Release" or something administrative like that.

CREATE TABLE REVISION( RELEASE_NUMBER CHAR(20) );
In your application, connect to your database normally.
Execute a simple query against the revision table. Here's what can happen.
- The query fails to execute: your database doesn't exist, so execute a series of CREATE statements to build it.
- The query succeeds but returns no rows or the release number is lower than expected: your database exists, but is out of date. You need to migrate from that release to the current release. Hopefully, you have a sequence of DROP, CREATE and ALTER statements to do this.
- The query succeeds, and the release number is the expected value. Do nothing more, your database is configured correctly.

answered 2008-10-17T10:30:02.670

As @diciu pointed out, the database file will be created by sqlite3.connect. If you want to take a special action when the file is not there, you'll have to explicitly check for existance:

import os
import sqlite3
if not os.path.exists(mydb_path):
    #create new DB, create table stocks
    con = sqlite3.connect(mydb_path)
    con.execute('''create table stocks
      (date text, trans text, symbol text, qty real, price real)''')
else:
    #use existing DB
    con = sqlite3.connect(mydb_path)
...

answered 2008-10-17T09:40:12.880

Using SQLite in a Python program

2 Answers

Your Answer