Re: [sqlalchemy] Selectinload doesn't exploit FK

[yoch melka]

I've tested the patch. Works perfectly with my usecase.

Thank you very much !

Le vendredi 19 juillet 2019 20:22:40 UTC+3, Mike Bayer a écrit :
>
>
>
> On Fri, Jul 19, 2019, at 4:40 AM, yoch melka wrote:
>
> Thank you for your help.
>
> I'd be happy to submit a PR (I've already tried to fix this by myself), 
> but the code is a little hard to master.
>
>
>
> I'm glad you tried.The feature is in review at 
> https://gerrit.sqlalchemy.org/#/c/1368/. 
> <https://gerrit.sqlalchemy.org/#/c/1368/>   Of particular importance is 
> that it has a new "degrade" step in the very unlikely case that the foreign 
> key attributes on the parent object are unloaded; rather than emitting a 
> separate query for those,  it goes back to the JOIN.
>
> I would encourage you, if possible, to download the patch for the 1.3 
> series from 
> https://gerrit.sqlalchemy.org/#/c/sqlalchemy/sqlalchemy/+/1369/ and 
> ensure it works and performs as expected for your use case, though a 
> SQLAlchemy release is now overdue and this may go out quickly.
>
>
>
> Le vendredi 19 juillet 2019 01:24:03 UTC+3, Mike Bayer a écrit :
>
>
>
> On Thu, Jul 18, 2019, at 5:02 PM, yoch melka wrote:
>
> Hi,
>
> According to the documentation 
> <https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#select-in-loading>,
>  
> from 1.3 "selectin loading can omit the JOIN for a simple one-to-many 
> collection" in case if the PK is known by the primary request.
>
> But in the opposite case that we know the FK, it's not exploited by 
> Sqlalchemy to produce more effective queries.
>
> Example:
>
> from sqlalchemy import Table, Column, Integer, ForeignKey, create_engine
> from sqlalchemy.orm import relationship, sessionmaker, selectinload
> from sqlalchemy.ext.declarative import declarative_base 
>
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True)
> child_id = Column(Integer, ForeignKey('child.id'))
> child = relationship("Child")
>
> class Child(Base):
> __tablename__ = 'child'
> id = Column(Integer, primary_key=True)
>
>
> engine = create_engine('sqlite://')
> engine.echo = True
>
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
> db=Session()
>
> # create some data
> c1 = Child()
> db.add(c1)
> for n in range(10):
> p = Parent(child=c1)
> db.add(p)
> db.commit()
>
>
> l = db.query(Parent).options(selectinload(Parent.child)).all()
>
> The last instruction above will produce the following two SQL queries :
>
> SELECT parent.id AS parent_id, parent.child_id AS parent_child_id 
> FROM parent
>
> SELECT parent_1.id AS parent_1_id, child.id AS child_id 
> FROM parent AS parent_1 JOIN child ON child.id = parent_1.child_id 
> WHERE parent_1.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
> ORDER BY parent_1.id
>
> But in fact, the ORM have enough information to made the second query 
> simply:
>
> SELECT child.id AS child_id
> FROM child
> WHERE id IN (1)
>
> because we know that all child_id we have in parents are 1.
>
> Is it can be improved ?
>
>
>
> certainly, do you have resources to work on a pull request?  it would 
> require a new method like _init_for_omit_join() [1] as well as an alternate 
> strategy inside of _load_for_path() [2] which loads the related objects, 
> and matches them to the parents, in a different way.
>
> You can get almost the same effect right now, with a little bit less 
> internal effiency,  to just load the Child objects directly in a list, then 
> access the attribute upon each Parent, which loads them from the identity 
> map:
>
> l = db.query(Parent).all()
> c = db.query(Child).filter(Child.id.in_([p.id for p in l])).all()
> for p in l:
> p.child  # no query is emitted
>
>
> a PR for this feature could be informed by a similar strategy, though the 
> "p.child" part should likely use a faster route to populating the attribute 
> directly the way the current strategy does.
>
>
> [1] 
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L2097
> [2] 
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L2196
>
>
>
>
> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> You received this message because you are subscribed

Re: [sqlalchemy] Selectinload doesn't exploit FK

[yoch melka]

Thank you for your help.

I'd be happy to submit a PR (I've already tried to fix this by myself), but 
the code is a little hard to master.

Le vendredi 19 juillet 2019 01:24:03 UTC+3, Mike Bayer a écrit :
>
>
>
> On Thu, Jul 18, 2019, at 5:02 PM, yoch melka wrote:
>
> Hi,
>
> According to the documentation 
> <https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#select-in-loading>,
>  
> from 1.3 "selectin loading can omit the JOIN for a simple one-to-many 
> collection" in case if the PK is known by the primary request.
>
> But in the opposite case that we know the FK, it's not exploited by 
> Sqlalchemy to produce more effective queries.
>
> Example:
>
> from sqlalchemy import Table, Column, Integer, ForeignKey, create_engine
> from sqlalchemy.orm import relationship, sessionmaker, selectinload
> from sqlalchemy.ext.declarative import declarative_base 
>
>
> Base = declarative_base()
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True)
> child_id = Column(Integer, ForeignKey('child.id'))
> child = relationship("Child")
>
> class Child(Base):
> __tablename__ = 'child'
> id = Column(Integer, primary_key=True)
>
>
> engine = create_engine('sqlite://')
> engine.echo = True
>
> Base.metadata.create_all(engine)
>
> Session = sessionmaker(bind=engine)
> db=Session()
>
> # create some data
> c1 = Child()
> db.add(c1)
> for n in range(10):
> p = Parent(child=c1)
> db.add(p)
> db.commit()
>
>
> l = db.query(Parent).options(selectinload(Parent.child)).all()
>
> The last instruction above will produce the following two SQL queries :
>
> SELECT parent.id AS parent_id, parent.child_id AS parent_child_id 
> FROM parent
>
> SELECT parent_1.id AS parent_1_id, child.id AS child_id 
> FROM parent AS parent_1 JOIN child ON child.id = parent_1.child_id 
> WHERE parent_1.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
> ORDER BY parent_1.id
>
> But in fact, the ORM have enough information to made the second query 
> simply:
>
> SELECT child.id AS child_id
> FROM child
> WHERE id IN (1)
>
> because we know that all child_id we have in parents are 1.
>
> Is it can be improved ?
>
>
>
> certainly, do you have resources to work on a pull request?  it would 
> require a new method like _init_for_omit_join() [1] as well as an alternate 
> strategy inside of _load_for_path() [2] which loads the related objects, 
> and matches them to the parents, in a different way.
>
> You can get almost the same effect right now, with a little bit less 
> internal effiency,  to just load the Child objects directly in a list, then 
> access the attribute upon each Parent, which loads them from the identity 
> map:
>
> l = db.query(Parent).all()
> c = db.query(Child).filter(Child.id.in_([p.id for p in l])).all()
> for p in l:
> p.child  # no query is emitted
>
>
> a PR for this feature could be informed by a similar strategy, though the 
> "p.child" part should likely use a faster route to populating the attribute 
> directly the way the current strategy does.
>
>
> [1] 
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L2097
> [2] 
> https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L2196
>
>
>
>
> --
> SQLAlchemy - 
> The Python SQL Toolkit and Object Relational Mapper
>  
> http://www.sqlalchemy.org/
>  
> To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description.
> --- 
> 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 sqlal...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/b199fba3-8aaf-4e23-b406-865c3e57986c%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/b199fba3-8aaf-4e23-b406-865c3e57986c%40googlegroups.com?utm_medium=email_source=footer>
> .
> For more options, visit https://groups.google.com/d/optout.
>
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this mess

[sqlalchemy] Selectinload doesn't exploit FK

[yoch melka]

Hi,

According to the documentation 
,
 
from 1.3 "selectin loading can omit the JOIN for a simple one-to-many 
collection" in case if the PK is known by the primary request.

But in the opposite case that we know the FK, it's not exploited by 
Sqlalchemy to produce more effective queries.

Example:

from sqlalchemy import Table, Column, Integer, ForeignKey, create_engine
from sqlalchemy.orm import relationship, sessionmaker, selectinload
from sqlalchemy.ext.declarative import declarative_base 


Base = declarative_base()

class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
child_id = Column(Integer, ForeignKey('child.id'))
child = relationship("Child")

class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)


engine = create_engine('sqlite://')
engine.echo = True

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
db=Session()

# create some data
c1 = Child()
db.add(c1)
for n in range(10):
p = Parent(child=c1)
db.add(p)
db.commit()


l = db.query(Parent).options(selectinload(Parent.child)).all()

The last instruction above will produce the following two SQL queries :

SELECT parent.id AS parent_id, parent.child_id AS parent_child_id 
FROM parent

SELECT parent_1.id AS parent_1_id, child.id AS child_id 
FROM parent AS parent_1 JOIN child ON child.id = parent_1.child_id 
WHERE parent_1.id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
ORDER BY parent_1.id

But in fact, the ORM have enough information to made the second query 
simply:

SELECT child.id AS child_id
FROM child
WHERE id IN (1)

because we know that all child_id we have in parents are 1.

Is it can be improved ?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/b199fba3-8aaf-4e23-b406-865c3e57986c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to bind all class associated with an engine ?

[yoch . melka]

Oh, it's perfect for me if I can use bases for bindings.

Thank you

Le mardi 6 novembre 2018 21:27:19 UTC+2, Mike Bayer a écrit :
>
> On Tue, Nov 6, 2018 at 2:20 PM > wrote: 
> > 
> > I have many classes, so it's seems a better idea to use something like 
> that (but I don't know what exactly is this registry, and why it's a 
> WeakRef dict) : 
> > 
> > binding = {cls: engine for cls in Base._decl_class_registry.values()} 
>
>
> see I don't want you to use that because it's a private variable.  You 
> should make your own "registry" as you do the automap. 
>
>
> > 
> > If not, maybe I can use the second approach, overloading Session, but is 
> this safe ? 
> > 
> > class BindSession(Session): 
> > def get_bind(self, mapper=None, clause=None): 
> > if mapper: 
> > if issubclass(mapper.class_, SomeBase): 
> > return some_engine 
> > elif issubclass(mapper.class_, OtherBase): 
> > return other_engine 
>
> oh is it based on two different bases?   Guess what, you can use those 
> base classes: 
>
> session = Session(binds={SomeBase: engine1, OtherBase: engine2}) 
>
> docs seem to not be in-depth enough or adequately cross-linked, will fix 
>
>
>
> ># correct ? 
> >return super().get_bind(mapper,clause) 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > Le mardi 6 novembre 2018 18:07:48 UTC+2, Mike Bayer a écrit : 
> >> 
> >> On Tue, Nov 6, 2018 at 9:41 AM  wrote: 
> >> > 
> >> > Hi, 
> >> > 
> >> > I'm using a schema with multiple DB, like : 
> >> > 
> >> > engine1 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1)) 
> >> > engine2 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2)) 
> >> > 
> >> > Then I use automap and reflection to generate mapping of all classes 
> needed. 
> >> > 
> >> > Sometime, I want to use classes from both databases, but I cannot 
> bind my session directly with two engines. 
> >> > 
> >> > I noticed that I can use the Session `binds` argument to decide which 
> class to bind to some engine, but I have many classes to include here, and 
> this is somewhat error prone. 
> >> > 
> >> > Is there any solution to automate the binding definition against each 
> class ? Or to bind a session directly to multiple engines ? 
> >> 
> >> you would use the "binds" argument as you saw in 
> >> 
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.
>  
>
> >> 
> >> as you noticed though, you have to tell the Session which classes map 
> >> to which engine.   So when you use automap, each time you reflect() 
> >> for a particular engine, you need to gather all the classes that were 
> >> generated for that call and add them to a dictionary, which you can 
> >> then pass to session.binds. 
> >> 
> >> If you have some totally other way to tell the Session, given a class, 
> >> which engine to use, you can also make your own method to do whatever 
> >> you want and override it, by overriding get_bind: 
> >> 
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
>  
> >> .you probably don't need to do it this way but there's an example 
> >> of how that looks at 
> >> 
> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.
>  
>
> >> 
> >> 
> >> 
> >> > 
> >> > Thank you 
> >> > 
> >> > 
> >> > -- 
> >> > SQLAlchemy - 
> >> > The Python SQL Toolkit and Object Relational Mapper 
> >> > 
> >> > http://www.sqlalchemy.org/ 
> >> > 
> >> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description. 
> >> > --- 
> >> > 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. 
> >> > To post to this group, send email to sqlal...@googlegroups.com. 
> >> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> >> > For more options, visit https://groups.google.com/d/optout. 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description. 
> > --- 
> > 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 . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

Re: [sqlalchemy] How to bind all class associated with an engine ?

[yoch . melka]

I have many classes, so it's seems a better idea to use something like that 
(but I don't know what exactly is this registry, and why it's a WeakRef 
dict) :

binding = {cls: engine for cls in Base._decl_class_registry.values()}

If not, maybe I can use the second approach, overloading Session, but is 
this safe ?

class BindSession(Session):
def get_bind(self, mapper=None, clause=None):
if mapper:
if issubclass(mapper.class_, SomeBase):
return some_engine
elif issubclass(mapper.class_, OtherBase):
return other_engine
   # correct ?
   return super().get_bind(mapper,clause)








