[sqlalchemy] many-to-many relation: unexpected count of rows
Hello,
I have relation User-User2Group-Group with additional attribute System on
User2Group table.
The System is part of primary key, which means
*'user can me member of group via multiple systems'.*
class User(Base):
__tablename__ = 'User'
name = Column('Name', Unicode(256), primary_key=True)
class Group(Base):
__tablename__ = 'Group'
name = Column('Name', Unicode(256), primary_key=True)
class User2Group(Base):
__tablename__ = 'User2Group'
userName = Column('UserName', Unicode(256), ForeignKey(User.name),
primary_key=True)
groupName = Column('GroupName', Unicode(256), ForeignKey(Group.name),
primary_key=True)
systemName = Column('SystemName', Enum('A', 'B', 'C'), primary_key=True)
User.groups = relationship(Group, secondary=User2Group.__table__)
Table User2Group looks like following
UserName | GroupName | SystemName
-
fred | admins | A
fred | admins | B
fred | admins | C
However when trying to delete user 'fred' wich is assinged to group
'admins' via multiple systems A, B, C.
I'm getting error: DELETE statement on table 'User2Group' expected to
delete 1 row(s); Only 3 were matched.
Did I misconfigured something?
Thanks
--
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: many-to-many relation: unexpected count of rows
Hi,
Adding viewonly=True on User.groups relationship solved the issue.
cheers!
Dne čtvrtek 5. března 2015 11:26:58 UTC+1 Pavel S napsal(a):
Hello,
I have relation User-User2Group-Group with additional attribute System on
User2Group table.
The System is part of primary key, which means
*'user can me member of group via multiple systems'.*
class User(Base):
__tablename__ = 'User'
name = Column('Name', Unicode(256), primary_key=True)
class Group(Base):
__tablename__ = 'Group'
name = Column('Name', Unicode(256), primary_key=True)
class User2Group(Base):
__tablename__ = 'User2Group'
userName = Column('UserName', Unicode(256), ForeignKey(User.name),
primary_key=True)
groupName = Column('GroupName', Unicode(256), ForeignKey(Group.name),
primary_key=True)
systemName = Column('SystemName', Enum('A', 'B', 'C'),
primary_key=True)
User.groups = relationship(Group, secondary=User2Group.__table__)
Table User2Group looks like following
UserName | GroupName | SystemName
-
fred | admins | A
fred | admins | B
fred | admins | C
However when trying to delete user 'fred' wich is assinged to group
'admins' via multiple systems A, B, C.
I'm getting error: DELETE statement on table 'User2Group' expected to
delete 1 row(s); Only 3 were matched.
Did I misconfigured something?
Thanks
--
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: many-to-many relation: unexpected count of rows
Hi,
I just noticed that User.groups relationship is not needed at all (it was a
relict of previous setup before System column was added).
So the problem is completely gone.
---
I already use *AssociationProxy* pattern in order to have relations like
*User.A_groups,
User.B_group**s, USer.C_groups.*
However I figured out other issue that '*creator*' callback to
*AssociationProxy* does not pass the parent object to it, which is weird.
See example:
user = ScopedSession.query(User).get('fred')
user.A_groups = [ Group('admins') ]
It should create also intermediary instance of User2Group. It does, but
user attribute of intermediary object is null.
So I created *AssociationFactory* as the following:
class AssociationFactory(AssociationProxy):
def __init__(self, *args, **kwargs):
self.creator_factory = kwargs.pop('creator_factory')
super(AssociationFactory, self).__init__(*args, **kwargs)
def __get__(self, obj, class_):
if obj:
self.creator = self.creator_factory(weakref.ref(obj))
return super(AssociationFactory, self).__get__(obj, class_)
for system in ('A', 'B', 'C'):
def creator_factory(user_ref):
def creator(group):
user = user_ref()
if user is None:
raise TypeError('Stale object reference')
return User2Group(user=user, group=group, system=system)
return creator
setattr(User, system + '_groups',
AssociationFactory(system+'_users2groups', 'group',
creator_factory=creator_factory)
Then all works nicely. Do you see the point?
cheers!
Dne čtvrtek 5. března 2015 11:58:50 UTC+1 Simon King napsal(a):
Glad you've figured it out. This is touched on briefly in the docs, in
the note at the bottom of:
http://docs.sqlalchemy.org/en/rel_0_9/orm/basic_relationships.html#association-object
As an alternative to making User.groups a relationship, you could also
consider using the Association Proxy extension:
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html#simplifying-association-objects
Hope that helps,
Simon
On Thu, Mar 5, 2015 at 10:43 AM, Pavel S pa...@schon.cz javascript:
wrote:
Hi,
Adding viewonly=True on User.groups relationship solved the issue.
cheers!
Dne čtvrtek 5. března 2015 11:26:58 UTC+1 Pavel S napsal(a):
Hello,
I have relation User-User2Group-Group with additional attribute System
on
User2Group table.
The System is part of primary key, which means 'user can me member of
group via multiple systems'.
class User(Base):
__tablename__ = 'User'
name = Column('Name', Unicode(256), primary_key=True)
class Group(Base):
__tablename__ = 'Group'
name = Column('Name', Unicode(256), primary_key=True)
class User2Group(Base):
__tablename__ = 'User2Group'
userName = Column('UserName', Unicode(256), ForeignKey(User.name),
primary_key=True)
groupName = Column('GroupName', Unicode(256),
ForeignKey(Group.name),
primary_key=True)
systemName = Column('SystemName', Enum('A', 'B', 'C'),
primary_key=True)
User.groups = relationship(Group, secondary=User2Group.__table__)
Table User2Group looks like following
UserName | GroupName | SystemName
-
fred | admins | A
fred | admins | B
fred | admins | C
However when trying to delete user 'fred' wich is assinged to group
'admins' via multiple systems A, B, C.
I'm getting error: DELETE statement on table 'User2Group' expected to
delete 1 row(s); Only 3 were matched.
Did I misconfigured something?
Thanks
--
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.
Re: [sqlalchemy] Re: many-to-many relation: unexpected count of rows
Yes.
However doing so:
user.keywords = [ Keyword('one'), Keyword('two') ]
...will create UserKeywords with user unset. This is the problem.
Dne čtvrtek 5. března 2015 15:11:20 UTC+1 Simon King napsal(a):
I haven't looked at your code closely so I'm not sure what the problem
is, but I'm not sure your creator factory should be necessary. The
association proxy docs say that something like:
kw = Keyword('one')
user.keywords.append(kw)
has the same effect as:
kw = Keyword('one')
uk = UserKeyword(kw)
user.user_keywords.append(uk)
In this code, the UserKeyword's user property is not set explicitly,
but because of the backref on User.user_keywords, appending to the
list will cause the user property to be assigned.
Simon
On Thu, Mar 5, 2015 at 11:46 AM, Pavel S pa...@schon.cz javascript:
wrote:
Hi,
I just noticed that User.groups relationship is not needed at all (it
was a
relict of previous setup before System column was added).
So the problem is completely gone.
---
I already use AssociationProxy pattern in order to have relations like
User.A_groups, User.B_groups, USer.C_groups.
However I figured out other issue that 'creator' callback to
AssociationProxy does not pass the parent object to it, which is weird.
See example:
user = ScopedSession.query(User).get('fred')
user.A_groups = [ Group('admins') ]
It should create also intermediary instance of User2Group. It does, but
user
attribute of intermediary object is null.
So I created AssociationFactory as the following:
class AssociationFactory(AssociationProxy):
def __init__(self, *args, **kwargs):
self.creator_factory = kwargs.pop('creator_factory')
super(AssociationFactory, self).__init__(*args, **kwargs)
def __get__(self, obj, class_):
if obj:
self.creator = self.creator_factory(weakref.ref(obj))
return super(AssociationFactory, self).__get__(obj, class_)
for system in ('A', 'B', 'C'):
def creator_factory(user_ref):
def creator(group):
user = user_ref()
if user is None:
raise TypeError('Stale object reference')
return User2Group(user=user, group=group, system=system)
return creator
setattr(User, system + '_groups',
AssociationFactory(system+'_users2groups', 'group',
creator_factory=creator_factory)
Then all works nicely. Do you see the point?
cheers!
Dne čtvrtek 5. března 2015 11:58:50 UTC+1 Simon King napsal(a):
Glad you've figured it out. This is touched on briefly in the docs, in
the note at the bottom of:
http://docs.sqlalchemy.org/en/rel_0_9/orm/basic_relationships.html#association-object
As an alternative to making User.groups a relationship, you could also
consider using the Association Proxy extension:
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html#simplifying-association-objects
Hope that helps,
Simon
On Thu, Mar 5, 2015 at 10:43 AM, Pavel S pa...@schon.cz wrote:
Hi,
Adding viewonly=True on User.groups relationship solved the issue.
cheers!
Dne čtvrtek 5. března 2015 11:26:58 UTC+1 Pavel S napsal(a):
Hello,
I have relation User-User2Group-Group with additional attribute
System
on
User2Group table.
The System is part of primary key, which means 'user can me member
of
group via multiple systems'.
class User(Base):
__tablename__ = 'User'
name = Column('Name', Unicode(256), primary_key=True)
class Group(Base):
__tablename__ = 'Group'
name = Column('Name', Unicode(256), primary_key=True)
class User2Group(Base):
__tablename__ = 'User2Group'
userName = Column('UserName', Unicode(256),
ForeignKey(User.name),
primary_key=True)
groupName = Column('GroupName', Unicode(256),
ForeignKey(Group.name),
primary_key=True)
systemName = Column('SystemName', Enum('A', 'B', 'C'),
primary_key=True)
User.groups = relationship(Group, secondary=User2Group.__table__)
Table User2Group looks like following
UserName | GroupName | SystemName
-
fred | admins | A
fred | admins | B
fred | admins | C
However when trying to delete user 'fred' wich is assinged to group
'admins' via multiple systems A, B, C.
I'm getting error: DELETE statement on table 'User2Group' expected
to
delete 1 row(s); Only 3 were matched.
Did I misconfigured something?
Thanks
--
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
Re: [sqlalchemy] SQLAlchemy as plain-SQL generator
Hi Richard, thanks, your solution works. ( I don't need formatted output) However using *query* twice in the expression looks to me a bit awkward. Isn't there some shortcut? Dne středa 22. dubna 2015 14:27:41 UTC+2 Richard Kuesters napsal(a): hi, you must use a specific dialect so sqlalchemy can create it for you. not the best usage, imho, but here it goes: *stmt = query.compile().process(query, literal_binds=True)* i don't know if you want it formated or what, if so, *sqlparse* provides a good way to do it. but, again, this will only work if you BIND an engine to your metadata, so sqlalchemy can write the right sql for you. also, your query should be a *select()* object, like your example. this worked for me for some time, using SA 0.9, i don't know about 1.0, but it should work too. cheers, richard. On 04/22/2015 04:26 AM, Pavel S wrote: Hello, I have pythonic application which imports custom module written in C++ using boost::python. The module creates database connection(s) and executes queries. The python calls various methods on that module and passes plain SQL into them. Results are then returned to python. I would like to use SQLALchemy Core to generate plain SQL strings which will be passed the module. Lets say I want to generate the following string: SELECT A, B FROM Tbl WHERE C = Foo; The equivalent in SA is: query = select([ Tbl.c.A, Tbl.c.B ]).where( Tbl.c.C == Foo ) How can I produce the string from that query? -- 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.
Re: [sqlalchemy] SQLAlchemy as plain-SQL generator
Hi Mike, your solutions works too. Thanks to you both!
Dne středa 22. dubna 2015 16:07:28 UTC+2 Michael Bayer napsal(a):
On 4/22/15 8:27 AM, Richard Gerd Kuesters wrote:
hi,
you must use a specific dialect so sqlalchemy can create it for you. not
the best usage, imho, but here it goes:
*stmt = query.compile().process(query, literal_binds=True)*
OK since Pavel noted the double compile, if you want the literal_binds
part (not clear if that is needed here?), it goes into compile()
stmt = str(query.compile(compile_kwargs={literal_binds: True}))
i don't know if you want it formated or what, if so, *sqlparse* provides
a good way to do it.
but, again, this will only work if you BIND an engine to your metadata, so
sqlalchemy can write the right sql for you. also, your query should be a
*select()* object, like your example.
this worked for me for some time, using SA 0.9, i don't know about 1.0,
but it should work too.
cheers,
richard.
On 04/22/2015 04:26 AM, Pavel S wrote:
Hello,
I have pythonic application which imports custom module written in C++
using boost::python.
The module creates database connection(s) and executes queries.
The python calls various methods on that module and passes plain SQL into
them. Results are then returned to python.
I would like to use SQLALchemy Core to generate plain SQL strings which
will be passed the module.
Lets say I want to generate the following string:
SELECT A, B FROM Tbl WHERE C = Foo;
The equivalent in SA is:
query = select([
Tbl.c.A,
Tbl.c.B
]).where(
Tbl.c.C == Foo
)
How can I produce the string from that query?
--
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+...@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] SAWarning: Unicode type received non-unicode bind param value
Hi, it happened to me many times during development, mainly when used custom column types, that I passed wrong type of value to the query. Then the the following warning was emitted: SAWarning: Unicode type received non-unicode bindparam value The problem with such warning is it does not say 1) were the problem occurred (the line number points to somewhere in sqlalchemy and not to my application) 2) what was the value which caused the problem ( repr() of that value would be nice to have) I always had to hack sqlalchemy/sql/sqltypes.py and add print value before the warning is emitted to actually see what was wrong. Is there any convenient way how to solve such issue? -- 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] SQLAlchemy as plain-SQL generator
Hello, I have pythonic application which imports custom module written in C++ using boost::python. The module creates database connection(s) and executes queries. The python calls various methods on that module and passes plain SQL into them. Results are then returned to python. I would like to use SQLALchemy Core to generate plain SQL strings which will be passed the module. Lets say I want to generate the following string: SELECT A, B FROM Tbl WHERE C = Foo; The equivalent in SA is: query = select([ Tbl.c.A, Tbl.c.B ]).where( Tbl.c.C == Foo ) How can I produce the string from that query? -- 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] SAWarning: Unicode type received non-unicode bind param value
If I turn warnings into errors, the problem is still the same:
1) where the error occured as the code path in traceback is not pointing to
the place where select() has been bound with values
2) you don't know what was the value
Dne středa 22. dubna 2015 10:09:51 UTC+2 SElsner napsal(a):
Hey,
you dan do:
import warnings
warnings.simplefilter('error')
This will raise an exception. and give you a stacktrace on where the
Unicode warnign happened.
On 04/22/2015 09:48 AM, Pavel S wrote:
Hi,
it happened to me many times during development, mainly when used custom
column types, that I passed wrong type of value to the query.
Then the the following warning was emitted:
SAWarning: Unicode type received non-unicode bindparam value
The problem with such warning is it does not say
1) were the problem occurred (the line number points to somewhere in
sqlalchemy and not to my application)
2) what was the value which caused the problem ( repr() of that value
would be nice to have)
I always had to hack sqlalchemy/sql/sqltypes.py and add print value
before the warning is emitted to actually see what was wrong.
Is there any convenient way how to solve such issue?
--
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.
--
*check out pointcloud9.com* http://pointcloud9.com/
* Sebastian Elsner - Pipeline Techincal Director - RISE t: +49 30 20180300
sebast...@risefx.com javascript: f: +49 30 61651074 www.risefx.com
http://www.risefx.com/*
*RISE FX GmbH*
* Schlesische Strasse 28, 10997 Berlin An der Schanz 1A, 50735 Köln
Büchsenstraße 20, 70174 Stuttgart Gumpendorferstrasse 55, 1060 Wien
Geschaeftsfuehrer: Sven Pannicke, Robert Pinnow Handelsregister Berlin HRB
106667 B*
--
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: func.substr() documentation?
You should refer documentation of your RDBMS. '*func*' is just proxy to any function defined inside RDBMS. If your RDBMS has function *bla* (e.g. CREATE FUNCTION bla...), then you can call if from python using *func.bla()*. Dne neděle 2. srpna 2015 19:12:04 UTC+2 c.b...@posteo.jp napsal(a): I see in a lot of example code func.substr(). But I can not find it in the official docs (not in my local ones, not on the website). Did I use the search feauter wrong? -- -BEGIN PGP PUBLIC KEY BLOCK- Version: GnuPG v1 mQENBFQIluABCACfPwAhRAwFD3NXgv5CtVUGSiqdfJGVViVBqaKd+14E0pASA0MU G0Ewj7O7cGy/ZIoiZ0+lIEZmzJKHfuGwYhXjR/PhnUDrQIHLBvh9WuD6JQuULXfH kXtVm/i9wm76QAcvr2pwYgNzhcJntUHl2GcgnInYbZDeVmg+p9yIPJjuq73/lRS3 0/McgNoFOBhKK/S6STQuFyjr9OyJyYd1shoM3hmy+kg0HYm6OgQBJNg92WV9jwGe GzlipvEp2jpLwVsTxYir2oOPhfd9D1fC9F/l/3gXbfjd5GIIVrZFq2haZmoVeJ33 LJxo3RA5Tf9LoUeels1b4s9kFz6h7+AHERUpABEBAAG0IUNocmlzdGlhbiBCdWh0 eiA8YnVodHpAcG9zdGVvLmRlPokBPgQTAQIAKAUCVAiW4AIbAwUJAeEzgAYLCQgH AwIGFQgCCQoLBBYCAwECHgECF4AACgkQZLsXsAdRqOxNUAf/V/hDA5zGDpySuCEj DhjiVRK74J9Wd8gfH0WAf1Co5HZ24wZH8rgOIVIgXw8rWkOw/VA6xfdfT+64xjTY Fhkpbrk199nDzp72F7Jc4NC+x8xac2e3rK5ifSWhZx7L5A32pGYE+d16m3EEqImK D4gcZl38x9zdUnD4hHyXkIPz1uCfuMuGgWEnaUk4Wbj41CBZr3O0ABue6regV15U jaes8r+B8iCcY+0yP2kse+3iaCaMqNv5FgQZ9+b2Cql8pFkZJVtBVUw4GW3DWZJi du0O/YrC9TgS+xY9ht/MD2qSHwjcK1sdImjqBO7xP8TIOwKeYyDvGKnSO3EJ/sSA UPGEPrkBDQRUCJbgAQgA0k/Qg67CCUJE2/zuxBEoK4wLJpDRJzh8CQPZpjWx8VP0 KL892jwfxymXn8KNhuy1SgCBFSeV9jg4VZNWDlUGJc2lo82ajr9PzIsrQwu4lf0B zrUWV5hWepKu/kb8uSjx58YYfx0SFz4+9akX3Wwu9TUHntzL5Gk3Q26nnsr1xEJ+ VEumvCH9AE0Tk0K7dQpJ2/JcLuO+uhrpd/lHFDYVN5NsG3P015uFOkDI6N/xNFCj v95XNR93QlfKpK3qWlFGescfG+o/7Ub6s67/i/JoNbw0XgPEHmQfXpD7IHO4cu+p +ETb11cz+1mmi96cy98ID+uTiToJ8G//yD9rmtyxoQARAQABiQElBBgBAgAPBQJU CJbgAhsMBQkB4TOAAAoJEGS7F7AHUajs6sQH/iKs6sPc0vkRJLfbwrijZeecwCWF blo/jzIQ8jPykAj9SLjV20Xwqg3XcJyko8ZU6/zuRJq9xjlv9pZr/oVudQAt6v+h 2Cf4rKEjmau483wjMV2xjTXQhZi9+ttDbia4fgdmGtKsOicn5ae2fFXcXNPu3RiW sZKifWdokA6xqMW6iIG9YjjI5ShxngHWp2xfPscBFMDRtFOMags/Yx+YvwoyEZ4A dURYMFHFqpwILEc8hIzhRg1gq40AHbOaEdczS1Rr3T7/gS6eBs4u6HuY5g2Bierm lLjpspFPjMXwJAa/XLOBjMF2vsHPrZNcouNKkumQ36yq/Pm6DFXAseQDxOk= =PGP9 -END PGP PUBLIC KEY BLOCK- -- 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] onupdate with ORM
Hi,
I have declarative Model with couple of columns, one of them is
lastAccess = Column('LastAccess', DateTime, nullable=False,
onupdate=datetime.datetime.utcnow)
I fetch the object from database like the following:
obj = ScopedSession.query(Model).get(something)
Later in code I commit the session as:
ScopedSession.commit()
I would like to have *lastAccess* updated in database when issuing
*commit()*. However the column remains unchanged.
I do understand that *onupdate* callback is called on update, but how to
trigger the update since I don't want to change any other column?
In other words, how can I mark the *obj* as "dirty" so *onupdate* will take
effect?
--
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] ORM: walk() related objects
Hi Michael, this is amazing, thanks!!!
On Thursday, September 10, 2015 at 3:35:39 PM UTC+2, Michael Bayer wrote:
>
>
>
> On 9/10/15 8:48 AM, Pavel S wrote:
>
> Let's say, I have declarative classes A, B, C, D.
>
> A is the parent
> B has FK to A
> C has FK to B,
> D has FK to C etc...
>
> I'd like to implement *generic method* walk(obj) which will recursively
> yield dependent/related objects of obj (which is instance of A).
>
> I know that there is introspection interface inspect(), however I'm don't
> really understand how to use it properly in my use case.
>
> Shall I do inspect(obj) or rather inspect(obj.__class__) and then somehow
> apply inspection to obj?
>
> Are there an examples and best practices?
>
> right now you can kind of get this effect using cascade_iterator:
> http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=cascade_iterator#sqlalchemy.orm.mapper.Mapper.cascade_iterator
>
> the limitation is that right now its based on relationship cascade
> settings, as that's what it was intended for, so you'd probably want to use
> "save-update":
>
> insp = inspect(my_object)
> for obj in insp.mapper.cascade_iterator("save-update", insp):
># ...
>
> to implement your own system, the graph of objects is strictly based on
> relationship. so walk() is pretty simple:
>
> def walk(obj):
> yield obj
> insp = inspect(obj)
> for relationship in insp.mapper.relationships:
> related = getattr(obj, relationship.key)
> if relationship.uselist:
> for collection_member in related:
> for walk_related in walk(collection_member):
> yield walk_related
> elif related is not None:
> for walk_related in walk(related):
> yield walk_related
>
>
>
>
>
>
> --
> 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 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] ORM: walk() related objects
Let's say, I have declarative classes A, B, C, D. A is the parent B has FK to A C has FK to B, D has FK to C etc... I'd like to implement *generic method* walk(obj) which will recursively yield dependent/related objects of obj (which is instance of A). I know that there is introspection interface inspect(), however I'm don't really understand how to use it properly in my use case. Shall I do inspect(obj) or rather inspect(obj.__class__) and then somehow apply inspection to obj? Are there an examples and best practices? -- 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] ORM: walk() related objects
In the meanwhile, I've implemented similar:
def walk(obj, level=0, memo=None):
memo = memo or set()
if obj not in memo:
yield level, obj
memo.add(obj)
insp = inspect(obj)
for relationship in insp.mapper.relationships:
related = getattr(obj, relationship.key)
if relationship.uselist:
for collection_member in related:
for walk_related in walk(collection_member, level + 1,
memo):
yield walk_related
elif related is not None:
for walk_related in walk(related, level + 1, memo):
yield walk_related
On Thursday, September 10, 2015 at 3:48:55 PM UTC+2, Michael Bayer wrote:
>
>
>
> On 9/10/15 9:35 AM, Mike Bayer wrote:
>
>
>
> On 9/10/15 8:48 AM, Pavel S wrote:
>
> Let's say, I have declarative classes A, B, C, D.
>
> A is the parent
> B has FK to A
> C has FK to B,
> D has FK to C etc...
>
> I'd like to implement *generic method* walk(obj) which will recursively
> yield dependent/related objects of obj (which is instance of A).
>
> I know that there is introspection interface inspect(), however I'm don't
> really understand how to use it properly in my use case.
>
> Shall I do inspect(obj) or rather inspect(obj.__class__) and then somehow
> apply inspection to obj?
>
> Are there an examples and best practices?
>
> right now you can kind of get this effect using cascade_iterator:
> http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=cascade_iterator#sqlalchemy.orm.mapper.Mapper.cascade_iterator
>
> the limitation is that right now its based on relationship cascade
> settings, as that's what it was intended for, so you'd probably want to use
> "save-update":
>
> insp = inspect(my_object)
> for obj in insp.mapper.cascade_iterator("save-update", insp):
># ...
>
> to implement your own system, the graph of objects is strictly based on
> relationship. so walk() is pretty simple:
>
> def walk(obj):
> yield obj
> insp = inspect(obj)
> for relationship in insp.mapper.relationships:
> related = getattr(obj, relationship.key)
> if relationship.uselist:
> for collection_member in related:
> for walk_related in walk(collection_member):
> yield walk_related
> elif related is not None:
> for walk_related in walk(related):
> yield walk_related
>
>
> here's one im putting in the FAQ for now, which solves recursion depth as
> well as cycles:
>
> def walk(obj):
> stack = [obj]
>
> seen = set()
>
> while stack:
> obj = stack.pop(0)
> if obj in seen:
> continue
> else:
> seen.add(obj)
> yield obj
> insp = inspect(obj)
> for relationship in insp.mapper.relationships:
> related = getattr(obj, relationship.key)
> if relationship.uselist:
> stack.extend(related)
> elif related is not None:
> stack.append(related)
>
>
>
>
>
>
>
>
>
>
> --
> 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 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+...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> 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.
Re: [sqlalchemy] ORM: walk() related objects
Final version:
def walk_related(obj, enableDuplicates=False, level=-1, memo=None):
memo = memo or set()
insp = inspect(obj)
for relobj in insp.mapper.cascade_iterator('delete', insp):
if enableDuplicates or relobj[0] not in memo:
memo.add(relobj[0])
yield level, relobj[0]
relatedObjects = walk_related(relobj[0],
enableDuplicates=enableDuplicates, level=level + 1, memo=
memo)
for walked_relobj in relatedObjects:
yield walked_relobj
Dne pondělí 14. září 2015 11:57:05 UTC+2 Pavel S napsal(a):
>
> Hi,
>
> I just realized that I need your first solution, since I need to get only
> those objects that would cascade in case of deletion.
>
> But thanks anyhow...
>
> P
>
> Dne čtvrtek 10. září 2015 15:35:39 UTC+2 Michael Bayer napsal(a):
>>
>>
>>
>> On 9/10/15 8:48 AM, Pavel S wrote:
>>
>> Let's say, I have declarative classes A, B, C, D.
>>
>> A is the parent
>> B has FK to A
>> C has FK to B,
>> D has FK to C etc...
>>
>> I'd like to implement *generic method* walk(obj) which will recursively
>> yield dependent/related objects of obj (which is instance of A).
>>
>> I know that there is introspection interface inspect(), however I'm
>> don't really understand how to use it properly in my use case.
>>
>> Shall I do inspect(obj) or rather inspect(obj.__class__) and then
>> somehow apply inspection to obj?
>>
>> Are there an examples and best practices?
>>
>> right now you can kind of get this effect using cascade_iterator:
>> http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=cascade_iterator#sqlalchemy.orm.mapper.Mapper.cascade_iterator
>>
>> the limitation is that right now its based on relationship cascade
>> settings, as that's what it was intended for, so you'd probably want to use
>> "save-update":
>>
>> insp = inspect(my_object)
>> for obj in insp.mapper.cascade_iterator("save-update", insp):
>># ...
>>
>> to implement your own system, the graph of objects is strictly based on
>> relationship. so walk() is pretty simple:
>>
>> def walk(obj):
>> yield obj
>> insp = inspect(obj)
>> for relationship in insp.mapper.relationships:
>> related = getattr(obj, relationship.key)
>> if relationship.uselist:
>> for collection_member in related:
>> for walk_related in walk(collection_member):
>> yield walk_related
>> elif related is not None:
>> for walk_related in walk(related):
>> yield walk_related
>>
>>
>>
>>
>>
>>
>> --
>> 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 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.
Re: [sqlalchemy] ORM: walk() related objects
Hi,
I just realized that I need your first solution, since I need to get only
those objects that would cascade in case of deletion.
But thanks anyhow...
P
Dne čtvrtek 10. září 2015 15:35:39 UTC+2 Michael Bayer napsal(a):
>
>
>
> On 9/10/15 8:48 AM, Pavel S wrote:
>
> Let's say, I have declarative classes A, B, C, D.
>
> A is the parent
> B has FK to A
> C has FK to B,
> D has FK to C etc...
>
> I'd like to implement *generic method* walk(obj) which will recursively
> yield dependent/related objects of obj (which is instance of A).
>
> I know that there is introspection interface inspect(), however I'm don't
> really understand how to use it properly in my use case.
>
> Shall I do inspect(obj) or rather inspect(obj.__class__) and then somehow
> apply inspection to obj?
>
> Are there an examples and best practices?
>
> right now you can kind of get this effect using cascade_iterator:
> http://docs.sqlalchemy.org/en/rel_1_0/orm/mapping_api.html?highlight=cascade_iterator#sqlalchemy.orm.mapper.Mapper.cascade_iterator
>
> the limitation is that right now its based on relationship cascade
> settings, as that's what it was intended for, so you'd probably want to use
> "save-update":
>
> insp = inspect(my_object)
> for obj in insp.mapper.cascade_iterator("save-update", insp):
># ...
>
> to implement your own system, the graph of objects is strictly based on
> relationship. so walk() is pretty simple:
>
> def walk(obj):
> yield obj
> insp = inspect(obj)
> for relationship in insp.mapper.relationships:
> related = getattr(obj, relationship.key)
> if relationship.uselist:
> for collection_member in related:
> for walk_related in walk(collection_member):
> yield walk_related
> elif related is not None:
> for walk_related in walk(related):
> yield walk_related
>
>
>
>
>
>
> --
> 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 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] unregister Tables from Metadata
Hi, we use application-level partitioning (but no real partitioning in mysqld), where new tables are created daily with the same structure, but different name (suffix). - mysqld is shared for these components: - daemon written in C++ - creates new tables every day and fills them with records - other daemon written in Python: - periodically scans *information_schema.tables* for new tables and creates Table objects, which haven't been initialized before - the same daemon then processes records from all tables and computes some statistics on top of them - daily cronjob drops tables older than *n* days *The problem:* Pythonic daemon has to somehow notice that some tables were dropped and unload them from Metadata object. Otherwise, it will fail on ProgrammingError: table does not exit We don't want to establish any messaging between daemons, just to keep the setup as it is. Daemon should compare what's in *information_schema* and what's in Metadata and remove Table objects. How to do that? -- 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] Debugging complex SQLAlchemy applications
Hi, I maintain extremely complex application (>100k LOC) which uses SQLAlchemy. Often in the log I find errors like: sqlalchemy/engine/default.py:450: Warning: Truncated incorrect DOUBLE value: 'Foo' or sqlalchemy/sql/sqltypes.py:201: SAWarning: Unicode type received non-unicode bind param value 'Foo'. (this warning may be suppressed after 10 occurrences) (util.ellipses_string(value),)) While those are just warnings and even if I turn warning into errors, traceback does NOT point to the place where the problem was caused, which is very frustrating. What's the best practice finding and solving those problems? -- 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.
