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 :-)

• S.Lott: Creating dynamic tables is a bad idea. Adding a key to a table to distinguish instances of players is a good idea. What are you trying to accomplish with this "dynamic table" business?
• Jay Atkinson: Basically each player get's their own scores table to track their scores over time. A player could be added any point in time, so it would be very hard to track it in a giant scores table with all the players in it...at least to me it does.
• Jay Atkinson: Of course, the process of asking the question makes me think about it in a whole new light. I probably could create a huge scores table that includes the player's id in a column along with their score info. I could then do a query on the player id to pull in their scores. Thanks everyone.
• Isvara: Yes, that (a foreign key to the player ID in the score table) is exactly the right way to do it. You say, "I'm fairly new to using relational databases, so I prefer using a good ORM to simplify things," but you really should take the time to learn how relational database work, even though you're using an ORM, otherwise you'll end up with terrible schema and queries.

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.

• Jay Atkinson: Thanks for the info. Not quite what I was looking for. I was attempting something beyond that, another table per player.
• zylatis: I don't understand what the last block of code shows: I would get that output without any of the sqlalchemy mapping, right?

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.

Answer 3

If you are looking to create dynamic classes and tables you can use the following technique based from this tutorial URL I found here (http://sparrigan.github.io/sql/sqla/2016/01/03/dynamic-tables.html), I modified how he did it a bit.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///test.db', echo=True)
from sqlalchemy import Column, Integer,Float,DateTime, String, MetaData
metadata = MetaData()
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session() # create a Session
Base = declarative_base()

First include all the needed dependencies and create your session and Base.

The key to creating it dynamically is this here:

attr_dict = {'__tablename__': 'default','id': Column(Integer, primary_key=True, auto_increment=True)}

you could create a table from just this by taking advantage of the 'type' function in python.

myClass = type('ClassnameHere', (Base,), attr_dict)

Note that we are passing in attr_dict, this will give the required tablename and column information to our class, but the difference is we are defining the class name through a string! This means you could create a loop for example going through an array of strings to start creating tables dynamically!

Next all you have to do is simply call

Base.metadata.create_all(engine)

Because the dynamic class we created inherits from Base the command will simply create the tables!

You add to this table for example like this now:

SomeRow = myClass(id='2')
session.add(SomeRow)
session.commit()

This can go even further if you you don't know the column names as well. Just refer to the article to learn how to do that.

You would essentially do something like this though:

firstColName = "Ill_decide_later"
secondColName = "Seriously_quit_bugging_me"
new_row_vals = myClass(**{firstColName: 14, secondColName: 33})

The ** operator takes the object and unpacks it so that firstColName and secondColName are added with assignment operators so it would essentially be the same thing as this:

new_row_vals = myClass(firstColName=14, secondColName=33)

The advantage of this technique is now you can dynamically add to the table without even having to define the column names!

These column names could be stored in a string array for example or whatever you wanted and you just take it from there.

Answer 4

Maybe look at SQLSoup, which is layer over SQLAlchemy.

You can also create the tables using plain SQL, and to dynamically map, use these libraries if they already don't have create table function.

Or alternatively create a dynamic class and map it:

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(...).

• Peter Hoffmann: Where do I import BaseTable from? I can't find it in sqlalchemy.
• Mike: Elixir has not been updated since 2009 and seems to have been abandoned. It does not have any support for python 2.7+.

Answer 5

you can use declarative method for dynamically creating tables in database

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

• Jens: This does not create a table in the database; this declares a class Language which maps an assumed table languages. I think the initializer isn’t necessary. Several of the imports aren’t used.

Answer 6

maybe i didn't quite understand what you want, but this recipe create identical column in different __tablename__

class TBase(object):
 """Base class is a 'mixin'.
 Guidelines for declarative mixins is at:
 http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#mixin-classes
 """
 id = Column(Integer, primary_key=True)
 data = Column(String(50))
 def __repr__(self):
 return "%s(data=%r)" % (
 self.__class__.__name__, self.data
 )
class T1Foo(TBase, Base):
 __tablename__ = 't1'
class T2Foo(TBase, Base):
 __tablename__ = 't2'
engine = create_engine('sqlite:///foo.db', echo=True)
Base.metadata.create_all(engine)
sess = sessionmaker(engine)()
sess.add_all([T1Foo(data='t1'), T1Foo(data='t2'), T2Foo(data='t3'),
 T1Foo(data='t4')])
print sess.query(T1Foo).all()
print sess.query(T2Foo).all()
sess.commit()

info in example sqlalchemy

Answer 7

I suspect that sqlalchemy has improved a lot as a library since 2009, and there are now more straightforward and clear ways to write the code to interact with a database. Here's an example:

A more modern solution:

Here's a simpler solution. In my opinion this code is pretty straight forward to understand and essentially does exactly what it says.

# An example test code which you can run as a
# stand-alone "executable" using
#
# $ python3 main.py
#
#
import sqlalchemy
def main():
 db_url = "postgresql://postgres:password@localhost/postgres_db_name"
 engine = sqlalchemy.create_engine(url=db_url)
 metadata = sqlalchemy.MetaData()
 with engine.connect() as connection:
 # repeat for each table you wish to load/reflect
 # loading each table explicitly means you can load
 # only the tables you require - thus not slowing
 # down the loading of your app too much
 my_table_object = sqlalchemy.Table('my_table_name', metadata, schema='my_schema_name', autoload_with=engine)
 # perform a `SELECT * from MY_TABLE` query
 query = sqlalchemy.select(my_table_object)
 result_proxy = connection.execute(query)
 result_set = result_proxy.fetchall()
 # print up to 10 entries (rows of data)
 print(result_set[:10])
if __name__ == '__main__':
 main()

Details on how to work with the returned data:

The result_set and result_proxy objects returned in the previous example can be used in various ways. In the above example, how to retrieve all available data and convert it to a list was demonstrated.

Iterating over the results:

for row in result_proxy:
 # print data in `row` as a tuple
 print(row._t)

The attributes of a row:

A row object contains a number of attributes. I have found the most useful of those to be the following:

• print(row.__dir__()): Find out what other methods and attributes are available. (Same as print(dir(row)).)
• row._t: The tuple representation of the data in each column
• row._fields: Names of the columns, in the same order as the data contained in the tuple
• row._data: Same as row._t as far as I can tell
• row._key_to_index: A dictionary which converts the set of possible keys to the index. There are three keys for each column. One is an sqlalchemy Column object. The other two are simpler: A fully qualified name (column name and schema name concatinated), and a column name.
• row._mapping: A dictionary which maps the column names to their values. Essentially a combination of _fields and _data together. Iterate over it using for key, value in row._mapping.items(): where key is the column name, and value is the data value.

Getting the column names from the query rather than the query result:

You can also get the column names from the table object:

for column_name in my_table_object.columns:
 print(column.name)