Le mardi 6 novembre 2018 18:07:48 UTC+2, Mike Bayer a écrit :
>
> On Tue, Nov 6, 2018 at 9:41 AM > wrote: 
> > 
> > Hi, 
> > 
> > I'm using a schema with multiple DB, like : 
> > 
> > engine1 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1)) 
> > engine2 = 
> create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2)) 
> > 
> > Then I use automap and reflection to generate mapping of all classes 
> needed. 
> > 
> > Sometime, I want to use classes from both databases, but I cannot bind 
> my session directly with two engines. 
> > 
> > I noticed that I can use the Session `binds` argument to decide which 
> class to bind to some engine, but I have many classes to include here, and 
> this is somewhat error prone. 
> > 
> > Is there any solution to automate the binding definition against each 
> class ? Or to bind a session directly to multiple engines ? 
>
> you would use the "binds" argument as you saw in 
>
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.binds.
>  
>
>
> as you noticed though, you have to tell the Session which classes map 
> to which engine.   So when you use automap, each time you reflect() 
> for a particular engine, you need to gather all the classes that were 
> generated for that call and add them to a dictionary, which you can 
> then pass to session.binds. 
>
> If you have some totally other way to tell the Session, given a class, 
> which engine to use, you can also make your own method to do whatever 
> you want and override it, by overriding get_bind: 
>
> https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.get_bind
>  
> .you probably don't need to do it this way but there's an example 
> of how that looks at 
>
> http://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/.
>  
>
>
>
>
> > 
> > Thank you 
> > 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> description. 
> > --- 
> > 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 . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] How to bind all class associated with an engine ?

[yoch . melka]

Hi,

I'm using a schema with multiple DB, like :

engine1 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db1
))
engine2 = create_engine("mysql://localhost/{db}?charset=utf8".format(db=db2
))

Then I use automap and reflection to generate mapping of all classes needed.

Sometime, I want to use classes from both databases, but I cannot bind my 
session directly with two engines.

I noticed that I can use the Session `binds` argument to decide which class 
to bind to some engine, but I have many classes to include here, and this 
is somewhat error prone. 

Is there any solution to automate the binding definition against each class 
? Or to bind a session directly to multiple engines ?

Thank you


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] How to use labels directly in compare expression

[yoch . melka]

OK, thank you very much !

Le vendredi 22 juin 2018 20:10:53 UTC+3, Mike Bayer a écrit :
>
> Just use literal_column for now , that is the clearest statement of 
> intention , I'll try to find time to look at the code to see if there's 
> some flag I'm forgetting 
>
> On Fri, Jun 22, 2018, 3:08 AM > wrote:
>
>> Yes, I tried that. It produces the following SQL:
>>
>> SELECT json_value(user.meta, :json_value_1) AS code
>> FROM user
>> HAVING json_value(user.meta, :json_value_1) IS NOT NULL
>>
>> This make sense in general, because in MySQL you cannot refer to an alias 
>> in the WHERE part.
>> But I want to refer directly to the alias, which is possible in HAVING 
>> clauses (MySQL extension).
>>
>>
>> Le vendredi 22 juin 2018 03:40:23 UTC+3, Mike Bayer a écrit :
>>>
>>> Did you try using the code object itself?  There's some dialect-specific 
>>> rules for when it rewrites the expression vs.  uses the label name in the 
>>> expression but it may work (am traveling and can't check the code right now)
>>>
>>> On Thu, Jun 21, 2018, 10:10 AM  wrote:
>>>
 Hi,

 I want to translate this MariaDB query to sqlalchemy :

 SELECT JSON_VALUE(user.meta, '$.code') AS code
 FROM user
 HAVING code IS NOT NULL;

 (Note: the use of HAVING is because I want to filter on the alias 
 `code`.)

 I don't find a simple way to use the alias in comparison, currently I 
 wrote this code :

 code = func.json_value(User.meta, '$.code').label('code')
 db.query(code).having(literal_column(code.name)!=None).all()

 Is this correct ? Is there any better way to do that ?

 Thank you

 -- 
 SQLAlchemy - 
 The Python SQL Toolkit and Object Relational Mapper
  
 http://www.sqlalchemy.org/
  
 To post example code, please provide an MCVE: Minimal, Complete, and 
 Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
 description.
 --- 
 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.
 To post to this group, send email to sqlal...@googlegroups.com.
 Visit this group at https://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.

>>> -- 
>> SQLAlchemy - 
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> --- 
>> 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 .
>> To post to this group, send email to sqlal...@googlegroups.com 
>> .
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] How to use labels directly in compare expression

[yoch . melka]

In standard SQL, I might use a subquery with filter:

code = func.json_value(User.meta, '$.code').label('code')
q = db.query(code).subquery()
db.query(q).filter(q.c.code!=None).all()


Le vendredi 22 juin 2018 10:08:04 UTC+3, yoch@gmail.com a écrit :
>
> Yes, I tried that. It produces the following SQL:
>
> SELECT json_value(user.meta, :json_value_1) AS code
> FROM user
> HAVING json_value(user.meta, :json_value_1) IS NOT NULL
>
> This make sense in general, because in MySQL you cannot refer to an alias 
> in the WHERE part.
> But I want to refer directly to the alias, which is possible in HAVING 
> clauses (MySQL extension).
>
>
> Le vendredi 22 juin 2018 03:40:23 UTC+3, Mike Bayer a écrit :
>>
>> Did you try using the code object itself?  There's some dialect-specific 
>> rules for when it rewrites the expression vs.  uses the label name in the 
>> expression but it may work (am traveling and can't check the code right now)
>>
>> On Thu, Jun 21, 2018, 10:10 AM  wrote:
>>
>>> Hi,
>>>
>>> I want to translate this MariaDB query to sqlalchemy :
>>>
>>> SELECT JSON_VALUE(user.meta, '$.code') AS code
>>> FROM user
>>> HAVING code IS NOT NULL;
>>>
>>> (Note: the use of HAVING is because I want to filter on the alias 
>>> `code`.)
>>>
>>> I don't find a simple way to use the alias in comparison, currently I 
>>> wrote this code :
>>>
>>> code = func.json_value(User.meta, '$.code').label('code')
>>> db.query(code).having(literal_column(code.name)!=None).all()
>>>
>>> Is this correct ? Is there any better way to do that ?
>>>
>>> Thank you
>>>
>>> -- 
>>> SQLAlchemy - 
>>> The Python SQL Toolkit and Object Relational Mapper
>>>  
>>> http://www.sqlalchemy.org/
>>>  
>>> To post example code, please provide an MCVE: Minimal, Complete, and 
>>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>>> description.
>>> --- 
>>> 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.
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] How to use labels directly in compare expression

[yoch . melka]

Yes, I tried that. It produces the following SQL:

SELECT json_value(user.meta, :json_value_1) AS code
FROM user
HAVING json_value(user.meta, :json_value_1) IS NOT NULL

This make sense in general, because in MySQL you cannot refer to an alias 
in the WHERE part.
But I want to refer directly to the alias, which is possible in HAVING 
clauses (MySQL extension).


Le vendredi 22 juin 2018 03:40:23 UTC+3, Mike Bayer a écrit :
>
> Did you try using the code object itself?  There's some dialect-specific 
> rules for when it rewrites the expression vs.  uses the label name in the 
> expression but it may work (am traveling and can't check the code right now)
>
> On Thu, Jun 21, 2018, 10:10 AM > wrote:
>
>> Hi,
>>
>> I want to translate this MariaDB query to sqlalchemy :
>>
>> SELECT JSON_VALUE(user.meta, '$.code') AS code
>> FROM user
>> HAVING code IS NOT NULL;
>>
>> (Note: the use of HAVING is because I want to filter on the alias `code`.)
>>
>> I don't find a simple way to use the alias in comparison, currently I 
>> wrote this code :
>>
>> code = func.json_value(User.meta, '$.code').label('code')
>> db.query(code).having(literal_column(code.name)!=None).all()
>>
>> Is this correct ? Is there any better way to do that ?
>>
>> Thank you
>>
>> -- 
>> SQLAlchemy - 
>> The Python SQL Toolkit and Object Relational Mapper
>>  
>> http://www.sqlalchemy.org/
>>  
>> To post example code, please provide an MCVE: Minimal, Complete, and 
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
>> description.
>> --- 
>> 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 .
>> To post to this group, send email to sqlal...@googlegroups.com 
>> .
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] How to use labels directly in compare expression

[yoch . melka]

Hi,

I want to translate this MariaDB query to sqlalchemy :

SELECT JSON_VALUE(user.meta, '$.code') AS code
FROM user
HAVING code IS NOT NULL;

(Note: the use of HAVING is because I want to filter on the alias `code`.)

I don't find a simple way to use the alias in comparison, currently I wrote 
this code :

code = func.json_value(User.meta, '$.code').label('code')
db.query(code).having(literal_column(code.name)!=None).all()

Is this correct ? Is there any better way to do that ?

Thank you

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

OK, thank a lot !

Le jeudi 13 juillet 2017 06:01:45 UTC+3, Mike Bayer a écrit :
>
> this is how that would have to be mapped, hypothetically: 
>
> class EngineerBase(Person): 
> __tablename__ = 'engineer' 
>
> id = Column(ForeignKey('person.id'), primary_key=True) 
> engineer_name = Column(String(30)) 
>
> __mapper_args__ = { 
> 'polymorphic_load': 'selectin' 
> } 
>
>
> class EngineerType1(EngineerBase): 
> __mapper_args__ = { 
> 'polymorphic_identity': 'engineer_t1', 
> 'polymorphic_load': 'inline' 
> } 
>
>
> class EngineerType2(EngineerBase): 
> __mapper_args__ = { 
> 'polymorphic_identity': 'engineer_t2', 
> 'polymorphic_load': 'inline' 
> } 
>
>
> doesn't work of course since the polymorphic selectin load is a new 
> feature trying to work its way into a very intricate workflow.  I 
> threw up 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4026/mixing-selectin-load-on-top-of-inline
>  
> to look into it.  not sure how easy / hard it would be. 
>
> On Wed, Jul 12, 2017 at 7:09 PM,   
> wrote: 
> > Here a MCWE : 
> > 
> > from sqlalchemy import Table, Column, Integer, String, ForeignKey, 
> > create_engine 
> > from sqlalchemy.orm import Session 
> > from sqlalchemy.ext.declarative import declarative_base 
> > 
> > 
> > Base = declarative_base() 
> > 
> > 
> > class Person(Base): 
> > __tablename__ = 'person' 
> > 
> > id = Column(Integer, primary_key=True) 
> > type = Column(String(50), nullable=False) 
> > name = Column(String(50)) 
> > 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'person', 
> > 'polymorphic_on': type 
> > } 
> > 
> > 
> > class Manager(Person): 
> > __tablename__ = 'manager' 
> > 
> > id = Column(ForeignKey('person.id'), primary_key=True) 
> > manager_name = Column(String(30)) 
> > 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'manager', 
> > 'polymorphic_load': 'selectin' 
> > } 
> > 
> > 
> > class EngineerBase(Person): 
> > __tablename__ = 'engineer' 
> > 
> > id = Column(ForeignKey('person.id'), primary_key=True) 
> > engineer_name = Column(String(30)) 
> > 
> > 
> > class EngineerType1(EngineerBase): 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'engineer_t1', 
> > 'polymorphic_load': 'selectin' 
> > } 
> > 
> > 
> > class EngineerType2(EngineerBase): 
> > __mapper_args__ = { 
> > 'polymorphic_identity': 'engineer_t2', 
> > 'polymorphic_load': 'selectin' 
> > } 
> > 
> > 
> > engine = create_engine('sqlite://') 
> > Base.metadata.create_all(engine) 
> > 
> > engine.echo = True 
> > 
> > session = Session(engine) 
> > 
> > eng1 = EngineerType1() 
> > eng2 = EngineerType2() 
> > 
> > session.add_all([eng1, eng2]) 
> > session.commit() 
> > 
> > session.query(Person).all() 
> > 
> > produces the following three queries : 
> > 
> > 2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine SELECT 
> person.id 
> > AS person_id, person.type AS person_type, person.name AS person_name 
> > FROM person 
> > 2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine () 
> > 2017-07-13 00:59:07,248 INFO sqlalchemy.engine.base.Engine SELECT 
> > engineer.id AS engineer_id, person.id AS person_id, person.type AS 
> > person_type 
> > FROM person JOIN engineer ON person.id = engineer.id 
> > WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id 
> > 2017-07-13 00:59:07,249 INFO sqlalchemy.engine.base.Engine (2, 
> > 'engineer_t2') 
> > 2017-07-13 00:59:07,253 INFO sqlalchemy.engine.base.Engine SELECT 
> > engineer.id AS engineer_id, person.id AS person_id, person.type AS 
> > person_type 
> > FROM person JOIN engineer ON person.id = engineer.id 
> > WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id 
> > 2017-07-13 00:59:07,254 INFO sqlalchemy.engine.base.Engine (1, 
> > 'engineer_t1') 
> > 
> > The last two queries can be grouped together. 
> > 
> > Le jeudi 13 juillet 2017 01:45:46 UTC+3, yoch@gmail.com a écrit : 
> >> 
> >> I have a mixed configuration with both joined and single table 
> subclasses 
> >> in a two-levels inheritance (like that), so selectin seems to be the 
> right 
> >> choice for me. 
> >> 
> >> Le jeudi 13 juillet 2017 01:09:50 UTC+3, Mike Bayer a écrit : 
> >>> 
> >>> On Wed, Jul 12, 2017 at 4:54 PM,   wrote: 
> >>> > I noticed that {'polymorphic_load': 'selectin'} on single table 
> >>> > inheritance 
> >>> > can make several SQL queries unnecessarily. 
> >>> 
> >>> well "selectin" loading would be inappropriate for single table 
> >>> inheritance because you are telling it to emit additional queries for 
> >>> additional subclasses, when in reality you'd like the original query 
> >>> just to include all columns. For this reason the traditional 
> >>> "with_polymorphic" approach is more appropriate for eager loading of 
> >>> single table subclasses. 
> >>> 

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

