SQL Dump for MS Access databases (.mdb files) on Linux

I recently had to work with some data that came in a huge Microsoft Access database. Because I like SQLite (and despise Access), I’ve decided to export the data to an SQLite file. The first thing I needed to do was to somehow get all the data out of the db. Being a Linux user, complicates things a bit, but thanks to mdb-tools it’s possible to process the .mdb files without resorting to Windows and buying Access. Using mdb-tools directly can be tedious if you want to export a large db with multiple tables, so when I’ve looked for a way to automate it, I came across Liberating data from Microsoft Access “.mdb” files. This post shows a nice script that dumps every table in a .mdb file to separate CSV file.

While useful, I wanted something that I could easily import into SQLite. So I’ve modified their script to generate an SQL dump of the db. Given a db file, it writes to stdout SQL statements describing the schema of the DB followed by INSERTs for each table. Actually because mdb-tools doesn’t support SQLite as a backend, the dump uses a MySQL dialect, but it should be fine with SQLite as well (SQLite will mostly ignore the parts it can’t process such as COMMENTs). The easiest way to use the script is

$ python AccessDump.py access.mdb | sqlite3 new.db

If the original db contains non-ascii characters, and isn’t encoded in UTF-8, you should set the MDB_JET3_CHARSET environment variable to the correct charset. The dump itself will be UTF-8 encoded.

$ MDB_JET3_CHARSET="cp1255" python AccessDump.py access.mdb | sqlite3 new.db

Continue reading SQL Dump for MS Access databases (.mdb files) on Linux

Starting Djano upon Reboot using Cron

Three years ago I wrote about starting services as user via cron instead of init.d (specifically Trac). However this method has a serious downside: it has no support for dependencies. This really bothered me when I used cron to start a Django server, has it would attempt to load before MySQL was running. This made the cron useless, as always after rebooting I would receive an error email saying it couldn’t connect to the MySQL server, and I would have to login and start the Django Server manually. Yesterday I got sick of it, and decided to hack something that will work properly. So my crontab had the following line:

@reboot python ./manage.py runfcgi ...options...

which I changed into:

@reboot until /sbin/status mysql | grep start/running ; do echo "Mysql isn't running yet...>&2"; sleep 1; done; python ./manage.py runfcgi ...options...

Basically it loops and checks whether the MySQL service got started. If so, it would start Django as it did before. On the other hand, if MySQL isn’t running it would just sleep for a second and repeat the check.

A small issue is that if for some reason MySQL won’t start at all, it will loop forever. If this happens, it would mean that I’ll have to manually kill that cronjob, but I would have to login anyway to see what wrong with the MySQL. So, while this method can’t support dependencies like init.d does, it does provide a good-enough solution.

Update 2012-11-23: Fixed the crontab line (it would fail when mysql was in start/post-start state).