Using mysqldb and sqlite3 in the same Python 2.7 script: Should I throw in the towel?

Question

I'm writing a Python script that is meant to pull from, process, and update to a MySQL database.

I originally started attacking this problem with comma-separated value dumps of the MySQL databases, which I'd throw into an sqlite database (using sqlite3). I'd do the processing in Python (2.7), create a CSV file of the output, which I'd upload back to the MySQL database with another script.

Well, then I thought I'd try to just pull/push to the MySQL database directly from the Python script. So I installed MySQLdb, and went to town on that.

What I'm finding out now is that my INSERTs from the MySQL database (into the sqlite database) aren't joining the way they were before. The representations of the integers now have an L tacked on the end, and decimal values are expressed as something like Decimal('4.00').

Basically, things that JOINed nicely when I was inserting them from the CSV files aren't working so well now.

My question: Am I asking for a world of pain continuing down this path, or is there an easy way to get MySQLdb and sqlite3 libraries to play together? If there isn't, then I'll install a MySQL server and refactor my code to use MySQL only.

Answer 1

Each database backend supports different types of data. The sqlite and mysqldb python modules try to help you out by doing appropriate type conversions based on the field types. So, if your mysql database has a DECIMAL field, MySQLdb will return that field automatically as a Python Decimal object.

You can request MySQLdb (and sqlite if you want) to do appropriate type conversion between database and Python types. It's up to you to determine what type conversion is appropriate. For example, since your database has a DECIMAL field, how are you going to represent that value in sqlite which doesn't have a native DECIMAL field? You'll probably end up using a REAL, but of course this isn't the same thing as a DECIMAL which will maintain the required precision.

Since you were already converting from csv data, I suspect you've been using the Python float type, indicating that you're happy to convert MySQL decimal fields to float. In this case, you can then request that MySQLdb do a conversion from DECIMAL to float on all field results.

Here is an example bit of code which creates two tables, one each in mysqldb and sqlite. The MySQL version has a DECIMAL field. You can see in the query_dbs function how to create your own conversion functions.

#!/usr/bin/env python

import os
import sqlite3
import MySQLdb
from MySQLdb.constants import FIELD_TYPE

user = os.getenv('USER')

def create_mysql_table():
    conn = MySQLdb.connect(user=user, db='foo')
    c = conn.cursor()
    c.execute("DROP TABLE stocks")
    c.execute("CREATE TABLE stocks"
              "(date text, trans text, symbol text, qty real, price Decimal(10,2) UNSIGNED NOT NULL)")
    c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
    conn.commit()

def create_sqlite_table():
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    c.execute("DROP TABLE stocks")
    c.execute("CREATE TABLE stocks"
              "(date text, trans text, symbol text, qty real, price real)")
    c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
    conn.commit()

def query_dbs(use_type_converters):
    conn = sqlite3.connect('test.db')
    c = conn.cursor()
    for row in c.execute('SELECT * FROM stocks'):
        print 'SQLITE: %s' % str(row)

    type_converters = MySQLdb.converters.conversions.copy()

    if use_type_converters:
        type_converters.update({
            FIELD_TYPE.DECIMAL: float,
            FIELD_TYPE.NEWDECIMAL: float,
        })

    conn = MySQLdb.connect(user=user, db='foo', conv=type_converters)
    c = conn.cursor()
    c.execute('SELECT * FROM stocks')
    for row in c.fetchall():
        print 'MYSQLDB: %s' % str(row)

create_sqlite_table()
create_mysql_table()

print "Without type conversion:"
query_dbs(False)
print "With type conversion:"
query_dbs(True)

This script produces the following output on my machine:

Without type conversion:
SQLITE: (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
MYSQLDB: ('2006-01-05', 'BUY', 'RHAT', 100.0, Decimal('35.14'))
With type conversion:
SQLITE: (u'2006-01-05', u'BUY', u'RHAT', 100.0, 35.14)
MYSQLDB: ('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)

What this is showing, is that by default MySQLdb is returning Decimal types, but can be coerced to return a different type, suitable for use with sqlite.

Then, once you have all of the types normalized between the two databases, you should no longer have problems with joins.

Python MySQLdb docs are here

Answer 2

There is no conflict between sqlite3 and MySQLdb, so you should be able to use them in the same program. However, you might also consider using SQLAlchemy, which provides a higher-level interface to both kinds of databases.

As far as why you are actually seeing this problem, the symptoms you describe suggest that you're incorrectly converting numbers to strings - in particular, that you're using repr() rather than str().