[sqlalchemy] Re: ORM lifecycle questions
On Dec 17, 2008, at 11:32 AM, Ross Vandegrift wrote: On Thu, Dec 11, 2008 at 02:59:39PM -0500, Michael Bayer wrote: On Dec 11, 2008, at 2:04 PM, Ross Vandegrift wrote: So if I do something like this: p = meta.session.query(model.Pool) pool = p.get(7) meta.session.refresh(pool) InvalidRequestError: Instance 'p...@0xa6aca8c' is not persistent within this Session um thats kind of weird. that sort of looks like meta.session is being closed out at some point (i.e. not illustrated in the example) and being recreated later on is that possible ? I think I worked around this issue and I believe it really stemmed from a misunderstanding of how shared session data is. Suppose that I am running paster serve --reload dev.ini and paster shell dev.ini against the same db. Potentially, using the web-version of the app could cause objects to change state in the shell version of the app? if paster restarted itself, which is what --reload does, then yeah. --~--~-~--~~~---~--~~ 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: setting up insert many stmt?
On Dec 17, 2008, at 8:18 AM, Max Ischenko wrote: Hello, I'm having trouble doing insertmany correctly. I read the docs and came up with this code: s = Session() ins = data_tbl.insert() records = [] for rec in s.execute(sql).fetchall(): records.append(rec) ins.execute(records) im not entirely sure of the mechanics there but try: records.append(dict(rec)) if still no go, make sure records is coming up as a list of dicts, and each item in the dict is of the appropriate key/value to work as a value for your insert statement. --~--~-~--~~~---~--~~ 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: ORM lifecycle questions
On Thu, Dec 11, 2008 at 02:59:39PM -0500, Michael Bayer wrote: On Dec 11, 2008, at 2:04 PM, Ross Vandegrift wrote: So if I do something like this: p = meta.session.query(model.Pool) pool = p.get(7) meta.session.refresh(pool) InvalidRequestError: Instance 'p...@0xa6aca8c' is not persistent within this Session um thats kind of weird. that sort of looks like meta.session is being closed out at some point (i.e. not illustrated in the example) and being recreated later on is that possible ? I think I worked around this issue and I believe it really stemmed from a misunderstanding of how shared session data is. Suppose that I am running paster serve --reload dev.ini and paster shell dev.ini against the same db. Potentially, using the web-version of the app could cause objects to change state in the shell version of the app? Ross -- Ross Vandegrift r...@kallisti.us If the fight gets hot, the songs get hotter. If the going gets tough, the songs get tougher. --Woody Guthrie --~--~-~--~~~---~--~~ 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] speed eficiency
Hi, A very simple question... Context: * I only need to retrieve values from a table * I've a mapper object for the table Question: In a performance point a view which is the more suitable method: - mappers = session.query(User).filter_by(name='ed').all() or - results = session.execute(select([User], User.name == 'ed')) In case where the select statement is the fastest method, Is it possible to transform a mapper query into a select query? ex: query = session.query(User).filter_by(name='ed') results = session.execute(select(query)) 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] Re: Abstract base class
On Dec 17, 2008, at 2:28 AM, Joril wrote: Michael Bayer ha scritto: concrete inheritance, as referenced in that post, was not designed to be used as a configurational spacesaver and always requires a mapped selectable for the base, which you don't have here, so it's not appropriate for this use case. So for this you'd need a custom metaclass: I see, thanks! Now my problem is that I already have a custom metaclass, defining a set of common attributes for ALL my classes.. So, to declare additional fields for just a few classes, would it be ok to subclass the custom metaclass again and re-call declarative_base ()? To clarify: All classes have a timestamp attribute, and a few of them has a notes attribute too. Thanks again! yes that is entirely OK. A second declarative_base may share the metadata of the first, Base = declarative_base(metadata=somemetadata). If you want to use strings for relation() classes across the two bases, the _decl_class_registry would need to be shared as well: meta = MetaData() Base1 = declarative_base(metadata=meta, metaclass=MyMetaClass) Base2 = declarative_base(metadata=meta, metaclass=MyOtherMetaClass) Base2._decl_class_registry = Base1._decl_class_registry This is a use case we can make easier in a future release. --~--~-~--~~~---~--~~ 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] reflection for indices, function-based indices
Hi, we've got some function-based indices we currently create using plain text()-calls, like this: CREATE UNIQUE INDEX users_lower_email_index ON user_db.users (lower(email)); This works, but it would be nice to stay in the SA-world even with these indices. And for schema-evolution purposes, we utilize reflection to compare tables. Reflection does not not include indices like the above though. Or I missed them? If not, is there a way to add that to SA, maybe through a plugin? I'd be happy to contribute if this is seen as valuable addition, I must add though that because we solely use postgres, I won't be able to add support for anything beyond that - but maybe others would step up to integrate other engines. Diez --~--~-~--~~~---~--~~ 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: Vague InterfaceError (threading issue?) running normal query
On Dec 17, 2008, at 10:10 AM, Ken wrote: Actually, I found that the error goes away if I don't set pool_recycle=True. However, I'm told pool_recycle is necessary to prevent MySQL has gone away errors. yes but pool_recycle is documented as a number of seconds, and a typical value is 3600 for one hour. setting it to True means it will recycle the connection every second. Technically, that shouldn't cause any broken behavior either, but that seems to be the source here. --~--~-~--~~~---~--~~ 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] exceptions.ArgumentError when trying to map class to a table
Hi everyone , I have the following setup (snipped): in_table = Table('in', metadata, Column('id', Integer, primary_key=True), Column('pA', String(6), ForeignKey('p.id'), nullable=False), Column('pB', String(6), ForeignKey('p.id'), nullable=False), Column('evidence', Integer, ForeignKey('documents.id'), nullable=False) ) mapper(Paper, document_table, properties={'authors':relation(Author, secondary=papers_to_authors_table, backref='publications')}) mapper(Author, authors_table) mapper(Ent, p_table) mapper(Cooccurrence, in_table, properties={ 'evidence':relation(Paper) }) I can't seem to get the last mapper to work and I get the following exception: raise exceptions.ArgumentError(WARNING: column '%s' not being added due to property '%s'. Specify 'allow_column_override=True' to mapper() to ignore this condition. % (column.key, repr(prop))) sqlalchemy.exceptions.ArgumentError: WARNING: column 'evidence' not being added due to property 'sqlalchemy.orm.properties.PropertyLoader object at 0x2aa171b6d0'. Specify 'allow_column_override=True' to mapper() to ignore this condition. So I try to use the allow_column_override but it doesnt work and I just get a new error: raise exceptions.UnmappedColumnError(Can't execute sync rule for destination column '%s'; mapper '%s' does not map this column. Try using an explicit `foreign_keys` collection which does not include this column (or use a viewonly=True relation). % (dest_column, source_mapper)) sqlalchemy.exceptions.UnmappedColumnError: Can't execute sync rule for destination column 'ppi.evidence'; mapper 'Mapper|Paper|documents' does not map this column. Try using an explicit `foreign_keys` collection which does not include this column (or use a viewonly=True relation). Any help greatly appreciated, Many thanks in advance, Nathan --~--~-~--~~~---~--~~ 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] association( viewonly ) bug?
i found some discrepancies which may or may not be a bug. When a many2many implicit assoc via secondary_table is setup with view_only=True: a) proploader.remote_side contains only the primaryjoin item (which is probably ok, i dont know what it's used for), so i have to dig secondary_synchronize_pairs b) the query(A).filter( A.m2mx.contains( someX)) produces ...WHERE ... AND mmt_B_h_2_X_1.right_id = X.db_id instead of ...WHERE ... AND mmt_B_g_2_X_1.right_id = ? is b) bug or intentional? if intentional, how i can do that ? svil --~--~-~--~~~---~--~~ 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: reflection for indices, function-based indices
On Dec 17, 2008, at 6:36 AM, Diez B. Roggisch wrote: Hi, we've got some function-based indices we currently create using plain text()-calls, like this: CREATE UNIQUE INDEX users_lower_email_index ON user_db.users (lower(email)); This works, but it would be nice to stay in the SA-world even with these indices. And for schema-evolution purposes, we utilize reflection to compare tables. Reflection does not not include indices like the above though. Or I missed them? If not, is there a way to add that to SA, maybe through a plugin? I'd be happy to contribute if this is seen as valuable addition, I must add though that because we solely use postgres, I won't be able to add support for anything beyond that - but maybe others would step up to integrate other engines. Randall Smith has been working on a schema inspection API which I believe seeks to reflect a wider variety of constructs such as indexes. His progress can be seen here: http://www.sqlalchemy.org/trac/browser/sqlalchemy/branches/reflection He was pretty active as of three weeks ago but hasn't been around lately, I can give you his email address if you want to contact him to collaborate further. Search this mailing list for a discussion about the API. --~--~-~--~~~---~--~~ 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] setting up insert many stmt?
Hello, I'm having trouble doing insertmany correctly. I read the docs and came up with this code: s = Session() ins = data_tbl.insert() records = [] for rec in s.execute(sql).fetchall(): records.append(rec) ins.execute(records) It gives an error: File '/var/www/site-baseline/doupy/doupy/controllers/salarydb.py', line 170 in update_clean ins.execute(records) File '/var/www/site-baseline/py/lib/python2.5/site-packages/ SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/sql/expression.py', line 1129 in execute return e.execute_clauseelement(self, multiparams, params) File '/var/www/site-baseline/py/lib/python2.5/site-packages/ SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/engine/base.py', line 1181 in execute_clauseelement return connection.execute_clauseelement(elem, multiparams, params) File '/var/www/site-baseline/py/lib/python2.5/site-packages/ SQLAlchemy-0.5.0rc4-py2.5.egg/sqlalchemy/engine/base.py', line 848 in execute_clauseelement keys = params[0].keys() AttributeError: 'list' object has no attribute 'keys' Huh? Max. --~--~-~--~~~---~--~~ 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: Vague InterfaceError (threading issue?) running normal query
Actually, I found that the error goes away if I don't set pool_recycle=True. However, I'm told pool_recycle is necessary to prevent MySQL has gone away errors. My guess then is that SQLAclehmy preparing a statement, it works right the first time, and thereafter there is an issue with it. So you're right, it is deterministic. Ideas on how to address it though? On Dec 16, 5:02 pm, Michael Bayer mike...@zzzcomputing.com wrote: ive never seen that error before. threading issues are usually not very deterministic either (happens on the 2nd request guaranteed is deterministic). On Dec 16, 2008, at 1:09 PM, Ken wrote: I'm using CherryPy with SQLAlchemy. I've tried this with both SQLAlchemy's scoped_session thread-local sessions, as well as (also local-to-thread) sessions I create in my web code manually. 2008-12-16 11:51:14,524 DEBUG cherrypy.error.140068141119376: [16/Dec/ 2008:11:51:14] HTTP Traceback (most recent call last): File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 1027, in first ret = list(self[0:1]) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 952, in __getitem__ return list(res) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 1088, in __iter__ return self._execute_and_instances(context) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/query.py, line 1091, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /usr/lib/python2.4/site-packages/sqlalchemy/orm/session.py, line 749, in execute return self.__connection(engine, close_with_result=True).execute( File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 806, in execute return Connection.executors[c](self, object, multiparams, params) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 856, in execute_clauseelement return self.__execute_context(context) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 878, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 927, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File /usr/lib/python2.4/site-packages/sqlalchemy/engine/base.py, line 909, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) InterfaceError: (InterfaceError) (0, '') u'SELECT login.username AS login_username, login.password AS login_password, login.`loginId` AS `login_loginId`, login.salt AS login_salt, login.email AS login_email, login.status AS login_status, login.message AS login_message, login.`accountId` AS `login_accountId` \nFROM login \nWHERE login.`loginId` = %s \n LIMIT 0, 1' [36L] The first time this query runs, it works fine. Thereafter I get this error message. Any ideas? --~--~-~--~~~---~--~~ 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: simulation/synonim of a relation?
for the record, if anyone wants doing such stuff: there is a many to one reference A.b to B. one day it becomes bitemporal, i.e. an association A.b to B via A2B table, which contains other columns that cannot be invented by default values (timestamps, disabled, etc). Hence, the association has to be explicit via assoc.object. Still, for the sake of querying across A.b to B, noone cares about those columns, and the code contains lots of that plain usage... (in my case there are hundreds of references in hundreds of classes that became bitemporal...) a) create the explicit assoc A2B, say b_exp b) create a descriptor that will handle __get__, __set__, __del__ etc of single values using the explicit association b_exp c) create an implicit assoc via same A2B table, view_only=True, say b_imp; it seems usable that the original backref (if any) is put here; d) add synonym() property of the implicit assoc, via the descriptor above so the result is: - print a.b #single B, via the descriptor, in my case latest non-disabled version - a.b = someb #works and appends a link to someb using explicit_assoc - ... - query(A).join('b') #gives all Bs, jumping across the assoc) - query(A).filter( A.b.contains(someb)) #works ok - query(A).join('b_exp') #gives all A2Bs for whatever querying - a.b_exp.append( ... ) for manual adding of A2B links thus simulating the single-reference behaviour. for complete simulation, it needs query(A).filter_by(b=someb) to work. Which needs a comparator inheriting PropLoader.Comparator, for which _eq_ does .contains() for many2many relations, and eventualy .has does .any. may look too magical and too automatic but fits the bill.. all this will go into dbcook.sf.net soon anyway, so check there. ciao svilen On Wednesday 17 December 2008 00:35:03 Michael Bayer wrote: On Dec 16, 2008, at 3:04 PM, a...@svilendobrev.com wrote: hi i have a relation say, foo. i want to have another descriptor, bar that does certain other things around that relation, but can be used just in same way as foo can in the aspects of expressions and query-joins. i.e. query(A).join( A.foo) and query(A).join( A.bar) to produce same thing; as well as query(A).filter( A.foo == x) and query(A).filter( A.bar == x). this is the use case for synonym(). i managed to get the latter by simulating the PropComparator interface, and i sort of managed to get the join working... but my relation is to an explicit association and i want to hide the explicitness. so i want query(A).join( A.bar) to behave same as query(A).join( A.bar).join( FooAssoc.otherside), or actualy as query(A).join( A.foo) as IF foo was implicit association via secondary table. ignoring the A.bar would be 'the same' as A.bar.otherside portion of what you said, since that doesn't make any sense, it seems you're asking for A.foo to mean the same thing as A.bar.otherside. this seems something like associationproxy plus a comparable_property(). have you tried that ? --~--~-~--~~~---~--~~ 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] how to maping single-table inheritance using declarative
below is ext.declarative.py's doc: class Person(Base): __tablename__ = 'people' id = Column(Integer, primary_key=True) discriminator = Column(String(50)) __mapper_args__ = {'polymorphic_on': discriminator} class Engineer(Person): __tablename__ = 'engineers' __mapper_args__ = {'polymorphic_identity': 'engineer'} id = Column(Integer, ForeignKey('people.id'), primary_key=True) primary_language = Column(String(50)) For single-table inheritance, the ``__tablename__`` and ``__table__`` class variables are optional on a class when the class inherits from another mapped class. according it i think it should be like this: class Engineer(Person): __mapper_args__ = {'polymorphic_identity': 'engineer'} primary_language = Column(String(50)) but : File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg \sqlalchemy\s ql\expression.py, line 2636, in description return self.name.encode('ascii', 'backslashreplace') AttributeError: 'NoneType' object has no attribute 'encode' what should i do? 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] Re: how to maping single-table inheritance using declarative
all Column objects have to be on the base class if you're using single table inheritance. declarative joins the Table and class definitions together, and the base class has to represent the Table completely. On Dec 17, 2008, at 10:36 PM, Jeffrey wrote: below is ext.declarative.py's doc: class Person(Base): __tablename__ = 'people' id = Column(Integer, primary_key=True) discriminator = Column(String(50)) __mapper_args__ = {'polymorphic_on': discriminator} class Engineer(Person): __tablename__ = 'engineers' __mapper_args__ = {'polymorphic_identity': 'engineer'} id = Column(Integer, ForeignKey('people.id'), primary_key=True) primary_language = Column(String(50)) For single-table inheritance, the ``__tablename__`` and ``__table__`` class variables are optional on a class when the class inherits from another mapped class. according it i think it should be like this: class Engineer(Person): __mapper_args__ = {'polymorphic_identity': 'engineer'} primary_language = Column(String(50)) but : File c:\python25\lib\site-packages\sqlalchemy-0.5.0rc1-py2.5.egg \sqlalchemy\s ql\expression.py, line 2636, in description return self.name.encode('ascii', 'backslashreplace') AttributeError: 'NoneType' object has no attribute 'encode' what should i do? 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 -~--~~~~--~~--~--~---