[sqlalchemy] Re: Custom secondary relation with composite primary keys

[Alex Plugaru]

It worked! Thanks a lot!

On Friday, 28 April 2017 18:49:40 UTC-7, Alex Plugaru wrote:
>
> Hello, 
>
> There are 3 tables: `*Account*`, `*Role*`, `*User*`. Both `*Role*` and `
> *User*` have a foreign key `*account_id*` that points to `*Account*`.
>
> A user can have multiple roles, hence the `*roles_users*` table which 
> acts as the secondary relation table between `*Role*` and `*User*`.
>
> The `*Account*` table is a tenant table for our app, it is used to 
> separate different customers.
>
> Note that all tables have (besides `*Account*`) have composite primary 
> keys with `*account_id*`. This is done for a few reasons, but let's say 
> it's done to keep everything consistent.
>
> Now if I have a simple secondary relationship (`*User.roles*` - the one 
> that is commented out) all works as expected. Well kind of.. it throws a 
> legitimate warning (though I believe it should be an error):
>
>
> SAWarning: relationship 'User.roles' will copy column role.account_id to 
> column roles_users.account_id, which conflicts with relationship(s): 
> 'User.roles' (copies user.account_id to roles_users.account_id). Consider 
> applying viewonly=True to read-only relationships, or provide a 
> primaryjoin condition marking writable columns with the foreign() 
> annotation.
>
> That's why I created the second relation `*User.roles*` - the one that is 
> not commented out. Querying works as expected which has 2 conditions on 
> join and everything. However I get this error when I try to save some roles 
> on the user:
>
> sqlalchemy.orm.exc.UnmappedColumnError: Can't execute sync rule for 
> source column 'roles_users.role_id'; mapper 'Mapper|User|user' does not 
> map this column.  Try using an explicit `foreign_keys` collection which 
> does not include destination column 'role.id' (or use a viewonly=True 
> relation).
>
>
> As far as I understand it, SA is not able to figure out how to save the 
> secondary because it has a custom `*primaryjoin*` and `*secondaryjoin*` 
> so it proposes to use `*viewonly=True*` which has the effect of just 
> ignoring the roles relation when saving the model.
>
> The question is how to save the roles for a user without having to do it 
> by hand (the example is commented out in the code). In the real app we have 
> many secondary relationships and we're saving them in many places. It would 
> be super hard to rewrite them all.
>
> Is there a solution to keep using `*User.roles = some_roles*` while 
> keeping the custom `*primaryjoin*` and `*secondaryjoin*` below?
>
> The full example using SA 1.1.9:
>
>
> from sqlalchemy import create_engine, Column, Integer, Text, Table, 
> ForeignKeyConstraint, ForeignKey, and_
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy.orm import foreign, relationship, Session
>
>
> Base = declarative_base()
>
>
>
>
> class Account(Base):
> __tablename__ = 'account'
> id = Column(Integer, primary_key=True)
>
>
>
>
> roles_users = Table(
> 'roles_users', Base.metadata,
> Column('account_id', Integer, primary_key=True),
> Column('user_id', Integer, primary_key=True),
> Column('role_id', Integer, primary_key=True),
>
>
> ForeignKeyConstraint(['user_id', 'account_id'], ['user.id', 
> 'user.account_id']),
> ForeignKeyConstraint(['role_id', 'account_id'], ['role.id', 
> 'role.account_id']),
> )
>
>
>
>
> class Role(Base):
> __tablename__ = 'role'
> id = Column(Integer, primary_key=True)
> account_id = Column(Integer, ForeignKey('account.id'), primary_key=
> True)
> name = Column(Text)
>
>
> def __str__(self):
> return ''.format(self.id, self.name)
>
>
>
>
> class User(Base):
> __tablename__ = 'user'
> id = Column(Integer, primary_key=True)
> account_id = Column(Integer, ForeignKey('account.id'), primary_key=
> True)
> name = Column(Text)
>
>
> # This works as expected: It saves data in roles_users
> # roles = relationship(Role, secondary=roles_users)
>
>
> # This custom relationship - does not work
> roles = relationship(
> Role,
> secondary=roles_users,
> primaryjoin=and_(foreign(Role.id) == roles_users.c.role_id,
>  Role.account_id == roles_users.c.account_id),
> secondaryjoin=and_(foreign(id) == roles_users.c.user_id,
>account_id == roles_users.c.account_id))
>
>
>
>
> engine = create_engine('sqlite:///')
> engine.echo = True
> Base.metadata.create_all(engine)
> session = Session(engine)
>
>
> # Create our account
> a = Account()
> session.add(a)
> session.commit()
>
>
> # Create 2 roles
> u_role = Role()
> u_role.id = 1
> u_role.account_id = a.id
> u_role.name = 'user'
> session.add(u_role)
>
>
> m_role = Role()
> m_role.id = 2
> m_role.account_id = a.id
> m_role.name = 'member'
> session.add(m_role)
> session.commit()
>
>
> # Create 1 user
> u = User()
> u.id = 1
> u.account_id = a.id
> u.name = 'user'
>
>
> # This does not work
> # u.roles = [u_role, 