Here a MCWE :

from sqlalchemy import Table, Column, Integer, String, ForeignKey, 
create_engine
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Person(Base):
__tablename__ = 'person'

id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False)
name = Column(String(50))

__mapper_args__ = {
'polymorphic_identity': 'person',
'polymorphic_on': type
}


class Manager(Person):
__tablename__ = 'manager'

id = Column(ForeignKey('person.id'), primary_key=True)
manager_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity': 'manager',
'polymorphic_load': 'selectin'
}


class EngineerBase(Person):
__tablename__ = 'engineer'

id = Column(ForeignKey('person.id'), primary_key=True)
engineer_name = Column(String(30))


class EngineerType1(EngineerBase):
__mapper_args__ = {
'polymorphic_identity': 'engineer_t1',
'polymorphic_load': 'selectin'
}


class EngineerType2(EngineerBase):
__mapper_args__ = {
'polymorphic_identity': 'engineer_t2',
'polymorphic_load': 'selectin'
}


engine = create_engine('sqlite://')
Base.metadata.create_all(engine)

engine.echo = True

session = Session(engine)

eng1 = EngineerType1()
eng2 = EngineerType2()

session.add_all([eng1, eng2])
session.commit()

session.query(Person).all()

produces the following three queries :

2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine SELECT person.id 
AS person_id, person.type AS person_type, person.name AS person_name
FROM person
2017-07-13 00:59:07,243 INFO sqlalchemy.engine.base.Engine ()
2017-07-13 00:59:07,248 INFO sqlalchemy.engine.base.Engine SELECT engineer.id 
AS engineer_id, person.id AS person_id, person.type AS person_type
FROM person JOIN engineer ON person.id = engineer.id
WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id
2017-07-13 00:59:07,249 INFO sqlalchemy.engine.base.Engine (2, 'engineer_t2'
)
2017-07-13 00:59:07,253 INFO sqlalchemy.engine.base.Engine SELECT engineer.id 
AS engineer_id, person.id AS person_id, person.type AS person_type
FROM person JOIN engineer ON person.id = engineer.id
WHERE person.id IN (?) AND person.type IN (?) ORDER BY person.id
2017-07-13 00:59:07,254 INFO sqlalchemy.engine.base.Engine (1, 'engineer_t1'
)

The last two queries can be grouped together.

Le jeudi 13 juillet 2017 01:45:46 UTC+3, yoch@gmail.com a écrit :
>
> I have a mixed configuration with both joined and single table subclasses 
> in a two-levels inheritance (like that 
> ), so 
> selectin seems to be the right choice for me.
>
> Le jeudi 13 juillet 2017 01:09:50 UTC+3, Mike Bayer a écrit :
>>
>> On Wed, Jul 12, 2017 at 4:54 PM,   wrote: 
>> > I noticed that {'polymorphic_load': 'selectin'} on single table 
>> inheritance 
>> > can make several SQL queries unnecessarily. 
>>
>> well "selectin" loading would be inappropriate for single table 
>> inheritance because you are telling it to emit additional queries for 
>> additional subclasses, when in reality you'd like the original query 
>> just to include all columns. For this reason the traditional 
>> "with_polymorphic" approach is more appropriate for eager loading of 
>> single table subclasses. 
>>
>>
>> > 
>> > 
>> > Le mercredi 12 juillet 2017 22:02:04 UTC+3, yoch@gmail.com a écrit 
>> : 
>> >> 
>> >> Very awaited version for me (because the selectin) ! 
>> >> 
>> >> I tested in my code both the eagerload and the polymorphic usages, and 
>> >> everything works perfectly. 
>> >> 
>> >> Thank you Mike 
>> >> 
>> >> Le lundi 10 juillet 2017 16:44:03 UTC+3, Mike Bayer a écrit : 
>> >>> 
>> >>> SQLAlchemy release 1.2.0b1 is now available. 
>> >>> 
>> >>> This is the first beta for the 1.2 series of SQLAlchemy. As is always 
>> >>> the case for beta releases, the release is available on Pypi, but 
>> only 
>> >>> installable when specifying the --pre flag with pip. 
>> >>> 
>> >>> Key highlights of version 1.2 include: 
>> >>> 
>> >>> * Connection pool pre-ping - The connection pool now includes an 
>> >>> optional "pre ping" feature that will test the "liveness" of a pooled 
>> >>> connection for every connection checkout, transparently recycling the 
>> >>> DBAPI connection if the database is disconnected. This feature 
>> >>> eliminates the need for the "pool recycle" flag as well as the issue 
>> >>> of errors raised when a pooled connection is used after a database 
>> >>> restart. 
>> >>> 
>> >>> * New eagerloading features - an all-new eager loader called 
>> >>> "selectin" is added. This loader is similar to "subquery" eager 
>> >>> loading, but foregoes embedding a complex subquery in favor of using 
>> a 
>> >>> simple IN expression which uses the primary key values of the 
>> >>> just-loaded objects to locate related rows. 

Re: [sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

I have a mixed configuration with both joined and single table subclasses 
in a two-levels inheritance (like that 
), so 
selectin seems to be the right choice for me.

Le jeudi 13 juillet 2017 01:09:50 UTC+3, Mike Bayer a écrit :
>
> On Wed, Jul 12, 2017 at 4:54 PM,   
> wrote: 
> > I noticed that {'polymorphic_load': 'selectin'} on single table 
> inheritance 
> > can make several SQL queries unnecessarily. 
>
> well "selectin" loading would be inappropriate for single table 
> inheritance because you are telling it to emit additional queries for 
> additional subclasses, when in reality you'd like the original query 
> just to include all columns. For this reason the traditional 
> "with_polymorphic" approach is more appropriate for eager loading of 
> single table subclasses. 
>
>
> > 
> > 
> > Le mercredi 12 juillet 2017 22:02:04 UTC+3, yoch@gmail.com a écrit 
> : 
> >> 
> >> Very awaited version for me (because the selectin) ! 
> >> 
> >> I tested in my code both the eagerload and the polymorphic usages, and 
> >> everything works perfectly. 
> >> 
> >> Thank you Mike 
> >> 
> >> Le lundi 10 juillet 2017 16:44:03 UTC+3, Mike Bayer a écrit : 
> >>> 
> >>> SQLAlchemy release 1.2.0b1 is now available. 
> >>> 
> >>> This is the first beta for the 1.2 series of SQLAlchemy. As is always 
> >>> the case for beta releases, the release is available on Pypi, but only 
> >>> installable when specifying the --pre flag with pip. 
> >>> 
> >>> Key highlights of version 1.2 include: 
> >>> 
> >>> * Connection pool pre-ping - The connection pool now includes an 
> >>> optional "pre ping" feature that will test the "liveness" of a pooled 
> >>> connection for every connection checkout, transparently recycling the 
> >>> DBAPI connection if the database is disconnected. This feature 
> >>> eliminates the need for the "pool recycle" flag as well as the issue 
> >>> of errors raised when a pooled connection is used after a database 
> >>> restart. 
> >>> 
> >>> * New eagerloading features - an all-new eager loader called 
> >>> "selectin" is added. This loader is similar to "subquery" eager 
> >>> loading, but foregoes embedding a complex subquery in favor of using a 
> >>> simple IN expression which uses the primary key values of the 
> >>> just-loaded objects to locate related rows. This style of loading will 
> >>> perform dramatically better than "subquery" eager loading in many 
> >>> cases. 
> >>> 
> >>> * New polymorphic loading options - A new "selectin" loader is also 
> >>> implemented for polymorphic inheritance hierarchies, which will load 
> >>> the extra table rows for subclasses in a result set all at once, 
> >>> without the need to use JOIN or "with_polymorphic". New declarative 
> >>> options are added to allow per-subclass polymorphic loading 
> >>> configuration as well. 
> >>> 
> >>> * The IN operator now emits a simple expression for empty IN - The 
> >>> long standing behavior of "empty in" resolving to a non-performant 
> >>> expression with a warning has been removed as default behavior; for an 
> >>> IN with no elements, a simple 1 != 1 expression is now used to 
> >>> evaluate to "false". 
> >>> 
> >>> * INSERT..ON DUPLICATE KEY UPDATE support in MySQL - complementing the 
> >>> support for PostgreSQL "INSERT..ON CONFLICT" in 1.1, the MySQL dialect 
> >>> now supports the ON DUPLICATE KEY phrase. Simple database-agnostic 
> >>> "merge" routines can now be composed against these two backends. 
> >>> 
> >>> * COMMENT Support - thanks to a generous contribution, version 1.2 can 
> >>> emit all necessary DDL as well as fully reflect SQL comments for 
> >>> tables and columns against the MySQL, PostgreSQL, and Oracle backends. 
> >>> 
> >>> * SQL expression caching within ORM loaders - the "lazyload" feature 
> >>> as well as the new "selectin" loading now use SQL expression caching 
> >>> using the "baked query" extension when emitting queries, removing a 
> >>> significant bulk of Python overhead from these very common operations. 
> >>> 
> >>> Users should carefully review the "What's New in SQLAlchemy 1.2? 
> >>> document" [1] as well as the Changelog [2] to note which behaviors and 
> >>> issues are affected. We'd like to thank the many contributors who 
> >>> helped with this release. 
> >>> 
> >>> SQLAlchemy 1.2.0b1 is available on the Download Page [3]. 
> >>> 
> >>> [1] http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html 
> >>> [2] http://www.sqlalchemy.org/changelog/CHANGES_1_2_0b1 
> >>> [3] http://www.sqlalchemy.org/download.html 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > You received this message because you are 

[sqlalchemy] Re: SQLAlchemy 1.2.0b1 released

[yoch . melka]

I noticed that {'polymorphic_load': 'selectin'} on single table inheritance 
can make several SQL queries unnecessarily.

Le mercredi 12 juillet 2017 22:02:04 UTC+3, yoch@gmail.com a écrit :
>
> Very awaited version for me (because the selectin) !
>
> I tested in my code both the eagerload and the polymorphic usages, and 
> everything works perfectly.
>
> Thank you Mike
>
> Le lundi 10 juillet 2017 16:44:03 UTC+3, Mike Bayer a écrit :
>>
>> SQLAlchemy release 1.2.0b1 is now available. 
>>
>> This is the first beta for the 1.2 series of SQLAlchemy. As is always 
>> the case for beta releases, the release is available on Pypi, but only 
>> installable when specifying the --pre flag with pip. 
>>
>> Key highlights of version 1.2 include: 
>>
>> * Connection pool pre-ping - The connection pool now includes an 
>> optional "pre ping" feature that will test the "liveness" of a pooled 
>> connection for every connection checkout, transparently recycling the 
>> DBAPI connection if the database is disconnected. This feature 
>> eliminates the need for the "pool recycle" flag as well as the issue 
>> of errors raised when a pooled connection is used after a database 
>> restart. 
>>
>> * New eagerloading features - an all-new eager loader called 
>> "selectin" is added. This loader is similar to "subquery" eager 
>> loading, but foregoes embedding a complex subquery in favor of using a 
>> simple IN expression which uses the primary key values of the 
>> just-loaded objects to locate related rows. This style of loading will 
>> perform dramatically better than "subquery" eager loading in many 
>> cases. 
>>
>> * New polymorphic loading options - A new "selectin" loader is also 
>> implemented for polymorphic inheritance hierarchies, which will load 
>> the extra table rows for subclasses in a result set all at once, 
>> without the need to use JOIN or "with_polymorphic". New declarative 
>> options are added to allow per-subclass polymorphic loading 
>> configuration as well. 
>>
>> * The IN operator now emits a simple expression for empty IN - The 
>> long standing behavior of "empty in" resolving to a non-performant 
>> expression with a warning has been removed as default behavior; for an 
>> IN with no elements, a simple 1 != 1 expression is now used to 
>> evaluate to "false". 
>>
>> * INSERT..ON DUPLICATE KEY UPDATE support in MySQL - complementing the 
>> support for PostgreSQL "INSERT..ON CONFLICT" in 1.1, the MySQL dialect 
>> now supports the ON DUPLICATE KEY phrase. Simple database-agnostic 
>> "merge" routines can now be composed against these two backends. 
>>
>> * COMMENT Support - thanks to a generous contribution, version 1.2 can 
>> emit all necessary DDL as well as fully reflect SQL comments for 
>> tables and columns against the MySQL, PostgreSQL, and Oracle backends. 
>>
>> * SQL expression caching within ORM loaders - the "lazyload" feature 
>> as well as the new "selectin" loading now use SQL expression caching 
>> using the "baked query" extension when emitting queries, removing a 
>> significant bulk of Python overhead from these very common operations. 
>>
>> Users should carefully review the "What's New in SQLAlchemy 1.2? 
>> document" [1] as well as the Changelog [2] to note which behaviors and 
>> issues are affected. We'd like to thank the many contributors who 
>> helped with this release. 
>>
>> SQLAlchemy 1.2.0b1 is available on the Download Page [3]. 
>>
>> [1] http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html 
>> [2] http://www.sqlalchemy.org/changelog/CHANGES_1_2_0b1 
>> [3] http://www.sqlalchemy.org/download.html 
>>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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: SQLAlchemy 1.2.0b1 released

[yoch . melka]

Very awaited version for me (because the selectin) !

I tested in my code both the eagerload and the polymorphic usages, and 
everything works perfectly.

Thank you Mike

Le lundi 10 juillet 2017 16:44:03 UTC+3, Mike Bayer a écrit :
>
> SQLAlchemy release 1.2.0b1 is now available. 
>
> This is the first beta for the 1.2 series of SQLAlchemy. As is always 
> the case for beta releases, the release is available on Pypi, but only 
> installable when specifying the --pre flag with pip. 
>
> Key highlights of version 1.2 include: 
>
> * Connection pool pre-ping - The connection pool now includes an 
> optional "pre ping" feature that will test the "liveness" of a pooled 
> connection for every connection checkout, transparently recycling the 
> DBAPI connection if the database is disconnected. This feature 
> eliminates the need for the "pool recycle" flag as well as the issue 
> of errors raised when a pooled connection is used after a database 
> restart. 
>
> * New eagerloading features - an all-new eager loader called 
> "selectin" is added. This loader is similar to "subquery" eager 
> loading, but foregoes embedding a complex subquery in favor of using a 
> simple IN expression which uses the primary key values of the 
> just-loaded objects to locate related rows. This style of loading will 
> perform dramatically better than "subquery" eager loading in many 
> cases. 
>
> * New polymorphic loading options - A new "selectin" loader is also 
> implemented for polymorphic inheritance hierarchies, which will load 
> the extra table rows for subclasses in a result set all at once, 
> without the need to use JOIN or "with_polymorphic". New declarative 
> options are added to allow per-subclass polymorphic loading 
> configuration as well. 
>
> * The IN operator now emits a simple expression for empty IN - The 
> long standing behavior of "empty in" resolving to a non-performant 
> expression with a warning has been removed as default behavior; for an 
> IN with no elements, a simple 1 != 1 expression is now used to 
> evaluate to "false". 
>
> * INSERT..ON DUPLICATE KEY UPDATE support in MySQL - complementing the 
> support for PostgreSQL "INSERT..ON CONFLICT" in 1.1, the MySQL dialect 
> now supports the ON DUPLICATE KEY phrase. Simple database-agnostic 
> "merge" routines can now be composed against these two backends. 
>
> * COMMENT Support - thanks to a generous contribution, version 1.2 can 
> emit all necessary DDL as well as fully reflect SQL comments for 
> tables and columns against the MySQL, PostgreSQL, and Oracle backends. 
>
> * SQL expression caching within ORM loaders - the "lazyload" feature 
> as well as the new "selectin" loading now use SQL expression caching 
> using the "baked query" extension when emitting queries, removing a 
> significant bulk of Python overhead from these very common operations. 
>
> Users should carefully review the "What's New in SQLAlchemy 1.2? 
> document" [1] as well as the Changelog [2] to note which behaviors and 
> issues are affected. We'd like to thank the many contributors who 
> helped with this release. 
>
> SQLAlchemy 1.2.0b1 is available on the Download Page [3]. 
>
> [1] http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html 
> [2] http://www.sqlalchemy.org/changelog/CHANGES_1_2_0b1 
> [3] http://www.sqlalchemy.org/download.html 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] Is this a valid inheritance configuration ?

[yoch . melka]

Thank you Mike !

Le mardi 20 juin 2017 21:23:01 UTC+3, Mike Bayer a écrit :
>
>
>
> On 06/20/2017 02:08 PM, yoch@gmail.com  wrote: 
> > Hi, 
> > 
> > I wish to have a two-levels inheritance, but I don't know how to 
> proceed. 
> > The docs says that "only one discriminator column or SQL expression may 
> > be configured for the entire inheritance hierarchy". 
> > 
> > I tried with this example (mixing joined and single inheritances) : 
> > 
> > | 
> > classPerson(Base): 
> >  __tablename__ ='person' 
> > 
> >  id =Column(Integer,primary_key=True) 
> >  type =Column(String(50),nullable=False) 
> >  name =Column(String(50)) 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'person', 
> > 'polymorphic_on':type 
> > } 
> > 
> > classManager(Person): 
> >  __tablename__ ='manager' 
> > 
> >  id =Column(ForeignKey('person.id'),primary_key=True) 
> >  manager_name =Column(String(30)) 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'manager', 
> > } 
> > 
> > classEngineerBase(Person): 
> >  __tablename__ ='engineer' 
> > 
> >  id =Column(ForeignKey('person.id'),primary_key=True) 
> >  engineer_name =Column(String(30)) 
> > 
> > classEngineerType1(EngineerBase): 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'engineer_t1', 
> > } 
> > 
> > classEngineerType2(EngineerBase): 
> > 
> >  __mapper_args__ ={ 
> > 'polymorphic_identity':'engineer_t2', 
> > } 
> > | 
> > 
> > It seems to work, but it this correct ? 
>
> it's correct, you only have one "polymorphic_on" set up, and that's what 
> that means. 
>
>
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > 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  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Is this a valid inheritance configuration ?

