Dynamic Table Creation and ORM mapping in SqlAlchemy

Question

I'm fairly new to using relational databases, so I prefer using a good ORM to simplify things. I spent time evaluating different Python ORMs and I think SQLAlchemy is what I need. However, I've come to a mental dead end.

I need to create a new table to go along with each instance of a player I create in my app's player table. I think I know how to create the table by changing the name of the table through the metadata then calling the create function, but I have no clue on how to map it to a new dynamic class.

Can someone give me some tips to help me get past my brain freeze? Is this even possible?

Note: I'm open to other ORMs in Python if what I'm asking is easier to implement.Just show me how :-)

Answer 1

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.

Answer 2

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.