Code generating a Python web application with LLBLGen, SqlAlchemy and the Tornado Web Server

While I work mostly with Microsoft technologies and .NET/C# these days, I love digging into other languages when the opportunity calls. About a week ago, I thought up the idea of using the LLBLGen Pro code-generation tool, which I have primarily used for .NET development, to build a basic database editor tool in Python. To achieve this, we will combine LLBLGen, Sql Alchemy and the Tornado web server. While I work mostly with Microsoft technologies and .NET/C# these days, I love digging into other languages when the opportunity calls. About a week ago, I thought up the idea of using the LLBLGen Pro code-generation tool, which I have primarily used for .NET development, to build a basic database editor tool in Python. So putting a couple hours into it in the evenings this week, I think something fairly nice came out of it, and here’s been my experience. After reading about various Python ORMs, it was clear to me SqlAlchemy, a Python SQL Toolkit and Object Relational Mapper, would be the library I would use for interacting with the database. Then, I hesitated slightly between using web.py and Tornado. Both, from my reading, seemed to be minimalistic in their approach, which I really liked. In the end, I chose Tornado simply because it had just enough of the barebone features I wanted to pull a nice simple MVC design off. A week later, I’ve come to really appreciate the power of SqlAlchemy and the simplicity of Tornado, and I would recommend them to all Pythonistas. To be honest, I’m the one that’s late to the party here . I also give credit to Haldun for helping me get started with Tornado with his nice Tornado starter template. The final product looks like the following (code can be downloaded at the end of this post).The application is 100% code-generated using LLBLGen: The main features I was looking to implement included:

• Entity selection (includes some real-time jQuery filtering in case the model gets really large with lots of entities)
• All CRUD features (Add, Edit, Delete, View)
• Searching against any fields in the model
• Sorting and Paging
• Client-side & server-side validation
• MVC-type framework (the code should be intuitive to a .NET MVC developer)

Things I purposefully left out-of-scope:

• Security/Authentication
• Extended relational features (like custom web controls for selecting related items, etc…)
• Other things not specifically outlined above …

Here were the steps.

Step 1 – LLBLGen and SqlAlchemy model generation

With LLBLGen, you can begin a project by creating a model from a database, or just create the model in the model designer. I wanted to get up and running fast, so I connected to a Northwind database in my local SQL Server, and brought the model into the designer that way (as a shortcut). One of the nice things is that because the entire code-generation process to Python is custom, and because SqlAlchemy works with a quantity of dialects, this process generates just as well for SQLite, as for MySQL, as for SQLServer. The model looks like this in LLBLGen: From this model, I created an LLBLGen code-generation template to create the SqlAlchemy model file (1 file per dialect). The model file is essentially a single *.py file which contains Table and Class representations of the diagram above. I use the awesome LINQPad tool to write my LLBLGen templates as it gives me full-intellisense over the LLBLGen SDK API (I created a simple one liner that takes the linqpad file and converts it then to a *.lpt LLBLGen template file). The LLBLGen LINQPad template for the Sql Server dialect looks like this:

/*
* This linqpad file can be used to create LLBLGen templates. When you own a licensed version of Linqpad, you get full
* .NET intellisense, which makes this a great tool to build and leverage the full LLBLGen SDK to generate useful code
* for your project starting from either a database model, or just an entity model.
*/
public void Main(){ LLBLGen.LinqpadExtensions.LptFileGenerator.GenerateLptFile( Util.CurrentQueryPath, Console.Out); }

public Generator _executingGenerator { get; set; }
public EntityDefinition _activeObject { get; set; }
public IDictionary<string, TaskParameter> _parameters { get; set; }

