Using SQLite in a Python program

Question

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.

Answer 1

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.

1. 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) );

2. In your application, connect to your database normally.

3. 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.

Answer 2

AFAIK an SQLITE database is just a file. To check if the database exists, check for file existence.

When you open a SQLITE database it will automatically create one if the file that backs it up is not in place.

If you try and open a file as a sqlite3 database that is NOT a database, you will get this:

"sqlite3.DatabaseError: file is encrypted or is not a database"

so check to see if the file exists and also make sure to try and catch the exception in case the file is not a sqlite3 database