best way to declare one-to-one relationships with automap and non-explicit relationships

[Brian Cherinka]

Hi, 

I'm trying to use automap a schema, and let it generate all classes and relationships between my tables.  It seems to work well for all relationships except for one-to-one.  I know to set a one-to-one relationship, you must apply the uselist=True keyword in relationship().  What's the best way to do that when I'm letting automap generate them?  Without having to manually do it myself by removing the automap generated ones, and setting them explicitly.  Here is what I'm trying so far, but it's not working. 

onetoones = ['file']

def _gen_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw):

    if local_cls.__table__.name in onetoones:

        kw['uselist'] = False

    # make use of the built-in function to actually return the result.

    return generate_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw)

def camelizeClassName(base, tablename, table):

    return str(tablename[0].upper() + re.sub(r'_([a-z])', lambda m: m.group(1).upper(), tablename[1:]))

_pluralizer = inflect.engine()

def pluralize_collection(base, local_cls, referred_cls, constraint):

    referred_name = referred_cls.__name__

    uncamelized = re.sub(r'[A-Z]', lambda m: "_%s" % m.group(0).lower(), referred_name)[1:]

    pluralized = _pluralizer.plural(uncamelized)

    return pluralized

# Grabs engine

db = DatabaseConnection()

engine = db.engine

# Selects schema and automaps it.

metadata = MetaData(schema='mangadapdb')

Base = automap_base(bind=engine, metadata=metadata)

# Pre-define Dap class.  Necessary so automap knows to join this table to a declarative base class from another schema

class Dap(Base):

    __tablename__ = 'dap'

    cube_pk = Column(Integer, ForeignKey(datadb.Cube.pk))

    cube = relationship(datadb.Cube, backref='dap', uselist=False)

# Prepare the base

Base.prepare(engine, reflect=True, classname_for_table=camelizeClassName, name_for_collection_relationship=pluralize_collection, generate_relationship=_gen_relationship)

# Explicitly declare classes

for cl in Base.classes.keys():

    exec('{0} = Base.classes.{0}'.format(cl))

[Michael Bayer]

On Sunday, February 14, 2016 at 4:12:36 PM UTC-5, Brian Cherinka wrote:

Hi, 

I'm trying to use automap a schema, and let it generate all classes and relationships between my tables.  It seems to work well for all relationships except for one-to-one

 I know to set a one-to-one relationship, you must apply the uselist=True keyword in relationship().  What's the best way to do that when I'm letting automap generate them?  Without having to manually do it myself by removing the automap generated ones, and setting them explicitly. 

 

you'd need to implement a generate_relationship function as described at http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#custom-relationship-arguments which applies the rules you want in order to establish those relationships that you'd like to be one-to-one.

 

 Here is what I'm trying so far, but it's not working. 

I don't see anything obviously wrong with it but you'd want to step through with pdb.set_trace() to ensure every aspect of it is doing what you'd expect.   Otherwise "not working" can mean lots of things.

 

[Brian Cherinka]

you'd need to implement a generate_relationship function as described at http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#custom-relationship-arguments which applies the rules you want in order to establish those relationships that you'd like to be one-to-one.

 

Yeah, that's what I tried to do here, but it appeared to do nothing.  That documentation isn't entirely clear I'm afraid.  The direction input doesn't have an interaction.ONETOONE option, so I tried to find an alternative way of identifying which tables needed a one-to-one relationship.  Having a look, I don't think this function works as is because I don't define the pair of tables (from local_cls, referred_cls) that are in a one-to-one.  I only define one in my onetoones list.  

onetoones = ['file']

def _gen_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw):

    if local_cls.__table__.name in onetoones:

        kw['uselist'] = False

    # make use of the built-in function to actually return the result.

    return generate_relationship(base, direction, return_fn, attrname, local_cls, referred_cls, **kw)

 

 

 Here is what I'm trying so far, but it's not working. 

I don't see anything obviously wrong with it but you'd want to step through with pdb.set_trace() to ensure every aspect of it is doing what you'd expect.   Otherwise "not working" can mean lots of things.

 

Ok.  Well I'll keep digging around.  

[Mike Bayer]

On 02/14/2016 06:13 PM, Brian Cherinka wrote:
>
>
>
> you'd need to implement a generate_relationship function as
> described at
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#custom-relationship-arguments
> <http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#custom-relationship-arguments>
> which applies the rules you want in order to establish those
> relationships that you'd like to be one-to-one.
>
> Yeah, that's what I tried to do here, but it appeared to do nothing.
> That documentation isn't entirely clear I'm afraid. The direction
> input doesn't have an interaction.ONETOONE option, so I tried to find an
> alternative way of identifying which tables needed a one-to-one
> relationship. Having a look, I don't think this function works as is
> because I don't define the pair of tables (from local_cls, referred_cls)
> that are in a one-to-one. I only define one in my onetoones list.

OK so if you suggest edits to the documentation here, that would help.
Below is a working example where I basically cut-and-pasted directly
from the documentation example. The import for
"sqlalchemy.orm.interfaces" is missing, otherwise everything works as
advertised just by changing the arguments in the example to match the
use case. Let me know what needs clarification here, thanks!


from sqlalchemy import create_engine

e = create_engine("sqlite://", echo=True)
e.execute("""
create table a (id integer primary key, data varchar)
""")
e.execute("""
create table b (id integer primary key, data varchar,
foreign key (id) references a(id))
""")

# now i cut and paste from the automap docs at
#
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#custom-relationship-arguments

from sqlalchemy.ext.automap import generate_relationship

# this import is missing from the docs, OK.
from sqlalchemy.orm import interfaces

def _gen_relationship(

base, direction, return_fn,
attrname, local_cls, referred_cls, **kw):

if direction is interfaces.ONETOMANY:
# I change this line
kw['uselist'] = False

# default naming scheme for onetomany will
# come up with "<tablename>_collection", so chop that
# off assuming we want to do onetoone
if attrname.endswith("_collection"):
attrname = attrname[:-11]

return generate_relationship(base, direction, return_fn,
attrname, local_cls, referred_cls, **kw)

from sqlalchemy.ext.automap import automap_base

Base = automap_base()

# change this to point to the engine above
Base.prepare(
e, reflect=True,
generate_relationship=_gen_relationship)

# end-cut and paste. now im using it
A = Base.classes.a
B = Base.classes.b

from sqlalchemy.orm import Session


s = Session(e)

s.add(A(data="some a", b=B(data="some b")))

s.commit()

a1 = s.query(A).first()
assert a1.b.data == "some b"

> <http://datadb.Cube.pk>))

> cube = relationship(datadb.Cube, backref='dap', uselist=False)
>
> # Prepare the base
> Base.prepare(engine, reflect=True,
> classname_for_table=camelizeClassName,
> name_for_collection_relationship=pluralize_collection,
> generate_relationship=_gen_relationship)
>
> # Explicitly declare classes
> for cl in Base.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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.