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 differing Python SQL libraries 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 python SQL libraries if what I'm asking is easier to implement. Just show me how:-)

Answer 1

Why exactly do you need a new table? Are you sure you don't just need a new row in the "Player" table?

Answer 2

May be look at sql soup , which is layer over sqlalchemy http://www.sqlalchemy.org/trac/wiki/SqlSoup

or elixir (again a wrapper over sqlalchemy) http://elixir.ematia.de/trac/wiki

you may also create table using plain sql, and to dynamically map use these libs if they already don't have create table function.

or alternatively create a dynamic class and map it e.g. snippet from my own code

tableClass = type(str(table.fullname), (BaseTable.BaseTable,), {})
mapper(tableClass, table)

where BaseTable can be any python class which you want all your table classes to inherit from, e.g. such Base class may have some utility or common methods, e.g. basic CRUD methods

class BaseTable(object): pass

otherwise you need not pass any bases to type(...)

Answer 3

We are absolutely 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,
Mike Bayer has made this even easier
with the all-in-one "declarative" method.

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.
Also, the SqlAlchemy googlegroup is great.
Mike Bayer is very quick to answer questions.

Answer 4

you can use declarative method for dynamically dreate table in datebase

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey


Base = declarative_base()

class Language(Base):
    __tablename__ = 'languages'

    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    extension = Column(String(20))

    def __init__(self, name, extension):
        self.name = name
        self.extension = extension

Answer 5

I faced the same problem when I was trying to automate simple CRUD tasks using SQLAlchemy. Here is simple explanation and some code: http://www.devx.com/dbzone/Article/42015