A few struggles because we had spaces (and other funny characters) in our column names, and had a ’schema’ (formerly, owner) in ms sql. Figured out how to automatically create a ‘key’ for each column: make a mapper shim function that removes the bad characters from the column name, making it into a valid Python identifier. From our model python source:
from sqlalchemy.orm import mapper as sa_mapper
import re
colname_omit_re = re.compile("[^A-Za-z0-9_]") #match funny chars to remove
def mapper(cls, tbl, **kwargs):
"Our own mapper that adds Python-identifier-suitable key for multi-word column names"
kprop = kwargs.get('properties',None) # use existing
for c in tbl.c:
key = colname_omit_re.sub("",c.name) # omit bads
if key != c.name:
if not kprop: kprop = {}
if not kprop.has_key(key): kprop[key] = c # allow override in mapper call
if kprop: kwargs['properties'] = kprop # set, if new
return sa_mapper(cls, tbl, **kwargs)
Each Column() specification (within Table()) had to include parameter schema=’albert’ (if albert was our schema!), and also when we used foreign keys: ForeignKeyConstraint(['Col Name'], ['albert.JoeTableName.Column Name'], name=”explanation of some kind”)
