Re: [sqlalchemy] Re: best way to declare one-to-one relationships with automap and non-explicit relationships

[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


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 "_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"







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.




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 

[sqlalchemy] Re: best way to declare one-to-one relationships with automap and non-explicit relationships

[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.

 

>  
>  
>


> 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))
>
>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Re: best way to declare one-to-one relationships with automap and non-explicit relationships

[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.  
 

>  
>
>>  
>>  
>>
>
>
>> 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))
>>
>>

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.