Re: [sqlalchemy] Re: Custom secondary relation with composite primary keys

[mike bayer]

try setting all but one of the four column targets as "foreign" so that 
there isn't an overlapping "foreign" constraint:


roles = relationship(
Role,
secondary=roles_users,
primaryjoin=and_(id == foreign(roles_users.c.user_id),
 account_id == foreign(roles_users.c.account_id)),
secondaryjoin=and_(Role.id == foreign(roles_users.c.role_id),
   Role.account_id == roles_users.c.account_id))



alternatively, set the Python warnings filter to "ignore" for that 
particular class / regular expression of warning.




On 05/04/2017 06:33 PM, Alex Plugaru wrote:

Hi Mike,

Thanks! I followed your advice and indeed it does work as expected. 
However I still get this warning:


|
SAWarning:relationship 'User.roles'will copy column role.account_id to 
column roles_users.account_id,which conflicts 
withrelationship(s):'User.roles'(copies user.account_id to 
roles_users.account_id).Considerapplying viewonly=Trueto read-only 
relationships,orprovide a primaryjoin condition marking writable columns 
withthe foreign()annotation.


|

I have many m2m tables and there is a huge output of these warnings 
every time which is super annoying. Is there a way to tell SA not to 
complain about this and only this? I would still like to see other warnings.


Again the full code:

|
fromsqlalchemy 
importcreate_engine,Column,Integer,Text,Table,ForeignKeyConstraint,ForeignKey,and_

fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importforeign,relationship,Session,joinedload,remote


Base=declarative_base()




classAccount(Base):
 __tablename__ ='account'
 id =Column(Integer,primary_key=True)




roles_users =Table(
'roles_users',Base.metadata,
Column('account_id',Integer,primary_key=True),
Column('user_id',Integer,primary_key=True),
Column('role_id',Integer,primary_key=True),


ForeignKeyConstraint(
['user_id','account_id'],
['user.id','user.account_id']),
ForeignKeyConstraint(
['role_id','account_id'],
['role.id','role.account_id']),
)




classRole(Base):
 __tablename__ ='role'
 id =Column(Integer,primary_key=True)
 account_id =Column(Integer,ForeignKey('account.id'),primary_key=True)
 name =Column(Text)


def__str__(self):
return''.format(self.id,self.name)




classUser(Base):
 __tablename__ ='user'
 id =Column(Integer,primary_key=True)
 account_id =Column(Integer,ForeignKey('account.id'),primary_key=True)
 name =Column(Text)


# This works as expected: It saves data in roles_users
# roles = relationship(Role, secondary=roles_users)


