sqlalchemy 0.6 legacy database access?

Question

I feel like this should be simple, but i cant find a single example of it being done.

As an example I have the following existing tables:

CREATE TABLE `source` (
  `source_id` tinyint(3) unsigned NOT NULL auto_increment,
  `name` varchar(40) default NULL,
  PRIMARY KEY  (`source_id`),
  UNIQUE KEY `source_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `event` (
  `source_id` tinyint(3) unsigned NOT NULL default '0',
  `info` varchar(255) NOT NULL default '',
  `item` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`source_id`,`info`,`item`),
  KEY `event_fkindex1` (`source_id`),
  CONSTRAINT `event_fk1` FOREIGN KEY (`source_id`) REFERENCES `source` (`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'd like to use sqlalchemy 0.6 to add a lot of rows to the events table. I've seen some sqlsoup examples, but really hate the way it accesses the db by constantly calling the db object. I followed the docs for the db reflection stuff and got this far:

import sqlalchemy
from sqlalchemy import Table, Column, MetaData, create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine('mysql://user:pass@server/db', echo=True)
metadata = MetaData()
source = Table('source', metadata, autoload=True, autoload_with=engine)
Session = sessionmaker(bind=engine)
session = Session()
session.query(source).first()

This returns a really ugly object. I really want the mapper functionality of the sqlalchemy ORM so I can construct Event objects to insert into the DB.

I looked at the sqlsoup stuff:

from sqlalchemy.ext.sqlsoup import SqlSoup
db = SqlSoup(engine)
db.sources.all() #this kinda works out bet

But I couldn't figure out how to add objects form this point. I'm not even sure this is what I want, I'd like to be able to follow the tutorial and the declarative_base stuff. Is this possible without having to rewrite a class to model the whole table structure? If its not, can someone show me how I'd do this in this example?

Can someone set me down the right path for getting the mapper stuff to work?

Answer 1

You can use a predefined/autoloaded table with declarative_base by assigning it to the __table__ attribute. The columns are picked up from the table, but you'll still have declare any relations you want to use.

class Source(Base):
    __table__ = source

class Event(Base):
    __table__ = event
    source = relation(Source)

However if you are going to be inserting a huge amount of rows, then going around the ORM and using executemany will get you a large performance increase. You can use execute many like this:

conn = engine.connect()
conn.execute(event.insert(),[
    {'source_id': 1, 'info': 'xyz', 'item': 'foo'},
    {'source_id': 1, 'info': 'xyz', 'item': 'bar'},
    ...
])