Re: [sqlalchemy] Re: hierarchical data storage and searching
On 08/09/2010 19:23, Gunnlaugur Briem wrote: http://communities.bmc.com/communities/docs/DOC-9902 Thanks for all of the references, but this one in particular. Materialized paths looks like its the closest to what I'm after. However, with materialized paths, I'm wondering with a structure like: / /a/ /a/b /a/b/1 /a/b/2 /a/c /b/ /b/1 /b/2 How to phrases the sql to answer the question: Does the current user have access to anything in /a or below ...particularly where the user is granted access only to /a/b/1, for example. and here for links to more than you really want to know on the subject: http://troels.arvin.dk/db/rdbms/links/#hierarchical Hah, excellent, thanks :-) SQLAlchemy support any of these approaches well enough that I don't think you need to factor SQLAlchemy into your choice of relational design at all. Check out /examples/nested_sets/ and /examples/ adjacency_list/ (in the SQLAlchemy distribution) for what are probably the two most common approaches. Cool, I'd forgotten about them, although the nested sets example doesn't appear to show the delete or modify implementations, which are the tricky bits ;-) cheers, Chris PS: I appreciate that while I need to get any solution working with SQLAlchemy, this is a generic relational database question; if anyone can point me at a more correct forum to ask, I'll be happy to do so there :-) -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] SQL / SQLAlchemy'centric approach to problem
Hi All I'm new to SQLAlchemy (love it) and also new to SQL in general, although I have 25 years experience in a range of obscure databases. I have what I hope will be a simple question as I believe I'm missing a critical understanding of some of the underlying SQL machinery. This is the situation (dumbed down for the example); 1) I have selected a row from a table somewhere else and therefore have a session.query resulted mapped class instance of that row (Or the primary key(s), which ever is most useful). We'll call this row Fred. 2) I have an ordered_by x result of a query on the same table (with many rows) that I wish to navigate with first/next/prev/last type controls. This I have implemented using the cool result[position] syntax and that all works fine. (Gets a little slow over several million rows, but that's outside the scope of the design requirement) My problem is this; I want to find out what position my recordFred is within the larger result set.. so that I can then locate myself onto fred and move to the next or previous row in that query as normal. To do that, I need the position value so I can do the slice. Sure, I could do an .all() on the query and then loop through until I found fred counting as I go, but that's horrible and smacks of you don't know what you are doing.. Picture it like this; table has rows 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. I query that and order it by something else which gives me an ordered result of 1, 3, 5, 7, 9, 2, 4, 6, 8, 0 I know that fred's key is 9, so I want to determine that in the ordered list (above), 9 is the 4th (zero based) element. I can then locate fred by saying result[4] and next is simply result[5] etc. Is there a good way? I suspect I could do something with a secondary query that would produce the results 1, 3, 5, 7 and then I could count that and that would be my position, but it's all sounding a little amateurish to me. Any advice would be most appreciated. :-) Cheers Warwick Warwick Prince Managing Director mobile: +61 411 026 992 skype: warwickprince phone: +61 7 3102 3730 fax: +61 7 3319 6734 web: www.mushroomsys.com -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Combining aliases with labels
On 09/08/2010 01:05 PM, Jack Kordas wrote:
When I try to use both aliases and labels, the results are not named
as expected.
Instead of being able to access the columns as label-name_column-
name it appears as original-table-name_numeric-sequence_column-
name
Thanks,
Jack
Sample code follows:
parent = Table('parent', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128)),
Column('first_id', INTEGER(),
ForeignKey(u'child.id')),
)
child = Table('child', metadata,
Column('id', INTEGER(), primary_key=True),
Column('name', VARCHAR(length=128))
)
def test_labels1(conn):
s = select([parent,child], use_labels=True)
s = s.where(parent.c.first_id==child.c.id)
return conn.execute(s).fetchone()
def test_alias1(conn):
firstchild = child.alias()
s = select([parent,firstchild], use_labels=True)
s = s.where(parent.c.first_id==firstchild.c.id)
return conn.execute(s).fetchone()
conn = engine.connect()
results = test_labels1(conn)
print results.parent_name
print results.child_name
results = test_alias1(conn)
print 'alias1 results: '
print results.parent_name
#print results.firstchild_name # expected this to work
print results.child_1_name # this worked instead
You need to set an explicit name for the alias to prevent SQLAlchemy
from generating an anonymous name[1]:
firstchild = child.alias(firstchild)
-Conor
[1]
http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.FromClause.alias
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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: hierarchical data storage and searching
On 09/09/2010 02:18 AM, Chris Withers wrote: On 08/09/2010 19:23, Gunnlaugur Briem wrote: http://communities.bmc.com/communities/docs/DOC-9902 Thanks for all of the references, but this one in particular. Materialized paths looks like its the closest to what I'm after. However, with materialized paths, I'm wondering with a structure like: / /a/ /a/b /a/b/1 /a/b/2 /a/c /b/ /b/1 /b/2 How to phrases the sql to answer the question: Does the current user have access to anything in /a or below ...particularly where the user is granted access only to /a/b/1, for example. It's hard to give specifics without knowing what kind of access control you are using (e.g. separate read/write permissions, inherited permissions, full ACLs), but here is one attempt: SELECT EXISTS (SELECT 1 FROM access_control WHERE (path = '/a' OR path LIKE '/a/%') AND user = :user AND permission = :permission) Most materialized path queries use LIKE a lot. As long as you keep the % character at the end, a good DB will be able to use an index to speed up the query. -Conor -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: hierarchical data storage and searching
Conor wrote: SELECT EXISTS (SELECT 1 FROM access_control WHERE (path = '/a' OR path LIKE '/a/%') AND user = :user AND permission = :permission) Most materialized path queries use LIKE a lot. As long as you keep the % character at the end, a good DB will be able to use an index to speed up the query. Ah, of course, many thanks! :-) Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Copying instances from old to new schema DB
Thank you Michael, that's for the catch-the-error part. How about the set-relations-right part, if I decided to go with my fix-as-you go recipe ? do you have any idea ? the dictionary approach seems good, but I still am curious about how to set relations generically on models. I think I'll use it somewhere else in my code. By the way, I think the pseudo should have been : instance = next_instance_from_csv() try: session.commit() except IntegrityError,e : session.rollback() if e.orig == UniqueConstraintError : original_instance = ModelClass.get(instance.id) for relation_name in instance.get_relation_names() : # Is this correct ? path_to_attribute= mapper.+ relation +.inverse # I don't know where to find this ? instance.set_attribute(path_to_attribute,original_instance) session.commit() class BaseModel (DeclarativeBase): ... def set_attribute(self,path,value): nodes= path.explode(.) current_node = self for next_node in nodes : current_node = getattr(current_node,next_node,None) if not current_node: raise InvalidAttribute set_attribute(current_node,value) # sqla's set_attribute, for whatever reason... Y.Chaouche --- On Thu, 9/9/10, Michael Bayer mike...@zzzcomputing.com wrote: From: Michael Bayer mike...@zzzcomputing.com Subject: Re: [sqlalchemy] Copying instances from old to new schema DB To: sqlalchemy@googlegroups.com Date: Thursday, September 9, 2010, 5:59 AM On Sep 9, 2010, at 6:31 AM, chaouche yacine wrote: Hello list, My schema has changed, and now I want to retrieve my old data (of the old schema) to the new database (with the new schema) from csv files (I export the tables of the old database to csv files and then load those csv files to the new database with some column mapping and some gap filling for new columns) The problem is that my new schema have a UniqueConstraint for some tables (like : the name column of the city table should be unique within a country). This constraint was not present in the old schema and data is corrupt (two cities with the same name in the same country). So when I try to insert them in the new database, I have IntegrityErrors. The solution I thought of was : * Catch the IntegrityError exception * If it's a problem on a UniqueConstraint, then the exception was raised because I tried to insert instance B that has the same key as instance A that was inserted before. * So for all children of B (B's relations), set their parent to A. For example, for all citizens of B, set their city to A, beause A and B ought to be the same. * Then, safely ignore B and move on to the next instance. Here's what has been done so far (that works, I just use psuedo code for illustration purpose. If necessary, you can look at the actual attached source files): line = csvloader.next_row() ModelClass = get_current_model() instance = ModelClass.create_instance(**(to_dict(line))) session.add(instance) I wish I could do something like this : try: session.commit() except IntegrityError,e : session.rollback() errror = get_error() if type_of(error) == UniqueConstraintError : original_instance = ModelClass.get(instance.id) for relation in instance.get_relations() : # Is this correct ? instance.relation.inverse = original_instance session.commit() My questions are : how to write get_error, type_of, where to get UniqueContraintError, how to write get_relations, how to set the inverse of a relation (is instance.realtion.inverse the right thing to set ?) and is this approach correct ? There's no portable way to detect unique constraint errors across DBAPIs, you'd have to catch the specific error that you've observed your DBAPI emits as well as the message/codes contained within it, and code against that. SQLAlchemy wraps DBAPI exceptions in its own same-named wrapper and the object emitted by the DBAPI is available under the .orig member. One improvement to the recipe would be if you used savepoints, if supported by your database, so that when you issue rollback() inside a savepoint block, the whole transaction isn't rolled back and you can maintain the whole operation in one transaction. The session can start a savepoint using begin_nested(). When I do csv loads like these however I usually load the full list of identifiers to be checked into memory ahead of time. If the csv is less than 100K rows and represents the full table of data, I just load the whole thing into a dictionary, formatted according to whatever these are the fields that are unique I'm dealing with, and consult the dictionary as I go along. Otherwise, I load individual blocks of data in as I scan through portions of the csv (like, give me all the
Re: [sqlalchemy] Copying instances from old to new schema DB
On Sep 9, 2010, at 11:31 AM, chaouche yacine wrote: Thank you Michael, that's for the catch-the-error part. How about the set-relations-right part, if I decided to go with my fix-as-you go recipe ? do you have any idea ? the dictionary approach seems good, but I still am curious about how to set relations generically on models. I think I'll use it somewhere else in my code. You don't need to set any relations. If your new B has a fully populated primary key, you should just be using session.merge() for the whole thing. This can work with your integrity error scheme, or more simply with the select first schemes below: for row in csv: new_object = make_an_object_from_csv_row(row) new_object = session.merge(new_object) session.commit() if new_object does *not* have the correct primary key, then: for row in csv: new_object = make_an_object_from_csv_row(row) existing_object = session.query(cls).filter(cls.the_unique_column==new_object.the_unique_column).first() if existing_object is not None: new_object.id = existing_object.id new_object = session.merge(new_object) session.commit() if you don't like the many indvidual SELECT statements, then # load a dictionary of (unique attr, primary key) lookup = dict( session.query(cls.the_unique_column, cls.id) ) for row in csv: new_object = make_an_object_from_csv_row(row) if new_object.the_unique_column in lookup: new_object.id = lookup[new_object.the_unique_column] new_object = session.merge(new_object) session.commit() or if most of your rows are replacement rows, quicker to preload everything: # load a dictionary of (unique attr, instance) lookup = dict( session.query(cls.the_unique_column, cls) ) for row in csv: new_object = make_an_object_from_csv_row(row) if new_object.the_unique_column in lookup: new_object.id = lookup[new_object.the_unique_column].id new_object = session.merge(new_object) session.commit() By the way, I think the pseudo should have been : instance = next_instance_from_csv() try: session.commit() except IntegrityError,e : session.rollback() if e.orig == UniqueConstraintError : original_instance = ModelClass.get(instance.id) for relation_name in instance.get_relation_names() : # Is this correct ? path_to_attribute= mapper.+ relation +.inverse # I don't know where to find this ? instance.set_attribute(path_to_attribute,original_instance) session.commit() class BaseModel (DeclarativeBase): ... def set_attribute(self,path,value): nodes= path.explode(.) current_node = self for next_node in nodes : current_node = getattr(current_node,next_node,None) if not current_node: raise InvalidAttribute set_attribute(current_node,value) # sqla's set_attribute, for whatever reason... Y.Chaouche --- On Thu, 9/9/10, Michael Bayer mike...@zzzcomputing.com wrote: From: Michael Bayer mike...@zzzcomputing.com Subject: Re: [sqlalchemy] Copying instances from old to new schema DB To: sqlalchemy@googlegroups.com Date: Thursday, September 9, 2010, 5:59 AM On Sep 9, 2010, at 6:31 AM, chaouche yacine wrote: Hello list, My schema has changed, and now I want to retrieve my old data (of the old schema) to the new database (with the new schema) from csv files (I export the tables of the old database to csv files and then load those csv files to the new database with some column mapping and some gap filling for new columns) The problem is that my new schema have a UniqueConstraint for some tables (like : the name column of the city table should be unique within a country). This constraint was not present in the old schema and data is corrupt (two cities with the same name in the same country). So when I try to insert them in the new database, I have IntegrityErrors. The solution I thought of was : * Catch the IntegrityError exception * If it's a problem on a UniqueConstraint, then the exception was raised because I tried to insert instance B that has the same key as instance A that was inserted before. * So for all children of B (B's relations), set their parent to A. For example, for all citizens of B, set their city to A, beause A and B ought to be the same. * Then, safely ignore B and move on to the next instance. Here's what has been done so far (that works, I just use psuedo code for illustration purpose. If necessary, you can look at the actual attached source files): line = csvloader.next_row() ModelClass = get_current_model() instance = ModelClass.create_instance(**(to_dict(line))) session.add(instance) I wish I could do something like this : try: session.commit() except IntegrityError,e : session.rollback()
[sqlalchemy] SQLAlchemy / Turbogears2.0 transaction rollbacks
Hello, I am trying to use Turbogears 2.x with SQLAlchemy 0.5.1. TG2 uses scoped sessions and uses an additional layer of transaction manager. If I have a scenario as follows: import transaction def main() try: add main_record to session query main_record to get primary key call foo() except Exception, e: transaction.abort() def foo(pid): add dependent record with main_record_primary_key as foreign_key raise exception for testing The above code is not rolling back the transaction. I realize the 'query main_record' will flush the main_record to the DB. But I am wondering if it is also 'commiting' the record. How do I debug this? Is there a debug flag in sqlalchemy which logs a commit operation? The associated initialization code in TG2 is: from zope.sqlalchemy import ZopeTransactionExtension from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base # Global session manager: DBSession() returns the Thread-local # session object appropriate for the current web request. maker = sessionmaker(autoflush=True, autocommit=False, extension=ZopeTransactionExtension()) DBSession = scoped_session(maker) metadata = DeclarativeBase.metadata The link to the thread on turbogears group is: http://groups.google.com/group/turbogears/browse_thread/thread/363c2e5ac7211a8 Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] SQLAlchemy / Turbogears2.0 transaction rollbacks
On Sep 9, 2010, at 3:51 PM, ozwyzard wrote: Hello, I am trying to use Turbogears 2.x with SQLAlchemy 0.5.1. TG2 uses scoped sessions and uses an additional layer of transaction manager. If I have a scenario as follows: import transaction def main() try: add main_record to session query main_record to get primary key call foo() except Exception, e: transaction.abort() def foo(pid): add dependent record with main_record_primary_key as foreign_key raise exception for testing The above code is not rolling back the transaction. I realize the 'query main_record' will flush the main_record to the DB. But I am wondering if it is also 'commiting' the record. How do I debug this? Is there a debug flag in sqlalchemy which logs a commit operation? the regular engine logging will show the string COMMIT and ROLLBACK in the logs. See the Engine documentation for how to use the echo flag as well as more comprehensive logging. The associated initialization code in TG2 is: from zope.sqlalchemy import ZopeTransactionExtension from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base # Global session manager: DBSession() returns the Thread-local # session object appropriate for the current web request. maker = sessionmaker(autoflush=True, autocommit=False, extension=ZopeTransactionExtension()) DBSession = scoped_session(maker) metadata = DeclarativeBase.metadata The link to the thread on turbogears group is: http://groups.google.com/group/turbogears/browse_thread/thread/363c2e5ac7211a8 Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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.