# This custom relationship - does not work
 roles =relationship(
Role,
 secondary=roles_users,
 primaryjoin=and_(id ==roles_users.c.user_id,
  account_id ==roles_users.c.account_id),
 secondaryjoin=and_(Role.id ==roles_users.c.role_id,
Role.account_id ==roles_users.c.account_id))




engine =create_engine('sqlite://')
# engine.echo = True
Base.metadata.create_all(engine)
session =Session(engine)


# Create our account
a1 =Account()
a2 =Account()
session.add(a1)
session.add(a2)
session.commit()


# Create roles
u_role =Role()
u_role.id =1
u_role.account_id =a1.id
u_role.name ='user'
session.add(u_role)


m_role =Role()
m_role.id =2
m_role.account_id =a1.id
m_role.name ='member'
session.add(m_role)


a2_role =Role()
a2_role.id =3
a2_role.account_id =a2.id
a2_role.name ='member'
session.add(a2_role)
session.commit()


# Create 1 user
u =User()
u.id =1
u.account_id =a1.id
u.name ='user'


# This does not work
u.roles =[u_role,m_role,a2_role]
session.add(u)
session.commit()


# Works as expected
# i = roles_users.insert()
# i = i.values([
# dict(account_id=a.id, role_id=u_role.id, user_id=u.id),
# dict(account_id=a.id, role_id=m_role.id, user_id=u.id),
# ])
# session.execute(i)


# re-fetch user from db
u =session.query(User).options(joinedload('roles')).first()
forr inu.roles:
print(r)
|


Thank you!
Alex.

On Friday, 28 April 2017 18:49:40 UTC-7, Alex Plugaru wrote:

Hello,

There are 3 tables: `*Account*`, `*Role*`, `*User*`. Both `*Role*`
and `*User*` have a foreign key `*account_id*` that points to
`*Account*`.

A user can have multiple roles, hence the `*roles_users*` table
which acts as the secondary relation table between `*Role*` and
`*User*`.

The `*Account*` table is a tenant table for our app, it is used to
separate different customers.

Note that all tables have (besides `*Account*`) have composite
primary keys with `*account_id*`. This is done for a few reasons,
but let's say it's done to keep everything consistent.

Now if I have a simple secondary relationship (`*User.roles*` - the
one that is commented out) all works as expected. Well kind of.. it
throws a legitimate warning (though I believe it should be an error):


|
SAWarning:relationship 'User.roles'will copy column role.account_id
to column 

[sqlalchemy] Re: Custom secondary relation with composite primary keys

[Alex Plugaru]

Hi Mike,

Thanks! I followed your advice and indeed it does work as expected. However 
I still get this warning:

SAWarning: relationship 'User.roles' will copy column role.account_id to 
column roles_users.account_id, which conflicts with relationship(s): 
'User.roles' (copies user.account_id to roles_users.account_id). Consider 
applying viewonly=True to read-only relationships, or provide a primaryjoin 
condition marking writable columns with the foreign() annotation.


I have many m2m tables and there is a huge output of these warnings every 
time which is super annoying. Is there a way to tell SA not to complain 
about this and only this? I would still like to see other warnings.

Again the full code:

from sqlalchemy import create_engine, Column, Integer, Text, Table, 
ForeignKeyConstraint, ForeignKey, and_
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import foreign, relationship, Session, joinedload, 
remote


Base = declarative_base()




class Account(Base):
__tablename__ = 'account'
id = Column(Integer, primary_key=True)




roles_users = Table(
'roles_users', Base.metadata,
Column('account_id', Integer, primary_key=True),
Column('user_id', Integer, primary_key=True),
Column('role_id', Integer, primary_key=True),


ForeignKeyConstraint(
['user_id', 'account_id'],
['user.id', 'user.account_id']),
ForeignKeyConstraint(
['role_id', 'account_id'],
['role.id', 'role.account_id']),
)




class Role(Base):
__tablename__ = 'role'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('account.id'), primary_key=True)
name = Column(Text)


def __str__(self):
return ''.format(self.id, self.name)




