[sqlalchemy] Re: passive deletes
Hello Michael, thank you for your answer. You are right that this behavior can be enforced by setting the viewonly attribute of the relation, and the others are not needed. However, I do need to set it on both sides of the relation, or it does not work. But this not working might be triggered by another part of the model where there is a join between B and another table to form C. I will have to investigate further to get a definitive answer. Regards, Erik On Aug 10, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Aug 10, 2011, at 6:56 AM, erikj wrote: Hi, I have a table and a view mapped to objects as such : class A(object): mapped to a table with a primary key column id with a onetomany attribute list_of_b, being a relation to all rows of B with a_id == self.id class B(object): mapped to a view with a column a_id being a reference to the id column of A Since B is mapped to a view, no updates should happen on B. So, in A, the list_of_b relation is configured with : passive_updates = True, passive_deletes = 'all', viewonly = True, cascade = False But whenever an object of A is deleted, sqlalchemy tries to update the a_id columns of all rows of B that were loaded in memory, which of course should not happen, since B is a view. You should only need viewonly=True, which disables any persistence activity on the relationship. A.list_of_bs would not participate in the flush including no foreign key sync operations. A full test case demonstrating this behavior, using SQLite so that a B table can be created with a non-enforcing FK, is attached. I thought perhaps setting a backref might not propagate the viewonly but this also works as expected. Check your configuration again to ensure it isn't doing something else. viewonly_doesnt_affect_b.py 1KViewDownload -- 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] joining to a from_statement
Hi, thank you for your help, and the effort at writing a good piece of code. however, while playing around with, and trying to execute it in PGAdmin, I stumbled upon some problems the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id) generates a SQL like (I've inserted real values): SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = 4 UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id it chokes on : ERROR: invalid reference to FROM-clause entry for table groups_recursive LINE 3: (SELECT groups_recursive.id AS id, 1 ^ HINT: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. ** Error ** ERROR: invalid reference to FROM-clause entry for table groups_recursive SQL state: 42P01 Hint: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. Character: 242 obviously, there is a problem at the JOIN stage ... SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE I tried s = select([groups.c.id, column(1)],from_obj=groups) to no better luck many thanks again NiL -- 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/-/4YkzRT9s2bAJ. 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] Can pk_col function be adapted for Firebird?
I can't figure out how I could adapt the pk_col function on this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle the Sequence definition needed for e.g. Firebird. At the point when Column is instantiated I don't have access to table.name and I can't figure it out either how to do it in on_table_attach. Would appreciate any tips on this. Werner -- 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] Simple select is really slow when executed via ORM
Hi everyone, I'm doing some test to evaluate the performance of querying with sqlalchemy via ORM. I wrote a simple script to measure the execution time of a simple select query made on relatively small table (300 000 records, 6 columns) in sqlite. Here is the script: from sqlalchemy import * from sqlalchemy.orm import mapper, sessionmaker from time import clock engine = create_engine('sqlite:///test.db', echo=False) metadata = MetaData() table = Table('projects', metadata, Column('id', Integer, primary_key=True), Column('inp1', String(50)), Column('inp2', String(50)), Column('inp3', String(50)), Column('inp4', String(50)), Column('inp5', String(50)), ) class Project(object) : pass mapper(Project, table) metadata.create_all(engine) t = [] for i in range(30) : t.append({inp1:str(i), inp2:str(i), inp3:str(i), inp4:str(i), inp5:str(i)}) c = clock() engine.execute(table.insert(), t) print Insert: +str(clock()-c) session = sessionmaker(bind=engine)() c = clock() res = engine.execute(table.select()).fetchall() print Sql query: +str(clock()-c) c = clock() res = session.query(Project).all() print Session query: +str(clock()-c) On my PC (windows 7, 64-bit, intel i7 2.93 Ghz) this is the output: Insert: 3.41080167807 Sql query: 1.26728367673 Session query: 19.6452334842 The execution time of the ORM query is about 20 times the SQL one, and this is definitely discouraging. So I guess if I'm doing something wrong or if there are some tricks when using ORM that I'm not considering. Any help is really appreciated. Thanks in advance! -- 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] Can pk_col function be adapted for Firebird?
On 08/12/2011 02:18 PM, werner wrote: I can't figure out how I could adapt the pk_col function on this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle the Sequence definition needed for e.g. Firebird. At the point when Column is instantiated I don't have access to table.name and I can't figure it out either how to do it in on_table_attach. Would appreciate any tips on this. Did a bit more searching and trying and came up with this: def pk_col(cls, **kw): Produce a primary key column for a table. e.g.:: pk_col() is equivalent to:: Column(id, sa.BigInteger, doc = Primary key column for tablename, primary_key=True, sequence=sa.Sequence('tablename_id') ) kw['primary_key'] = True c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % (cls.__tablename__, dbg.pkId)), **kw) @sa.event.listens_for(c, before_parent_attach) def on_table_attach(column, table): column.name = column.key = dbg.pkId column.doc = Primary key column for %r % table.name c._creation_order = 0 # forces it to the top when using declarative return c I.e. pass cls in so I can get to __tablename__. Is this an o.k. way of doing it or is there a better/cleaner way? Werner -- 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] joining to a from_statement
On Aug 12, 2011, at 6:00 AM, NiL wrote: Hi, thank you for your help, and the effort at writing a good piece of code. however, while playing around with, and trying to execute it in PGAdmin, I stumbled upon some problems the q = Session().query(Group).join(all_parents, all_parents.c.id==Group.id) generates a SQL like (I've inserted real values): SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = 4 UNION SELECT groups_recursive.id AS id, rank + 1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id it chokes on : ERROR: invalid reference to FROM-clause entry for table groups_recursive LINE 3: (SELECT groups_recursive.id AS id, 1 ^ HINT: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. ** Error ** ERROR: invalid reference to FROM-clause entry for table groups_recursive SQL state: 42P01 Hint: There is an entry for table groups_recursive, but it cannot be referenced from this part of the query. Character: 242 obviously, there is a problem at the JOIN stage ... SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE I tried s = select([groups.c.id, column(1)],from_obj=groups) to no better luck OK so I'd need you to wrestle with the PG side here, and figure out exactly what query PG accepts on this. I've little experience with CTEs, not sure if perhaps the WITH always needs to be the outermost expression, etc. Perhaps the query for the full span of mapped columns needs to be where we're putting the select * from all_parents part, and it joins to all_parents right there. The CTE construct will ultimately have to work that way anyway, in that you can put any SELECT you want at that point. -- 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] Softcoding .filter(...)
I'm trying to discern a means of creating a .filter(A rel B) where the values for A, rel and B come from an parameters passed in to the web page. I already have an SQLAlchemy statement, say query = Session.query(table).filter(A==B) and I want to be able to allow for a drilldown of sorts by the, such that from the web page they can pick a value from a dropdown, a relation (from a dropdown) and a textbox to compare to. But my problem is once I have these three values, how do I get them into the .filter() function? That's not going to merely accept string values -- is there a way to do this? Thanks, RVince -- 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] Relationship spanning on multiple tables
On Aug 11, 2011, at 6:45 PM, neurino wrote: Sorry if I bother again but adding some others relationships like this spanning on 5 tables: mapper(UserLayer, inherits=base_user_layer_mapper, polymorphic_identity=CLASS_LAYER, properties={ ... 'lca_transport': relationship(LCATransport, primaryjoin=and_( user_layers.c.id_mat==lca_transport.c.id_mat, user_layers.c.id_user_str==user_stratigraphies.c.id, user_stratigraphies.c.id_prov==provinces.c.id, provinces.c.id_reg==regions.c.id, regions.c.id_mr==lca_transport.c.id_mr), foreign_keys=(user_layers.c.id_mat, user_layers.c.id_user_str)), }) if you take a look at http://www.sqlalchemy.org/trac/attachment/ticket/2255/very_long_m2o.py the mappings succeed in compiling but many-to-one lazyloads on a long chain like that have never been tested. Overall we don't have strong documentation / methodologies written down for long chain relationships like these since this is not really the primary use case of relationship. I'd advise not relying upon relationship() to join out that long for now.In my own applications, I reserve all my long chains of joins as explicitly written in session.query() calls. The relationships themselves are simple. The issue here is one of those where I have to set aside 2 hours of time and focus on it so I'll try to get to it when I can. -- 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] Softcoding .filter(...)
On Aug 12, 2011, at 10:21 AM, RVince wrote: I'm trying to discern a means of creating a .filter(A rel B) where the values for A, rel and B come from an parameters passed in to the web page. I already have an SQLAlchemy statement, say query = Session.query(table).filter(A==B) and I want to be able to allow for a drilldown of sorts by the, such that from the web page they can pick a value from a dropdown, a relation (from a dropdown) and a textbox to compare to. But my problem is once I have these three values, how do I get them into the .filter() function? That's not going to merely accept string values -- is there a way to do this? Thanks, RVince You can build your SQLAlchemy queries dynamically, i.e. q1 = query.Session.query(table).filter(A == B) q2 = q1.filter(C == D) q3 = q2.filter(E == F) you could apply different relationships using conditional Python statements: if rel == 'eq': q4 = q3.filter(G == H) elif rel == 'neq': q4 = q3.filter(G != H) is this what you're looking for? Mark -- 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] Simple select is really slow when executed via ORM
Here's a comparison that begins to be slightly fair regarding the work of fetching raw rows versus generating and identity-managing full object rows. On my mac the SQL query takes 7 seconds and the Session query 13.7, so twice as slow, rather than 20. The difference is we are actually fetching the *data* from the SQLite result row, and additionally generating some real Python objects, with a check in the session identity map to simulate a small amount of ORM bookkeeping.The ORM maintains an additional object known as InstanceState for each mapped object, and establishing this object adds a significant chunk of time as well. Python is very slow in creating objects, and in calling functions. Here, each tiny little additional thing the ORM does is multiplied by 30, so it adds up. As you can see, just adding a small handful of steps to the SQL version gets it much closer very quickly. For a 300K row grab you are better off fetching tuples, that is query(Project.id, Project.inp1, Project.inp2), etc., which eliminates all the ORM bookkeeping associated with mapped objects. from sqlalchemy import * from sqlalchemy.orm import mapper, sessionmaker from time import clock import os if os.path.exists(test.db): os.remove(test.db) engine = create_engine('sqlite:///test.db', echo=False) metadata = MetaData() table = Table('projects', metadata, Column('id', Integer, primary_key=True), Column('inp1', String(50)), Column('inp2', String(50)), Column('inp3', String(50)), Column('inp4', String(50)), Column('inp5', String(50)), ) class Project(object) : pass mapper(Project, table) metadata.create_all(engine) t = [] for i in range(30) : t.append({inp1:str(i), inp2:str(i), inp3:str(i), inp4:str(i), inp5:str(i)}) c = clock() engine.execute(table.insert(), t) print Insert: +str(clock()-c) session = sessionmaker(bind=engine)() class UnmappedProject(object): pass c = clock() res = [] for row in engine.execute(table.select()): identity_key = row[table.c.id] if identity_key in session.identity_map: # here we'd use existing object assert False else: obj = UnmappedProject() for col in table.c: setattr(obj, col.key, row[col]) res.append(obj) print Sql query: +str(clock()-c) c = clock() res = session.query(Project).all() print Session query: +str(clock()-c) On Aug 12, 2011, at 8:51 AM, Massi wrote: Hi everyone, I'm doing some test to evaluate the performance of querying with sqlalchemy via ORM. I wrote a simple script to measure the execution time of a simple select query made on relatively small table (300 000 records, 6 columns) in sqlite. Here is the script: from sqlalchemy import * from sqlalchemy.orm import mapper, sessionmaker from time import clock engine = create_engine('sqlite:///test.db', echo=False) metadata = MetaData() table = Table('projects', metadata, Column('id', Integer, primary_key=True), Column('inp1', String(50)), Column('inp2', String(50)), Column('inp3', String(50)), Column('inp4', String(50)), Column('inp5', String(50)), ) class Project(object) : pass mapper(Project, table) metadata.create_all(engine) t = [] for i in range(30) : t.append({inp1:str(i), inp2:str(i), inp3:str(i), inp4:str(i), inp5:str(i)}) c = clock() engine.execute(table.insert(), t) print Insert: +str(clock()-c) session = sessionmaker(bind=engine)() c = clock() res = engine.execute(table.select()).fetchall() print Sql query: +str(clock()-c) c = clock() res = session.query(Project).all() print Session query: +str(clock()-c) On my PC (windows 7, 64-bit, intel i7 2.93 Ghz) this is the output: Insert: 3.41080167807 Sql query: 1.26728367673 Session query: 19.6452334842 The execution time of the ORM query is about 20 times the SQL one, and this is definitely discouraging. So I guess if I'm doing something wrong or if there are some tricks when using ORM that I'm not considering. Any help is really appreciated. Thanks in advance! -- 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] joining to a from_statement
hi again, after playing a while with PG, here is a SQL statement that outputs the expected result SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id; and FYI, here is what the code is issuing (not functionnal) SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id we have problems such as column 1 does not exists if we add groups_recursive in both FROM clauses regards Nil -- 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/-/cTkUMo18h_IJ. 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] Can pk_col function be adapted for Firebird?
On Aug 12, 2011, at 9:04 AM, werner wrote: On 08/12/2011 02:18 PM, werner wrote: I can't figure out how I could adapt the pk_col function on this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle the Sequence definition needed for e.g. Firebird. At the point when Column is instantiated I don't have access to table.name and I can't figure it out either how to do it in on_table_attach. Would appreciate any tips on this. Did a bit more searching and trying and came up with this: def pk_col(cls, **kw): Produce a primary key column for a table. e.g.:: pk_col() is equivalent to:: Column(id, sa.BigInteger, doc = Primary key column for tablename, primary_key=True, sequence=sa.Sequence('tablename_id') ) kw['primary_key'] = True c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % (cls.__tablename__, dbg.pkId)), **kw) @sa.event.listens_for(c, before_parent_attach) def on_table_attach(column, table): column.name = column.key = dbg.pkId column.doc = Primary key column for %r % table.name c._creation_order = 0 # forces it to the top when using declarative return c I.e. pass cls in so I can get to __tablename__. Is this an o.k. way of doing it or is there a better/cleaner way? you should be able to set the Sequence name directly in the attach event (starting with a fake name). not sure how you're using pk_col() above (how it gets at 'cls'). Its also possible to create + attach the Sequence to the Column after the fact but I don't know that the public API is there for that quite yet. (i think calling seq._set_parent(column) would be sufficient ...) -- 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] joining to a from_statement
sure, couple of small adjustments, attached On Aug 12, 2011, at 10:44 AM, NiL wrote: hi again, after playing a while with PG, here is a SQL statement that outputs the expected result SELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_name FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id; and FYI, here is what the code is issuing (not functionnal) SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( (SELECT groups_recursive.id AS id, 1 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id) ) SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id we have problems such as column 1 does not exists if we add groups_recursive in both FROM clauses regards Nil -- 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/-/cTkUMo18h_IJ. 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. On Aug 12, 2011, at 10:44 AM, NiL wrote:hi again,after playing a while with PG, here is a SQL statement that outputs the expected resultSELECT groups_recursive.id AS groups_recursive_id, groups_recursive.name AS groups_recursive_name, groups_recursive.display_name AS groups_recursive_display_nameFROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ( SELECT groups_recursive.id AS groups_recursive_id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = 4 AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id UNION SELECT groups_recursive.id, rank+1 FROM all_parents, groups_recursive, groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = all_parents.id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id )SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.id;and FYI, here is what the code is issuing (not functionnal)SELECT groups_recursive.id AS groups_recursive_id FROM groups_recursive JOIN (WITH RECURSIVE all_parents(id, rank) AS ((SELECT groups_recursive.id AS id, "1" FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive.id = groups_recursive_parents__groups_recursive_children.parents_id AND groups_recursive_parents__groups_recursive_children.children_id = :groupid UNION SELECT groups_recursive.id AS id, rank + :rank_1 AS anon_2 FROM groups_recursive_parents__groups_recursive_children WHERE groups_recursive_parents__groups_recursive_children.children_id = id AND groups_recursive_parents__groups_recursive_children.parents_id = groups_recursive.id))SELECT * FROM all_parents) AS anon_1 ON anon_1.id = groups_recursive.idwe have problems such as column "1" does not existsif we add groups_recursive in both FROM clausesregardsNil -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view
Re: [sqlalchemy] Can pk_col function be adapted for Firebird?
On 08/12/2011 04:46 PM, Michael Bayer wrote: On Aug 12, 2011, at 9:04 AM, werner wrote: On 08/12/2011 02:18 PM, werner wrote: I can't figure out how I could adapt the pk_col function on this page http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions to handle the Sequence definition needed for e.g. Firebird. At the point when Column is instantiated I don't have access to table.name and I can't figure it out either how to do it in on_table_attach. Would appreciate any tips on this. Did a bit more searching and trying and came up with this: def pk_col(cls, **kw): Produce a primary key column for a table. e.g.:: pk_col() is equivalent to:: Column(id, sa.BigInteger, doc = Primary key column fortablename, primary_key=True, sequence=sa.Sequence('tablename_id') ) kw['primary_key'] = True c = sa.Column(sa.BigInteger(), sa.Sequence('seq_%s_%s' % (cls.__tablename__, dbg.pkId)), **kw) @sa.event.listens_for(c, before_parent_attach) def on_table_attach(column, table): column.name = column.key = dbg.pkId column.doc = Primary key column for %r % table.name c._creation_order = 0 # forces it to the top when using declarative return c I.e. pass cls in so I can get to __tablename__. Is this an o.k. way of doing it or is there a better/cleaner way? you should be able to set the Sequence name directly in the attach event (starting with a fake name). not sure how you're using pk_col() above (how it gets at 'cls'). I had this as part of the declarative base. @sad.declared_attr def id(cls): # use a method so that the pk_col() returned # here is the one used instead of a copy return pk_col(cls) Its also possible to create + attach the Sequence to the Column after the fact but I don't know that the public API is there for that quite yet. (i think calling seq._set_parent(column) would be sufficient ...) Yeap, great. got this now: @sa.event.listens_for(c, before_parent_attach) def on_table_attach(column, table): column.name = column.key = dbg.pkId column.doc = Primary key column for %r % table.name seq = sa.Sequence('seq_%s_%s' % (table.name, dbg.pkId)) seq._set_parent(column) I guess I just have to watch out for whenever you provide a public API for _set_parent. Thanks Werner -- 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] joining to a from_statement
thank you so much Michael !! much better few last things are WITH RECURSIVE all_parents(id, rank) AS SELECT groups_recursive.id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children quotes around the 1, this leads to ERROR: column 1 does not exist + the param :rank_1 feels weird, it is generated by the rank + 1 in union( select([groups.c.id, rank + 1]).\ but it feels really close to the solution best NiL -- 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/-/Q0jzhkVnW6gJ. 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: Softcoding .filter(...)
Mark, yes, in part. What I cannot figure out -- and am not sure this is possible with SQLAlchemy, is to go from strings, as returned from HTTP Post's, representing the fields in the table (for the left side of the relation) to the actual statemetn itself. In other words, if the HTTP post calls for campring the Lastname column for equality to the value smith, I can build the == 'smith' portfion, but how do I Ibuild a String for the column name, into the value for that column name so as to comport with SQLAlchemy's sytnax requirements. I don;t think I can just have a query, q, and then say: q = q.filter(lastname == 'smith') can I? RVince On Aug 12, 10:33 am, Mark Erbaugh m...@microenh.com wrote: On Aug 12, 2011, at 10:21 AM, RVince wrote: I'm trying to discern a means of creating a .filter(A rel B) where the values for A, rel and B come from an parameters passed in to the web page. I already have an SQLAlchemy statement, say query = Session.query(table).filter(A==B) and I want to be able to allow for a drilldown of sorts by the, such that from the web page they can pick a value from a dropdown, a relation (from a dropdown) and a textbox to compare to. But my problem is once I have these three values, how do I get them into the .filter() function? That's not going to merely accept string values -- is there a way to do this? Thanks, RVince You can build your SQLAlchemy queries dynamically, i.e. q1 = query.Session.query(table).filter(A == B) q2 = q1.filter(C == D) q3 = q2.filter(E == F) you could apply different relationships using conditional Python statements: if rel == 'eq': q4 = q3.filter(G == H) elif rel == 'neq': q4 = q3.filter(G != H) is this what you're looking for? Mark -- 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: Softcoding .filter(...)
say you want to filter on the 'field' (field would be a string representing the name of the field) on objects of class == Klass field_attr = getattr(Klass, field) would give you the instrumented attribute then Session.query(Klass).filter(field_attr == searchString) or Session.query(Klass).filter(field_attr.endswith(searchString)) would run HTH NiL -- 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/-/DWEguyVv4dIJ. 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: Softcoding .filter(...)
On Aug 12, 2011, at 11:52 AM, NiL wrote: say you want to filter on the 'field' (field would be a string representing the name of the field) on objects of class == Klass field_attr = getattr(Klass, field) would give you the instrumented attribute then Session.query(Klass).filter(field_attr == searchString) or Session.query(Klass).filter(field_attr.endswith(searchString)) would run HTH NiL You can also use the class's __dict__ member: field_attr = Klass.__dict__['field'] It really amazes me how Pythonic SQLAlchemy makes database access. Mark -- 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: Softcoding .filter(...)
On 08/12/2011 05:52 PM, NiL wrote: say you want to filter on the 'field' (field would be a string representing the name of the field) on objects of class == Klass field_attr = getattr(Klass, field) would give you the instrumented attribute then Session.query(Klass).filter(field_attr == searchString) or Session.query(Klass).filter(field_attr.endswith(searchString)) would run Alternatively if you are only interested in equality you can skip the getattr and use filter_by in combination with python's keyword argument handling: Session.query(klass).filter_by(**{field: value}) Wichert. -- 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] joining to a from_statement
its at the point where you should be able to tweak it using documented processes. column() applies quotes for example, whereas literal_column(1) would not. Same for an expression x + 1 will turn the 1 into a bind, would not if you again use literal_column() http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.column http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.expression.literal_column On Aug 12, 2011, at 11:27 AM, NiL wrote: thank you so much Michael !! much better few last things are WITH RECURSIVE all_parents(id, rank) AS SELECT groups_recursive.id, 1 FROM groups_recursive, groups_recursive_parents__groups_recursive_children quotes around the 1, this leads to ERROR: column 1 does not exist + the param :rank_1 feels weird, it is generated by the rank + 1 in union( select([groups.c.id, rank + 1]).\ but it feels really close to the solution best NiL -- 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/-/Q0jzhkVnW6gJ. 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] bulk selection
Dear All, I have a list of elements for which I need to establish if they are in a tadabase. I can make for each element a separate query but I am afraid that that is not the best approach what is the best practice in this case? Thanks -- 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] joining to a from_statement
yes, many thanks I now have a method in my Group class (still in elixir syntax) def hierarchy_ng(self): with CommonTableExpression.create( all_parents, [id, rank]) as all_parents: rank = literal_column(rank) groups = Group.table groups_assoc = Group._descriptor.find_relationship('parents').table s = select([groups.c.id, literal_column(1)]).\ where(groups.c.id==groups_assoc.c.parents_id).\ where(groups_assoc.c.children_id==bindparam(groupid)).\ correlate(None).\ union( select([groups.c.id, rank + literal_column(1)]).\ where(groups_assoc.c.children_id== all_parents.c.id).\ where(groups_assoc.c.parents_id==groups.c.id ).\ correlate(None) ).params(groupid=self.id) all_parents = SelectFromCTE(all_parents, s) all_parents = all_parents.alias() q = Group.query.join((all_parents, all_parents.c.id==Group.id)) return q.all() and it's ok now ! -- 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/-/ji9w6r2l09IJ. 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: Softcoding .filter(...)
Wickert, can you give me an example ? I'm a little confused by this posts of yours. RVince On Aug 12, 12:20 pm, Wichert Akkerman wich...@wiggy.net wrote: On 08/12/2011 05:52 PM, NiL wrote: say you want to filter on the 'field' (field would be a string representing the name of the field) on objects of class == Klass field_attr = getattr(Klass, field) would give you the instrumented attribute then Session.query(Klass).filter(field_attr == searchString) or Session.query(Klass).filter(field_attr.endswith(searchString)) would run Alternatively if you are only interested in equality you can skip the getattr and use filter_by in combination with python's keyword argument handling: Session.query(klass).filter_by(**{field: value}) Wichert. -- 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: Softcoding .filter(...)
Thanks to all you guys. Really. I didn't think I would be able to do this! RVince -- 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] group_by argument as a result of a query
Dear all, I am trying to limit group_by function only on the rows that satisfy certain query if I use group_by(table.c.something) this pertains to whole table. I tried to use elements of the tuple rendered as a result of a query as arguments (as it has been suggested on this forum) but it did not work. Is this possible at all? Thanks -- 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] The capacity of the session
Dear all, How can I determine the number of objects (the memory capacity) that a session can take? How can I determine the size of an object? Thanks -- 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] bulk selection
On 12.08.11 09:41, Eduardo wrote: Dear All, I have a list of elements for which I need to establish if they are in a tadabase. I can make for each element a separate query but I am afraid that that is not the best approach what is the best practice in this case? Thanks Depending on the size of the list I would use the in_ operator[1]. If you're talking about a really long list [2] you might drop everything to a temporary list and join from there. Mariano [1] http://www.sqlalchemy.org/docs/orm/tutorial.html#common-filter-operators [2] in old days there were limits to the amount of items in an in operator, I'm not sure if it still holds nowadays. -- 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] group_by argument as a result of a query
can you please illustrate a simple SQL statement that illustrates what you are trying to achieve ? On Aug 12, 2011, at 1:00 PM, Eduardo wrote: Dear all, I am trying to limit group_by function only on the rows that satisfy certain query if I use group_by(table.c.something) this pertains to whole table. I tried to use elements of the tuple rendered as a result of a query as arguments (as it has been suggested on this forum) but it did not work. Is this possible at all? Thanks -- 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] The capacity of the session
I'm not familiar with any method for doing that, and it depends mostly on how much data each of your objects contains - the Session/ORM will have a small amount of memory overhead beyond that which is of a fixed size. At best I'd say you can estimate the memory size per object by running a program, put 1000 objects in the Session, then run top and see the memory, then put 5000 in, compare, 1, compare, to get an approximation.This would be specific to the mappings you're using as well as the data being loaded into them. On Aug 12, 2011, at 1:03 PM, Eduardo wrote: Dear all, How can I determine the number of objects (the memory capacity) that a session can take? How can I determine the size of an object? Thanks -- 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] declarative __table__ columns
Is there a way to access the parameters to the Column() call used to set up a database table when given either an instance field or class field? For example: class MyClass(Base): ... f1 = Column(Integer, nullable=False, info={'min':0}) ... If I have MyClass.f1 or my_class.f1 (where my_class is an instance of MyClass) is there a way to get nullable or info? The only way I've come up with so far is to match the __table__.columns elements on the name parameter. Thanks, Mark -- 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] declarative __table__ columns
On Aug 12, 2011, at 5:05 PM, Mark Erbaugh wrote: Is there a way to access the parameters to the Column() call used to set up a database table when given either an instance field or class field? For example: class MyClass(Base): ... f1 = Column(Integer, nullable=False, info={'min':0}) ... If I have MyClass.f1 or my_class.f1 (where my_class is an instance of MyClass) is there a way to get nullable or info? The only way I've come up with so far is to match the __table__.columns elements on the name parameter. if you have MyClass.fi, column is MyClass.f1.property.columns[0]. -- 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] declarative __table__ columns
On Aug 12, 2011, at 5:26 PM, Michael Bayer wrote: Is there a way to access the parameters to the Column() call used to set up a database table when given either an instance field or class field? For example: class MyClass(Base): ... f1 = Column(Integer, nullable=False, info={'min':0}) ... If I have MyClass.f1 or my_class.f1 (where my_class is an instance of MyClass) is there a way to get nullable or info? The only way I've come up with so far is to match the __table__.columns elements on the name parameter. if you have MyClass.fi, column is MyClass.f1.property.columns[0]. Thanks - just what I was looking for! Mark -- 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] string concatentation of multiple columns in select statement
I'm trying to do something like this in sqlalchemy: select (score/10)*10 || '-' || (score/10)*10+9 as scorerange, count(*)from scoresgroup by score/10 order by 1 which should give: scorerange | count +--- 0-9|11 10-19 |14 20-29 | 3 30-39 | 2 Any ideas on how to do the string concatentation in the ranges? -- 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] string concatentation of multiple columns in select statement
On Aug 12, 2011, at 6:43 PM, nospam wrote: I'm trying to do something like this in sqlalchemy: select (score/10)*10 || '-' || (score/10)*10+9 as scorerange, count(*)from scoresgroup by score/10 order by 1 which should give: scorerange | count +--- 0-9|11 10-19 |14 20-29 | 3 30-39 | 2 Any ideas on how to do the string concatentation in the ranges? the + operator resolves to the concatenation operator if used against an expression with a string type, or if using untyped elements concat() will get you there: from sqlalchemy.sql import column column('foo', String) + bar column('foo').concat(bar) your expression above appears to be evaluating a numeric against a string (so...this is MySQL ? :) ) you might consider calling cast(expr, String) on the numeric value before evaluating in a string context. ((score / 10) * 10).concat(-).concat((score / 10)*10) + 9).label('scorerange') from sqlalchemy import cast, String cast((score / 10) * 10, String).concat(-).concat(cast((score / 10)*10) + 9, String)).label('scorerange') -- 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.