gabe westmaas’ blog

OK! So I’ve been using SQLAlchemy a lot lately so I am guessing there are going to be a few of these posts at first.

Here is all the python code below in a single file, in case you don’t want to read what I have to say about it.

SQLAlchemy is an ORM for python, and its pretty sweet! There are lots of cool things you can do with it, but I just found one that I thought was especially exciting. Basically you can store data in multiple databases, even if the data is in two completely different DB engines. Yeah!

One might be wondering “why the hell would I want to do that?” I appreciate you only thinking that and not saying it out loud, it really breaks my concentration. I’m sure there are lots of reasons, but the reason I have is to support a legacy design that is too large to change, especially for the small amount of data I want.

One might now be wondering “how the hell do I do that?” I’m glad you thought that, because I am about to tell you, and that was a nice segue. We are going to use an example that has nothing to do with my work, because I don’t want to get fired. Basically we are going to store data about books in both MySQL and PostgreSQL. Book IDs and book titles will be in MySQL and chapter titles will be in PostgreSQL. Please note that this is a horrible idea, if the data is actually related. If you do this, you have removed nearly all the benefit of using a DBMS. Why would you do this?! Okay, that’s out of the way.

First, the boring stuff, just so its all here. First we create our schemas in both Postgres and MySQL. Please note that I know nothing about PostgreSQL, so if I made hilarious mistakes in my syntax try not to think too little of me. In a database called “test” create this table:

CREATE TABLE book_chapters
(
  "bookID" integer NOT NULL,
  chapter_title text NOT NULL,
  CONSTRAINT id_chapter_constraint PRIMARY KEY ("bookID", chapter_title)
)
WITH (OIDS=FALSE);

I do know a bit about MySQL, so feel free to laugh at me here. In a database called “mytest” create this table:

CREATE TABLE `books` (
  `bookID` int(11) NOT NULL AUTO_INCREMENT,
  `bookTitle` varchar(255) DEFAULT NULL,
  PRIMARY KEY  (`bookID`)
) ENGINE=MyISAM

And then we do out required imports and define our python objects. All the attributes will be loaded from the tables, no need to specify them here.

from sqlalchemy import MetaData, create_engine, Table
from sqlalchemy.orm import sessionmaker, mapper, relation

class Book(object):
    pass

class Chapter(object):
    pass

Next, we create the engines and metadata for each database, and bind the two together. Please pick better passwords! Or post your bank’s url and username here!

mystr = 'mysql://gabe:password@localhost/mytest'
mydb = create_engine(mystr)
mymetadata = MetaData()
mymetadata.bind = mydb

pgstr = 'postgres://gabe:password@localhost/test'
pgdb = create_engine(pgstr)
pgmetadata = MetaData()
pgmetadata.bind = pgdb

Then we create the appropriate tables in python. This is the Table object in SQLAlchemy and this is the portion of the code that cares which database the tables are in:

bookChapters = Table('book_chapters', pgmetadata,autoload=True)
books = Table('books', mymetadata,autoload=True)

Next, we map the python objects to SQLAlchemy tables. Order does not matter here, but since SQLAlchemy cannot discover the foreign key automagically, we must specify it in the mapping for Book:

mapper(Chapter, bookChapters)
mapper(Book, books, properties={
    'chapters': relation(Chapter, primaryjoin=
    books.c.bookID==bookChapters.c.bookID,
    foreign_keys=[bookChapters.c.bookID])
})

Then we put data in the python objects. Note that the attribute “chapters” is now a list on instantiated Book objects, and thus append is allowed.

ch1 = Chapter()
ch1.chapter_title = 'chapter 1'
ch2 = Chapter()
ch2.chapter_title = 'chapter 2'
book = Book()
book.bookTitle = 'Book title'
book.chapters.append(ch1)
book.chapters.append(ch2)

My goodness, what a creative author! I certainly cannot wait to read “Book title: chapter 2″! Finally, we create the session and save the Book to the databases.

Session = sessionmaker()
session = Session()
session.add(book)
session.commit()

The data should now be in their respective databases and correct.

Here is all the python code in a single file, in case its easier.

I hope to fill this site with posts about coding, software development, and my experiences with both (a real niche market, I know). And when I say fill I likely mean post once every 2-3 years, so make sure you subscribe right away, you don’t want to miss a thing!

I am a software developer and team lead with Mailtrust (a division of Rackspace) and I am quite happy there. Its incredible working in a place where you love your job, and your co-workers seem to love it too. I am lucky to work with people much smarter than me. Unfortunately for you, they are much too busy being smart to have blogs, so you are stuck with me. Don’t worry though, when I learn cool things from them I will run back here and post them as if they are my own ideas! Good thing dry humor is so easy to discern in text, or people would think I was a big fan of plagiarism, haha!

Thanks for stopping by, I am sure to see you here again and again. Yes, I am speaking to you specifically, since you are the only one who reads this. (Thanks mom!)