class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
account_id = Column(Integer, ForeignKey('account.id'), primary_key=True)
name = Column(Text)


# This works as expected: It saves data in roles_users
# roles = relationship(Role, secondary=roles_users)


# This custom relationship - does not work
roles = relationship(
Role,
secondary=roles_users,
primaryjoin=and_(id == roles_users.c.user_id,
 account_id == roles_users.c.account_id),
secondaryjoin=and_(Role.id == roles_users.c.role_id,
   Role.account_id == roles_users.c.account_id))




engine = create_engine('sqlite://')
# engine.echo = True
Base.metadata.create_all(engine)
session = Session(engine)


# Create our account
a1 = Account()
a2 = Account()
session.add(a1)
session.add(a2)
session.commit()


# Create roles
u_role = Role()
u_role.id = 1
u_role.account_id = a1.id
u_role.name = 'user'
session.add(u_role)


m_role = Role()
m_role.id = 2
m_role.account_id = a1.id
m_role.name = 'member'
session.add(m_role)


a2_role = Role()
a2_role.id = 3
a2_role.account_id = a2.id
a2_role.name = 'member'
session.add(a2_role)
session.commit()


# Create 1 user
u = User()
u.id = 1
u.account_id = a1.id
u.name = 'user'


# This does not work
u.roles = [u_role, m_role, a2_role]
session.add(u)
session.commit()


# Works as expected
# i = roles_users.insert()
# i = i.values([
# dict(account_id=a.id, role_id=u_role.id, user_id=u.id),
# dict(account_id=a.id, role_id=m_role.id, user_id=u.id),
# ])
# session.execute(i)


# re-fetch user from db
u = session.query(User).options(joinedload('roles')).first()
for r in u.roles:
print(r)


Thank you!
Alex.

On Friday, 28 April 2017 18:49:40 UTC-7, Alex Plugaru wrote:
>
> Hello, 
>
> There are 3 tables: `*Account*`, `*Role*`, `*User*`. Both `*Role*` and `
> *User*` have a foreign key `*account_id*` that points to `*Account*`.
>
> A user can have multiple roles, hence the `*roles_users*` table which 
> acts as the secondary relation table between `*Role*` and `*User*`.
>
> The `*Account*` table is a tenant table for our app, it is used to 
> separate different customers.
>
> Note that all tables have (besides `*Account*`) have composite primary 
> keys with `*account_id*`. This is done for a few reasons, but let's say 
> it's done to keep everything consistent.
>
> Now if I have a simple secondary relationship (`*User.roles*` - the one 
> that is commented out) all works as expected. Well kind of.. it throws a 
> legitimate warning (though I believe it should be an error):
>
>
> SAWarning: relationship 'User.roles' will copy column role.account_id to 
> column roles_users.account_id, which conflicts with relationship(s): 
> 'User.roles' (copies user.account_id to roles_users.account_id). Consider 
> applying viewonly=True to read-only relationships, or provide a 
> primaryjoin condition marking writable columns with the foreign() 
> annotation.
>
> That's why I created the second relation `*User.roles*` - the one that is 
> not commented out. Querying works as expected which has 2 conditions on 
> join and everything. However I get this error 

[sqlalchemy] Re: Bulk Lazy Loader for relationships

[Jonathan Vanasco]

On Thursday, May 4, 2017 at 12:16:20 AM UTC-4, David Chanin wrote:
>
> How do objects get registered into the aggressive loader? Does it happen 
> automatically when they're initially loaded via query or cache? Ideally we 
> wanted to group items together when they're loaded and do bulk lazy loading 
> on that group rather than on all models in the session, but couldn't figure 
> out a good way to accomplish that. Ex if we run users = 
> session.query(User).limit(10).all() then we'd just want to do bulk lazy 
> loading within the users from that query rather than all users that happen 
> to be in the session, but it seems like we'd need to do some pretty 
> invasive changes to SQLAlchemy to keep track of which models were initially 
> loaded together.
>

