Turning an MS Access application into a Windows-ready Flask app

I'm not from the old days where the normal thing to do was to write software that lived inside people's computer, most of them running Windows, with embedded databases and so on. I don't know anything about that. The only way I know of building usable (not Linux command-line stuff I can only use myself) applications is the web app (or webapp, if you prefer). With an HTTP server and "cloud" databases, or even pure in-browser Javascript, but those also have to be served from somewhere.

Recently, though, I was faced with the problem of "saving" a program written in 1995 as an Microsoft Access application, that have been working and being filled with data without any maintenance since that time. The problem of saving the thing just emerged because Microsoft Access's updates through the time caused progressive appearance of errors and feature loss to the app.

My first thought was, of course, "I'll export the data, put it at some database hosted by someone else and turn the whole app into SaaS". "No cloud", I heard back from the fairly untechnical program owner, "I don't want my data floating into these clouds and being seem by everyone who can access the database".

Then I looked at some Python GUI frameworks. They all looked very nice. They seemed easy to use, but I didn't want to stop and learn all the inner workings of GUIs like I've never did before just to write just a simple smallish application just once, with only one user. So I decided to do what I knew better: write a web app and make it run locally on a standard Windows computer.

Exporting a .mdb file to SQLite

All I have was an Access .mdb file, (these JET database files have been working with MS Access since long before SQLite), so I used mdb-tools to export the data and put it inside a SQLite database. On Debian and Ubuntu, mdb-tools is accessible through sudo aptitude install mdbtools.

The first step was to dump the schema with

mdb-schema ooold.mdb mysql > ooold.schema.sql

mdb-tools doesn't support dumping to SQLite, but MySQL format is fine. We just have to manually tweak the schema definitions to fix some types that MySQL supports and SQLite doesn't. Also we need to manually add CONSTRAINTS, PRIMARY KEYs and FOREIGN KEYs to the table declarations.

After doing that and copying some code from this gist I ended up with the following script:

import StringIO
import datetime
import subprocess
import csv
import sqlite3

db = 'ooold.mdb'

conn = sqlite3.connect('neeew.db')
c = conn.cursor()

with open('ooold.schema.sql') as f:
    schema = f.read()

for statement in schema.split('\n\n'):

