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" <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
