I think that the SQL Alchemy SchemaItem objects can be reasonably code- generated from the schema. I don't feel the same way about the mapped classes: the naming of classes and the naming of the various relations is something that benefits from some thought and design, more than seems practical from a code-generator. I have a rough and ready tool which is a SA wrapper over a centralized set of tables which contain meta-data about *all* dataservers, databases, tables and columns. The schema of the metadata tables is based on the signatures of the ODBC catalog functions. My experience so far (based on a couple of months) is that is *vastly* simpler to code-generate from a relational DB representation of the metadata than it is to code-generate from the catalog functions: the SA model takes care of all the heavy lifting.
I am working on making the code usable with cog (http:// nedbatchelder.com/code/cog) and may make a public release of it at some point. pjjH mapper(Relation, tables, properties = { 'attributes' : relation(Attribute, order_by = asc (Attribute.ORDINAL_POSITION)), 'primary_key' : relation(_PrimaryKey, uselist=False), # At most one PK is allowed. 'indexes' : relation(_Index), 'foreign_keys' : relation(_ForeignKey) }) Here are the Chetah templates that code-generate the Tables, some stub classes and the mapper invocations. $TABLE_NAME = Table('$TABLE_NAME', metadata, #for $column in $attributes Column('$column.COLUMN_NAME', $column.as_alchemy_type_name, nullable=#if $column.nullable#True#else#False#end if#), #end for schema = '$TABLE_SCHEM' ) #if $primary_key ${TABLE_NAME}.append_constraint(PrimaryKeyConstraint( #for e in $primary_key.elements '$e.COLUMN_NAME', #end for ) ) #end if #for $fk in $foreign_keys ${TABLE_NAME}.append_constraint(ForeignKeyConstraint([#for e in fk.elements#'$e.FKCOLUMN_NAME',#end for#], [ #for e in fk.elements '$e.fully_qualified_pk_column', #end for ], name='$e.FK_NAME' ) ) #end for ## Some quasi-arbitrary rules: ## ## . We do not attempt to map tables unless they have a primary key. ## ## . We don't map tables that start with an underscore but instead ## emit a comment to say that they have been ignored. ## ## . The class name is the CamelCase equivalent of the table ## name. Again we assume that the CamelCase equivalent of the table ## name is a valid Python identifier. #if $primary_key #unless $TABLE_NAME.startswith('_') class $camelize($TABLE_NAME)(object): pass mapper($camelize($TABLE_NAME), $TABLE_NAME) #else # ignoring [$TABLE_CAT].[$TABLE_SCHEM].[$TABLE_NAME] as it starts with '_' #end unless #else # ignoring [$TABLE_CAT].[$TABLE_SCHEM].[$TABLE_NAME] as a primary key is not defined for it. #end if # This is what the command-line driver looks like q = session.query(Relation) if (options.dataservers): q = q.filter(Relation.dataserver.in_(options.dataservers)) if (options.databases): q = q.filter(Relation.TABLE_CAT.in_(options.databases)) if (options.schemas): q = q.filter(Relation.TABLE_SCHEM.in_ (options.schemas)) if (options.tables): q = q.filter(Relation.TABLE_NAME.like(options.tables[0])) # XXX: figure out way of passing in multiple patterns # This restricts the query to look at samples with the largest # (i.e. most recent) timestamp. XXX: this may change as I am not # sure how to deal with time/history for this application. q = q.join((Sample, and_(Relation.dataserver == Sample.dataserver, Relation.TABLE_CAT == Sample.CATALOG_NAME, Relation.ts == Sample.ts))) for f in options.templates: for t in q.order_by(desc(Relation.TABLE_NAME)): logging.info(t.TABLE_NAME) template = Template(file=f, searchList=[t,{'camelize': camelize}]) print template On Feb 27, 10:45 am, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > Piotrek Byzia wrote: > > > Hi, > > > I bet that not only me is bored by having to write manually all the SA > > mappings and class definitions. > > there's a tool out there somewhere called "autocode" that does something > like this. > > But I never get bored writing mapped classes. That's the domain model for > my application, and beyond database enabled attributes they have all sorts > of behaviors and constraints that are specific to my application's > in-python behavior. So I'm not familiar with this issue of wanting the > application to be written "automatically". > > If all you need are objects representing tables, that's what Table objects > are for. They do generate from a database automatically. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---