SqlAlchemy and Microsoft Access

Update (10/12/2010) – One of my alert readers told me that SqlAlchemy 0.6.x currently does NOT support the Access dialect. Read here for more info.

A year or two ago, I was asked to transfer some data from some old Microsoft Access files to our Microsoft SQL Server. Since I enjoy using SqlAlchemy, I decided to see if it supported Access. The documentation at the time was pretty unhelpful in this regard, but it did seem to be possible and I found one thread about it on SqlAlchemy’s Google group.

The code to connect to Microsoft Access is pretty simple. It goes something like this:

from sqlalchemy import create_engine
engine = create_engine(r'access:///C:/some/path/database.MDB')

See how easy that was? You just tell SqlAlchemy what kind of database to connect to, add three forward slashes and then the path to the file. Once that’s done, you can do pretty much anything with the Access file that you can do with a normal database:

########################################################################
class TableName(Base):
    """
    MS Access database
    """
    __tablename__ = "ROW"
    __table_args__ = ({"autoload":True})  # load the database
    FILENUM = Column("FILE #", Integer, key="FILENUM")

In the code above, I use SqlAlchemy’s declarative syntax to autoload the database’s structure. I can’t recall is this database had its primary key set, but I’m guessing it didn’t since I had to add that last line.

Anyway, once you have your connection, you can just run queries as you normally would. In my case, I ended up creating a model file to hold all the table definitions for the Access file and the SQL Server database, then I did a SELECT * on the Access file, looped over the result and inserted each row into the SQL Server one. The only thing you have to watch out for is that Access is a lot more forgiving of NULLS than SQL Server is, so I had to write some special handling around that atrocity.

Well, that’s really all there is to it. You can check out my other SqlAlchemy tutorials for more general information about using SqlAlchemy to interact with your database.