[yoch . melka]

Hi,

I wish to have a two-levels inheritance, but I don't know how to proceed.
The docs says that "only one discriminator column or SQL expression may be 
configured for the entire inheritance hierarchy".

I tried with this example (mixing joined and single inheritances) :

class Person(Base):
__tablename__ = 'person'

id = Column(Integer, primary_key=True)
type = Column(String(50), nullable=False)
name = Column(String(50))

__mapper_args__ = {
'polymorphic_identity': 'person',
'polymorphic_on': type
}

class Manager(Person):
__tablename__ = 'manager'

id = Column(ForeignKey('person.id'), primary_key=True)
manager_name = Column(String(30))

__mapper_args__ = {
'polymorphic_identity': 'manager',
}

class EngineerBase(Person):
__tablename__ = 'engineer'

id = Column(ForeignKey('person.id'), primary_key=True)
engineer_name = Column(String(30))

class EngineerType1(EngineerBase):

__mapper_args__ = {
'polymorphic_identity': 'engineer_t1',
}

class EngineerType2(EngineerBase):

__mapper_args__ = {
'polymorphic_identity': 'engineer_t2',
}

It seems to work, but it this correct ?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

[yoch . melka]

Thank you Mike for this detailled response !

The UniqueObject recipe is very interesting, but not very suitable to my 
case because my webservice don't use the same session on each Post 
insertion.

Maybe a session.merge_all() method can help to improve performance in such 
cases by grouping the underlying SQL queries (only one SELECT and one 
INSERT) for all instances.


Le mardi 23 mai 2017 19:48:11 UTC+3, Mike Bayer a écrit :
>
> we don't have ON DUPLICATE KEY UPDATE for MySQL as of yet however there 
> is a PR that I will attend to at some point for possible 1.2 inclusion. 
>
> However, note you can just as well just emit the SQL string for ON 
> DUPLCIATE KEY UPDATE if you're only targeting MySQL.   Then just create 
> the object you need that corresponds to this row, then use 
> make_transient_to_detached() -> session.add() to make it happen (see 
> example below). 
>
> Theoretically, the functionality of session.merge() could provide an 
> alternate form that makes use of ON DUPLICATE KEY UPDATE automatically 
> however for the foreseeable future, you'd need to roll this yourself 
> once you make use of the SQL statement. 
>
> The traditional way to handle this is largely like what you have except 
> that client-side caching is used to make it more efficient.   One 
> example is 
> https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject, 
> which caches per single object.   Other ways include just selecting a 
> whole batch of objects based on keys you know you will be working with 
> up front. 
>
>
> from sqlalchemy import * 
> from sqlalchemy.orm import * 
> from sqlalchemy.ext.declarative import declarative_base 
>
> Base = declarative_base() 
>
> post_tags = Table( 
>  "post_tags", Base.metadata, 
>  Column("postid", ForeignKey('post.id'), primary_key=True), 
>  Column("tagid", ForeignKey('tag.key'), primary_key=True) 
> ) 
>
>
> class Post(Base): 
>  __tablename__ = 'post' 
>
>  id = Column(Integer, primary_key=True) 
>  _tags = relationship('Tag', secondary='post_tags') 
>
>  @property 
>  def tags(self): 
>  return ';'.join(tag.key for tag in self._tags) 
>
>  @tags.setter 
>  def tags(self, s): 
>  lst = [Tag(key=tag) for tag in s.split(';')] 
>  self._tags = lst 
>
>
> class Tag(Base): 
>  __tablename__ = 'tag' 
>
>  key = Column(String(40), primary_key=True) 
>  count = Column(Integer) 
>
> e = create_engine("mysql://scott:tiger@localhost/test", echo=True) 
> Base.metadata.drop_all(e) 
> Base.metadata.create_all(e) 
> s = Session(e) 
>
>
> def make_tag(name): 
>  s.execute( 
>  "INSERT INTO tag (`key`, `count`) VALUES (:name, 1) " 
>  "ON DUPLICATE KEY UPDATE count = count + 1", 
>  {"name": name} 
>  ) 
>  tag_obj = Tag(key=name) 
>  make_transient_to_detached(tag_obj) 
>  return s.merge(tag_obj, load=False) 
>
>
> s.add(Post(_tags=[make_tag("tag_a")])) 
> s.commit() 
>
> s.add(Post(_tags=[make_tag("tag_a")])) 
> s.commit() 
>
> assert s.query(Tag).count() == 1 
>
>
>
> On 05/22/2017 09:59 AM, yoch@gmail.com  wrote: 
> > Hi, 
> > 
> > I'm trying to create a tag system with a many-to-many relationship 
> > approach, and I have problems with the updating phase. 
> > 
> > | 
> > classPost(Base): 
> >  __tablename__ ='post' 
> > 
> >  id =Column(Integer,primary_key=True) 
> >  _tags =relationship('Tag',secondary='post_tags') 
> > 
> > @property 
> > deftags(self): 
> > return';'.join(tag.key fortag inself._tags) 
> > 
> > @tags.setter 
> > deftags(self,s): 
> >  lst =[Tag(key=tag)fortag ins.split(';')] 
> > self._tags =lst 
> > 
> > classTag(Base): 
> >  __tablename__ ='tag' 
> > 
> >  key =Column(String(40),primary_key=True) 
> >  count =Column(Integer) 
> > | 
> > 
> > 
> > And the `post_tags` is defined as : 
> > 
> > | 
> > CREATE TABLE `post_tags`( 
> > `idpost`INT(10)UNSIGNED NOT NULL, 
> > `tag`VARCHAR(40)NOT NULL, 
> >   PRIMARY KEY (`idpost`,`tag`), 
> >   INDEX `FK_post_tags`(`tag`), 
> >   FOREIGN KEY (`tag`)REFERENCES `tag`(`key`), 
> >   FOREIGN KEY (`idpost`)REFERENCES `post`(`id`)ON DELETE CASCADE 
> > ); 
> > | 
> > 
> > 
> > The problem is that when adding tags that already exists in the `tag` 
> table 
> > 
> > | 
> > post.tags ='a'# tag 'a' already created 
> > | 
> > 
> > it produces this exception : sqlalchemy.orm.exc.FlushError: New instance 
> >  with identity key (, 
> > ('a',)) conflicts with persistent instance  
> > 
> > 
> > Ideally, I'd like to be able to produce a query like (MySQL) : 
> > 
> > | 
> > INSERT INTO tag (`key`,`count`)VALUES (%s,1)ON DUPLICATE KEY UPDATE 
> > count =count +1 
> > | 
> > 
> > Instead that, the way I found to do seems much less efficient : 
> > 
> > | 
> > @tags.setter 
> > deftags(self,s): 
> >  sess =object_session(self) 
> >  lst =[sess.merge(Tag(key=tag))fortag ins.split(';')] 
> > self._tags =lst 
> > | 
> > 
> > (and the counter is managed by a 

[sqlalchemy] many-to-many relationship : how to update items properly if there are duplicates entries ?

[yoch . melka]

Hi,

I'm trying to create a tag system with a many-to-many relationship 
approach, and I have problems with the updating phase.

class Post(Base):
__tablename__ = 'post'

id = Column(Integer, primary_key=True)
_tags = relationship('Tag', secondary='post_tags')

@property
def tags(self):
return ';'.join(tag.key for tag in self._tags)

@tags.setter
def tags(self, s):
lst = [Tag(key=tag) for tag in s.split(';')]
self._tags = lst

class Tag(Base):
__tablename__ = 'tag'

key = Column(String(40), primary_key=True)
count = Column(Integer)


And the `post_tags` is defined as :

CREATE TABLE `post_tags` (
 `idpost` INT(10) UNSIGNED NOT NULL,
 `tag` VARCHAR(40) NOT NULL,
 PRIMARY KEY (`idpost`, `tag`),
 INDEX `FK_post_tags` (`tag`),
 FOREIGN KEY (`tag`) REFERENCES `tag` (`key`),
 FOREIGN KEY (`idpost`) REFERENCES `post` (`id`) ON DELETE CASCADE
);


The problem is that when adding tags that already exists in the `tag` table

post.tags = 'a'  # tag 'a' already created

it produces this exception : sqlalchemy.orm.exc.FlushError: New instance 
 with identity key (, ('a',)) 
conflicts with persistent instance 


Ideally, I'd like to be able to produce a query like (MySQL) :

INSERT INTO tag (`key`, `count`) VALUES (%s, 1) ON DUPLICATE KEY UPDATE 
count = count + 1

Instead that, the way I found to do seems much less efficient :

@tags.setter
def tags(self, s):
sess = object_session(self)
lst = [sess.merge(Tag(key=tag)) for tag in s.split(';')]
self._tags = lst

(and the counter is managed by a trigger on `post_tags` INSERT)

Is there another way to do that properly and efficiently ?

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] bulk update with inheritance : weird behavior

