On 02/14/2016 05:01 PM, Brian Cherinka wrote:
What is the proper way to get pluralized shortened names for
many-to-many tables when using automap? I currently have it set to
generate pluralized lowercase names for collections instead of the
default "_collection". This is what I want for one-to-many or
many-to-one relationships, but not many-to-many. For example, I have
two tables, hdu, and extcol, joined together through a many-to-many
table, hdu_to_extcol
|
create table hdu (pk serial primary key notnull,extname_pk
integer,exttype_pk integer,extno integer,file_pk integer);
create table hdu_to_extcol (pk serial primary key notnull,hdu_pk
integer,extcol_pk integer);
create table extcol (pk serial primary key notnull,name text);
ALTER TABLE ONLY mangadapdb.hdu_to_extcol
ADD CONSTRAINT hdu_fk
FOREIGN KEY (hdu_pk)REFERENCES mangadapdb.hdu(pk)
ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ONLY mangadapdb.hdu_to_extcol
ADD CONSTRAINT extcol_fk
FOREIGN KEY (extcol_pk)REFERENCES mangadapdb.extcol(pk)
ON UPDATE CASCADE ON DELETE CASCADE;
|
When I use SQLalchemy to automap the Base classes, the relationship
this generates on the Hdu and Extcol classes are *Hdu.hdu_to_extcol,
and Extcol.hdu_to_extcols*, using the below pluralize, and
relationship, code. However, ideally what I'd like the names to be
are *Hdu.extcols, and Extcol.hdus*, respectively. What's the best to
generate this for these many-to-many tables? I'm not sure if automap
is recognizing these as many-to-many tables. The direction indicated
when I print during the relationship stage don't indicate as such.
symbol('ONETOMANY') extcol
symbol('MANYTOONE')
hdu_to_extcol
symbol('ONETOMANY') hdu
symbol('MANYTOONE')
hdu_to_extcol
many-to-many is detected based on a specific configuration of table
described at
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#many-to-many-relationships.
The table must have two foreign key constraints set up and all columns
must be members of these constraints. In this case, hdu_to_extcol
contains a surrogate primary key "pk" which disqualifies it as a typical
"many to many" table, and instead automap will assume it's a mapped entity.
To establish these relationships manually you can just add the
relationship() with "secondary" argument that you want onto the
appropriate class which you'd pre-declare. The mappings that automap
generates can just be ignored.
Otherwise, if you really don't want to "pre-declare" but instead have
some kind of lookup table, you can intercept classes as they are mapped
using an event like mapper_configured
(http://docs.sqlalchemy.org/en/rel_1_0/orm/events.html?highlight=orm%20events#sqlalchemy.orm.events.MapperEvents.mapper_configured),
check them against your lookup, and add additional relationships as desired.
automap is intended for the use case of quickly building up a mapping to
get at some existing, possibly legacy, database where it's not really
worth building up complete explicit classes. But because it's designed
for expediency, it isn't intended to deliver perfection. If you have
very specific ORM mapping patterns that you'd like to see on top of
existing tables, the best way to do that is to map them explicitly using
normal Declarative directives. Let automap just fill in all the extra
columns that aren't so important, but structural stuff is going to be
easier just to set up explicitly.
Here is my Base class generation code.
|
def_gen_relationship(base,direction,return_fn,attrname,local_cls,referred_cls,**kw):
iflocal_cls.__table__.name inonetoones:
kw['uselist']=False
# make use of the built-in function to actually return the result.
returngenerate_relationship(base,direction,return_fn,attrname,local_cls,referred_cls,**kw)
_pluralizer =inflect.engine()
defpluralize_collection(base,local_cls,referred_cls,constraint):
referred_name =referred_cls.__name__
uncamelized
=re.sub(r'[A-Z]',lambdam:"_%s"%m.group(0).lower(),referred_name)[1:]
pluralized =_pluralizer.plural(uncamelized)
returnpluralized
# Grabs engine
db =DatabaseConnection()
engine =db.engine
# Selects schema and automaps it.
metadata =MetaData(schema='mangadapdb')
Base=automap_base(bind=engine,metadata=metadata)
Base.prepare(engine,reflect=True,classname_for_table=camelizeClassName,name_for_collection_relationship=pluralize_collection,generate_relationship=_gen_relationship)
# Explicitly declare classes
forcl inBase.classes.keys():
exec('{0} = Base.classes.{0}'.format(cl))
|
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at