Our implementation (which is a dirty hack) is loosely this:

class Students(Base):
 aggresive_load = ['school', 'report_cards', 'classes']

class Classes(Base):
 aggresive_load = ['teacher', 'students']

agLoader = AggressiveLoader(dbSession)
students = session.query(Students).limit(10).all()
agLoader.register(students)
agLoader.load()

The `register` function adds the students at loaded.
The `load` function inspects all the loaded objects and derives the 
potential primary keys/relationships to load.

To put this in context, the use-case for our concern is an multiple 
newspaper view.  One page had hundreds of Articles, from dozens of 
Publications, with tend of Authors; this could happen in multiple context 
zones.  
* Non-lazyload -- 4000+ sql statements
* lazyloading -- 1200+ sql statements
* aggressive loading like this -- 100+ statements

We specifically avoided your intended approach of 'bulk on the group', 
because we wanted to defer all the unloaded primary-key candidates from 
multiple queries into a single select.  In the newspaper view above, there 
could be 30 content zones that have a relationship to the "Author" table -- 
with overlap; our goal was to aggregate all 30 SELECTs that might return 
900 rows into a single SELECT that returns only the distinct rows-- perhaps 
100. 




 

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

[mike bayer]

On 05/04/2017 09:29 AM, yoch.me...@gmail.com wrote:


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.


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











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

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.

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

[mike bayer]

On 05/04/2017 08:41 AM, yoch.me...@gmail.com wrote:

Hi,

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

These two queries work differently :

# raise : Unconsumed column names: name
try:
 
session.query(Engineer).filter(Engineer.name=='bar').update({'name':'baz'})

 session.commit()
exceptExceptionaserr:
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()
exceptExceptionaserr:
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 ?


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


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







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.


--
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] struggling with postgres json related query using sqlalchemy -- sqlalchemy bug or issue with query?

[mike bayer]

there's a long term issue to get around to handling all of PG's syntaxes 
fully at 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs. 
   Current target is 1.3 because it will be a huge effort, and it may 
continue moving out milestones.


On 05/04/2017 05:17 AM, Darin Gordon wrote:
I'm working on another json query and thought I'd circle back around to 
see whether I'd have to continue using the original "as_row" recipe.  
Not sure whether Lukas was correct about sharing a similar issue as the 
one I originally raised.


On Thursday, March 16, 2017 at 10:24:51 AM UTC-4, Mike Bayer wrote:

I put up

https://bitbucket.org/zzzeek/sqlalchemy/issues/3939/alias-as-column-expr-needs-tweak-to



with the specific Alias issue but if you can add context what the end
goal is that would be helpful, thanks.



On 03/16/2017 09:56 AM, Lukas Siemon wrote:
 > Makes perfect sense. I'll post a complete bug report in the
tracker later today (it's early morning here atm).
 >

--
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 - 
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: Bulk Lazy Loader for relationships

[mike bayer]

On 05/04/2017 12:07 AM, David Chanin wrote:
Interesting - so it will be possible in 1.2 to do more custom 
relationship loading in a "post load" hook?


it will, however this hook is still local to the objects that are local 
to the results of that Query.




Thanks for the feedback! That definitely makes sense - I didn't fully 
understand all the logic in _emit_lazyload() initially so I wasn't sure 
what was OK to remove and what wasn't. I made a PR with those changes 
here: https://github.com/operator/sqlalchemy_bulk_lazy_loader/pull/1.


One thing I still don't fully understand is the passive variable. What 
does passive mean, and is it set per-model or per-relationship?


"passive" is an integer value that originates from various functions in 
the ORM, usually from functions in orm/attributes.py and 
orm/unitofwork.py.  It is a value that is specific to a particular 
attribute on a class (column or relationship) and it is used in the 
context of a particular read operation on an attribute.


This flag started out as a True/False.  When True, it meant, "give me 
this information but please don't emit SQL if you don't have it". 
Later, it became an enumeration of integers that referred to a wider 
range of possible scenarios and behaviors. Finally, it moved from an 
enumeration of sequential integers to a bitflag; still an integer value, 
but one that can be individually broken into specific true/false values. 
   This lets us move from logic like "if flag == VALUE_A or flag == 
VALUE_B or flag == VALUE_C" into "if flag & HAS_THING"; it's a lot more 
direct.


The elements refer to such behaviors as "should I return the DB-loaded 
value for this attribute, or whatever the user has just placed here?", 
"if I have to emit SQL, should I turn off autoflush?", "if this object 
is not actually "persistent" in the Session, can I still emit SQL using 
the primary key value it happens to have?" (this is a special use case 
added by popular request), "if I go to fetch this value, and it has no 
value set at all, should I populate the attribute with a default 
value/collection?".


