Dynamic Table Creation and ORM mapping in SqlAlchemy

It's a very old question. Anyway if you prefer ORM, it's quite easy to generate table class with type:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String


Base = declarative_base()

Test = type('Test', (Base,), {
    '__tablename__': 'test',
    'test_id': Column(Integer, primary_key=True, autoincrement=True),
    'fldA': Column(String),  
    ... other columns
    }
)

Base.metadata.create_all(engine)

#  passed session create with sqlalchemy
session.query(Test).all()

Making a class factory, it's easy to assign names to a class and database table.

We are spoiled by SQLAlchemy.
What follows below is taken directly from the tutorial,
and is really easy to setup and get working.

And because it is done so often,
the documentation moved to full declarative in Aug 2011.

Setup your environment (I'm using the SQLite in-memory db to test):

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
>>> from sqlalchemy import Table, Column, Integer, String, MetaData
>>> metadata = MetaData()

Define your table:

>>> players_table = Table('players', metadata,
...   Column('id', Integer, primary_key=True),
...   Column('name', String),
...   Column('score', Integer)
... )
>>> metadata.create_all(engine) # create the table

If you have logging turned on, you'll see the SQL that SQLAlchemy creates for you.

Define your class:

>>> class Player(object):
...     def __init__(self, name, score):
...         self.name = name
...         self.score = score
...
...     def __repr__(self):
...        return "<Player('%s','%s')>" % (self.name, self.score)

Map the class to your table:

>>> from sqlalchemy.orm import mapper
>>> mapper(Player, players_table) 
<Mapper at 0x...; Player>

Create a player:

>>> a_player = Player('monty', 0)
>>> a_player.name
'monty'
>>> a_player.score
0

That's it, you now have a your player table.