Re: [sqlalchemy] ClassManager.new_instance does not longer initialize polymorphic column
Hi Michael, On Tue, 2012-01-31 at 10:26 -0500, Michael Bayer wrote: If you're using new_instance() to create the instance, then you are deserializing data from somewhere, why isn't the discriminator value, which is after all one of the column values in the table, not present in this deserialization ? Because I considered the value of the type column an implementation detail and it was managed automatically by SQLAlchemy so far. Further, the serialized format is not that close to the database format (it is actually XML). Right now the event in question is emitted only from the __init__() method of your object, which is obviously what you don't call when deserializing.The discriminator value is now considered to be just another instance variable that you can change freely - a default for it is configured from __init__(). I actually think about diverging from my original approach. In case anybody cares: So far, I am using __init__ to initialize some required columns from context (for example, the creator column is initialized from the current user). During deserialization this should not happen as the current user will probably be different from the creator of the received instance. Probably it will be more pythonic by changing the implementation that it is possible to create an empty instance for every class and catch missing information at the database level. Anyway the event here is the init event which you can emit from classmanager: manager.dispatch.init(state, args, kw) where args, kw are what would normally be sent to __init__, but can just be blank here. Invoking the events is not entirely public API, though. Thanks for the information. Therefore, I will probably go to set the discriminator column manually while deserialization can not go via __init__. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Stuttgart, HRB 733694 Managing director: Prof. Dr. Karl Schweizerhof, Dipl.-Math. Ulrich Franz -- 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] Possible bug with join condition on self-referential outer joins
Hi! I have a table with a self-reference of two columns that represents a tree structure. I was trying to build an outerjoin to select all the nodes have children but NO grandchildren, but the SQL constructed was incorrect, as it was not aliasing properly one of the columns. note_t Table('node_t', metadata, Column('id', Integer, primary_key=True), Column('project_id', Integer), Column('parent_id', Integer), sa.ForeignKeyConstraint( ['project_id', 'parent_id'], ['node_t.project_id', 'node_t.id])) mapper(Node, node_t, properties= { 'children' : relationship(Node, remote_side=[note_t.c.id, node_t.c.project_id] ) }) *print str(session.query(Node).outerjoin(Node, Node.children, aliased=True)))* Generated (simplified): SELECT node.id, node.project_id, node.parent_id FROM node LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id AND node.project_id = *node*.project_id Expected: SELECT node.id, node.project_id, node.parent_id FROM node LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id AND node.project_id = *parent*.project_id * * Making the join condition explicit generates the correct SQL *Parent = aliased(Node)* *print str(session.query(**Node**).outerjoin(**Parent**, (Node.parent_id == Parent.id) (Node.project_id == Parent.project_id)))* * * I have attached a small test file (test.py) that shows this behaviour and is based on one of your tests suites (test_relationships). Thanks in advance! Pau. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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. #!/usr/bin/env python import sqlalchemy as sa from sqlalchemy import Integer, String, ForeignKey, MetaData,\ and_, Table, Column, engine_from_config from sqlalchemy.orm import mapper, relationship, relation, \ backref, create_session, configure_mappers, \ clear_mappers, sessionmaker, attributes,\ Session, composite, column_property, \ scoped_session, joinedload, aliased metadata=MetaData() company_t = Table('company_t', metadata, Column('company_id', Integer, primary_key=True), Column('name', sa.Unicode(30))) employee_t = Table('employee_t', metadata, Column('company_id', Integer, primary_key=True), Column('emp_id', Integer, primary_key=True), Column('name', sa.Unicode(30)), Column('reports_to_id', Integer), sa.ForeignKeyConstraint( ['company_id'], ['company_t.company_id']), sa.ForeignKeyConstraint( ['company_id', 'reports_to_id'], ['employee_t.company_id', 'employee_t.emp_id'])) class Company(object): pass class Employee(object): def __init__(self, name, company, emp_id, reports_to=None): self.name = name self.company = company self.emp_id = emp_id self.reports_to = reports_to mapper(Company, company_t) mapper(Employee, employee_t, properties= { 'company' : relationship(Company, backref='employees'), 'reports_to' : relationship(Employee, _local_remote_pairs = [ (employee_t.c.reports_to_id, employee_t.c.emp_id), (employee_t.c.company_id, employee_t.c.company_id) ], foreign_keys=[employee_t.c.reports_to_id], backref=backref('employees', foreign_keys=None) ) }) c1 = Company() c2 = Company() e1 = Employee(u'emp1', c1, 1) e2 = Employee(u'emp2', c1, 2, e1) e3 = Employee(u'emp3', c1, 3, e1) e4 = Employee(u'emp4', c1, 4, e3) e5 = Employee(u'emp5', c2, 1) e6 = Employee(u'emp6', c2, 2, e5) e7 = Employee(u'emp7', c2, 3, e5) engine = engine_from_config({ sqlalchemy.url : sqlite:///memory, sqlalchemy.echo : False, }) sess = scoped_session(sessionmaker(bind=engine))() metadata.bind=engine metadata.create_all() sess.add_all((c1, c2)) sess.flush() sess.expunge_all() print SQL for: sess.query(Employee).outerjoin(Employee, Employee.reports_to)\n print str(sess.query(Employee).outerjoin(Employee, Employee.reports_to)) print \n\nSQL for: sess.query(Employee).outerjoin(Employee, Employee.reports_to, aliased=True)\n print str(sess.query(Employee).outerjoin(Employee, Employee.reports_to, aliased=True)) print \n\nSQL for: Boss = aliased(Employee); sess.query(Employee).outerjoin(Boss, (Employee.reports_to_id == Boss.emp_id) (Employee.company_id == Boss.company_id))\n Boss = aliased(Employee) print str(sess.query(Employee).outerjoin(Boss, (Employee.reports_to_id == Boss.emp_id) (Employee.company_id == Boss.company_id)))
Re: [sqlalchemy] Overload Query Object
Hi, right, it's that thanks 2012/1/31 Tate Kim insight...@gmail.com ** Hi, Have you checked the __iter__ method ? -- *From: * Christian Démolis christiandemo...@gmail.com *Sender: * sqlalchemy@googlegroups.com *Date: *Tue, 31 Jan 2012 17:39:54 +0100 *To: *sqlalchemysqlalchemy@googlegroups.com *ReplyTo: * sqlalchemy@googlegroups.com *Subject: *[sqlalchemy] Overload Query Object Hi Michael, i overload class Query in my script. i have 4 ways to obtain query's results. 1/ session.query(model.x).all() 2/ session.query(model.x).first() 3/ session.query(model.x).one() 4/ for e in session.query(model.x): print e in case 1,2,3, i know which method is used What method is used in case 4 ? Thanks in advance. Chris class Query(Query): def __init__(self, *arg, **kw): self._populate_existing = True super(Query, self).__init__(*arg, **kw) def all(self): print all, threading.current_thread() param.lock_bdd.acquire() global session try: x = super(Query, self).all() except exc2.OperationalError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except exc2.StatementError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except: raise param.lock_bdd.release() print /all, threading.current_thread() return x def one(self): print one, threading.current_thread() param.lock_bdd.acquire() global session try: x = super(Query, self).one() except exc2.OperationalError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except exc2.StatementError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except: raise param.lock_bdd.release() print /one, threading.current_thread() return x def first(self): print first, threading.current_thread() param.lock_bdd.acquire() global session try: x = super(Query, self).first() except exc2.OperationalError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except exc2.StatementError: import common common.alerte(L'écriture a échoué. Retentez l'action, Erreur MySQL) session.rollback() except: raise param.lock_bdd.release() print /first, threading.current_thread() return x -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Python 2.7.1 + SQLAlchemy 0.6.8 + Kinterbasdb == (error) argument for 's' must be a string None None
Hi, I'm trying to use Python 2.7.1 + SA 0.6.8 + kinterbasdb (3.3.0) and I'm always getting the same error when I try to do some query: File /home/leon/app/env/tg2/local/lib/python2.7/site-packages/ kinterbasdb/__init__.py, line 1695, in addString newEntry = struct.pack(format, codeAsByte, chr(sLen), s) sqlalchemy.exc.DBAPIError: (error) argument for 's' must be a string None None I've tried with: session.execute('select * from foo') -- error an_engine = create_engine('firebird://SYSDBA:masterkey@localhost/ foo', pool_size=1, pool_recycle=30) meta = MetaData(bind=an_engine) tbl = Table('foo', meta, autoload=True) -- error The same thing works on SA 0.5.8 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.
[sqlalchemy] Re: Python 2.7.1 + SQLAlchemy 0.6.8 + Kinterbasdb == (error) argument for 's' must be a string None None
Forget it... I was passing the url as a unicode string because I saw some warnings in the past with postgresql. The same thing makes kinterbasdb (firebird) fail. León Domingo Ender - Factoría de Software leon.domi...@ender.es jestre...@ender.es Avda. Cerro del Águila, 7. S23 - 2ª Planta 28703 S.S. de los Reyes (MADRID) Tlf. 902 01 44 01 http://www.ender.es/ http://www.facebook.com/home.php?#%21/pages/San-Sebastian-de-los-Reyes-Spain/Ender-Factoria-de-Software/142213119143359?ref=ts * http://www.ender.es* On 31 January 2012 17:37, León Domingo leon.domi...@ender.es wrote: Hi, I'm trying to use Python 2.7.1 + SA 0.6.8 + kinterbasdb (3.3.0) and I'm always getting the same error when I try to do some query: File /home/leon/app/env/tg2/local/lib/python2.7/site-packages/ kinterbasdb/__init__.py, line 1695, in addString newEntry = struct.pack(format, codeAsByte, chr(sLen), s) sqlalchemy.exc.DBAPIError: (error) argument for 's' must be a string None None I've tried with: session.execute('select * from foo') -- error an_engine = create_engine('firebird://SYSDBA:masterkey@localhost/ foo', pool_size=1, pool_recycle=30) meta = MetaData(bind=an_engine) tbl = Table('foo', meta, autoload=True) -- error The same thing works on SA 0.5.8 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.
[sqlalchemy] Best practices for SaaS application
Hello, I have an application SaaS with mongodb and I need to change to sqlalchemy using postgresql or mysql. My requirements are: What the best options for SaaS app with sqlalchemy? - Should I use 1 database per customer. - Sharding. - Schema per customer (postgresql) with subdomain (my current app). Best regards, Samir Mamude -- 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] Possible bug with join condition on self-referential outer joins
This is essentially ticket #1401 and I've attached this there as well as moved up the priority, however this issue is extremely complicated and would require some serious rethinking of the relationship()'s inner workings.It would take several days to come up with a general solution so I can't give you a fix for this right now. http://www.sqlalchemy.org/trac/ticket/1401 On Feb 1, 2012, at 5:40 AM, Pau Tallada wrote: Hi! I have a table with a self-reference of two columns that represents a tree structure. I was trying to build an outerjoin to select all the nodes have children but NO grandchildren, but the SQL constructed was incorrect, as it was not aliasing properly one of the columns. note_t Table('node_t', metadata, Column('id', Integer, primary_key=True), Column('project_id', Integer), Column('parent_id', Integer), sa.ForeignKeyConstraint( ['project_id', 'parent_id'], ['node_t.project_id', 'node_t.id])) mapper(Node, node_t, properties= { 'children' : relationship(Node, remote_side=[note_t.c.id, node_t.c.project_id] ) }) print str(session.query(Node).outerjoin(Node, Node.children, aliased=True))) Generated (simplified): SELECT node.id, node.project_id, node.parent_id FROM node LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id AND node.project_id = node.project_id Expected: SELECT node.id, node.project_id, node.parent_id FROM node LEFT OUTER JOIN node AS parent ON node.parent_id = parent.id AND node.project_id = parent.project_id Making the join condition explicit generates the correct SQL Parent = aliased(Node) print str(session.query(Node).outerjoin(Parent, (Node.parent_id == Parent.id) (Node.project_id == Parent.project_id))) I have attached a small test file (test.py) that shows this behaviour and is based on one of your tests suites (test_relationships). Thanks in advance! Pau. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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. test.py -- 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] Relationship definition problem
I am currently facing a problem with a legacy database for which I am creating a SQLAlchemy layer. With regards to mirroring the columns everything goes well. However I am having difficulties defining a relationship which is not many-to-many, but does use something which can be compared with an association proxy (I think). The simplified model: class Session(Base): __tablename__ = 'sessions' id = Column(Integer, primary_key=True) class Area(Base): __tablename__ = 'areas' session_id = Column(Integer, ForeignKey('sessions.id'), primary_key=True) area_id = Column(Integer, primary_key=True) name = Column(String) class Parent(Base): __tablename__ = 'parents' id = Column(Integer, primary_key=True) session_id = Column(Integer, ForeignKey('sessions.id')) class Child(Base): __tablename__ = 'children' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parents.id')) area_id = Column(Integer) area = relationship(???) In short; the Area class has a multi-column primary key (sesseion_id, area_id). I am trying to create the relationship for area in the Child class, however this would need the session_id which can be found in the parent class. This was my last effort (again assuming I could treat the parent class as a association proxy): area = relationship( 'Area', secondary='parents', primaryjoin='children.c.parent_id==parents.c.id', secondaryjoin='and_(parents.c.session_id==areas.c.session_id, children.c.area_id==areas.c.id)', viewonly=True ) No able to figure out any foreign key relations if those would be required. Any help and/or insight would be appreciated, Thijs -- 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 definition problem
On Feb 1, 2012, at 11:14 AM, Thijs Engels wrote: In short; the Area class has a multi-column primary key (sesseion_id, area_id). I am trying to create the relationship for area in the Child class, however this would need the session_id which can be found in the parent class. This was my last effort (again assuming I could treat the parent class as a association proxy): area = relationship( 'Area', secondary='parents', primaryjoin='children.c.parent_id==parents.c.id', secondaryjoin='and_(parents.c.session_id==areas.c.session_id, children.c.area_id==areas.c.id)', viewonly=True ) No able to figure out any foreign key relations if those would be required. Any help and/or insight would be appreciated, it's not very streamlined but a blunt approach using EXISTS seems to work: class Child(Base): __tablename__ = 'children' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey('parents.id')) area_id = Column(Integer) area = relationship(Area, foreign_keys=area_id, primaryjoin= (Area.area_id==area_id) exists().where( (Parent.session_id==Area.session_id) (parent_id==Parent.id)) ) There's two ways to navigate from Child to Area. What does it mean for Child.area_id to have a value that doesn't match on Child.parent_id-Parent.session_id-Area.session_id ? Is that essentially corrupt data ?That there's two ways to navigate implies that there are essentially two different relationships to Area - one is the straight relationship() on FK. The other is a long association proxy across Parent-Session-Area. Then the area you're looking for here is the intersection of those two. The primaryjoin we have above is basically doing the same thing in SQL, though if I wanted to be able to get at Child-Area in both ways independently I might build it out as the two relationships. If there's no need for two different ways to navigate from Child-Area, that only points out how this relational design is wrong - it's capable of storing corrupt data. Child.area_id might be just a denormalized shortcut to the longer chain across Parent. -- 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] What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE
assuming this is the same thing asked on reddit: http://www.reddit.com/r/Python/comments/p5grh/sqlalchemy_whats_the_idiomatic_way_of_writing/ I mentioned ticket #960 as where we'd someday support MERGE and its variants, but this can also be rolled with @compiles, see the example in that ticket. http://www.sqlalchemy.org/trac/ticket/960 On Jan 31, 2012, at 11:32 PM, Didip Kerabat wrote: when using SqlAlchemy Core (not using Session/ORM)? I have quite a few tables with composite primary keys that can use it for optimization. At the moment what I have is quite ghetto, below is a contrive example of it: stmt = str(user_table.insert().values(email=email, name=name)) stmt += ON DUPLICATE KEY UPDATE name=%s engine.execute(stmt, email, name, name) NOTE: I believe this is MySQL specific. -- 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] What's the idiomatic way of writing INSERT... ON DUPLICATE KEY UPDATE
Correct, the same guy. Thank you for the pointer! Today I learned about the compiles decorator. - Didip - On Feb 1, 2012, at 10:56 AM, Michael Bayer wrote: assuming this is the same thing asked on reddit: http://www.reddit.com/r/Python/comments/p5grh/sqlalchemy_whats_the_idiomatic_way_of_writing/ I mentioned ticket #960 as where we'd someday support MERGE and its variants, but this can also be rolled with @compiles, see the example in that ticket. http://www.sqlalchemy.org/trac/ticket/960 On Jan 31, 2012, at 11:32 PM, Didip Kerabat wrote: when using SqlAlchemy Core (not using Session/ORM)? I have quite a few tables with composite primary keys that can use it for optimization. At the moment what I have is quite ghetto, below is a contrive example of it: stmt = str(user_table.insert().values(email=email, name=name)) stmt += ON DUPLICATE KEY UPDATE name=%s engine.execute(stmt, email, name, name) NOTE: I believe this is MySQL specific. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: session.query().get() is unsupported during flush for getting an object that was just added?
If the value is based on what's already been INSERTed for previous rows, I'd emit a SQL statement to get at the value.If it's based on some kind of natural consideration that isn't dependent on the outcome of an INSERT statement, then you can do the looping above within the before_flush() event and assign everything at once.Basically you need to batch the same way the UOW itself does. is IdentitySet an OrderedSet? if I loop through session.new, for example, am I guaranteed to hit these in the same order they were added to the session? -- 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.