Also in base.py you will see "pre-packaged" sets of bitflags that 
correspond to known use cases, which correspond roughly to the 
enumerated values these originally derived from.





Thanks a lot Mike!

On Wednesday, May 3, 2017 at 10:14:03 PM UTC+8, Mike Bayer wrote:


Related note, in 1.2 I'm adding a new mechanism for loading things
which
is a "post load" hook, that is, a bunch of objects are loaded in a
query, and then afterwards, more loaders can run on a batch of
completed
objects.   The effect looks similar to how "subqueryload" works right
now, except "subqueryload" fires off within the initial population step
of the objects.  The two kinds of loaders using this are the "select
IN"
loading, which is like subqueryloading but more or less better in most
ways (also seems you're doing "select IN" here), and "select IN"
loading
of subclass attributes on a joined inheritance mapping.

The hook you have here would be a third kind of hook, a "lazyload that
works across the Session" hook.   Definitely something new.   I'm not
sure all of the logic that's been copied into _emit_lazyload() really
applies though; the top logic is all related to the specific object
that
has triggered the load, like if its pending or not, if it had any query
option set up, the state.load_path, etc.   You can't assume any of that
stuff applies to all the other states if you are going across the whole
result.It's probably better, since this is a very different kind of
loader, to make it just load for all the states in the same way without
looking at any of their options or things like that.



On 05/03/2017 08:52 AM, David Chanin wrote:
 > Ack, thanks Simon! That is definitely a bug :). I just pushed a fix.
 >
 > Thanks for the feedback!
 > David
 >
 > On Wednesday, May 3, 2017 at 5:47:54 PM UTC+8, David Chanin wrote:
 >
 > Hi Everyone,
 >
 > We just open-sourced a custom lazy loader for SQLAlchemy that
does
 > bulk lazy loading of relations - essentially a lazy
subqueryload.
 > The idea is that whenever a relation is lazy-loaded on a
model, the
 > loader will look for all similar models in the session that
haven't
 > had that relation populated yet and will issue a single SQL
query to
 > populate them all in bulk. I'm really curious to hear any
feedback
 > you may have on this idea / implementation, pitfalls that we're
 > overlooking, or ideas for improvement. The repo is at
 > https://github.com/operator/sqlalchemy_bulk_lazy_loader

 > 

[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] struggling with postgres json related query using sqlalchemy -- sqlalchemy bug or issue with query?

[Darin Gordon]

I'm working on another json query and thought I'd circle back around to see 
whether I'd have to continue using the original "as_row" recipe.  Not sure 
whether Lukas was correct about sharing a similar issue as the one I 
originally raised.  

On Thursday, March 16, 2017 at 10:24:51 AM UTC-4, Mike Bayer wrote:
>
> I put up 
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3939/alias-as-column-expr-needs-tweak-to
>  
> with the specific Alias issue but if you can add context what the end 
> goal is that would be helpful, thanks. 
>
>
>
> On 03/16/2017 09:56 AM, Lukas Siemon wrote: 
> > Makes perfect sense. I'll post a complete bug report in the tracker 
> later today (it's early morning here atm). 
> > 
>

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