table_names = subprocess.Popen(["mdb-tables", "-1", db], stdout=subprocess.PIPE).communicate()[0]
tables = table_names.splitlines()
for table in tables:
    if table != '':
        data = subprocess.Popen(["mdb-export", db, table],
        filelike = StringIO.StringIO(data)
        rows = csv.reader(filelike, delimiter=',', quotechar='"')

        cols = rows.next()
        for row in rows:
            vals = []
            for val in row:
                val = val.strip(' "')
                val = val.replace("'", "''")
                if len(val.split(' ')) == 2:
                    # this is only needed because the date fields in the original db were
                    # poorly formatted. these conversions can be better done and need to be
                    # checked in each particular case.
                        val = datetime.datetime.strptime(val.split(' ')[0], '%m/%d/%y').isoformat().replace('T', ' ')
                    except ValueError, e:
                val = unicode(val, 'utf-8')
                val = 'NULL' if not val else val if val.isdigit() else "'" + val + "'"

                vals = u", ".join(vals).encode('utf-8')
                sql = "INSERT INTO '{table}' ({cols}) VALUES ({vals})".format(
                    cols=', '.join(cols),
            except sqlite3.OperationalError, e:
                print sql
                print e
                raise sqlite3.OperationalError


Running this script would create the neeew.db file as the complete SQLite database, with all the data correctly written to it.

Writing a standalone Flask app to run in Windows

The second effort was to make a webapp and turn it into a self-contained Windows executable. I was going to write a Flask application, because Flask is good, then compile it with pyinstaller.

Basically, the care you should take in this step is that some modules are not easily bundled with pyinstaller. Everything after flask.ext, for example, come with some problems.

I used SQLAlchemy to manage the SQLite connection. I don't like ORMs or other unnecessary abstration, but in this case I felt it was ok, since it provided an easy way to access the already created (not by me) table structure. Here are the first lines of the only and main Python file:

import json
import os
import sys
import sqlite3

from flask import Flask, request, render_template
from sqlalchemy import Table, Column, Integer, String
from sqlalchemy import create_engine, MetaData

app = Flask(__name__)
app.debug = True

if getattr(sys, 'frozen'):
    here = sys.executable
    here = os.path.realpath(__file__)
db = os.path.join(os.path.dirname(here), 'neeew.db')
connstring = 'sqlite:///' + db
engine = create_engine(connstring, convert_unicode=True)

metadata = MetaData(bind=engine)
table1 = Table('table1', metadata=metadata, autoload=True)
table2 = Table('table2', metadata=metadata, autoload=True)
table3 = Table('table3', metadata=metadata, autoload=True)

The path magic up there is also important, as it will allow you to load the SQLite database from the same folder in which the application is contained. Probably it would be easier to specify an absolute path, but for what I remember being a Windows user I never liked those programs that spreaded data all over my computer without even telling me where.

In the end of the file, after I've declared my routes and application logic and all that, I added something like the following:

if __name__ == '__main__':
    import webbrowser
    import threading
    import random

    port = 5000 + random.randint(0, 999)
    url = '' % port

    threading.Timer(1.45, lambda: webbrowser.open(url)).start()
    app.run(port=port, debug=False)

The threading magic is for running the app while also running/opening a browser tab at is location. debug=False is important because without it Flask will start two times (don't ask me why), open two tabs et cetera. The random port is also useful and doesn't hurt. In fact, using random ports is great because you can turn debug=True and debug the app (if you don't bother the two tabs) without the second instance failing while trying to open the same port as the first.

Bundling everything into an app with pyinstaller on Windows

If you're like me and has never used Windows for any programming task, you wouldn't even know how to start. Well, first you will need a computer running Windows. Then, you'll need a shell. I don't understand how to use cmd, but I also don't know Powershell or any other alternative, so I used the excellent git bash that comes with this git for windows package. Go there and download it, then run the git bash. It's bash, it just works and in a minute you'll be walking through Windows directories with cd and listing their contents with ls.

Then, Python. Download it for Windows at the official website. It will come with pip, good.

Now clone your repo from your other computer, GitHub, Bitbucket, or copy it from a USB drive. You must get your code from your Linux computer to the Windows computer somehow. git clone will work. scp, and ssh are also included in the git bash

If you were developing with virtualenv, then do the same here, install virtualenv, then use your requirements.txt file to download all the modules your app depends on:

pip install virtualenv # will install it globally
virtualenv venv # inside your app folder
venv/Scripts/pip.exe install -r requirements.txt

You will need also a library called pywin32. So follow these instructions:

  1. Activate your virtualenv with source venv/Scripts/activate
  2. Browse http://sourceforge.net/projects/pywin32/files/ for the file matching your Python installation
  3. Run easy_install http://path.to/exe/download (something like http://sourceforge.net/projects/pywin32/files/pywin32/Build%20219/pywin32-219.win32-py2.7.exe/download)

Then install pyinstaller:

venv/Scripts/pip.exe install pyinstaller

Finally, considering your main Python file is named main.py and you want to get your executable your_app_name.exe in a folder named bin/your_app_name go to your app folder and run

venv/Scripts/pyinstaller.exe --distpath=bin --workpath=pyinstaller -n your_app_name main.py

You can add --onefile as an option to this command to make it output a single .exe file, but I couldn't make it work with Flask. Also, it isn't worth it, because you'll have to add your templates and static files to the generated folder so Flask can use them:

cp -r templates bin/your_app_name/
cp -r static bin/your_app_name/

Don't forget to put your database inside this same folder: scp somewhere:/path/to/neeew.db bin/your_app_name/

Now you can run the program as a normal .exe and by the way Windows people are used to: by clicking on it.