[yoch . melka]

Thank you Mike for this clear explanation !

Le jeudi 4 mai 2017 16:41:37 UTC+3, Mike Bayer a écrit :
>
>
> > 
> > In my real use case, we have to update both parent and child columns, so 
> > I want to use the Children class. 
>
> so SQLite won't support that (nor will Postgresql or most other DBs). 
> Not possible with standard SQL.   Only MySQL's goofy syntax supports 
> UPDATE where values are modified in multiple tables at once and you need 
> to ensure your query uses filter() to provide the right ON clause. 
>
> note this is *different* from the case where you want to UPDATE values 
> that are only in *one* of the tables, but you still need both tables to 
> find the row.  In that case, Postgresql also supports having multiple 
> tables referred to in an UPDATE.   But still not SQLite.  For SQLite and 
> other databases, the table that's not the target of the update needs to 
> be in a correlated subquery. 
>
> I've updated your test with the final query against the two tables to 
> work on MySQL: 
>
> from sqlalchemy import Column, Integer, String, ForeignKey, create_engine 
> from sqlalchemy.orm import relationship, Session 
> from sqlalchemy.ext.declarative import declarative_base 
>
>
> Base = declarative_base() 
>
>
> class Person(Base): 
>  __tablename__ = 'person' 
>  id = Column(Integer, primary_key=True) 
>  name = Column(String(50)) 
>  type = Column(String(50)) 
>  __mapper_args__ = { 
>  'polymorphic_identity':'person', 
>  'polymorphic_on':type 
>  } 
>
> class Engineer(Person): 
>  __tablename__ = 'engineer' 
>  id = Column(Integer, ForeignKey('person.id'), primary_key=True) 
>  status = Column(String(30)) 
>  __mapper_args__ = { 
>  'polymorphic_identity':'engineer', 
>  } 
>
>
> engine = create_engine("mysql://scott:tiger@localhost/test", echo=True) 
>
> Base.metadata.drop_all(engine) 
> Base.metadata.create_all(engine) 
>
>
> if __name__ == '__main__': 
>  session = Session(engine) 
>  engineer = Engineer(name='me', status='working') 
>
>  # populates 
>  session.add(engineer) 
>  session.commit() 
>
>  session.query(Engineer).filter(Engineer.id == Person.id).\ 
>  filter(Engineer.status == 'working').\ 
>  update({'name': 'bar', 'status': 'done'}) 
>
>
>
> The query at the end comes out as: 
>
> UPDATE engineer, person SET person.name=%s, engineer.status=%s WHERE 
> engineer.id = person.id AND engineer.status = %s 
>
> ('bar', 'done', 'working') 
>
>
> that's totally a MySQL thing.   (also I'd love to see another SQL 
> expression language / ORM anywhere that supports MySQL's multi-table 
> UPDATE :) ) 
>
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] bulk update with inheritance : weird behavior

[yoch . melka]

Le jeudi 4 mai 2017 16:07:22 UTC+3, Mike Bayer a écrit :
>
>
>
> On 05/04/2017 08:41 AM, yoch@gmail.com  wrote: 
> > Hi, 
> > 
> > I'm facing to a strange behavior with bulk update on inherited class. 
> > 
> > Is this a bug ? 
>
> it's not. 
>
>
> http://docs.sqlalchemy.org/en/latest/orm/query.html?highlight=query%20update#sqlalchemy.orm.query.Query.update
>  
> 
>  
>
>
>
> **Warning** 
>
> The Query.update() method is a “bulk” operation, which bypasses ORM 
> unit-of-work automation in favor of greater performance. **Please read 
> all caveats and warnings below.** 
>
> ... below  
>
>
> * The method supports multiple table updates, as detailed in Multiple 
> Table Updates, and this behavior does extend to support updates of 
> joined-inheritance and other multiple table mappings. However, the join 
> condition of an inheritance mapper is not automatically rendered. Care 
> must be taken in any multiple-table update to explicitly include the 
> joining condition between those tables, even in mappings where this is 
> normally automatic. E.g. if a class Engineer subclasses Employee, an 
> UPDATE of the Engineer local table using criteria against the Employee 
> local table might look like: 
>
> session.query(Engineer).\ 
>  filter(Engineer.id == Employee.id).\ 
>  filter(Employee.name == 'dilbert').\ 
>  update({"engineer_type": "programmer"}) 
>

Thank you for this response.

In your case, your query is only targeting columns in the base "person" 
> table.   So this is not really a multiple table update and instead of 
> asking it for query(Engineer) you should be asking for 
> query(Person).filter(Person.type == 'engineer'). 
>
 
In my real use case, we have to update both parent and child columns, so I 
want to use the Children class.

 

>
> > 
> > Best regards, 
> > yoch 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > 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  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] bulk update with inheritance : weird behavior

[yoch . melka]

Hi,

I'm facing to a strange behavior with bulk update on inherited class.

These two queries work differently :

from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import relationship, Session
from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'person',
'polymorphic_on':type
}

class Engineer(Person):
__tablename__ = 'engineer'
id = Column(Integer, ForeignKey('person.id'), primary_key=True)
status = Column(String(30))
__mapper_args__ = {
'polymorphic_identity':'engineer',
}


#engine = create_engine('sqlite://', echo=True)
engine = create_engine("mysql://*/testing", echo=True)

Base.metadata.create_all(engine)


if __name__ == '__main__':
session = Session(engine)
engineer = Engineer(name='me', status='working')

# populates
session.add(engineer)
session.commit()

# raise : Unconsumed column names: name
try:
session.query(Engineer).filter(Engineer.name=='bar').update({'name': 
'baz'})
session.commit()
except Exception as err:
print(err)

# with MySQL engine , produce : 'UPDATE engineer, person SET 
person.name=%s WHERE person.name = %s'
# with SQLite engine, produce : 'UPDATE engineer SET name=? FROM person 
WHERE person.name = ?' [Syntax Error]
try:
session.query(Engineer).filter(Engineer.status=='working').update({
'name': 'bar'})
session.commit()
except Exception as err:
print(err)

The former query fails, presumably because sqlalchemy don't care with the 
parent class.
The later is handled correctly with MySQL (maybe because the fillter 
involve Person), but no with SQLite.

(I also tried with_polymorphic, but it doesn't solve the problem).

Is this a bug ?

Best regards,
yoch

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] automap with self-referential relationship

[yoch . melka]

Thank tou, I solved the problem with this code :

def name_for_collection_relationship(base, local_cls, referred_cls, 
constraint):
disc = '_'.join(col.name for col in constraint.columns)
return referred_cls.__name__.lower() + '_' + disc + "_collection"



Le lundi 3 avril 2017 17:41:20 UTC+3, Mike Bayer a écrit :
>
> hi - 
>
> this is perhaps a case automap should come up with something easier for, 
> and at least have a note in the docs that refers to this specifically, 
> however the general approach is to apply a naming convention to the 
> relationships that will allow the conflict to resolve.   you'd define 
> name_for_collection_relationship, following th guidelines in 
> the section: 
>
> http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html?highlight=automap#handling-simple-naming-conflicts
>  
>
>
> def name_for_collection_relationship(base, local_cls, referred_cls, 
> constraint): 
>  disc = constraint.columns[0].name 
>  return referred_cls.__name__.lower() + disc + "_collection" 
>
>
>
>
> On 04/02/2017 02:32 PM, yoch@gmail.com  wrote: 
> > Hi, 
> > 
> > 
> > I want to use automap to generate mapping from existing database, but 
> > the relation mapping fails. 
> > I understand what happens, but I can't find a proper workaround to fix 
> > this error. 
> > 
> > 
> > Here a minimal code example with the minimal schema : 
> > 
> > CREATE TABLE `user` ( 
> > `id` INT UNSIGNED NOT NULL, 
> > PRIMARY KEY (`id`) 
> > ) ENGINE=InnoDB; 
> > 
> > CREATE TABLE `contact` ( 
> > `iduser` INT UNSIGNED NOT NULL, 
> > `idcontact` INT UNSIGNED NOT NULL, 
> > PRIMARY KEY (`iduser`, `idcontact`), 
> > FOREIGN KEY (`iduser`) REFERENCES `user` (`id`), 
> > FOREIGN KEY (`idcontact`) REFERENCES `user` (`id`) 
> > ) ENGINE=InnoDB; 
> > 
> > 
> > The minimal code is : 
> > 
> > Base = automap_base() 
> > Base.prepare(engine, reflect=True) 
> > configure_mappers() 
> > 
> > which fails with this backtrace : 
> > 
> > Traceback (most recent call last): 
> >   File "", line 1, in  
> >   File "testing.py", line 11, in  
> > configure_mappers() 
> >   File "../site-packages/sqlalchemy/orm/mapper.py", line 2866, in 
> > configure_mappers 
> > mapper._post_configure_properties() 
> >   File "../site-packages/sqlalchemy/orm/mapper.py", line 1765, in 
> > _post_configure_properties 
> > prop.init() 
> >   File "../site-packages/sqlalchemy/orm/interfaces.py", line 184, in 
> init 
> > self.do_init() 
> >   File "../site-packages/sqlalchemy/orm/relationships.py", line 1656, in 
> > do_init 
> > self._generate_backref() 
> >   File "../site-packages/sqlalchemy/orm/relationships.py", line 1837, in 
> > _generate_backref 
> > (backref_key, self, m)) 
> > sqlalchemy.exc.ArgumentError: Error creating backref 'user_collection' 
> > on relationship 'user.user_collection': property of that name exists on 
> > mapper 'Mapper|user|user' 
> > 
> > 
> > In fact, I only want to have one-to-many relationship that connects a 
> > user to all his contacts. But Sqlalchemy try to make a bidirectional 
> > relationship (many-to-many), which fails because the relationship name 
> > is the same in both cases (user_collection). 
> > 
> > Do you have a good solution for this problem ? 
> > 
> > 
> > Best regards 
> > 
> > -- 
> > SQLAlchemy - 
> > The Python SQL Toolkit and Object Relational Mapper 
> > 
> > http://www.sqlalchemy.org/ 
> > 
> > To post example code, please provide an MCVE: Minimal, Complete, and 
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full 
> > description. 
> > --- 
> > 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  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] automap with self-referential relationship

[yoch . melka]

Hi,


I want to use automap to generate mapping from existing database, but the 
relation mapping fails.
I understand what happens, but I can't find a proper workaround to fix this 
error.


Here a minimal code example with the minimal schema :

