[sqlalchemy] pickling SQLAlchemy exceptions
Hi, See http://stackoverflow.com/questions/8785899/hang-in-python-script-using-sqlalchemy-and-multiprocessing particularly sbt's answer (http://stackoverflow.com/a/8795763/350713), and also the Python bug reports http://bugs.python.org/issue13751 and http://bugs.python.org/issue1692335. The upshot is that SQLAs exceptions don't play nice with Pythons pickle. While this may be a known issue, I could not find any discussion of it. If this is considered a bug, let me know, and I'll file a bug report. So, if possible, consider changing things to that SQLAs exceptions do work with pickle. Also, I'd welcome any suggestions how to implement sbt's copy_reg suggestion for the SQLA exception classes. Can I implement one workaround, or so I need one for every class that has a non-trivial constructor? Regards, Faheem -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Sequence on non primary_key and import data
Hi,
I am running into something
I am using MySQL but am moving to Postgress so I'm looking for something
compatible with the two.
I have to generate an unique number for each record created into a column
separate from the Id, call it SerialNumber
I have found the Sequence object.
Say I make the following class:
class Order(Base):
__tablename__ = 'product'
Id = Column(Integer, primary_Key=True)
SerialNr= Column(Integer,
Sequence('SerialNumber'))
after the create_all()
CreateSequence('SerialNumber')
All fine.
Now I need to import data from the current production system. This data already
has Serialnumbers generated.
What should I do to make this work? Do I need the sequence created after the
Import and set the Start value to the last imported SerialNumber+1 ?
I would prefer creating the sequence before the import and Update the
Sequence after the import.
I can also Drop and Recreate after the import. The Import will be done
several times during the test period. After the code is in production, I'll
never ever need to modify the seqence anymore.
Adding another table with just one column and setting SerialNr as a ForeignKey
might be a solution too (As SerialNr will also be a coded as a 4 character
string which needs to be in the database for compatability reasons)
I prefer the sequence though.
My real code is much more complex, the above example class is simplified. In
fact Order is a polymorphic base table and the sequence Column is in the
polymorphic child tables only where Orders are in fact Items to be produced.
The simplified version above is just to make things clear.
Martijn
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.7 event migration
funny story, here's where it was added: http://www.sqlalchemy.org/trac/ticket/1910 which is essentially your ticket ! :) I just double checked and I had patched in rfde41d0e9f70 http://www.sqlalchemy.org/trac/changeset/fde41d0e9f70/. Is there another commit that went against 1910? For example, was there logic in the attachment /load_on_fks.patch/ http://www.sqlalchemy.org/trac/attachment/ticket/1910/load_on_fks.patch that was committed? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: 0.7 event migration
See http://www.sqlalchemy.org/trac/ticket/2372 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] pickling SQLAlchemy exceptions
On Jan 10, 2012, at 4:01 AM, Faheem Mitha wrote: Hi, See http://stackoverflow.com/questions/8785899/hang-in-python-script-using-sqlalchemy-and-multiprocessing particularly sbt's answer (http://stackoverflow.com/a/8795763/350713), and also the Python bug reports http://bugs.python.org/issue13751 and http://bugs.python.org/issue1692335. The upshot is that SQLAs exceptions don't play nice with Pythons pickle. While this may be a known issue, I could not find any discussion of it. If this is considered a bug, let me know, and I'll file a bug report. So, if possible, consider changing things to that SQLAs exceptions do work with pickle. Also, I'd welcome any suggestions how to implement sbt's copy_reg suggestion for the SQLA exception classes. Can I implement one workaround, or so I need one for every class that has a non-trivial constructor? got the ticket and we can continue there on trac, unless something controversial comes up. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: 0.7 event migration
Mike,
Old code:
==
def visit_bindparam(bindparam):
if bindparam.key in bind_to_col:
bindparam.value = lambda:
mapper._get_state_attr_by_column(
state, dict_,
bind_to_col[bindparam.key])
==
New code (note that 'value' is now 'callable'):
def visit_bindparam(bindparam):
if bindparam._identifying_key in bind_to_col:
bindparam.callable = \
lambda: mapper._get_state_attr_by_column(
state, dict_,
bind_to_col[bindparam._identifying_key])
==
Now look at sql.util.py:
==
def bind_values(clause):
v = []
def visit_bindparam(bind):
value = bind.value
# evaluate callables
if callable(value):
value = value()
v.append(value)
visitors.traverse(clause, {}, {'bindparam':visit_bindparam})
return v
==
Aren't we missing this: ?
if bind.callable:
value = bind.callable()
I think this is why it isn't loading the way it used to.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] already has a primary mapper defined error occurs occasionally
I'm running web service using sqlalchemy with apache + wsgi + pyramid framework. I've initialize metadata like the following: Base = declarative_base() Base.metadata.reflect(bind=my_engine) user_table = DP_Base.metadata.tables[user] mapper(User, user_table) and I'm using scoped session since I'm running apache+wsgi with multiple processes and threads. weird thing is that, errors occur sometimes saying ArgumentError: Class 'class 'mypackage.User'' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. Once the error occurs, all the following requests to the web service encounter the same errors. Restarting httpd seems to resolve the problem temporarily. I'm afraid that I'm using reflection and mapping in the wrong way? or Is there something that I have to care about among multi-threaded, scoped session, and table mapping? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/TuEWdqXlg6EJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.7 event migration
Code wasn't covered and is a regresssion, fixed in rd6e321dc120d.
On Jan 10, 2012, at 10:58 AM, Kent wrote:
Mike,
Old code:
==
def visit_bindparam(bindparam):
if bindparam.key in bind_to_col:
bindparam.value = lambda:
mapper._get_state_attr_by_column(
state, dict_,
bind_to_col[bindparam.key])
==
New code (note that 'value' is now 'callable'):
def visit_bindparam(bindparam):
if bindparam._identifying_key in bind_to_col:
bindparam.callable = \
lambda: mapper._get_state_attr_by_column(
state, dict_,
bind_to_col[bindparam._identifying_key])
==
Now look at sql.util.py:
==
def bind_values(clause):
v = []
def visit_bindparam(bind):
value = bind.value
# evaluate callables
if callable(value):
value = value()
v.append(value)
visitors.traverse(clause, {}, {'bindparam':visit_bindparam})
return v
==
Aren't we missing this: ?
if bind.callable:
value = bind.callable()
I think this is why it isn't loading the way it used to.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] already has a primary mapper defined error occurs occasionally
On Jan 10, 2012, at 11:38 AM, Josh Ha-Nyung Chung wrote: I'm running web service using sqlalchemy with apache + wsgi + pyramid framework. I've initialize metadata like the following: Base = declarative_base() Base.metadata.reflect(bind=my_engine) user_table = DP_Base.metadata.tables[user] mapper(User, user_table) and I'm using scoped session since I'm running apache+wsgi with multiple processes and threads. weird thing is that, errors occur sometimes saying ArgumentError: Class 'class 'mypackage.User'' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. Once the error occurs, all the following requests to the web service encounter the same errors. Restarting httpd seems to resolve the problem temporarily. I'm afraid that I'm using reflection and mapping in the wrong way? or Is there something that I have to care about among multi-threaded, scoped session, and table mapping? does User extend Base ? In that case, it's already mapped. Unless you have a real need to use classical mappings there's no need to use mapper() (would be also curious what lead to that decision as it may be a documentation issue). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] already has a primary mapper defined error occurs occasionally
User class extends object, not Base. Actually User class is imported from another package, which is made by my coworker, and I need to separate my work from his. So I have no choice but using classical mapping since the User class is intended to be used with classical mapping. -- Josh Ha-Nyung Chung minorbl...@gmail.com On Wednesday, January 11, 2012 at 1:48 AM, Michael Bayer wrote: On Jan 10, 2012, at 11:38 AM, Josh Ha-Nyung Chung wrote: I'm running web service using sqlalchemy with apache + wsgi + pyramid framework. I've initialize metadata like the following: Base = declarative_base() Base.metadata.reflect(bind=my_engine) user_table = DP_Base.metadata.tables[user] mapper(User, user_table) and I'm using scoped session since I'm running apache+wsgi with multiple processes and threads. weird thing is that, errors occur sometimes saying ArgumentError: Class 'class 'mypackage.User'' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. Once the error occurs, all the following requests to the web service encounter the same errors. Restarting httpd seems to resolve the problem temporarily. I'm afraid that I'm using reflection and mapping in the wrong way? or Is there something that I have to care about among multi-threaded, scoped session, and table mapping? does User extend Base ? In that case, it's already mapped. Unless you have a real need to use classical mappings there's no need to use mapper() (would be also curious what lead to that decision as it may be a documentation issue). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com (mailto:sqlalchemy@googlegroups.com). To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com (mailto:sqlalchemy+unsubscr...@googlegroups.com). For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: 0.7 event migration
Thank you very much!
On 1/10/2012 11:47 AM, Michael Bayer wrote:
Code wasn't covered and is a regresssion, fixed in rd6e321dc120d.
On Jan 10, 2012, at 10:58 AM, Kent wrote:
Mike,
Old code:
==
def visit_bindparam(bindparam):
if bindparam.key in bind_to_col:
bindparam.value = lambda:
mapper._get_state_attr_by_column(
state, dict_,
bind_to_col[bindparam.key])
==
New code (note that 'value' is now 'callable'):
def visit_bindparam(bindparam):
if bindparam._identifying_key in bind_to_col:
bindparam.callable = \
lambda: mapper._get_state_attr_by_column(
state, dict_,
bind_to_col[bindparam._identifying_key])
==
Now look at sql.util.py:
==
def bind_values(clause):
v = []
def visit_bindparam(bind):
value = bind.value
# evaluate callables
if callable(value):
value = value()
v.append(value)
visitors.traverse(clause, {}, {'bindparam':visit_bindparam})
return v
==
Aren't we missing this: ?
if bind.callable:
value = bind.callable()
I think this is why it isn't loading the way it used to.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] already has a primary mapper defined error occurs occasionally
On Jan 10, 2012, at 12:12 PM, Josh Ha-Nyung Chung wrote: User class extends object, not Base. Actually User class is imported from another package, which is made by my coworker, and I need to separate my work from his. So I have no choice but using classical mapping since the User class is intended to be used with classical mapping. well mapper() is being called twice on it, somehow, that's all it is. -- Josh Ha-Nyung Chung minorbl...@gmail.com On Wednesday, January 11, 2012 at 1:48 AM, Michael Bayer wrote: On Jan 10, 2012, at 11:38 AM, Josh Ha-Nyung Chung wrote: I'm running web service using sqlalchemy with apache + wsgi + pyramid framework. I've initialize metadata like the following: Base = declarative_base() Base.metadata.reflect(bind=my_engine) user_table = DP_Base.metadata.tables[user] mapper(User, user_table) and I'm using scoped session since I'm running apache+wsgi with multiple processes and threads. weird thing is that, errors occur sometimes saying ArgumentError: Class 'class 'mypackage.User'' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper. clear_mappers() will remove *all* current mappers from all classes. Once the error occurs, all the following requests to the web service encounter the same errors. Restarting httpd seems to resolve the problem temporarily. I'm afraid that I'm using reflection and mapping in the wrong way? or Is there something that I have to care about among multi-threaded, scoped session, and table mapping? does User extend Base ? In that case, it's already mapped. Unless you have a real need to use classical mappings there's no need to use mapper() (would be also curious what lead to that decision as it may be a documentation issue). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: SQL Alchemy Closure Table Relationship Definition
Thanks for persisting with me - your solution was almost perfect, and
really pointing me in the right direction. Below is what eventually
worked:
areas = relationship('DbArea', backref = backref('routes',
order_by = 'DbRoute.name'),
secondary = area_relationship_table,
primaryjoin = area_id == area_relationship_table.c.descendent,
secondaryjoin = DbArea.area_id ==
area_relationship_table.c.ancestor,
innerjoin = True, order_by = DbArea.name,
foreign_keys = [area_relationship_table.c.ancestor,
area_relationship_table.c.descendent])
I swapped your descendent and ancestor, and added the foreign_keys due
a warning that I was receiving 'SAWarning: No ForeignKey objects were
present in secondary table 'area_relationship' ...'. I also added
some sorting for convenience.
This back and forth really helped me out in my work, but also to
better understand SA and particularly SA relationships. A million
thanks!
Jon
On Jan 9, 11:22 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 9, 2012, at 11:58 PM, jonstjohn wrote:
The route and area relationship is similar to the hypothetical problem
of a business location. Suppose you have a set of business locations,
each in a specific city. The business must be associated with one and
only one city. The city is located in ever widening areas, e.g., the
county, region, state, country, planet, etc. Suppose you want to find
all businesses within a given county, or a state. I'm not sure I
agree that storing the city id in the business violates normalization,
since the business can have only one city.
And I'm not sure that
storing the business id in every geographical designation (i.e.,
country, region, state, etc) is a better design. On the contrary, I
think that you would want to store the city id with the business, and
the relationship between geographical entities separately.
I was going to suggest relating the DbRoute directly to DbArea, then I
noticed that you've actually done this with DbRoute.area_id and DbRoute.area,
hadn't noticed that before.
DbRoute.areas asks relationship() to do something impossible - you're asking
it to load DbArea objects but then the relationship is forced to not look at
any columns that are actually in the DbArea table. You have it linking
back to DbRoute.area_id on both sides. Hence it tries to link
DbRoute.area_id to DbArea and fails.
As I mentioned in the original post, I can construct an SQL query that
gets at this relationship (sorry if it got buried):
SELECT route.route_id, route.name
FROM area
INNER JOIN area_relationship ON area.area_id =
area_relationship.ancestor
INNER JOIN route ON route.area_id = area_relationship.descendent
WHERE area.area_id = 1
This query suggests linking route on one side and area on the other, which is
more traditional, so you'd just need to link to DbArea.area_id:
areas = relationship('DbArea', backref = 'routes',
secondary = area_relationship_table,
primaryjoin = area_id == area_relationship_table.c.ancestor,
secondaryjoin = DbArea.area_id == area_relationship_table.c.descendent,
innerjoin=True)
This should produce the equivalent idea, an implicit join when lazily loaded
and INNER JOIN if joinedload() is used.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] More 0.7 migration pains
After 0.7.5 migration, I'm sometimes hitting an issue from within merge(). unitofwork.py def track_cascade_events(descriptor, prop): ... def set_(state, newvalue, oldvalue, initiator): # process save_update cascade rules for when an instance # is attached to another instance if oldvalue is newvalue: return newvalue sess = session._state_session(state) if sess: prop = state.manager.mapper._props[key] if newvalue is not None: newvalue_state = attributes.instance_state(newvalue) if prop.cascade.save_update and \ (prop.cascade_backrefs or key == initiator.key) and \ not sess._contains_state(newvalue_state): sess._save_or_update_state(newvalue_state) if oldvalue is not None and prop.cascade.delete_orphan: oldvalue_state = attributes.instance_state(oldvalue) # = if oldvalue_state in sess._new and \ prop.mapper._is_orphan(oldvalue_state): sess.expunge(oldvalue) return newvalue I'm hitting here with an oldvalue of attributes.PASSIVE_NO_RESULT, which naturally has no instance_state() ! So the first question is: is this due to one of my transient loader hacks or can you think of a path through merge() that you might reach this set event with an oldvalue of attributes.PASSIVE_NO_RESULT? Thanks again, of course! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] More 0.7 migration pains
On Jan 10, 2012, at 5:45 PM, Kent wrote: After 0.7.5 migration, I'm sometimes hitting an issue from within merge(). unitofwork.py def track_cascade_events(descriptor, prop): ... def set_(state, newvalue, oldvalue, initiator): # process save_update cascade rules for when an instance # is attached to another instance if oldvalue is newvalue: return newvalue sess = session._state_session(state) if sess: prop = state.manager.mapper._props[key] if newvalue is not None: newvalue_state = attributes.instance_state(newvalue) if prop.cascade.save_update and \ (prop.cascade_backrefs or key == initiator.key) and \ not sess._contains_state(newvalue_state): sess._save_or_update_state(newvalue_state) if oldvalue is not None and prop.cascade.delete_orphan: oldvalue_state = attributes.instance_state(oldvalue) # = if oldvalue_state in sess._new and \ prop.mapper._is_orphan(oldvalue_state): sess.expunge(oldvalue) return newvalue I'm hitting here with an oldvalue of attributes.PASSIVE_NO_RESULT, which naturally has no instance_state() ! So the first question is: is this due to one of my transient loader hacks or can you think of a path through merge() that you might reach this set event with an oldvalue of attributes.PASSIVE_NO_RESULT? Well many-to-ones, when you replace the value, generally don't load the old value since it's not needed for the UOW. if you set the active_history=True flag then it will load the old value when a new one is coming in. It used to load the old value for many years until people complained enough about how wasteful it was. Not sure here of 0.6/0.7 changes, it's very possible the mechanics there have been tweaked. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
