[sqlalchemy] SA 0.3.4 and sequence for non-primary key column
Hi, I have a table with column that must use sequence generated number (for example in Postgres), like this obj_id: table_Manager = Table( 'Manager', meta, Column( 'obj_id', Integer, Sequence('obj_id_seq'), ), Column( 'duties', type= String, ), Column( 'name', type= String, ), Column( 'id', Integer, primary_key= True, ), ) You see obj_id is not the primary key of the column. What i get as a result that SA correctly gets number from its obj_id_seq, logs shows that it even tries to insert it to the database, but in the end it remains Null (in the DB). Is it this my mistake or is this possible at all? TIA Stefan the code below demonstrates the issue: -- from sqlalchemy import * import os try: r = os.system( 'dropdb proba') r = os.system( 'createdb proba') except OSError: pass db_postgres = create_engine( 'postgres://[EMAIL PROTECTED]:5432/proba') assert not 'FIX USERNAME in the above line and than remove me!!!' def checkWith( db): meta = BoundMetaData( db) meta.engine.echo = 1 table_Manager = Table( 'Manager', meta, Column( 'obj_id', Integer, Sequence('obj_id_seq'), ), Column( 'duties', type= String, ), Column( 'name', type= String, ), Column( 'id', Integer, primary_key= True, ), ) class Manager( object): def set( me, **kargs): for k,v in kargs.iteritems(): setattr( me, k, v) return me def __str__(me): return str(me.__class__.__name__) +':'+str(me.name) __repr__ = __str__ meta.create_all() mapper_Manager = mapper( Manager, table_Manager) import datetime c = Manager().set( name= 'pencho', duties= 'many') session = create_session() session.save(c) session.flush() print c print session.query( Manager).select() d = Manager().set( name= 'torencho', duties= 'bany') e = Manager().set( name= 'mnogoVojdMalkoIndianec', duties= 'lany') session = create_session() session.save(d) session.save(e) session.flush() print '\n\nobjID in objects:', c.obj_id, d.obj_id, e.obj_id res = session.query( Manager).select() print '\nBEFORE session close' for i in res: print 'OBJ( Id: %s Obj_id: %s)' % (i.id, i.obj_id) session.close() session = create_session() res = session.query( Manager).select() print '\nAFTER session close' for i in res: print 'OBJ( Id: %s Obj_id: %s)' % (i.id, i.obj_id) checkWith( db_postgres) -- After session close Obj_id is None. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Left Joins and Improper Parenthesis when Building Query Expressions
I am building a SQL query one step at a time in different parts of my application. I am using MySQL 4.1 Currently, the query being automatically built is: (SELECT doc_index.doc_id, doc_index.doc_type, doc_index.ref_id, doc_index.create_date, doc_index.modified_date FROM doc_index) JOIN snack ON doc_index.ref_id = snack.snack_id AND doc_index.doc_type = %s JOIN drink ON doc_index.ref_id = drink.drink_id AND doc_index.doc_type = %s First, MySQL throws a syntax error because of the parenthesis around the SELECT expression. Is there any way to turn that off? Second, this has to be a LEFT JOIN, not a standard join, so something like this: SELECT doc_index.doc_id, doc_index.doc_type, doc_index.ref_id, doc_index.create_date, doc_index.modified_date FROM doc_index LEFT JOIN snack ON doc_index.ref_id = snack.snack_id AND doc_index.doc_type = %s LEFT JOIN drink ON doc_index.ref_id = drink.drink_id AND doc_index.doc_type = %s Here is a condensed version of the code stmt = doc_type_table.select() join_table = get_join_table('widgets') stmt = stmt.join(join_table, SA.and_(doc_type_table.c.ref_id==join_table.primary_key[0], doc_type_table.c.doc_type=='widgets')) join_table2 = get_join_table('spam') stmt = stmt.join(join_table2, SA.and_(doc_type_table.c.ref_id==join_table2.primary_key[0], doc_type_table.c.doc_type=='spam')) stmt.execute() I tried hacking with the _group_parenthesized attribute, but to no avail on the parenthesis. Any help would be appreciated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Left Joins and Improper Parenthesis when Building Query Expressions
stmt = doc_type_table.select() join_table = get_join_table('widgets') stmt = stmt.join(join_table, SA.and_(doc_type_table.c.ref_id==join_table.primary_key[0], doc_type_table.c.doc_type=='widgets')) join_table2 = get_join_table('spam') stmt = stmt.join(join_table2, SA.and_(doc_type_table.c.ref_id==join_table2.primary_key[0], doc_type_table.c.doc_type=='spam')) stmt.execute() isn't this supposed to be: s = join( join( doc_type_table, join_table, onclause1), anothertbl, abnotherclause)... print [q for q in s.select()] or something alike? maybe Another way is to use ext.SelectResults over the table.select() and add more joins/whatever to that one... --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Tool to check whether DB matches model
Hi, Well, I just happened to say model in my original message. I could have said Tool to check whether a bunch of SA tables matches the database tables. That is what model_update.py does (at least, it tries to!) - so is it something you'd consider including as an SA plugin? Paul On 2/1/07, Michael Bayer [EMAIL PROTECTED] wrote: well, just making an app in such a way that all the model classes are attached to a single-datamember called model is just one of an infinite number of ways to do MVC. so your app is sort of part of a larger framework (whether that framework explcitly exists or not). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] what is considered circular dep at session.flush() ?
hallo. AFAI understand from the source, in the graph used for topology sorting in session.flush(), the nodes are representing the objects' mappers. (and not the tables as in metadata.create_all() ). Also, mappers which inherit from others, are replaced by their bases/roots. Edges in the graph are the relations between the mappers, and post_update=True is considered as cut (i.e. is not an edge). And then, the graph of relations is checked for cycles. So if i want to run an automatical mincut algo, it should be over a graph, having root mappers as nodes and all relations as edges. i am right? ciao svil btw, in topological.py: QueueDependencySorter.sort(), the _find_cycles() call is duplicated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what is considered circular dep at session.flush() ?
i am asking this, because so far i am considering both table/foregn-key graph and mapper/relation graph same, thus applying the mincut for the table/foregnkey (which sets fkey.use_alter=True) also to the other (which sets respective relation.post_update=True). And in 99% of cases, this works. Exceptions become some 3 level concrete-inheritances. i know concrete inheriting of relations is broken (not inherited at all), but it does work if u re-add the relation again, with proper tables, foregn_keys, remote_side etc inside. Which sort-of makes the relation a different relation - the base one can have some characteristics different from the inheriting one (e.g. post_update, lazy etc). And now cutting the graph-edges as of the foreignkey on the inheriting mapper does not work because the obj-dependency graph does not make difference between a concrete mapper and non-concrete mapper, and is always assuming the link to be through the base mapper, and the relation there is not a post_update (in the example). or something of sorts. So the question really is: should i mincut the relation-graph with post_update's in a different way than the foregnkeys/use_alters of the underlying tables, or is this something to fix in the dependency-graph building for concrete inheritances. there is one example in ticket 452. bye svil hallo. AFAI understand from the source, in the graph used for topology sorting in session.flush(), the nodes are representing the objects' mappers. (and not the tables as in metadata.create_all() ). Also, mappers which inherit from others, are replaced by their bases/roots. Edges in the graph are the relations between the mappers, and post_update=True is considered as cut (i.e. is not an edge). And then, the graph of relations is checked for cycles. So if i want to run an automatical mincut algo, it should be over a graph, having root mappers as nodes and all relations as edges. i am right? ciao svil btw, in topological.py: QueueDependencySorter.sort(), the _find_cycles() call is duplicated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Left Joins and Improper Parenthesis when Building Query Expressions
a join() by itself shouldnt be executed. you need to call select() off of it (or use it inside the from_obj param of another select()). notice its not the parenthesis that are the problem, its the lack of an enclosing SELECT ... FROM. On Feb 2, 5:04 am, jlowery [EMAIL PROTECTED] wrote: I am building a SQL query one step at a time in different parts of my application. I am using MySQL 4.1 Currently, the query being automatically built is: (SELECT doc_index.doc_id, doc_index.doc_type, doc_index.ref_id, doc_index.create_date, doc_index.modified_date FROM doc_index) JOIN snack ON doc_index.ref_id = snack.snack_id AND doc_index.doc_type = %s JOIN drink ON doc_index.ref_id = drink.drink_id AND doc_index.doc_type = %s First, MySQL throws a syntax error because of the parenthesis around the SELECT expression. Is there any way to turn that off? Second, this has to be a LEFT JOIN, not a standard join, so something like this: SELECT doc_index.doc_id, doc_index.doc_type, doc_index.ref_id, doc_index.create_date, doc_index.modified_date FROM doc_index LEFT JOIN snack ON doc_index.ref_id = snack.snack_id AND doc_index.doc_type = %s LEFT JOIN drink ON doc_index.ref_id = drink.drink_id AND doc_index.doc_type = %s Here is a condensed version of the code stmt = doc_type_table.select() join_table = get_join_table('widgets') stmt = stmt.join(join_table, SA.and_(doc_type_table.c.ref_id==join_table.primary_key[0], doc_type_table.c.doc_type=='widgets')) join_table2 = get_join_table('spam') stmt = stmt.join(join_table2, SA.and_(doc_type_table.c.ref_id==join_table2.primary_key[0], doc_type_table.c.doc_type=='spam')) stmt.execute() I tried hacking with the _group_parenthesized attribute, but to no avail on the parenthesis. Any help would be appreciated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what is considered circular dep at session.flush() ?
a circular relationship between two mappers means that a dependency sort revealed that the mapper-level dependency graph contains cycles. that portion of the sort is then thrown away and replaced with a dependency sort where the nodes are individual object instances. so sort #1 is mappers as nodes and edges are relations. sort #2 is object instances and the relationships between them. also the rest of your questions are impossible for me to answer since i do not know what a mincut is, and a wikipedia search indicated its some kind of network optimization theory. i dont really see how optimization can apply to a dependency graph of database tables. one thing to note is, if i were rewriting the entire flush system from scratch, i would probably do it based on tables and rows directly instead of mappers and instances. On Feb 2, 9:45 am, svilen [EMAIL PROTECTED] wrote: i am asking this, because so far i am considering both table/foregn-key graph and mapper/relation graph same, thus applying the mincut for the table/foregnkey (which sets fkey.use_alter=True) also to the other (which sets respective relation.post_update=True). And in 99% of cases, this works. Exceptions become some 3 level concrete-inheritances. i know concrete inheriting of relations is broken (not inherited at all), but it does work if u re-add the relation again, with proper tables, foregn_keys, remote_side etc inside. Which sort-of makes the relation a different relation - the base one can have some characteristics different from the inheriting one (e.g. post_update, lazy etc). And now cutting the graph-edges as of the foreignkey on the inheriting mapper does not work because the obj-dependency graph does not make difference between a concrete mapper and non-concrete mapper, and is always assuming the link to be through the base mapper, and the relation there is not a post_update (in the example). or something of sorts. So the question really is: should i mincut the relation-graph with post_update's in a different way than the foregnkeys/use_alters of the underlying tables, or is this something to fix in the dependency-graph building for concrete inheritances. there is one example in ticket 452. bye svil hallo. AFAI understand from the source, in the graph used for topology sorting in session.flush(), the nodes are representing the objects' mappers. (and not the tables as in metadata.create_all() ). Also, mappers which inherit from others, are replaced by their bases/roots. Edges in the graph are the relations between the mappers, and post_update=True is considered as cut (i.e. is not an edge). And then, the graph of relations is checked for cycles. So if i want to run an automatical mincut algo, it should be over a graph, having root mappers as nodes and all relations as edges. i am right? ciao svil btw, in topological.py: QueueDependencySorter.sort(), the _find_cycles() call is duplicated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what is considered circular dep at session.flush() ?
A mincut algorithm finds the minimal number of edges to cut in a cycled graph so it becomes without cycles. http://en.wikipedia.org/wiki/Max-flow_min-cut_theorem i.e. applying such algorithm over the graph of table dependencies (foregnkey), one gets some minimal number of foreign keys to make use_alter=True. there might be many solutions. or in the mapper/relation graph, find which relations to make post_update=True so although obj-relaltions are cycling, the mapper/relations have no cycles. is it more clear now? a circular relationship between two mappers means that a dependency sort revealed that the mapper-level dependency graph contains cycles. that portion of the sort is then thrown away and replaced with a dependency sort where the nodes are individual object instances. so sort #1 is mappers as nodes and edges are relations. sort #2 is object instances and the relationships between them. also the rest of your questions are impossible for me to answer since i do not know what a mincut is, and a wikipedia search indicated its some kind of network optimization theory. i dont really see how optimization can apply to a dependency graph of database tables. one thing to note is, if i were rewriting the entire flush system from scratch, i would probably do it based on tables and rows directly instead of mappers and instances. On Feb 2, 9:45 am, svilen [EMAIL PROTECTED] wrote: i am asking this, because so far i am considering both table/foregn-key graph and mapper/relation graph same, thus applying the mincut for the table/foregnkey (which sets fkey.use_alter=True) also to the other (which sets respective relation.post_update=True). And in 99% of cases, this works. Exceptions become some 3 level concrete-inheritances. i know concrete inheriting of relations is broken (not inherited at all), but it does work if u re-add the relation again, with proper tables, foregn_keys, remote_side etc inside. Which sort-of makes the relation a different relation - the base one can have some characteristics different from the inheriting one (e.g. post_update, lazy etc). And now cutting the graph-edges as of the foreignkey on the inheriting mapper does not work because the obj-dependency graph does not make difference between a concrete mapper and non-concrete mapper, and is always assuming the link to be through the base mapper, and the relation there is not a post_update (in the example). or something of sorts. So the question really is: should i mincut the relation-graph with post_update's in a different way than the foregnkeys/use_alters of the underlying tables, or is this something to fix in the dependency-graph building for concrete inheritances. there is one example in ticket 452. bye svil hallo. AFAI understand from the source, in the graph used for topology sorting in session.flush(), the nodes are representing the objects' mappers. (and not the tables as in metadata.create_all() ). Also, mappers which inherit from others, are replaced by their bases/roots. Edges in the graph are the relations between the mappers, and post_update=True is considered as cut (i.e. is not an edge). And then, the graph of relations is checked for cycles. So if i want to run an automatical mincut algo, it should be over a graph, having root mappers as nodes and all relations as edges. i am right? ciao svil btw, in topological.py: QueueDependencySorter.sort(), the _find_cycles() call is duplicated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Left Joins and Improper Parenthesis when Building Query Expressions
As I understand the confusion here, Ive added a check for supports execution in rev 2289. so that when you try to execute the join, it will raise an error. originally i wasnt sure what would be executable and what would not but im hoping its pretty well nailed down at this point. so far its: Select/CompoundSelect _Insert/_Update/_Delete Alias (if its underlying target is a Select) _TextClause On Feb 2, 10:00 am, Michael Bayer [EMAIL PROTECTED] wrote: a join() by itself shouldnt be executed. you need to call select() off of it (or use it inside the from_obj param of another select()). notice its not the parenthesis that are the problem, its the lack of an enclosing SELECT ... FROM. On Feb 2, 5:04 am, jlowery [EMAIL PROTECTED] wrote: I am building a SQL query one step at a time in different parts of my application. I am using MySQL 4.1 Currently, the query being automatically built is: (SELECT doc_index.doc_id, doc_index.doc_type, doc_index.ref_id, doc_index.create_date, doc_index.modified_date FROM doc_index) JOIN snack ON doc_index.ref_id = snack.snack_id AND doc_index.doc_type = %s JOIN drink ON doc_index.ref_id = drink.drink_id AND doc_index.doc_type = %s First, MySQL throws a syntax error because of the parenthesis around the SELECT expression. Is there any way to turn that off? Second, this has to be a LEFT JOIN, not a standard join, so something like this: SELECT doc_index.doc_id, doc_index.doc_type, doc_index.ref_id, doc_index.create_date, doc_index.modified_date FROM doc_index LEFT JOIN snack ON doc_index.ref_id = snack.snack_id AND doc_index.doc_type = %s LEFT JOIN drink ON doc_index.ref_id = drink.drink_id AND doc_index.doc_type = %s Here is a condensed version of the code stmt = doc_type_table.select() join_table = get_join_table('widgets') stmt = stmt.join(join_table, SA.and_(doc_type_table.c.ref_id==join_table.primary_key[0], doc_type_table.c.doc_type=='widgets')) join_table2 = get_join_table('spam') stmt = stmt.join(join_table2, SA.and_(doc_type_table.c.ref_id==join_table2.primary_key[0], doc_type_table.c.doc_type=='spam')) stmt.execute() I tried hacking with the _group_parenthesized attribute, but to no avail on the parenthesis. Any help would be appreciated. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy + MySQL encoding error (probably wrong charset=... handling)
On Feb 2, 2007, at 2:49 AM, Andrew Stromnov wrote: cities = cities.select(limit=1).execute().fetchall() works well with this configuration, but leads to error in nregion = places.select(places.c.name == iv2).execute() if nregion = places.select(places.c.name == iv2).execute() used first, then string conversion error appear at cities = cities.select(limit=1).execute().fetchall() Probably, MySQL charset setting is library-wide, but not connection- wide. are you saying that executing a particular statement is changing some state on the local connection ? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy + MySQL encoding error (probably wrong charset=... handling)
On 2/2/07, Michael Bayer [EMAIL PROTECTED] wrote: are you saying that executing a particular statement is changing some state on the local connection ? Not exactly what I had in mind. Execution of first appeared statement assigns (underlying ?) charset for all following queries for all connections (in current program scope). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full Text Search using PostgreSQL and tsearch2
I have site in production right now that is using tsearch2. What I did to accommodate the results with SA was to simply not map the tsearch2 column to the SA object. I have a view that creates the tsvector objects based on the source table. (I actually created a materialized view and indexed it if you wanted to google for materialized views and PG), Next, I simply join the SA object with a custom query when I do the search. Example tsearch=engine.text('my tsearch query that returns the ids of the objs'). If you want objects returned, you can use the mapper.instances function myobj=MyObj.mapper.instances(tsearch.execute()) -Dennis --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA 0.3.4 and sequence for non-primary key column
thats a bug, i fixed it in r2291. if you dont want to use the trunk for now, you can probably define the column as: Column('obj_id', integer, default=func.obj_id_seq.nextval()) On Feb 2, 2007, at 4:05 AM, che wrote: Hi, I have a table with column that must use sequence generated number (for example in Postgres), like this obj_id: table_Manager = Table( 'Manager', meta, Column( 'obj_id', Integer, Sequence('obj_id_seq'), ), Column( 'duties', type= String, ), Column( 'name', type= String, ), Column( 'id', Integer, primary_key= True, ), ) You see obj_id is not the primary key of the column. What i get as a result that SA correctly gets number from its obj_id_seq, logs shows that it even tries to insert it to the database, but in the end it remains Null (in the DB). Is it this my mistake or is this possible at all? TIA Stefan the code below demonstrates the issue: -- from sqlalchemy import * import os try: r = os.system( 'dropdb proba') r = os.system( 'createdb proba') except OSError: pass db_postgres = create_engine( 'postgres://[EMAIL PROTECTED]:5432/proba') assert not 'FIX USERNAME in the above line and than remove me!!!' def checkWith( db): meta = BoundMetaData( db) meta.engine.echo = 1 table_Manager = Table( 'Manager', meta, Column( 'obj_id', Integer, Sequence('obj_id_seq'), ), Column( 'duties', type= String, ), Column( 'name', type= String, ), Column( 'id', Integer, primary_key= True, ), ) class Manager( object): def set( me, **kargs): for k,v in kargs.iteritems(): setattr( me, k, v) return me def __str__(me): return str(me.__class__.__name__) +':'+str(me.name) __repr__ = __str__ meta.create_all() mapper_Manager = mapper( Manager, table_Manager) import datetime c = Manager().set( name= 'pencho', duties= 'many') session = create_session() session.save(c) session.flush() print c print session.query( Manager).select() d = Manager().set( name= 'torencho', duties= 'bany') e = Manager().set( name= 'mnogoVojdMalkoIndianec', duties= 'lany') session = create_session() session.save(d) session.save(e) session.flush() print '\n\nobjID in objects:', c.obj_id, d.obj_id, e.obj_id res = session.query( Manager).select() print '\nBEFORE session close' for i in res: print 'OBJ( Id: %s Obj_id: %s)' % (i.id, i.obj_id) session.close() session = create_session() res = session.query( Manager).select() print '\nAFTER session close' for i in res: print 'OBJ( Id: %s Obj_id: %s)' % (i.id, i.obj_id) checkWith( db_postgres) -- After session close Obj_id is None. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what is considered circular dep at session.flush() ?
On Feb 2, 2007, at 10:33 AM, svilen wrote: A mincut algorithm finds the minimal number of edges to cut in a cycled graph so it becomes without cycles. http://en.wikipedia.org/wiki/Max-flow_min-cut_theorem i.e. applying such algorithm over the graph of table dependencies (foregnkey), one gets some minimal number of foreign keys to make use_alter=True. there might be many solutions. or in the mapper/relation graph, find which relations to make post_update=True so although obj-relaltions are cycling, the mapper/relations have no cycles. is it more clear now? no, not at all. thats the article I read, and it applies to a flow graph, which as far as I can tell has to apply numerical values to each edge in the graph and applies a capacity to the nodes. I dont see what numerical or capacity values would be applied to a topological sort. class User class Address User - one to many - Address whats the capacity for that graph ? whats the x/y to stick between those two nodes ? also, this whole topic is not very important to me, as its easy enough for someone to just add a post_update into their mapping configuration when an obvious inter-row dependency is detected. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy + MySQL encoding error (probably wrong charset=... handling)
right, except that when you say statement1.execute() and then statement2.execute(), its very likely that those two statements are using distinct connections from the connection pool. also, SA is not issuing any kind of charset anything on a connection so it would not be within SA's jurisdiction even if this was the case. On Feb 2, 2007, at 11:49 AM, Andrew Stromnov wrote: On 2/2/07, Michael Bayer [EMAIL PROTECTED] wrote: are you saying that executing a particular statement is changing some state on the local connection ? Not exactly what I had in mind. Execution of first appeared statement assigns (underlying ?) charset for all following queries for all connections (in current program scope). --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA 0.3.4 and sequence for non-primary key column
Hi, Michael Bayer написа: if you dont want to use the trunk for now, you can probably define the column as: Column('obj_id', integer, default=func.obj_id_seq.nextval()) this answers another question of mine :) that i planned to ask ...but it generates (on Postgres) this: SELECT obj_id_seq.nextval() and seems the proper syntax is: SELECT nextval( 'obj_id_seq'); regards, Stefan --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what is considered circular dep at session.flush() ?
i think you are looking for a feedback arc set, which describes exactly the problem that applying post_update to a mapping solves: http://en.wikipedia.org/wiki/Feedback_arc_set and we actually generate a set like this in the _find_cycles() method, except it doesnt find just one edge of the cycle, it contains all of the edges comprising the cycle. the mincut term is definitely wrong: A cut is minimal if the size of the cut is not larger than the size of any other cut. nothing to do with cycles, and a topological sort is definitely not a flow network (no source or sink to start with). of course, topological sort is a sort, but in order of it to work, the graph should not have cycles. Which are cut by use_alter's and post_updates, on foreign keys and relations. well, in our case all edges have cost of 1, except inheritance-related primary-key-joins which have cost of infinity, so they become uncuttable. Duplicate edges do matter as they increase the total cost. Thus min cut is a cut with mininal cost, which is the number of edges cut. i am trying to automaticaly add the relations' post_update, given a set of classes and their relations. i've already done the use_alter's on foreign keys in same automatical way. Anyway. My conclusion from the source code, is that current object/mappers-based flush() does not handle the concrete-inheritance in a different way than table-inheritance, while IMO they should differ as they touch different set of tables. which in a way confirms your saying about flush() would be better based on tables/foreign keys than obj/mappers. On Feb 2, 2007, at 12:07 PM, Michael Bayer wrote: On Feb 2, 2007, at 10:33 AM, svilen wrote: A mincut algorithm finds the minimal number of edges to cut in a cycled graph so it becomes without cycles. http://en.wikipedia.org/wiki/Max-flow_min-cut_theorem i.e. applying such algorithm over the graph of table dependencies (foregnkey), one gets some minimal number of foreign keys to make use_alter=True. there might be many solutions. or in the mapper/relation graph, find which relations to make post_update=True so although obj-relaltions are cycling, the mapper/relations have no cycles. is it more clear now? no, not at all. thats the article I read, and it applies to a flow graph, which as far as I can tell has to apply numerical values to each edge in the graph and applies a capacity to the nodes. I dont see what numerical or capacity values would be applied to a topological sort. class User class Address User - one to many - Address whats the capacity for that graph ? whats the x/y to stick between those two nodes ? also, this whole topic is not very important to me, as its easy enough for someone to just add a post_update into their mapping configuration when an obvious inter-row dependency is detected. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Tool to check whether DB matches model
oh sure, if not on the wiki directly of course it can linked/ downloaded/whatever from the site...id like to have as many recipes and things as possible (which is really what the wiki is for).if it were with the dist, id have it in a util or contrib folder off the root. On Feb 2, 2007, at 12:40 PM, Paul Johnston wrote: Hi, Doesn't have to be in the sqlalchemy.ext package, I just wondered if this is something you'd have tied to SA in any way, even if it's just a link from the site. If the answer is no, it's not a problem. BTW, did you get a chance to look at #298? Paul Michael Bayer wrote: by plugin does that mean, its in the sqlalchemy.ext package ? why is it important that it be there ? this is more of a distinct utility for a very narrow use case (i.e. when you can just ALTER TABLE and dont need any additional migrating SQL executed) and is not really an extension to SA itself. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SA 0.3.4 and sequence for non-primary key column
oh right...try func.nextval('obj_id_seq') On Feb 2, 2007, at 12:46 PM, che wrote: Hi, Michael Bayer написа: if you dont want to use the trunk for now, you can probably define the column as: Column('obj_id', integer, default=func.obj_id_seq.nextval()) this answers another question of mine :) that i planned to ask ...but it generates (on Postgres) this: SELECT obj_id_seq.nextval() and seems the proper syntax is: SELECT nextval( 'obj_id_seq'); regards, Stefan --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: what is considered circular dep at session.flush() ?
On Feb 2, 2007, at 2:14 PM, svilen wrote: of course, topological sort is a sort, but in order of it to work, the graph should not have cycles. Which are cut by use_alter's and post_updates, on foreign keys and relations. well, in our case all edges have cost of 1, except inheritance-related primary-key-joins which have cost of infinity, so they become uncuttable. Duplicate edges do matter as they increase the total cost. Thus min cut is a cut with mininal cost, which is the number of edges cut. yeah im no mathematician but i dont think you can use just part of a theory thats designed for flow diagrams with some totally different kind of diagram. we dont have a capacity concept here, nor cost, nor the source and sink endpoints described.the pages linked from wikipedia have the flow network problem and the feedback arc problems in completely different, non-referencing categories. i am trying to automaticaly add the relations' post_update, given a set of classes and their relations. if you make usage of the results of _find_cycles(), its already there. take the edges indicated in the cycle, remove one, then sort again. its an expensive operation, but as the wikipedia article states, its an APX Hard problem to find the minimal set of edges to be removed. My conclusion from the source code, is that current object/mappers-based flush() does not handle the concrete-inheritance in a different way than table-inheritance, while IMO they should differ as they touch different set of tables. possibly... it would involve changing the definition of _get_noninheriting_mappers() to return various concrete mappers as separate. the base of a mapper inheritance hierarchy is currently treated as a single node in the dependency tree. these are all things i will address when i begin to make concrete inheritance work for all reasonable cases. noting that, i have spent almost no time at all on supporting concrete patterns at this point (also, nobody has really complained). which in a way confirms your saying about flush() would be better based on tables/foreign keys than obj/mappers. possibly, but thats nothing that will happen anytime soon unless someone gives me a full time salary working on SA :). its not as easy as it sounds since we cant just compile a whole set of SQL statements together - there are still data synchronization operations, which are mapper specific, that must occur between each pair of dependencies. so there are still inter-mapper dependencies that may in some cases be independent of the dependencies of tables or rows. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Unit testing with SA?
Sorry for the rather newbie question, but I can't find anything about this anywhere else. How do people writing db applications with SA do unit testing on their code? Are there any good db unit testing frameworks that work well with SA? If I can't find anything I will probably just roll my own but I would like to keep from reinventing the wheel if I can help it. Thanks, Allen --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Unit testing with SA?
Allen schrieb: How do people writing db applications with SA do unit testing on their code? Are there any good db unit testing frameworks that work well with SA? If I can't find anything I will probably just roll my own but I would like to keep from reinventing the wheel if I can help it. Basically you have to initialize your database for each test and destroy it afterwards again (otherwise it wouldn't be a 'unit' test). In the 'unittest' module, you can use the 'setUp' and tearDown' methods of the TestCase class for this purpose. For example: # model contains the SA table and mapper classes import unittest import sqlalchemy import model class SATestCase(unittest.TestCase): def setUp(self): # do what you have to do to bind the metadata/engine to the model here # create all model tables for item in dir(self.model): item = getattr(self.model, item) if isinstance(item, sqlalchemy.Table): item.create(checkfirst=True) def tearDown(self): # drop all model tables for item in dir(self.model): item = getattr(self.model, item) if isinstance(item, sqlalchemy.Table): item.drop(checkfirst=True) # close database def testFoo(self): # here comes your unit test Chris --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---