Database interface

Question

I want to implement a database desktop application with Python (PyGTK). Up to now I was using sqlite3 but I read that it is not so nice, if few people wants to access it at the same time. I'm thinking now to switch to another interface but I don't know which one or whether it is really necessary.

What I need/have:

• 2 tables with about 2000 entries each, this is the main dataset but more will be added.
• The database will be stored at a local network. The application will be used by about 5 people, on 5 different PC's.
• Usage of the tool is not very often, it is very unlikely that few people use/edit at the same time but it can happen.
• Safety is a big point but since the tool is running in the intranet, I don't need to care about login/backups. This is done by the server and IT stuff.
• The database should be not too slow and here comes another point, each entry has about 3 image (all together 15000). I think to generate each of them live if the user select the corresponding entry will be to slow but I have to try this out. Alternatively I generate all images and store the file path in the DB.
• OS must be Windows (XP/7/64bit)

Any suggestions?

Answer 1

SQLite allows a single writer but multiple readers.

Of course in this case that doesn't matter. When multiple users try to open and write to that SQLite DB file on your network the last writer to the DB file would win and the other changes would be lost.

You'll want to use a database server like:

• PostgreSQL (psycopg2)
• MySQL (pymysql)
• SQLServer (pymssql)

The links in parenthesis are links to information on the Python DB-API drivers for those databases.

The good news is that if you already have an application that works with the sqlite3 driver you really just need to go through and replace sqlite3 with the appropriate driver for whatever database you switch to. Well, you'll also need to update your application with some additional information about where to connect to the database server on your network.

For example, SQLite only requires a path to the DB file while any of the databases above will require a host / IP address, port, database name, database username, and database password.

Personally I'd favor PostgreSQL but this most likely up to your IT and Server staff what they want to support. I imagine you could get by with something like SQLServer Express (as your dataset doesn't seem that large) and that may fit more nicely with the Windows environment.