Main Content

Connecting Python to sqlite and MySQL databases

Archive - Originally posted on "The Horse's Mouth" - 2010-04-28 20:15:46 - Graham Ellis

For developing a Python application, and for light use, the SQLite database may be a good choice ... but then you may want to be able to expand the system to use a central database with a more classic server such as MySQL at a later date.

Using Polymorphism in Python, and with a careful choice of which database connector you use, you should be able to write code that is easily ported.

I have written a simple demonstration that connects to a database (whether it's a local file via SQlite or a server via MySQL), creates two new tables, populates the tables with data, then does a left join.

The connection to the databases differs. Here it is for SQlite:

import sqlite3
conn = sqlite3.connect('/tmp/example')

and here it is for MySQL:

import mysql.connector
conn = mysql.connector.Connect(host='192.168.200.115',\
  ' user='demo',password='abc123',database='test')

But thereafter, the code is the same - so it will work equally on a local SQLite database, and on a database on my server at 192.168.200.115. Source code for the sqlite example - [here] and for the MySQL example [here].

The sqlite drivers are now INCLUDED with Python Distributions. MySQL connector/Python is available at places including [here] at Launchpad. Stated o be in early condition, but some people swear by it already!