More SQLAlchemy – column names with spaces, schema

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

Pylons, SQLAlchemy

Installed SQLAlchemy: easy_install SQLAlchemy

Trying the tutorial at http://docs.pythonweb.org/display/pylonscookbook/Making+a+Pylons+Blog

Did all steps without incident using sqlite and the MyBlog folder name, etc. and the “blog” works fine. Where is the SQL magic happening? In the blog controller (blog.py) in its index function:

     q = model.Session.query(model.Blog)
     c.posts = q.limit(5)
     return render("/blog/index.html")

Now looking at sqlautocode, which needs pymssql.  Found and installed (using .zip for Python 2.5).  Modified autocode so I didn’t have to use command line, see below.  One problem was that the tables in the database were NOT in the default dbo schema, but in “myschema” (another name actually), so had to specify this:

#File autocode.py
import sqlautocode
command = "mssql://sqlusername:sqlpass@sqlserver.dummy.net/sqldatabase -s myschema -t tablewildcard* --noindexes --output out.py"
argv = ("autocode "+command).split(" ")
sqlautocode.main(argv) #File sqlautocode\__init__.py
#def main(myargv):
#    config.configure(myargv)

Tried it without –noindexes, but get “It seems that this dialect does not support indexes!”  Foreign keys seem OK so far, and schema is passed in the output file.