CREATE TABLE `user` (
`id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `contact` (
`iduser` INT UNSIGNED NOT NULL,
`idcontact` INT UNSIGNED NOT NULL,
PRIMARY KEY (`iduser`, `idcontact`),
FOREIGN KEY (`iduser`) REFERENCES `user` (`id`),
FOREIGN KEY (`idcontact`) REFERENCES `user` (`id`)
) ENGINE=InnoDB;


The minimal code is :

Base = automap_base()
Base.prepare(engine, reflect=True)
configure_mappers()

which fails with this backtrace :

Traceback (most recent call last):
  File "", line 1, in 
  File "testing.py", line 11, in 
configure_mappers()
  File "../site-packages/sqlalchemy/orm/mapper.py", line 2866, in 
configure_mappers
mapper._post_configure_properties()
  File "../site-packages/sqlalchemy/orm/mapper.py", line 1765, in 
_post_configure_properties
prop.init()
  File "../site-packages/sqlalchemy/orm/interfaces.py", line 184, in init
self.do_init()
  File "../site-packages/sqlalchemy/orm/relationships.py", line 1656, in 
do_init
self._generate_backref()
  File "../site-packages/sqlalchemy/orm/relationships.py", line 1837, in 
_generate_backref
(backref_key, self, m))
sqlalchemy.exc.ArgumentError: Error creating backref 'user_collection' on 
relationship 'user.user_collection': property of that name exists on mapper 
'Mapper|user|user'


In fact, I only want to have one-to-many relationship that connects a user 
to all his contacts. But Sqlalchemy try to make a bidirectional 
relationship (many-to-many), which fails because the relationship name is 
the same in both cases (user_collection).

Do you have a good solution for this problem ?


Best regards

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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.

Re: [sqlalchemy] using mysql user variables

[yoch . melka]

OK, thank you.

I understand that there is no way to do this kind of thing with the ORM.
So I'm going to use litteral SQL in this case.

Le mercredi 13 janvier 2016 22:58:26 UTC+2, Michael Bayer a écrit :
>
> it would depend upon if this can be done within a single 
> cursor.execute() or if it would require multiple invocations. 
>
> At the very least, if the DBAPI that you're using can support it, I'd 
> start with direct cursor access as illustrated at 
>
> http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#working-with-raw-dbapi-connections.
>  
>
>  if the DBAPI can't support it then SQLAlchemy could not do it either. 
>
>
>
>
>
> On 01/13/2016 03:07 PM, yoch@gmail.com  wrote: 
> > Hello, 
> > 
> > I want to compute difference between successive records. 
> > In MySQL, I had written something like : 
> > 
> > | 
> > SET @prev:=0; 
> > SELECT time_to_sec(@prev),@prev:=ts FROM mytable; 
> > | 
> > 
> > How to achieve the same with sqlalchemy ? 
> > (I prefer to do it in SQL rather than python because I want to aggregate 
> > the results) 
> > 
> > Best regards 
> > 
> > -- 
> > 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  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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] using mysql user variables

[yoch . melka]

Hello,

I want to compute difference between successive records.
In MySQL, I had written something like : 

SET @prev := 0;
SELECT time_to_sec(@prev), @prev := ts FROM mytable;

How to achieve the same with sqlalchemy ?
(I prefer to do it in SQL rather than python because I want to aggregate 
the results)

Best regards

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

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

[yoch . melka]

For now, I don't have a sufficiently comprehensive view of the automap 
process and its effects to make a pull-request.
Maybe one day I'll take time to learn more about that, and to understand 
the corresponding tests.

Thank you

Le lundi 4 janvier 2016 05:19:17 UTC+2, Michael Bayer a écrit :
>
> there's actually two if you put your check everywhere it would be needed 
> (I already tried when you first suggested that it warn, which is the 
> origin of my response that this is not a one liner). 
>
> the warning here is doable it just needs more state to be tracked as 
> automap runs.   if you want to work on this I can accept a pull request 
> if you can add some tests. 
>
>
>
> On 01/03/2016 07:24 PM, yoch@gmail.com  wrote: 
> > Okay, I have one test failed for automap : 
> > 
> > ~/sqlalchemy $ ./sqla_nose.py test.ext.test_automap 
> > .E.. 
> > == 
> > ERROR: 
> > 
> test.ext.test_automap.AutomapTest.test_relationship_explicit_override_m2o 
> > -- 
> > Traceback (most recent call last): 
> >   File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in 
> runTest 
> > self.test(*self.arg) 
> >   File "/home/pi/sqlalchemy/test/ext/test_automap.py", line 55, in 
> > test_relationship_explicit_override_m2o 
> > Base.prepare() 
> >   File "/home/pi/sqlalchemy/lib/sqlalchemy/ext/automap.py", line 777, in 
> > prepare 
> > generate_relationship) 
> >   File "/home/pi/sqlalchemy/lib/sqlalchemy/ext/automap.py", line 956, in 
> > _relationships_for_fks 
> > util.warn(msg) 
> >   File "/home/pi/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 
> > 1292, in warn 
> > warnings.warn(msg, exc.SAWarning, stacklevel=2) 
> > SAWarning: Address relationship name conflict: users 
> > 
> > -- 
> > Ran 12 tests in 27.450s 
> > 
> > FAILED (errors=1) 
> > 
> > Le lundi 4 janvier 2016 01:06:42 UTC+2, Michael Bayer a écrit : 
> > 
> > 
> > 
> > On 01/03/2016 02:43 PM, yoch@gmail.com  wrote: 
> > > I've added theses lines here 
> > > 
> > <
> https://bitbucket.org/zzzeek/sqlalchemy/src/c7d6c667b53d96a65e0dedcb83c098e03d4c7453/lib/sqlalchemy/ext/automap.py?at=master=file-view-default#automap.py-953
>  
> > <
> https://bitbucket.org/zzzeek/sqlalchemy/src/c7d6c667b53d96a65e0dedcb83c098e03d4c7453/lib/sqlalchemy/ext/automap.py?at=master=file-view-default#automap.py-953>>
>  
>
> > 
> > > : 
> > > 
> > > | 
> > > ifrelationship_name inmap_config.properties: 
> > > msg ="%s relationship name conflict: 
> > > %s"%(local_cls.__name__,relationship_name) 
> > > util.warn(msg) 
> > > | 
> > > 
> > > and it produces warnings as excepted : 
> > > 
> >  from dbmodels import * 
> > > 
> /usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py:961: 
> > > SAWarning: *thermostats relationship name conflict: 
> dispositif_ref* 
> > >   util.warn(msg) 
> > > 
> > > Whats wrong with this approach ? Is there any case that overwrite 
> > > relationship_name in map_config.properties is correct ? 
> > 
> > please run the test suite and observe the tests that fail with this 
> > approach, specifically those which test the behavior of being able 
> to 
> > specify an explicit mapping with existing relationships. 
> > 
> > 
> > 
> > 
> > > 
> > > 
> > > Le dimanche 3 janvier 2016 19:09:04 UTC+2, Michael Bayer a écrit : 
> > > 
> > > 
> > > 
> > > On 01/03/2016 07:00 AM, yoch@gmail.com  
> wrote: 
> > > > OK, thanks you. 
> > > > 
> > > > I think it's a good idea to issue a warning in such cases. 
> > > 
> > > unfortunately this is a difficult situation to detect since it 
> > is a 
> > > valid use case to present a mapped class that already has 
> > relationships 
> > > present on it, which will not be overridden.   Additional 
> > bookkeeping 
> > > would need to be added to the automap process to track all 
> > those class/ 
> > > name combinations that were locally added without coming up 
> > with false 
> > > positives. 
> > > 
> > > 
> > > 
> > > > 
> > > > Best regards 
> > > > 
> > > > Le samedi 2 janvier 2016 19:18:12 UTC+2, Michael Bayer a 
> > écrit : 
> > > > 
> > > > 
> > > > 
> > > > On 01/02/2016 11:38 AM, yoch@gmail.com 
>  
> > wrote: 
> > > > > Thank you. 
> > > > > 
> > > > > I hesitate between using this way, or explicitly 
> > specify the 
> > > > relationship 
> > > > > (is this a good idea? In my test I found 3 relations 

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

[yoch . melka]

Okay, I have one test failed for automap : 

~/sqlalchemy $ ./sqla_nose.py test.ext.test_automap
.E..
==
ERROR: 
test.ext.test_automap.AutomapTest.test_relationship_explicit_override_m2o
--
Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/nose/case.py", line 197, in runTest
self.test(*self.arg)
  File "/home/pi/sqlalchemy/test/ext/test_automap.py", line 55, in 
test_relationship_explicit_override_m2o
Base.prepare()
  File "/home/pi/sqlalchemy/lib/sqlalchemy/ext/automap.py", line 777, in 
prepare
generate_relationship)
  File "/home/pi/sqlalchemy/lib/sqlalchemy/ext/automap.py", line 956, in 
_relationships_for_fks
util.warn(msg)
  File "/home/pi/sqlalchemy/lib/sqlalchemy/util/langhelpers.py", line 1292, 
in warn
warnings.warn(msg, exc.SAWarning, stacklevel=2)
SAWarning: Address relationship name conflict: users

--
Ran 12 tests in 27.450s

FAILED (errors=1)

Le lundi 4 janvier 2016 01:06:42 UTC+2, Michael Bayer a écrit :
>
>
>
> On 01/03/2016 02:43 PM, yoch@gmail.com  wrote: 
> > I've added theses lines here 
> > <
> https://bitbucket.org/zzzeek/sqlalchemy/src/c7d6c667b53d96a65e0dedcb83c098e03d4c7453/lib/sqlalchemy/ext/automap.py?at=master=file-view-default#automap.py-953>
>  
>
> > : 
> > 
> > | 
> > ifrelationship_name inmap_config.properties: 
> > msg ="%s relationship name conflict: 
> > %s"%(local_cls.__name__,relationship_name) 
> > util.warn(msg) 
> > | 
> > 
> > and it produces warnings as excepted : 
> > 
>  from dbmodels import * 
> > /usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py:961: 
> > SAWarning: *thermostats relationship name conflict: dispositif_ref* 
> >   util.warn(msg) 
> > 
> > Whats wrong with this approach ? Is there any case that overwrite 
> > relationship_name in map_config.properties is correct ? 
>
> please run the test suite and observe the tests that fail with this 
> approach, specifically those which test the behavior of being able to 
> specify an explicit mapping with existing relationships. 
>
>
>
>
> > 
> > 
> > Le dimanche 3 janvier 2016 19:09:04 UTC+2, Michael Bayer a écrit : 
> > 
> > 
> > 
> > On 01/03/2016 07:00 AM, yoch@gmail.com  wrote: 
> > > OK, thanks you. 
> > > 
> > > I think it's a good idea to issue a warning in such cases. 
> > 
> > unfortunately this is a difficult situation to detect since it is a 
> > valid use case to present a mapped class that already has 
> relationships 
> > present on it, which will not be overridden.   Additional 
> bookkeeping 
> > would need to be added to the automap process to track all those 
> class/ 
> > name combinations that were locally added without coming up with 
> false 
> > positives. 
> > 
> > 
> > 
> > > 
> > > Best regards 
> > > 
> > > Le samedi 2 janvier 2016 19:18:12 UTC+2, Michael Bayer a écrit : 
> > > 
> > > 
> > > 
> > > On 01/02/2016 11:38 AM, yoch@gmail.com  
> wrote: 
> > > > Thank you. 
> > > > 
> > > > I hesitate between using this way, or explicitly specify the 
> > > relationship 
> > > > (is this a good idea? In my test I found 3 relations after 
> > > prepare()) : 
> > > 
> > > it's fine to do that.  Automap is still building its own 
> > > relationship as 
> > > well which is why you end up with three of them.  The 
> > > generate_relationship hook can be used to return None in those 
> > cases 
> > > where you don't want automap to generate a relationship: 
> > > 
> > 
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship
>  
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship>
>  
>
> > 
> > > 
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship
>  
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship>>
>  
>
> > 
> > > 
> > > 
> > > 
> > > > 
> > > > | 
> > > > classThermostat(Base): 
> > > > __tablename__ ='thermostats' 
> > > > idbuiltin =Column(Integer,ForeignKey('device.id 
> >  
> > > ')) 
> > > > idthermometer =Column(Integer,ForeignKey('device.id 
> >  
> > > ')) 
> > > > thermometer 
> > =relationship(Dispositif,foreign_keys=idthermometer) 
> > > > builtin 

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

[yoch . melka]

OK, thanks you.

I think it's a good idea to issue a warning in such cases.

Best regards

Le samedi 2 janvier 2016 19:18:12 UTC+2, Michael Bayer a écrit :
>
>
>
> On 01/02/2016 11:38 AM, yoch@gmail.com  wrote: 
> > Thank you. 
> > 
> > I hesitate between using this way, or explicitly specify the 
> relationship 
> > (is this a good idea? In my test I found 3 relations after prepare()) : 
>
> it's fine to do that.  Automap is still building its own relationship as 
> well which is why you end up with three of them.  The 
> generate_relationship hook can be used to return None in those cases 
> where you don't want automap to generate a relationship: 
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship
>  
>
>
> > 
> > | 
> > classThermostat(Base): 
> > __tablename__ ='thermostats' 
> > idbuiltin =Column(Integer,ForeignKey('device.id')) 
> > idthermometer =Column(Integer,ForeignKey('device.id')) 
> > thermometer =relationship(Dispositif,foreign_keys=idthermometer) 
> > builtin =relationship(Dispositif,foreign_keys=idbuiltin) 
> > | 
> > 
> > 
> > Another interesting point is how to detect this error to warn. I tried 
> > to use name_for_scalar_relationship() for that, but I don't kow how to 
> > get the relationships mapper. 
> > 
> > Best regards 
> > 
> > Le vendredi 1 janvier 2016 18:27:14 UTC+2, Michael Bayer a écrit : 
> > 
> > you need to use the name generation functions 
> > name_for_scalar_relationship() and/or 
> > name_for_collection_relationship() 
> > to produce different names in each case.  The "constraint" parameter 
> > passed as we see in 
> > 
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts
>  
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts>
>  
>
> > 
> > is a ForeignKeyConstraint object, you can look inside of 
> > constraint.column_keys to see if it is ['idbuiltin'] or 
> > ['idthermometer'] and use that to generate a name. 
> > 
> > 
> > 
> > On 01/01/2016 04:16 AM, yoch@gmail.com  wrote: 
> > > Hi all, 
> > > 
> > > I use automap with database reflection to import schema with 
> > sqlalchemy. 
> > > 
> > > In case I have two relationships on same foreign key in some 
> > table, only 
> > > one relationship is created by prepare(), the second one seems 
> > overwrited. 
> > > 
> > > My table looks like : 
> > > 
> > > | 
> > > Table('thermostat', 
> > > Base.metadata, 
> > > Column('id',INTEGER(),primary_key=True,nullable=False), 
> > > Column('idbuiltin',INTEGER(),ForeignKey('device.id 
> > ')), 
> > > Column('idthermometer',INTEGER(),ForeignKey('device.id 
> > '))) 
> > > | 
> > > 
> > > How to control relationship creation to produce two distinct 
> > relationships ? 
> > > 
> > > Thank you and Happy New Year ! 
> > > 
> > > -- 
> > > 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  
> > >  >. 
> > > To post to this group, send email to sqlal...@googlegroups.com 
> >  
> > > . 
> > > Visit this group at https://groups.google.com/group/sqlalchemy 
> > . 
> > > For more options, visit https://groups.google.com/d/optout 
> > . 
> > 
> > -- 
> > 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  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

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

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

[yoch . melka]

I've added theses lines here 

 
:

if relationship_name in map_config.properties:
msg = "%s relationship name conflict: %s" % (
local_cls.__name__, relationship_name)
util.warn(msg)

and it produces warnings as excepted :

>>> from dbmodels import *
/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/automap.py:961: 
SAWarning: *thermostats relationship name conflict: dispositif_ref*
  util.warn(msg)

Whats wrong with this approach ? Is there any case that overwrite 
relationship_name in map_config.properties is correct ?


Le dimanche 3 janvier 2016 19:09:04 UTC+2, Michael Bayer a écrit :
>
>
>
> On 01/03/2016 07:00 AM, yoch@gmail.com  wrote: 
> > OK, thanks you. 
> > 
> > I think it's a good idea to issue a warning in such cases. 
>
> unfortunately this is a difficult situation to detect since it is a 
> valid use case to present a mapped class that already has relationships 
> present on it, which will not be overridden.   Additional bookkeeping 
> would need to be added to the automap process to track all those class/ 
> name combinations that were locally added without coming up with false 
> positives. 
>
>
>
> > 
> > Best regards 
> > 
> > Le samedi 2 janvier 2016 19:18:12 UTC+2, Michael Bayer a écrit : 
> > 
> > 
> > 
> > On 01/02/2016 11:38 AM, yoch@gmail.com  wrote: 
> > > Thank you. 
> > > 
> > > I hesitate between using this way, or explicitly specify the 
> > relationship 
> > > (is this a good idea? In my test I found 3 relations after 
> > prepare()) : 
> > 
> > it's fine to do that.  Automap is still building its own 
> > relationship as 
> > well which is why you end up with three of them.  The 
> > generate_relationship hook can be used to return None in those cases 
> > where you don't want automap to generate a relationship: 
> > 
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship
>  
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#sqlalchemy.ext.automap.generate_relationship>
>  
>
> > 
> > 
> > 
> > > 
> > > | 
> > > classThermostat(Base): 
> > > __tablename__ ='thermostats' 
> > > idbuiltin =Column(Integer,ForeignKey('device.id 
> > ')) 
> > > idthermometer =Column(Integer,ForeignKey('device.id 
> > ')) 
> > > thermometer 
> =relationship(Dispositif,foreign_keys=idthermometer) 
> > > builtin =relationship(Dispositif,foreign_keys=idbuiltin) 
> > > | 
> > > 
> > > 
> > > Another interesting point is how to detect this error to warn. I 
> > tried 
> > > to use name_for_scalar_relationship() for that, but I don't kow 
> > how to 
> > > get the relationships mapper. 
> > > 
> > > Best regards 
> > > 
> > > Le vendredi 1 janvier 2016 18:27:14 UTC+2, Michael Bayer a écrit : 
> > > 
> > > you need to use the name generation functions 
> > > name_for_scalar_relationship() and/or 
> > > name_for_collection_relationship() 
> > > to produce different names in each case.  The "constraint" 
> > parameter 
> > > passed as we see in 
> > > 
> > 
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts
>  
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts>
>  
>
> > 
> > > 
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts
>  
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts>>
>  
>
> > 
> > > 
> > > is a ForeignKeyConstraint object, you can look inside of 
> > > constraint.column_keys to see if it is ['idbuiltin'] or 
> > > ['idthermometer'] and use that to generate a name. 
> > > 
> > > 
> > > 
> > > On 01/01/2016 04:16 AM, yoch@gmail.com  
> wrote: 
> > > > Hi all, 
> > > > 
> > > > I use automap with database reflection to import schema with 
> > > sqlalchemy. 
> > > > 
> > > > In case I have two relationships on same foreign key in some 
> > > table, only 
> > > > one relationship is created by prepare(), the second one 
> seems 
> > > overwrited. 
> > > > 
> > > > My table looks like : 
> > > > 
> > > > | 
> > > > Table('thermostat', 
> > > > Base.metadata, 
> > > > Column('id',INTEGER(),primary_key=True,nullable=False), 
> > > > Column('idbuiltin',INTEGER(),ForeignKey('device.id 
> > 

Re: [sqlalchemy] automap : problem with two relationships on same foreign key

[yoch . melka]

Thank you.

I hesitate between using this way, or explicitly specify the relationship
(is this a good idea? In my test I found 3 relations after prepare()) :

class Thermostat(Base):
__tablename__ = 'thermostats'
idbuiltin = Column(Integer, ForeignKey('device.id'))
idthermometer = Column(Integer, ForeignKey('device.id'))
thermometer = relationship(Dispositif, foreign_keys=idthermometer)
builtin = relationship(Dispositif, foreign_keys=idbuiltin)


Another interesting point is how to detect this error to warn. I tried to 
use name_for_scalar_relationship() for that, but I don't kow how to get the 
relationships mapper.

Best regards

Le vendredi 1 janvier 2016 18:27:14 UTC+2, Michael Bayer a écrit :
>
> you need to use the name generation functions 
> name_for_scalar_relationship() and/or name_for_collection_relationship() 
> to produce different names in each case.  The "constraint" parameter 
> passed as we see in 
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#handling-simple-naming-conflicts
>  
> is a ForeignKeyConstraint object, you can look inside of 
> constraint.column_keys to see if it is ['idbuiltin'] or 
> ['idthermometer'] and use that to generate a name. 
>
>
>
> On 01/01/2016 04:16 AM, yoch@gmail.com  wrote: 
> > Hi all, 
> > 
> > I use automap with database reflection to import schema with sqlalchemy. 
> > 
> > In case I have two relationships on same foreign key in some table, only 
> > one relationship is created by prepare(), the second one seems 
> overwrited. 
> > 
> > My table looks like : 
> > 
> > | 
> > Table('thermostat', 
> > Base.metadata, 
> > Column('id',INTEGER(),primary_key=True,nullable=False), 
> > Column('idbuiltin',INTEGER(),ForeignKey('device.id')), 
> > Column('idthermometer',INTEGER(),ForeignKey('device.id'))) 
> > | 
> > 
> > How to control relationship creation to produce two distinct 
> relationships ? 
> > 
> > Thank you and Happy New Year ! 
> > 
> > -- 
> > 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  
> > . 
> > To post to this group, send email to sqlal...@googlegroups.com 
>  
> > . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>

-- 
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] automap : problem with two relationships on same foreign key

[yoch . melka]

Hi all,

I use automap with database reflection to import schema with sqlalchemy.

In case I have two relationships on same foreign key in some table, only 
one relationship is created by prepare(), the second one seems overwrited.

My table looks like :

Table('thermostat', 
Base.metadata,
Column('id', INTEGER(), primary_key=True, nullable=False), 
Column('idbuiltin', INTEGER(), ForeignKey('device.id')), 
Column('idthermometer', INTEGER(), ForeignKey('device.id')))

How to control relationship creation to produce two distinct relationships ?

Thank you and Happy New Year !

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

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

[yoch . melka]

Great thanks !


There is something I still doesn't understand with automap_base : some 
relationships are created only after the first query call (which takes much 
more time to terminate).

For instance :

>>> from dbaccess import *# import engine, models, etc.
>>> Dispositif.hardware_collection.attr   # doesn't exist yet
Traceback (most recent call last):
  File "", line 1, in 
  File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py", 
line 204, in attr
return (self.local_attr, self.remote_attr)
  File 
"/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py", 
line 184, in local_attr
return getattr(self.owning_class, self.target_collection)
AttributeError: type object 'Dispositif' has no attribute 
'disp_hdw_collection'
>>> dir(Dispositif)   # there is no 
'disp_hdw_collection', but 'hardware_collection' exists (seems because I 
define it explicitely)
[..., 'classes', 'hardware_collection', 'id', 'metadata', 'prepare']
>>> session = Session()   # create the first session
>>> l = session.query(Dispositif).all()   # first query call, takes about 2s
>>> Dispositif.hardware_collection.attr   # now exists...
(, <
sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x12f2f90>)
>>> dir(Dispositif)   # many news relationships and 
collections appear here
[..., 'classes', 'disp_hdw_collection', 'hardware_collection', 'id', 
'metadata', 'prepare', ]


Le lundi 31 août 2015 16:33:52 UTC+3, Michael Bayer a écrit :
>
>
>
> On 8/31/15 8:23 AM, yoch@gmail.com  wrote:
>
>
> Thank you very much.
>
> Le lundi 31 août 2015 06:17:37 UTC+3, Michael Bayer a écrit : 
>
>
>
> On 8/29/15 2:27 PM, yoch@gmail.com wrote:
>
> Thanks for the reply.
>
> Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit : 
>
>
>
> On 8/28/15 3:51 AM, yoch@gmail.com wrote:
>
> Another question is why sqlalchemy produce two queries to get hardwares 
> collections from a dispositif :
>
>
> >>> some_disp.hardwares
> 2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS 
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox 
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> There's no context given here, but that would indicate that the 
> "some_disp" instance has been expired.  When you .commit() a session, all 
> objects are expired by default.  This is so that if, when you next operate 
> on the objects, some other transaction has deleted that row, the session 
> can let you know that you're working on stale data.   See the session docs 
> for options on how to control this behavior.
>
>  
> Here the context, all in the same session :
>
> >>> from dbaccess import *# import engine, Base and tables objects
> >>> from sqlalchemy.orm.session import sessionmaker
> >>> Session = sessionmaker(bind=engine)
> >>> session = Session()
> >>> l = session.query(Dispositif).all()
> >>> some_disp = l[-2]
> >>> engine.echo = True
> >>> some_disp.hardwares
> 2015-08-29 20:34:30,972 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS 
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-29 20:34:30,975 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-29 20:34:31,018 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox 
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-29 20:34:31,022 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> oh.   Well this is association proxy, which means it needs to load two 
> separate relationships, so that is two SELECT statements with default 
> loader strategy.
>
>
> OK.
>
> In fact, *hardwares* can be retrieved in one query.
>
> instead of somewhat like :
>
> SELECT hardware.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, 
> hardware.idbox AS hardware_idbox
> FROM hardware
> JOIN disp_hdw ON hardware.id = disp_hdw.idhardware
> WHERE disp_hdw.iddispositif = %s
>
>
> sure thing, use joined eager loading:
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_relationships.html
>
>
> If I try :
> >>> x = db.query(Dispositif).options(joinedload(Dispositif.
> hardware_collection)).get(2721)
>
> I got an error : sqlalchemy.exc.ArgumentError: mapper option expects 
> string key or list of attributes.
>
>
> if 'hardware_collection' is the association proxy, right now you can't 
> pass it directly like 

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

[yoch . melka]

Thank you very much, it work fine now.

Le lundi 31 août 2015 23:03:17 UTC+3, Michael Bayer a écrit :
>
>
>
> On 8/31/15 3:39 PM, yoch@gmail.com  wrote:
>
> Great thanks ! 
>
>
> There is something I still doesn't understand with automap_base : some 
> relationships are created only after the first query call (which takes much 
> more time to terminate).
>
>
> call configure_mappers() after you call Base.prepare().
>
>
>
> For instance :
>
> >>> from dbaccess import *# import engine, models, etc.
> >>> Dispositif.hardware_collection.attr   # doesn't exist yet
> Traceback (most recent call last):
>   File "", line 1, in 
>   File 
> "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py"
> , line 204, in attr
> return (self.local_attr, self.remote_attr)
>   File 
> "/usr/local/lib/python2.7/dist-packages/sqlalchemy/ext/associationproxy.py"
> , line 184, in local_attr
> return getattr(self.owning_class, self.target_collection)
> AttributeError: type object 'Dispositif' has no attribute 
> 'disp_hdw_collection'
> >>> dir(Dispositif)   # there is no 
> 'disp_hdw_collection', but 'hardware_collection' exists (seems because I 
> define it explicitely)
> [..., 'classes', 'hardware_collection', 'id', 'metadata', 'prepare']
> >>> session = Session()   # create the first session
> >>> l = session.query(Dispositif).all()   # first query call, takes about 
> 2s
> >>> Dispositif.hardware_collection.attr   # now exists...
> (, <
> sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x12f2f90>)
> >>> dir(Dispositif)   # many news relationships and 
> collections appear here
> [..., 'classes', 'disp_hdw_collection', 'hardware_collection', 'id', 
> 'metadata', 'prepare', ]
>
>
> Le lundi 31 août 2015 16:33:52 UTC+3, Michael Bayer a écrit : 
>
>
>
> On 8/31/15 8:23 AM, yoch@gmail.com wrote:
>
>
> Thank you very much.
>
> Le lundi 31 août 2015 06:17:37 UTC+3, Michael Bayer a écrit : 
>
>
>
> On 8/29/15 2:27 PM, yoch@gmail.com wrote:
>
> Thanks for the reply.
>
> Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit : 
>
>
>
> On 8/28/15 3:51 AM, yoch@gmail.com wrote:
>
> Another question is why sqlalchemy produce two queries to get hardwares 
> collections from a dispositif :
>
>
> >>> some_disp.hardwares
> 2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS 
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox 
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> There's no context given here, but that would indicate that the 
> "some_disp" instance has been expired.  When you .commit() a session, all 
> objects are expired by default.  This is so that if, when you next operate 
> on the objects, some other transaction has deleted that row, the session 
> can let you know that you're working on stale data.   See the session docs 
> for options on how to control this behavior.
>
>  
> Here the context, all in the same session :
>
> >>> from dbaccess import *# import engine, Base and tables objects
> >>> from sqlalchemy.orm.session import sessionmaker
> >>> Session = sessionmaker(bind=engine)
> >>> session = Session()
> >>> l = session.query(Dispositif).all()
> >>> some_disp = l
>
> ...

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

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

[yoch . melka]

Thank you very much.

Le lundi 31 août 2015 06:17:37 UTC+3, Michael Bayer a écrit :
>
>
>
> On 8/29/15 2:27 PM, yoch@gmail.com  wrote:
>
> Thanks for the reply.
>
> Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit : 
>
>
>
> On 8/28/15 3:51 AM, yoch@gmail.com wrote:
>
> Another question is why sqlalchemy produce two queries to get hardwares 
> collections from a dispositif :
>
>
> >>> some_disp.hardwares
> 2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS 
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox 
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> There's no context given here, but that would indicate that the 
> "some_disp" instance has been expired.  When you .commit() a session, all 
> objects are expired by default.  This is so that if, when you next operate 
> on the objects, some other transaction has deleted that row, the session 
> can let you know that you're working on stale data.   See the session docs 
> for options on how to control this behavior.
>
>  
> Here the context, all in the same session :
>
> >>> from dbaccess import *# import engine, Base and tables objects
> >>> from sqlalchemy.orm.session import sessionmaker
> >>> Session = sessionmaker(bind=engine)
> >>> session = Session()
> >>> l = session.query(Dispositif).all()
> >>> some_disp = l[-2]
> >>> engine.echo = True
> >>> some_disp.hardwares
> 2015-08-29 20:34:30,972 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
> .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS 
> disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
> FROM disp_hdw
> WHERE %s = disp_hdw.iddispositif
> 2015-08-29 20:34:30,975 INFO sqlalchemy.engine.base.Engine (2721L,)
> 2015-08-29 20:34:31,018 INFO sqlalchemy.engine.base.Engine SELECT hardware
> .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox 
> AS hardware_idbox
> FROM hardware
> WHERE hardware.id = %s
> 2015-08-29 20:34:31,022 INFO sqlalchemy.engine.base.Engine (268L,)
>
>
> oh.   Well this is association proxy, which means it needs to load two 
> separate relationships, so that is two SELECT statements with default 
> loader strategy.
>

OK.

In fact, *hardwares* can be retrieved in one query.
>
> instead of somewhat like :
>
> SELECT hardware.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, 
> hardware.idbox AS hardware_idbox
> FROM hardware
> JOIN disp_hdw ON hardware.id = disp_hdw.idhardware
> WHERE disp_hdw.iddispositif = %s
>
>
> sure thing, use joined eager loading:
>
> http://docs.sqlalchemy.org/en/rel_1_0/orm/loading_relationships.html
>

If I try :
>>> x = db.query(Dispositif).options(joinedload(Dispositif.
hardware_collection)).get(2721)

I got an error : sqlalchemy.exc.ArgumentError: mapper option expects string 
key or list of attributes.

But it's still possible to "preload" the association part by using :
>>> x = db.query(Dispositif).options(joinedload(Dispositif.
disp_hdw_collection)).get(2721)

So calling x.hardware_collection after that produce only one query.

Is there a better way to do ?

> Le vendredi 28 août 2015 10:20:55 UTC+3, yoch@gmail.com a écrit :
>
> Hello, 
>
> I want to use assocation proxy pattern 
>  
> with automap. I tried this code :
>
>
> Base = automap_base()
>
> class Dispositif(Base):
> __tablename__ = 'dispositifs'
> hardwares = association_proxy('disp_hardwares', 'hardware')
>
> class Hardware(Base):
> __tablename__ = 'hardware'
>
> Base.prepare(engine, reflect=True)
>
> but it does not work.
>
> Any help appreciated.
>
> Best regards
>
> -- 
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at 
>
> ...

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

Re: [sqlalchemy] Re: how to set assocation proxy pattern with automap

[yoch . melka]

Thanks for the reply.

Le vendredi 28 août 2015 18:52:37 UTC+3, Michael Bayer a écrit :



 On 8/28/15 3:51 AM, yoch@gmail.com javascript: wrote:

 Another question is why sqlalchemy produce two queries to get hardwares 
 collections from a dispositif :


  some_disp.hardwares
 2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT disp_hdw
 .iddispositif AS disp_hdw_iddispositif, disp_hdw.idhardware AS 
 disp_hdw_idhardware, disp_hdw.instance AS disp_hdw_instance
 FROM disp_hdw
 WHERE %s = disp_hdw.iddispositif
 2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
 2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT hardware
 .hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox 
 AS hardware_idbox
 FROM hardware
 WHERE hardware.id = %s
 2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)


 There's no context given here, but that would indicate that the 
 some_disp instance has been expired.  When you .commit() a session, all 
 objects are expired by default.  This is so that if, when you next operate 
 on the objects, some other transaction has deleted that row, the session 
 can let you know that you're working on stale data.   See the session docs 
 for options on how to control this behavior.

 
Here the context, all in the same session :

 from dbaccess import *# import engine, Base and tables objects
 from sqlalchemy.orm.session import sessionmaker
 Session = sessionmaker(bind=engine)
 session = Session()
 l = session.query(Dispositif).all()
 some_disp = l[-2]
 engine.echo = True
 some_disp.hardwares
2015-08-29 20:34:30,972 INFO sqlalchemy.engine.base.Engine SELECT 
disp_hdw.iddispositif 
AS disp_hdw_iddispositif, disp_hdw.idhardware AS disp_hdw_idhardware, 
disp_hdw.instance AS disp_hdw_instance
FROM disp_hdw
WHERE %s = disp_hdw.iddispositif
2015-08-29 20:34:30,975 INFO sqlalchemy.engine.base.Engine (2721L,)
2015-08-29 20:34:31,018 INFO sqlalchemy.engine.base.Engine SELECT 
hardware.hdw_type 
AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox AS 
hardware_idbox
FROM hardware
WHERE hardware.id = %s
2015-08-29 20:34:31,022 INFO sqlalchemy.engine.base.Engine (268L,)

In fact, *hardwares* can be retrieved in one query.

 instead of somewhat like :

 SELECT hardware.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, 
 hardware.idbox AS hardware_idbox
 FROM hardware
 JOIN disp_hdw ON hardware.id = disp_hdw.idhardware
 WHERE disp_hdw.iddispositif = %s


 Le vendredi 28 août 2015 10:20:55 UTC+3, yoch@gmail.com a écrit : 

 Hello, 

 I want to use assocation proxy pattern 
 http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html 
 with automap. I tried this code :


 Base = automap_base()

 class Dispositif(Base):
 __tablename__ = 'dispositifs'
 hardwares = association_proxy('disp_hardwares', 'hardware')

 class Hardware(Base):
 __tablename__ = 'hardware'

 Base.prepare(engine, reflect=True)

 but it does not work.

 Any help appreciated.

 Best regards

 -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.com 
 javascript:.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




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

[sqlalchemy] Re: how to set assocation proxy pattern with automap

[yoch . melka]

I now have a code that seems to work, but I'm not sure I did it right :

Base = automap_base()

class Dispositif(Base):
__tablename__ = 'dispositifs'
hardwares = association_proxy('disp_hdw_collection', 'hardware_ref')

class Hardware(Base):
__tablename__ = 'hardware'

Base.prepare(engine, reflect=True)

Another question is why sqlalchemy produce two queries to get hardwares 
collections from a dispositif :

 some_disp.hardwares
2015-08-28 10:36:41,722 INFO sqlalchemy.engine.base.Engine SELECT 
disp_hdw.iddispositif 
AS disp_hdw_iddispositif, disp_hdw.idhardware AS disp_hdw_idhardware, 
disp_hdw.instance AS disp_hdw_instance
FROM disp_hdw
WHERE %s = disp_hdw.iddispositif
2015-08-28 10:36:41,725 INFO sqlalchemy.engine.base.Engine (2721L,)
2015-08-28 10:36:41,768 INFO sqlalchemy.engine.base.Engine SELECT 
hardware.hdw_type 
AS hardware_hdw_type, hardware.id AS hardware_id, hardware.idbox AS 
hardware_idbox
FROM hardware
WHERE hardware.id = %s
2015-08-28 10:36:41,772 INFO sqlalchemy.engine.base.Engine (268L,)


instead of somewhat like :

SELECT hardware.hdw_type AS hardware_hdw_type, hardware.id AS hardware_id, 
hardware.idbox AS hardware_idbox
FROM hardware
JOIN disp_hdw ON hardware.id = disp_hdw.idhardware
WHERE disp_hdw.iddispositif = %s


Le vendredi 28 août 2015 10:20:55 UTC+3, yoch@gmail.com a écrit :

 Hello,

 I want to use assocation proxy pattern 
 http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html 
 with automap. I tried this code :


 Base = automap_base()

 class Dispositif(Base):
 __tablename__ = 'dispositifs'
 hardwares = association_proxy('disp_hardwares', 'hardware')

 class Hardware(Base):
 __tablename__ = 'hardware'

 Base.prepare(engine, reflect=True)

 but it does not work.

 Any help appreciated.

 Best regards


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

[sqlalchemy] how to set assocation proxy pattern with automap

[yoch . melka]

Hello,

I want to use assocation proxy pattern 
http://docs.sqlalchemy.org/en/latest/orm/extensions/associationproxy.html 
with automap. I tried this code :


Base = automap_base()

class Dispositif(Base):
__tablename__ = 'dispositifs'
hardwares = association_proxy('disp_hardwares', 'hardware')

class Hardware(Base):
__tablename__ = 'hardware'

Base.prepare(engine, reflect=True)

but it does not work.

Any help appreciated.

Best regards

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

Re: [sqlalchemy] using inheritance with automap_base

[yoch . melka]

Wow, thank you. I'm going to try.

Le lundi 10 août 2015 18:01:37 UTC+3, Michael Bayer a écrit :



 On 8/10/15 10:55 AM, yoch@gmail.com javascript: wrote:

 Hello, 

 I use reflection with automap_base to load my database schema.

 # my code currently looks
 Base = automap_base()
 Base.prepare(engine, reflect=True)

 And for now, I want to configure the Base to take in account some 
 inheritance relationships.

 In database, inheritance are modeled by :

 -- base table
 CREATE TABLE `hardware` (
   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
   `type` VARCHAR(8) NOT NULL,  -- indicates the type of children used
   PRIMARY KEY (`id`));


 -- children tables (use same id of hardware)
 CREATE TABLE `hdw_xxx` (
   `id` INT UNSIGNED NOT NULL,
   ...
   PRIMARY KEY (`id`));


 CREATE TABLE `hdw_yyy` (
   `id` INT UNSIGNED NOT NULL,
   ...
   PRIMARY KEY (`id`));


 There is some proper way to do this ?

 you need to build out the structure of classes which inherit up front.  
 you can still use reflection for pulling in the columns.   see the example 
 at 
 http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html#using-automap-with-explicit-declarations
 .

 Here's also a test from the test suite, doing the same thing:

 Base = automap_base()

 class Joined(Base):
 __tablename__ = 'joined_base'

 type = Column(String)

 __mapper_args__ = {
 polymorphic_identity: u0,
 polymorphic_on: type}

 class SubJoined(Joined):
 __tablename__ = 'joined_inh'
 __mapper_args__ = {polymorphic_identity: u1}

 Base.prepare(engine=testing.db, reflect=True)








 Thanks you
 -- 
 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 javascript:.
 To post to this group, send email to sqlal...@googlegroups.com 
 javascript:.
 Visit this group at http://groups.google.com/group/sqlalchemy.
 For more options, visit https://groups.google.com/d/optout.




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

[sqlalchemy] using inheritance with automap_base

[yoch . melka]

Hello,

I use reflection with automap_base to load my database schema.

# my code currently looks
Base = automap_base()
Base.prepare(engine, reflect=True)

And for now, I want to configure the Base to take in account some 
inheritance relationships.

In database, inheritance are modeled by :

-- base table
CREATE TABLE `hardware` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(8) NOT NULL,  -- indicates the type of children used
  PRIMARY KEY (`id`));


-- children tables (use same id of hardware)
CREATE TABLE `hdw_xxx` (
  `id` INT UNSIGNED NOT NULL,
  ...
  PRIMARY KEY (`id`));


CREATE TABLE `hdw_yyy` (
  `id` INT UNSIGNED NOT NULL,
  ...
  PRIMARY KEY (`id`));


There is some proper way to do this ?

Thanks you

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