// START HERE – DO NOT ERASE THIS COMMENT LINE
public string GenerateCode() {
    var sb = new StringBuilder();
    sb.Append(
@"#!/usr/bin/env python
# models.py

from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, relation, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mssql import *

Base = declarative_base()

def init_db(engine):
    Base.metadata.create_all(bind=engine)

");
    navnavPairs = new List<string>();
    foreach(var entity in _executingGenerator.Entities.OrderBy(e => e.Name)) {
        sb.AppendLine(BuildEntityTableDefinition(entity));
    }
    foreach(var entity in _executingGenerator.Entities.OrderBy(e => e.Name)) {
        sb.AppendLine(BuildEntityClassDefinition(entity));
    }

sb.AppendLine(@"
def main():
    engine = create_engine('mssql+pyodbc://"+BuildConnectionString()+@"', convert_unicode=True, echo=True)
    init_db(engine)
    db = scoped_session(sessionmaker(bind=engine))
    #try a count on each table to make sure it's setup properly
    counts = [");
foreach(var entity in _executingGenerator.Entities.OrderBy (e => e.Name)) {
    sb.AppendLine("        str(db.query(" + entity.Name + ").count()) + \" " + entity.Name + " records\",");
}
sb.AppendLine(@"    ]
    for c in counts:
        print(c)

if __name__ == '__main__':
  main()
");

    return sb.ToString();
}

List<string> navnavPairs;
public string BuildEntityClassDefinition(EntityDefinition entity) {
    var entityMapping = GetEntityMapping(entity);
    var sb = new StringBuilder();
    sb.AppendFormat("class {0}(Base):\r\n", entity.Name);
    sb.AppendFormat("    __table__ = {0}Table\r\n", entity.Name);
    sb.Append(BuildRelationDefinitions(entity, navnavPairs));
    sb.AppendFormat("\r\n{0}", DotNetTemplateEngine.GetUserCodeRegion("additional_model_information_" + entity.Name.Replace(' ','_').ToLower(), "#").Replace("\t","    "));
    return sb.ToString();
}
public void LogNavNavPair(string entity1, string navigator1, string entity2, string navigator2, StringBuilder sb){
    //sb.AppendLine(string.Format("#LOG: {0}-{1}-{2}-{3}", entity1, navigator1, entity2, navigator2));
}
public bool HasNavNavPairAlreadyBeenMapped(string entity1, string navigator1, string entity2, string navigator2, List<string> navnavPairs) {
    return navnavPairs.Contains(string.Format("{0}-{1}-{2}-{3}", entity1, navigator1, entity2, navigator2)) || navnavPairs.Contains(string.Format("{0}-{1}-{2}-{3}", entity2, navigator2, entity1, navigator1));
}
public void RegisterNavNavPairUniqueIdentifiers(string entity1, string navigator1, string entity2, string navigator2, List<string> navnavPairs) {
    navnavPairs.AddRange(new[]{
        string.Format("{0}-{1}-{2}-{3}", entity1, navigator1, entity2, navigator2),
        string.Format("{0}-{1}-{2}-{3}", entity2, navigator2, entity1, navigator1)
    });
}
public string BuildRelationDefinitions(EntityDefinition entity, List<string> navnavPairs) {
    var entityMapping = GetEntityMapping(entity);
    var tableName = entityMapping.MappedTarget.Name;
    var sb = new StringBuilder();
    var fks = entityMapping.MappedTarget.ForeignKeyConstraints.Where(fkc => !fkc.MarkedForDeletion).OrderBy(fkc => fkc.ConstraintName);
    var allRelationshipInfosToTraverse = GeneratorUtils.GetAllRelationshipInfosForEntity(_executingGenerator, entity);
    var ert = new[]{false, false, false, false};
    var oneToOneAlreadyMapped = false;
    foreach(var relationshipInfo in allRelationshipInfosToTraverse.OrderBy(ri=>ri.Navigator).OrderBy(ri => ri.RelationshipType))
    {
        string property = string.Empty;
        // omit relationships that are flagged in the designer as hidden
        if(relationshipInfo.NavigatorIsHidden) continue;
        List<string> fragments = new List<string>();
        var fkFieldsFromFkSide = new List<FieldElement>();
        if(relationshipInfo.RelationshipType!=EntityRelationshipType.ManyToMany)
            fkFieldsFromFkSide = relationshipInfo.NormalRelationship.GetFkFieldsFromFkSide().ToList();
        switch(relationshipInfo.RelationshipType)
        {
            case EntityRelationshipType.ManyToMany:
                if(!ert[0]){sb.AppendFormat("\r\n    {0}\r\n", "#relation definitions: many to many with backref");ert[0]=true;}
                var relationshipAsMtoM = (IndirectRelationshipEdge)relationshipInfo.Relationship;
                if(!relationshipAsMtoM.IsPureManyToMany) {
                    //sql alchemy uses association objects for M:N relationships that are not pure
                    //TODO: let's implement this in a future version of this template
                    LogNavNavPair(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, sb);
                    oneToOneAlreadyMapped = HasNavNavPairAlreadyBeenMapped(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, navnavPairs);
                    sb.AppendFormat("    {3}{0} = relationship('{1}', backref='{2}')\r\n", relationshipInfo.Navigator, relationshipAsMtoM.DetermineIntermediateEntity().Name, relationshipInfo.OppositeNavigator, (oneToOneAlreadyMapped?"#see backref mapping on "+relationshipInfo.RelatedEntity.Name+" class –> ":""));
                    RegisterNavNavPairUniqueIdentifiers(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, navnavPairs);
                    continue;
                } else {
                    if((relationshipInfo.Relationship as NormalRelationshipEdge) != null && ((NormalRelationshipEdge)relationshipInfo.Relationship).EntityFkSide.IsPureManyToManyIntermediate) {
                        continue;
                    }
                    LogNavNavPair(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, sb);
                    oneToOneAlreadyMapped = HasNavNavPairAlreadyBeenMapped(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, navnavPairs);
                    sb.AppendFormat("    {4}{0} = relationship('{1}', secondary={2}Table, backref='{3}')\r\n", relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipAsMtoM.DetermineIntermediateEntity().Name, relationshipInfo.OppositeNavigator, (oneToOneAlreadyMapped?"#see backref mapping on "+relationshipInfo.RelatedEntity.Name+" class –> ":""));
                    RegisterNavNavPairUniqueIdentifiers(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, navnavPairs);
                }
                break;
            case EntityRelationshipType.OneToOne:
                oneToOneAlreadyMapped = HasNavNavPairAlreadyBeenMapped(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, navnavPairs);
                if(!ert[1]){sb.AppendFormat("\r\n    {0}\r\n", "#relation definitions: one to one with backref");ert[1]=true;}
                sb.AppendFormat("    {3}{0} = relationship('{1}', backref=backref('{2}', uselist=False))\r\n", relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, (oneToOneAlreadyMapped?"#see backref mapping on "+relationshipInfo.RelatedEntity.Name+" class –> ":""));
                RegisterNavNavPairUniqueIdentifiers(relationshipInfo.ForEntity.Name, relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, navnavPairs);
                break;
            case EntityRelationshipType.ManyToOne:
                if(!ert[2]){sb.AppendFormat("\r\n    {0}\r\n", "#relation definitions: many to one with backref (also takes care of one to many)");ert[2]=true;}
                // watch out for self-referential properties
                if(relationshipInfo.ForEntity.Name == relationshipInfo.RelatedEntity.Name) {
                sb.AppendFormat("    {0} = relationship('{1}', primaryjoin=({3}.c.{4}=={3}.c.{5}), remote_side='{1}.{4}', backref='{2}')\r\n",
                    relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator,
                    string.Format("{0}Table", relationshipInfo.ForEntity.Name),
                    GetTableColumnName(entity, relationshipInfo.GetRelationshipFieldPairs().First().Value2 as FieldElement)/*GetFieldMapping(entity, relationshipInfo.GetRelationshipFieldPairs().First().Value2 as FieldElement).MappedTarget.FieldName*/,
                    GetTableColumnName(entity, relationshipInfo.GetRelationshipFieldPairs().First().Value1 as FieldElement)/*GetFieldMapping(entity, relationshipInfo.GetRelationshipFieldPairs().First().Value1 as FieldElement).MappedTarget.FieldName*/);
                } else {
                sb.AppendFormat("    {0} = relationship('{1}', backref='{2}')\r\n", relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator);
                }
                break;
            case EntityRelationshipType.OneToMany:
                if(!ert[3]){sb.AppendFormat("\r\n    {0}\r\n", "#relation definitions: one to many");ert[3]=true;}
                sb.AppendFormat("    {3}{0} = relationship('{1}', backref='{2}')\r\n", relationshipInfo.Navigator, relationshipInfo.RelatedEntity.Name, relationshipInfo.OppositeNavigator, "#see backref mapping on "+relationshipInfo.RelatedEntity.Name+" class –> ");
                break;
        }
    }
    return sb.ToString();
}
public string BuildEntityTableDefinition(EntityDefinition entity) {
    var entityMapping = GetEntityMapping(entity);
    var tableName = entityMapping.MappedTarget.Name;
    var sb = new StringBuilder();
    sb.AppendFormat("{0}Table = Table(u'{1}', Base.metadata,\r\n", entity.Name, tableName);
    sb.AppendFormat("\r\n    {0}\r\n", "#column definitions");
    foreach(FieldElement field in entity.Fields.OrderBy(f => GetFieldMapping(entity, f).MappedTarget.OrdinalPosition))
        sb.AppendFormat("    {0},\r\n", BuildFieldColumnDefinition(entity, field));
    sb.AppendFormat("\r\n    {0}\r\n", "#foreign keys");
    sb.Append(BuildForeignKeyDefinitions(entity));
    sb.AppendFormat("\r\n    {0}\r\n", "#unique constraints");
    sb.Append(BuildUniqueConstraintDefinitions(entity));
    sb.AppendFormat("\r\n    {0}\r\n", "#schema");
    sb.AppendFormat("    schema=\"{0}\"\r\n", entityMapping.MappedTarget.ContainingSchema.SchemaOwner);
    sb.AppendFormat("\r\n{0}", DotNetTemplateEngine.GetUserCodeRegion("additional_table_information_" + tableName.Replace(' ','_').ToLower(), "#").Replace("\t","    "));
    sb.AppendLine(")");
    return sb.ToString();
}
public string BuildFieldColumnDefinition(EntityDefinition entity, FieldElement field){
    var entityMapping = GetEntityMapping(entity);
    var fieldMapping = GetFieldMapping(entity, field);
    var columnName = GetTableColumnName(entity, field)/*fieldMapping.MappedTarget.FieldName*/;
    var sequenceInfo = fieldMapping.SequenceToUse == null ? null: ", Sequence('seq_" + entityMapping.MappedTarget.Name.ToLowerInvariant() + "_" + columnName.ToLowerInvariant() + "')";
    return string.Format("Column(u'{0}', {1}{2}{3}{4})",
        columnName, ProduceSqlAlchemyDbTypeString(fieldMapping), sequenceInfo,
        (field.IsPartOfIdentifyingFields ? ", primary_key=True": ""),
        (!field.IsOptional ? ", nullable=False": "")
    );
}
public string BuildForeignKeyDefinitions(EntityDefinition entity) {
    var entityMapping = GetEntityMapping(entity);
    //var schema = entityMapping.MappedTarget.ContainingSchema.SchemaOwner;
    var sb = new StringBuilder();
    foreach(var fkc in entityMapping.MappedTarget.ForeignKeyConstraints.Where(fkc => !fkc.MarkedForDeletion).OrderBy(fkc => fkc.ConstraintName)) {
        var fkTable = fkc.AppliesToTable;
        var schemaName = fkTable.ContainingSchema.SchemaOwner;
        var pkTable = fkc.PKSide;
        var constraintName = fkc.ConstraintName;
        var fkpkPairs = fkc.GetFkPkFieldPairs();
        sb.AppendFormat("    ForeignKeyConstraint({0},{1},name={2}),\r\n",
            string.Join(", ", fkpkPairs.Select(p=>p.Value1).Select(f=>string.Format("['{0}']", f.FieldName)).ToArray()),
            string.Join(", ", fkpkPairs.Select(p=>p.Value2).Select(f=>string.Format("['{0}']", f.FullName/*.Replace(schemaName + ".","")*/)).ToArray()),
            string.Format("'{0}'", constraintName));
    }
    return sb.ToString();
}
public string BuildUniqueConstraintDefinitions(EntityDefinition entity) {
    var entityMapping = GetEntityMapping(entity);
    var sb = new StringBuilder();
    foreach(var uc in entityMapping.MappedTarget.UniqueConstraints.Where(uc => !uc.MarkedForDeletion).OrderBy(uc => uc.ConstraintName)) {
        var tableName = uc.AppliesToTable.Name;
        var constraintName = uc.ConstraintName;
        sb.AppendFormat("    UniqueConstraint({0},name={1}),\r\n",
            string.Join(", ", uc.Fields.Select(f => string.Format("'{0}'", f.FieldName)).ToArray()),
            string.Format("'{0}'", constraintName));
    }
    return sb.ToString();
}
public EntityMapping GetEntityMapping(EntityDefinition entity) {
    return (EntityMapping)_executingGenerator.ProjectDefinition.GetGroupableModelElementMapping(entity, _executingGenerator.DriverID);
}
public FieldMapping GetFieldMapping(EntityDefinition entity, FieldElement field) {
    return GetEntityMapping(entity).GetFieldMappingOfField(field);
}
public string GetTableColumnName(EntityDefinition entity, FieldElement field) {
    //return field.Name;
    return GetFieldMapping(entity, field).MappedTarget.FieldName;
}
public string ProduceSqlAlchemyDbTypeString(FieldMapping fieldMapping)
{
/*
    from sqlalchemy.dialects.mssql import \\cf0
        BIGINT, BINARY, BIT, CHAR, DATE, DATETIME, DATETIME2, \\cf0
        DATETIMEOFFSET, DECIMAL, FLOAT, IMAGE, INTEGER, MONEY, \\cf0
        NCHAR, NTEXT, NUMERIC, NVARCHAR, REAL, SMALLDATETIME, \\cf0
        SMALLINT, SMALLMONEY, SQL_VARIANT, TEXT, TIME, \\cf0
        TIMESTAMP, TINYINT, UNIQUEIDENTIFIER, VARBINARY, VARCHAR
*/
    var dbTypeAsString = fieldMapping.MappedTarget.DbTypeAsString.ToLowerInvariant();
    var dotNetType = fieldMapping.MappedTarget.TypeDefinition.DBTypeAsNETType;
    var length = fieldMapping.MappedTarget.Length;
    var precision = fieldMapping.MappedTarget.Precision;
    var scale = fieldMapping.MappedTarget.Scale;

    var sb = new StringBuilder();
    switch(dbTypeAsString) {
        case "int":
            sb.Append("INTEGER()");
            break;

        case "ntext":
            sb.AppendFormat("UnicodeText(length={0})", length);
            break;

        case "smalldatetime":
            sb.Append("DateTime()");
            break;

        case "tinyint":
            sb.Append("Integer()");
            break;

        case "binary":
        case "char":
        case "nchar":
        case "nvarchar":
        case "varbinary":
        case "varchar":
            sb.AppendFormat("{0}(length={1})", dbTypeAsString.ToUpper(), length);
            break;

        case "float":
        case "real":
            sb.AppendFormat("{0}(precision={1})", dbTypeAsString.ToUpper(), precision);
            break;

        case "decimal":
        case "numeric":
            sb.AppendFormat("{0}(precision={1}, scale={2})", dbTypeAsString.ToUpper(), precision, scale);
            break;

        //case "bigint":
        //case "bit":
        //case "date":
        //case "datetime":
        //case "datetimeoffset":
        //case "image":
        //case "money":
        //case "smallint":
        //case "smallmoney":
        //case "text":
        //case "time":
        //case "timestamp":
        //case "uniqueidentifier":
        default:
            sb.AppendFormat("{0}()", dbTypeAsString.ToUpper());
            break;
    }
    return sb.ToString();
}
public Project _project { get { return _executingGenerator.ProjectDefinition; } }
public string BuildConnectionString() {
  var csCatalog = _project.GetAllPopulatedCatalogsFromMetaData(_executingGenerator.DriverID).First();
  var csBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(_project.CreateConnectionStringForDatabase(_executingGenerator.DriverID));
  var cs = string.Format("{0}:{1}@{2}/{3}", csBuilder.UserID, (string.IsNullOrEmpty(csBuilder.Password) ? "admin": csBuilder.Password), csBuilder.DataSource, csCatalog.CatalogName);
  return cs;
}

The “models.py” output then looks like this:

#!/usr/bin/env python
# models.py

from sqlalchemy import *
from sqlalchemy.orm import scoped_session, sessionmaker, relation, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.mssql import *

Base = declarative_base()

def init_db(engine):
    Base.metadata.create_all(bind=engine)

CategoryTable = Table(u'Categories', Base.metadata,

    #column definitions
    Column(u'CategoryID', INTEGER(), Sequence('seq_categories_categoryid'), primary_key=True, nullable=False),
    Column(u'CategoryName', NVARCHAR(length=15), nullable=False),
    Column(u'Description', UnicodeText(length=1073741823)),
    Column(u'Picture', IMAGE()),

    #foreign keys

    #unique constraints
    UniqueConstraint('CategoryName',name='CategoryName'),

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_categories
    # __LLBLGENPRO_USER_CODE_REGION_END
)

CustomerTable = Table(u'Customers', Base.metadata,

    #column definitions
    Column(u'CustomerID', NCHAR(length=5), primary_key=True, nullable=False),
    Column(u'CompanyName', NVARCHAR(length=40), nullable=False),
    Column(u'ContactName', NVARCHAR(length=30)),
    Column(u'ContactTitle', NVARCHAR(length=30)),
    Column(u'Address', NVARCHAR(length=60)),
    Column(u'City', NVARCHAR(length=15)),
    Column(u'Region', NVARCHAR(length=15)),
    Column(u'PostalCode', NVARCHAR(length=10)),
    Column(u'Country', NVARCHAR(length=15)),
    Column(u'Phone', NVARCHAR(length=24)),
    Column(u'Fax', NVARCHAR(length=24)),

    #foreign keys

    #unique constraints
    UniqueConstraint('CompanyName',name='CompanyName'),

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_customers
    # __LLBLGENPRO_USER_CODE_REGION_END
)

CustomerCustomerDemoTable = Table(u'CustomerCustomerDemo', Base.metadata,

    #column definitions
    Column(u'CustomerID', NCHAR(length=5), primary_key=True, nullable=False),
    Column(u'CustomerTypeID', NCHAR(length=10), primary_key=True, nullable=False),

    #foreign keys
    ForeignKeyConstraint(['CustomerTypeID'],['dbo.CustomerDemographics.CustomerTypeID'],name='FK_CustomerCustomerDemo'),
    ForeignKeyConstraint(['CustomerID'],['dbo.Customers.CustomerID'],name='FK_CustomerCustomerDemo_Customers'),

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_customercustomerdemo
    # __LLBLGENPRO_USER_CODE_REGION_END
)

CustomerDemographicTable = Table(u'CustomerDemographics', Base.metadata,

    #column definitions
    Column(u'CustomerTypeID', NCHAR(length=10), primary_key=True, nullable=False),
    Column(u'CustomerDesc', UnicodeText(length=1073741823)),

    #foreign keys

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_customerdemographics
    # __LLBLGENPRO_USER_CODE_REGION_END
)

EmployeeTable = Table(u'Employees', Base.metadata,

    #column definitions
    Column(u'EmployeeID', INTEGER(), Sequence('seq_employees_employeeid'), primary_key=True, nullable=False),
    Column(u'LastName', NVARCHAR(length=20), nullable=False),
    Column(u'FirstName', NVARCHAR(length=10), nullable=False),
    Column(u'Title', NVARCHAR(length=30)),
    Column(u'TitleOfCourtesy', NVARCHAR(length=25)),
    Column(u'BirthDate', DATETIME()),
    Column(u'HireDate', DATETIME()),
    Column(u'Address', NVARCHAR(length=60)),
    Column(u'City', NVARCHAR(length=15)),
    Column(u'Region', NVARCHAR(length=15)),
    Column(u'PostalCode', NVARCHAR(length=10)),
    Column(u'Country', NVARCHAR(length=15)),
    Column(u'HomePhone', NVARCHAR(length=24)),
    Column(u'Extension', NVARCHAR(length=4)),
    Column(u'Photo', IMAGE()),
    Column(u'Notes', UnicodeText(length=1073741823)),
    Column(u'ReportsTo', INTEGER()),
    Column(u'PhotoPath', NVARCHAR(length=255)),

    #foreign keys
    ForeignKeyConstraint(['ReportsTo'],['dbo.Employees.EmployeeID'],name='FK_Employees_Employees'),

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_employees
    # __LLBLGENPRO_USER_CODE_REGION_END
)

EmployeeTerritoryTable = Table(u'EmployeeTerritories', Base.metadata,

    #column definitions
    Column(u'EmployeeID', INTEGER(), primary_key=True, nullable=False),
    Column(u'TerritoryID', NVARCHAR(length=20), primary_key=True, nullable=False),

    #foreign keys
    ForeignKeyConstraint(['EmployeeID'],['dbo.Employees.EmployeeID'],name='FK_EmployeeTerritories_Employees'),
    ForeignKeyConstraint(['TerritoryID'],['dbo.Territories.TerritoryID'],name='FK_EmployeeTerritories_Territories'),

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_employeeterritories
    # __LLBLGENPRO_USER_CODE_REGION_END
)

OrderTable = Table(u'Orders', Base.metadata,

    #column definitions
    Column(u'OrderID', INTEGER(), Sequence('seq_orders_orderid'), primary_key=True, nullable=False),
    Column(u'CustomerID', NCHAR(length=5)),
    Column(u'EmployeeID', INTEGER()),
    Column(u'OrderDate', DATETIME()),
    Column(u'RequiredDate', DATETIME()),
    Column(u'ShippedDate', DATETIME()),
    Column(u'ShipVia', INTEGER()),
    Column(u'Freight', MONEY()),
    Column(u'ShipName', NVARCHAR(length=40)),
    Column(u'ShipAddress', NVARCHAR(length=60)),
    Column(u'ShipCity', NVARCHAR(length=15)),
    Column(u'ShipRegion', NVARCHAR(length=15)),
    Column(u'ShipPostalCode', NVARCHAR(length=10)),
    Column(u'ShipCountry', NVARCHAR(length=15)),

    #foreign keys
    ForeignKeyConstraint(['CustomerID'],['dbo.Customers.CustomerID'],name='FK_Orders_Customers'),
    ForeignKeyConstraint(['EmployeeID'],['dbo.Employees.EmployeeID'],name='FK_Orders_Employees'),
    ForeignKeyConstraint(['ShipVia'],['dbo.Shippers.ShipperID'],name='FK_Orders_Shippers'),

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_orders
    # __LLBLGENPRO_USER_CODE_REGION_END
)

OrderDetailTable = Table(u'OrderDetails', Base.metadata,

    #column definitions
    Column(u'OrderID', INTEGER(), primary_key=True, nullable=False),
    Column(u'ProductID', INTEGER(), primary_key=True, nullable=False),
    Column(u'UnitPrice', MONEY(), nullable=False),
    Column(u'Quantity', SMALLINT(), nullable=False),
    Column(u'Discount', REAL(precision=24), nullable=False),

    #foreign keys
    ForeignKeyConstraint(['OrderID'],['dbo.Orders.OrderID'],name='FK_Order_Details_Orders'),
    ForeignKeyConstraint(['ProductID'],['dbo.Products.ProductID'],name='FK_Order_Details_Products'),

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_orderdetails
    # __LLBLGENPRO_USER_CODE_REGION_END
)

ProductTable = Table(u'Products', Base.metadata,

    #column definitions
    Column(u'ProductID', INTEGER(), Sequence('seq_products_productid'), primary_key=True, nullable=False),
    Column(u'ProductName', NVARCHAR(length=40), nullable=False),
    Column(u'SupplierID', INTEGER()),
    Column(u'CategoryID', INTEGER()),
    Column(u'QuantityPerUnit', NVARCHAR(length=20)),
    Column(u'UnitPrice', MONEY()),
    Column(u'UnitsInStock', SMALLINT()),
    Column(u'UnitsOnOrder', SMALLINT()),
    Column(u'ReorderLevel', SMALLINT()),
    Column(u'Discontinued', BIT(), nullable=False),

    #foreign keys
    ForeignKeyConstraint(['CategoryID'],['dbo.Categories.CategoryID'],name='FK_Products_Categories'),
    ForeignKeyConstraint(['SupplierID'],['dbo.Suppliers.SupplierID'],name='FK_Products_Suppliers'),

    #unique constraints
    UniqueConstraint('ProductName',name='ProductName'),

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_products
    # __LLBLGENPRO_USER_CODE_REGION_END
)

RegionTable = Table(u'Region', Base.metadata,

    #column definitions
    Column(u'RegionID', INTEGER(), primary_key=True, nullable=False),
    Column(u'RegionDescription', NCHAR(length=50), nullable=False),

    #foreign keys

    #unique constraints
    UniqueConstraint('RegionDescription',name='IX_Region'),

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_region
    # __LLBLGENPRO_USER_CODE_REGION_END
)

ShipperTable = Table(u'Shippers', Base.metadata,

    #column definitions
    Column(u'ShipperID', INTEGER(), Sequence('seq_shippers_shipperid'), primary_key=True, nullable=False),
    Column(u'CompanyName', NVARCHAR(length=40), nullable=False),
    Column(u'Phone', NVARCHAR(length=24)),

    #foreign keys

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_shippers
    # __LLBLGENPRO_USER_CODE_REGION_END
)

SupplierTable = Table(u'Suppliers', Base.metadata,

    #column definitions
    Column(u'SupplierID', INTEGER(), Sequence('seq_suppliers_supplierid'), primary_key=True, nullable=False),
    Column(u'CompanyName', NVARCHAR(length=40), nullable=False),
    Column(u'ContactName', NVARCHAR(length=30)),
    Column(u'ContactTitle', NVARCHAR(length=30)),
    Column(u'Address', NVARCHAR(length=60)),
    Column(u'City', NVARCHAR(length=15)),
    Column(u'Region', NVARCHAR(length=15)),
    Column(u'PostalCode', NVARCHAR(length=10)),
    Column(u'Country', NVARCHAR(length=15)),
    Column(u'Phone', NVARCHAR(length=24)),
    Column(u'Fax', NVARCHAR(length=24)),
    Column(u'HomePage', UnicodeText(length=1073741823)),

    #foreign keys

    #unique constraints
    UniqueConstraint('CompanyName',name='SupplierName'),

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_suppliers
    # __LLBLGENPRO_USER_CODE_REGION_END
)

TerritoryTable = Table(u'Territories', Base.metadata,

    #column definitions
    Column(u'TerritoryID', NVARCHAR(length=20), primary_key=True, nullable=False),
    Column(u'TerritoryDescription', NCHAR(length=50), nullable=False),
    Column(u'RegionID', INTEGER(), nullable=False),

    #foreign keys
    ForeignKeyConstraint(['RegionID'],['dbo.Region.RegionID'],name='FK_Territories_Region'),

    #unique constraints

    #schema
    schema="dbo"

    # __LLBLGENPRO_USER_CODE_REGION_START additional_table_information_territories
    # __LLBLGENPRO_USER_CODE_REGION_END
)

class Category(Base):
    __table__ = CategoryTable

    #relation definitions: one to many
    #see backref mapping on Product class –> Products = relationship('Product', backref='Category')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_category
    # __LLBLGENPRO_USER_CODE_REGION_END

class Customer(Base):
    __table__ = CustomerTable

    #relation definitions: one to many
    #see backref mapping on CustomerCustomerDemo class –> CustomerCustomerDemos = relationship('CustomerCustomerDemo', backref='Customer')
    #see backref mapping on Order class –> Orders = relationship('Order', backref='Customer')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_customer
    # __LLBLGENPRO_USER_CODE_REGION_END

class CustomerCustomerDemo(Base):
    __table__ = CustomerCustomerDemoTable

    #relation definitions: many to one with backref (also takes care of one to many)
    Customer = relationship('Customer', backref='CustomerCustomerDemos')
    CustomerDemographic = relationship('CustomerDemographic', backref='CustomerCustomerDemos')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_customercustomerdemo
    # __LLBLGENPRO_USER_CODE_REGION_END

class CustomerDemographic(Base):
    __table__ = CustomerDemographicTable

    #relation definitions: one to many
    #see backref mapping on CustomerCustomerDemo class –> CustomerCustomerDemos = relationship('CustomerCustomerDemo', backref='CustomerDemographic')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_customerdemographic
    # __LLBLGENPRO_USER_CODE_REGION_END

class Employee(Base):
    __table__ = EmployeeTable

    #relation definitions: one to many
    #see backref mapping on Employee class –> Employees = relationship('Employee', backref='Manager')
    #see backref mapping on EmployeeTerritory class –> EmployeeTerritories = relationship('EmployeeTerritory', backref='Employee')
    #see backref mapping on Order class –> Orders = relationship('Order', backref='Employee')

    #relation definitions: many to many with backref
    Territories = relationship('Territory', secondary=EmployeeTerritoryTable, backref='Employees')

    #relation definitions: many to one with backref (also takes care of one to many)
    Manager = relationship('Employee', primaryjoin=(EmployeeTable.c.EmployeeID==EmployeeTable.c.ReportsTo), remote_side='Employee.EmployeeID', backref='Employees')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_employee
    # __LLBLGENPRO_USER_CODE_REGION_END

class EmployeeTerritory(Base):
    __table__ = EmployeeTerritoryTable

    #relation definitions: many to one with backref (also takes care of one to many)
    Employee = relationship('Employee', backref='EmployeeTerritories')
    Territory = relationship('Territory', backref='EmployeeTerritories')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_employeeterritory
    # __LLBLGENPRO_USER_CODE_REGION_END

class Order(Base):
    __table__ = OrderTable

    #relation definitions: one to many
    #see backref mapping on OrderDetail class –> OrderDetails = relationship('OrderDetail', backref='Order')

    #relation definitions: many to many with backref
    ProductCollectionViaOrderDetail = relationship('OrderDetail', backref='OrderCollectionViaOrderDetail')

    #relation definitions: many to one with backref (also takes care of one to many)
    Customer = relationship('Customer', backref='Orders')
    Employee = relationship('Employee', backref='Orders')
    Shipper = relationship('Shipper', backref='Orders')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_order
    # __LLBLGENPRO_USER_CODE_REGION_END

class OrderDetail(Base):
    __table__ = OrderDetailTable

    #relation definitions: many to one with backref (also takes care of one to many)
    Order = relationship('Order', backref='OrderDetails')
    Product = relationship('Product', backref='OrderDetails')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_orderdetail
    # __LLBLGENPRO_USER_CODE_REGION_END

class Product(Base):
    __table__ = ProductTable

    #relation definitions: one to many
    #see backref mapping on OrderDetail class –> OrderDetails = relationship('OrderDetail', backref='Product')

    #relation definitions: many to many with backref
    #see backref mapping on Order class –> OrderCollectionViaOrderDetail = relationship('OrderDetail', backref='ProductCollectionViaOrderDetail')

    #relation definitions: many to one with backref (also takes care of one to many)
    Category = relationship('Category', backref='Products')
    Supplier = relationship('Supplier', backref='Products')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_product
    # __LLBLGENPRO_USER_CODE_REGION_END

class Region(Base):
    __table__ = RegionTable

    #relation definitions: one to many
    #see backref mapping on Territory class –> Territories = relationship('Territory', backref='Region')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_region
    # __LLBLGENPRO_USER_CODE_REGION_END

class Shipper(Base):
    __table__ = ShipperTable

    #relation definitions: one to many
    #see backref mapping on Order class –> Orders = relationship('Order', backref='Shipper')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_shipper
    # __LLBLGENPRO_USER_CODE_REGION_END

class Supplier(Base):
    __table__ = SupplierTable

    #relation definitions: one to many
    #see backref mapping on Product class –> Products = relationship('Product', backref='Supplier')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_supplier
    # __LLBLGENPRO_USER_CODE_REGION_END

class Territory(Base):
    __table__ = TerritoryTable

    #relation definitions: one to many
    #see backref mapping on EmployeeTerritory class –> EmployeeTerritories = relationship('EmployeeTerritory', backref='Territory')

    #relation definitions: many to many with backref
    #see backref mapping on Employee class –> Employees = relationship('Employee', secondary=EmployeeTerritoryTable, backref='Territories')

    #relation definitions: many to one with backref (also takes care of one to many)
    Region = relationship('Region', backref='Territories')

    # __LLBLGENPRO_USER_CODE_REGION_START additional_model_information_territory
    # __LLBLGENPRO_USER_CODE_REGION_END

def main():
    engine = create_engine('mssql+pyodbc://admin:admin@localhost/Northwind', convert_unicode=True, echo=True)
    init_db(engine)
    db = scoped_session(sessionmaker(bind=engine))
    #try a count on each table to make sure it's setup properly
    counts = [
        str(db.query(Category).count()) + " Category records",
        str(db.query(Customer).count()) + " Customer records",
        str(db.query(CustomerCustomerDemo).count()) + " CustomerCustomerDemo records",
        str(db.query(CustomerDemographic).count()) + " CustomerDemographic records",
        str(db.query(Employee).count()) + " Employee records",
        str(db.query(EmployeeTerritory).count()) + " EmployeeTerritory records",
        str(db.query(Order).count()) + " Order records",
        str(db.query(OrderDetail).count()) + " OrderDetail records",
        str(db.query(Product).count()) + " Product records",
        str(db.query(Region).count()) + " Region records",
        str(db.query(Shipper).count()) + " Shipper records",
        str(db.query(Supplier).count()) + " Supplier records",
        str(db.query(Territory).count()) + " Territory records",
    ]
    for c in counts:
        print(c)

if __name__ == '__main__':
  main()

To do a basic check and verify that the SqlAlchemy model file doesn’t have any errors in it, a simple count is applied against all entity classes/tables, by running “python models.py”. First against the modelssqlite.py file, which is for the SQLite dialect: That looks good, then against the SQL Server models.py file, which already has data stored in the database: Ok, now with the SqlAlchemy model created, time to start building the “web” infrastructure.

Step 2 – The Tornado Web Server infrastructure

The way I built out the web server infrastructure was to first build the HTML / CSS for a single entity (the Customer entity), then build the LLBLGen templates to generate the whole application. In the end, here is a picture of the set of templates used to build out the application. Here were some specific things I did in the web application design.

• Used Tornado UI modules (similar to Razor views in ASP.NET MVC, or UserControls in ASP.NET Forms) for control blocks
• Created model binder classes for each entity derived from a Base class, with each model binder being knowledgeable of how to bind the model to the request
• Created a very generic handlers.py file to dispatch requests to the appropriate view templates
• Used CSS to generate the image PNG files
• Used jQuery and jQuery validation for client-side validation
• Created a detailed error page that enabled me to parse errors quickly to get to the root-cause of issues as I faced them

In the end, the project structure, as seen in the Komodo IDE looks as follows:

Step 3 – Testing and Playing with the Application in a Browser

To run the application, it’s simply “python app.py” at the command line. And here are some screenshots of the application after it’s code-generated.

Custom error page

Home Page

Listing Records (with sorting, and paging)

Adding a Record (with client-side validation)

Editing a Record (with some jQuery UI)

Deleting a Record

Detailed search

View Record

Step 4 – In Conclusion

Building this little application was a lot of fun. Best of all, there are lots of opportunities for this to grow into something much bigger. Here are some things I take away from this experience:

• Python is awesome (of course)!
• The LLBLGen tool continues to pay major dividends, and this is proof that it isn’t only just for the .NET folks, and for .NET projects
• SqlAlchemy is a rock-solid ORM, and I clearly only tapped the surface of it in developing this app
• The simplicity of the Tornado Web Server / Framework was a real delight to work with, and I can see myself using it for many projects in the future

Feel free to download the code and experiment with it as you see fit. I have to make a disclaimer though, that I have not put this app through the testing ringer (I made it work for the objectives I set out for myself to accomplish); this project was for fun, even though I now think it could become of greater use in the future, but would need some additional work, use at your own risk . [button link="/funcoding/files/2012/06/PyNorthwind.zip" color="primary" target="_blank" size="large" title="Download Code" icon_before="download"]Download Code[/button] [button link="/funcoding/files/2012/06/database.zip" color="primary" target="_blank" size="large" title="Download Code" icon_before="download"]Sql Database[/button] Have fun coding! llblgen, python, sql, sqlalchemy, tornado web server