[sqlalchemy] insert to the database
Hi everybody!! I'm trying to do this: indg=IndicatorGroups() indg.name=group session.save(indg) ind=Indicators() ind.code=code ind.name=indicator ind.idindicatorgroup=indg session.save(ind) session.flush() but I have this error: Traceback (innermost last): Module ZPublisher.Publish, line 115, in publish Module ZPublisher.mapply, line 88, in mapply Module ZPublisher.Publish, line 41, in call_object Module Products.odr.lugabe_db.browser.csv_insert, line 26, in __call__ Module Products.odr.lugabe_db.insert, line 49, in insert Module sqlalchemy.orm.session, line 681, in flush Module sqlalchemy.orm.unitofwork, line 216, in flush Module sqlalchemy.orm.unitofwork, line 432, in execute Module sqlalchemy.orm.unitofwork, line 1051, in execute Module sqlalchemy.orm.unitofwork, line 1068, in execute_save_steps Module sqlalchemy.orm.unitofwork, line 1081, in execute_dependencies Module sqlalchemy.orm.unitofwork, line 1062, in execute_dependency Module sqlalchemy.orm.unitofwork, line 1017, in execute Module sqlalchemy.orm.dependency, line 282, in process_dependencies Module sqlalchemy.orm.dependency, line 317, in _synchronize Module sqlalchemy.orm.sync, line 91, in execute Module sqlalchemy.orm.sync, line 143, in execute Module sqlalchemy.orm.mapper, line 936, in set_attr_by_column Module sqlalchemy.orm.util, line 101, in __getitem__ KeyError: Column(u'idindicatorgroup', MSInteger(length=11), ForeignKey(u' indicatorgroups.idindicatorgroup'), nullable=False, default=PassiveDefault(u'0')) Does anyone knows what am I doing wrong?? Thanks a lot -- Lur Ibargutxi [EMAIL PROTECTED] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row
Michael, Michael Bayer wrote: On Nov 6, 2007, at 12:20 PM, Werner F. Bruhin wrote: I insert a raw into a table and then retrieve again but columns which are filled by a db trigger don't return the updated values. The following is a code snippet and I wonder what I am missing. engine = sa.create_engine(url, encoding='utf8', echo=False) Session = sao.sessionmaker(autoflush=True, transactional=True) Session.configure(bind=engine) session = Session() botlot = db.Bottaglot() session.save(botlot) session.commit() print 'org' print botlot.bottaglotid print botlot.updated botlot2 = session.query(db.Bottaglot).get(botlot.bottaglotid) print 'reloaded' print botlot2.bottaglotid print botlot2.updated Both columns updated will show None instead of at least for botlot2 it should show the current date which was inserted into that column by a db trigger. set a PassiveDefault on the triggered column. that will indicate to the mapper that it should post-fetch the value after an insert. note that if the trigger is on a primary key column, it wont work since we need primary key values in order to post-fetch. PassiveDefault is great to know. However I still have a problem with the following. In a program I do something like this: botlot3 = session.query(db.Bottaglot).get(39) Then some other user and/or application changes data (I faked this by setting a debugger break point and used the db admin tool to change some data and committed it) in the database and commits, then when I do this: botlot4 = session.query(db.Bottaglot).get(39) I expected to get the data from the database, however SA gets it from the session (I set echo=True) and I don't see a select being done between the first statement and the second. Searching in the documentation I see that it is documented that get is NOT querying the database if the key is present, however I haven't found how I can do a primary key query so that SA goes to the database. I'll keep searching in the doc, but would still appreciate any hints. Thanks in advance 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
This is strange. I had problems reproducing the bug for a long time and was now quite happy that I succeeded. Yes, I'm using PostgreSQL 8.1.5 and psycopg2. And I still see the behavior that I reported. That should narrow the problem down to something in my local setup... I'm sorry for wasting your time. Klaus On 7 Nov., 01:00, Michael Bayer [EMAIL PROTECTED] wrote: I cant reproduce this one. I see you have named bind params so I tried with postgres. it also works with sqlite. works with release 0.4.0 as well as the trunk.output is (with echoing): SELECT testView.id AS testView_id, testView.data AS testView_data FROM (SELECT test.id AS id, test.data AS data FROM test WHERE test.id IN (%(test_id)s, %(test_id_1)s, %(test_id_2)s, % (test_id_3)s, %(test_id_4)s, %(test_id_5)s, %(test_id_6)s, % (test_id_7)s, %(test_id_8)s, %(test_id_9)s, %(test_id_10)s)) AS testView WHERE testView.id = %(param_1)s ORDER BY testView.id 2007-11-06 18:57:05,087 INFO sqlalchemy.engine.base.Engine.0x..b0 {'test_id_3': 5, 'test_id_10': 45, 'param_1': 2, 'test_id_8': 30, 'test_id_9': 44, 'test_id_1': 3, 'test_id_2': 4, 'test_id': 2, 'test_id_4': 8, 'test_id_5': 10, 'test_id_6': 11, 'test_id_7': 13} __main__.Test object at 0xc4e610 2007-11-06 18:57:05,090 INFO sqlalchemy.engine.base.Engine.0x..b0 SELECT referer.id AS referer_id, referer.fk AS referer_fk FROM referer WHERE referer.id = %(param_1)s ORDER BY referer.id 2007-11-06 18:57:05,090 INFO sqlalchemy.engine.base.Engine.0x..b0 {'param_1': 1} 2 __main__.Test object at 0xc4e610 try coming up with a reproducing test case and reopen ticket #853 if you can come up with it. On Nov 6, 2007, at 10:33 AM, klaus wrote: Hi all, the following mapped select results in the wrong query. The problem seems to be related to the number of values in a list passed to in_ and maybe to holes in the list of chosen values. from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData(...) metadata.bind.echo=True table = Table(test, metadata, Column(id, Integer, primary_key=True), Column(data, String)) table.create() table.insert().execute([{data: 1}, {data: 2}, {data: 3}, {data: 4}, {data: 5}, {data: 6}, {data: 7}, {data: 8}, {data: 9}, {data: 10}, {data: 11}, {data: 12}, {data: 13}, {data: 14}, {data: 15}, {data: 30}, {data: 44}, {data: 55}]) test = table.select(table.c.id.in_([2, 3, 4, 5, 8, 10, 11, 13, 30, 44, 45])).alias(testView) class Test(object): pass mapper(Test, test) referer = Table(referer, metadata, Column(id, Integer, primary_key=True), Column(fk, Integer, ForeignKey(test.id))) referer.create() referer.insert().execute([{fk: 2}]) class Referer(object): pass mapper(Referer, referer, properties={ref: relation(Test)}) session = create_session() t = session.query(Test).get(2) print t r = session.query(Referer).get(1) print r.fk, r.ref It prints None 2 None and the SQL statement for the first get should have param_1=1 instead of None. 2007-11-06 16:26:30,394 INFO sqlalchemy.engine.base.Engine.0x..34 SELECT testView.id AS testView_id, testView.data AS testView_data FROM (SELECT test.id AS id, test.data AS data FROM test WHERE test.id IN (%(test_id)s, %(test_id_1)s, %(test_id_2)s, % (test_id_3)s, %(test_id_4)s, %(test_id_5)s, %(test_id_6)s, % (test_id_7)s, %(test_id_8)s, %(test_id_9)s, %(test_id_10)s)) AS testView WHERE testView.id = %(param_1)s ORDER BY testView.id 2007-11-06 16:26:30,394 INFO sqlalchemy.engine.base.Engine.0x..34 {'test_id_3': 5, 'test_id_10': 45, 'param_1': None, 'test_id_8': 30, 'test_id_9': 44, 'test_id_1': 3, 'test_id_2': 4, 'test_id': 2, 'test_id_4': 8, 'test_id_5': 10, 'test_id_6': 11, 'test_id_7': 13} Best regards Klaus --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Code Organisation
u can use the timephase-separation, i.e. declare vs runtime; i.e. use global scope in for B in A, but use runtime scope for A in B. modB.py: import A ... modA.py: def somefunc_or_method(): import B ... another solution is to have sort-of forward-text-declarations that at certain time are all translated into real things by someone else. But this has more overhead and is more usable on more larger-scale dependencies; i.e. all business-obj klasses Hi there, We have a pretty large project by now and we run into import loops. So I decided to restructure the code, and I hoped some people with more experience can comment on this. The basic problem is this: We have the database object code, mappers and tables neatly organized in one module (db). The controller code imports this module to get access to these objects. All fine. But we have another object called Connection which is a singleton class that actually manages the connection to our database. It is basically a wrapper for create_engine and contextual_session. But next to that it keeps info about the current login state like the employee, location etc. The mapped database objects need this info on their turn to add the current user to a new object etc. So the Connection object depends on the Mapped Database Objects, but the Mapped Database Object depend on the Connection object too. Anyone got a good tip to solve this? Or designed something similar? Thanks, Koen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Code Organisation
Hi there, We have a pretty large project by now and we run into import loops. So I decided to restructure the code, and I hoped some people with more experience can comment on this. The basic problem is this: We have the database object code, mappers and tables neatly organized in one module (db). The controller code imports this module to get access to these objects. All fine. But we have another object called Connection which is a singleton class that actually manages the connection to our database. It is basically a wrapper for create_engine and contextual_session. But next to that it keeps info about the current login state like the employee, location etc. The mapped database objects need this info on their turn to add the current user to a new object etc. So the Connection object depends on the Mapped Database Objects, but the Mapped Database Object depend on the Connection object too. Anyone got a good tip to solve this? Or designed something similar? Thanks, Koen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
On Nov 7, 2007, at 1:02 PM, svilen wrote: On Wednesday 07 November 2007 19:33:22 Michael Bayer wrote: ohyoure *extending* abstractclauseprocessor ??? well yes, thats going to change things quite a bit. I think you should study ACP in its current form; what its doing now is faithfully calling convert_element() for *every* element in the expression, and also is not copying any elements before calling convert_element() - convert_element() always gets components from the original clause only. if convert_element() returns non-None, the resulting element is assembled into the output, and traversal *stops* for the remainder of that element. this is different behavior than it was before. the reason it stops for a replaced element is because its assumed that the replacement value is not part of the expression which is being copied, and therefore should not be copied or processed itself. if its that second part of the behavior thats breaking it for you, we can add an option to switch it off (comment out line 156, stop_on.add(newelem) to produce this). this did not change things, the column is still not traversed. maybe something else also has to be changed. the traversal will hit everything except the external collections of columns on objects since this is usually not needed. to change this, set the column_collections attribute to True on the ACP object (or in your class, or anywhere). but the old version had the same option turned on so not sure how that would change things. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problems with session transaction in SQLAlchemy 0.4
I gleaned from your stack trace that youre using strategy=threadlocal (full test cases are much more helpful). anyway, its a bug, fixed in r3748. On Nov 7, 2007, at 9:22 AM, Manlio Perillo wrote: Hi. I'm trying to add support to SQLALchemy 0.4 in nadbapi: http://hg.mperillo.ath.cx/nweb/nadbapi/ However there is (among other things) a problem I do not understand. Here is the code: def _sessionTransaction(self, callable_, *args, **kwargs): from sqlalchemy import orm conn = self.contextual_connect() sess = orm.create_session(bind=conn) # This cannot fail try: trans = sess.create_transaction() try: ret = callable_(conn, sess, *args, **kwargs) trans.commit() return ret except: trans.rollback() raise finally: sess.close() # This cannot fail conn.close() And here is the problem: File /home/manlio/projects/hg/nweb/nadbapi/nadbapi/engine.py, line 305, in _sessionTransaction trans.commit() File /home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/ orm/session.py, line 222, in commit self.close() File /home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/ orm/session.py, line 245, in close t[1].close() File /home/manlio/projects/svn-external/sqlalchemy/trunk/lib/sqlalchemy/ engine/base.py, line 944, in close if not self._parent._is_active: AttributeError: 'TLTransaction' object has no attribute '_parent' I'm using SQLAlchemy from trunk. I see from the sources that now the Session object has a transactional argument. How can I use it in my code? Is it available in SQLAlchemy 0.3? Thanks Manlio Perillo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
On Wednesday 07 November 2007 19:33:22 Michael Bayer wrote: ohyoure *extending* abstractclauseprocessor ??? well yes, thats going to change things quite a bit. I think you should study ACP in its current form; what its doing now is faithfully calling convert_element() for *every* element in the expression, and also is not copying any elements before calling convert_element() - convert_element() always gets components from the original clause only. if convert_element() returns non-None, the resulting element is assembled into the output, and traversal *stops* for the remainder of that element. this is different behavior than it was before. the reason it stops for a replaced element is because its assumed that the replacement value is not part of the expression which is being copied, and therefore should not be copied or processed itself. if its that second part of the behavior thats breaking it for you, we can add an option to switch it off (comment out line 156, stop_on.add(newelem) to produce this). this did not change things, the column is still not traversed. maybe something else also has to be changed. i want a copy of original expression, where certain things are replaced by my things, and no need to go inside them - so this stop_on as u describe is okay... unless: what u mean remainder? that the returned element is not further traversed (thats ok), or the parent of that element is not traversed anymore (not ok)? this new version of ACP can locate things besides just plain Table, Alias and Column objects; it can locate things like Joins embedded in a clause which match the target selectable. On Nov 7, 2007, at 10:45 AM, svilen wrote: On Wednesday 07 November 2007 16:57:08 Michael Bayer wrote: On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc /aggregator/ (no it does not need dbcook) $ cd dbcook/misc/aggregator/tests $ PYTHONPATH=$PYTHONPATH:../.. python convertertest.py ... FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] i did print the interesting elements in my Converter.convert_element(), and the result is that a) order is slightly different - which i dont care b) 1 item is not traversed in r3727 e.g. r3626: Column tags.tabl Column tags.oid Column movies.id Column tags.tabl Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state r3627: Column tags.tabl Column tags.oid Column movies.id Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state the 2nd tags.tabl is missing, hence the assertFails ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)
Mike, Michael Bayer wrote: On Nov 7, 2007, at 12:32 PM, Werner F. Bruhin wrote: Can you point out what I did wrong, please. id have to see a larger example, seems like something is marking the column as modified when it should not be. I put a little test case together, which does very little but I get the exception. engine = sa.create_engine(url, encoding='utf8', echo=True) Session = sao.sessionmaker(autoflush=True, transactional=True) Session.configure(bind=engine) session = Session() dbItemConsumption = db.Consumption() dbItemConsumption.fk_cbbottleid = 33 session.save(dbItemConsumption) session.flush session.commit() Here the echo output and the exception is: 2007-11-07 18:52:53,438 INFO sqlalchemy.engine.base.Engine.0x..d0 BEGIN 2007-11-07 18:52:53,460 INFO sqlalchemy.engine.base.Engine.0x..d0 SELECT gen_id(gen_consumption_consumptionid, 1) FROM rdb$database 2007-11-07 18:52:53,460 INFO sqlalchemy.engine.base.Engine.0x..d0 None 2007-11-07 18:52:53,463 INFO sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO consumption (consumptionid, quantity, unitprice, consumedvalue, remarks, consumed, maturityfirst, maturitybest, maturitypast, created, updated, fk_reasonid, fk_cbbottleid, transferid, fk_cellarid, fk_ratingid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 2007-11-07 18:52:53,464 INFO sqlalchemy.engine.base.Engine.0x..d0 [58, None, None, None, None, None, None, None, None, None, None, None, 33, None, None, None] 2007-11-07 18:52:53,466 INFO sqlalchemy.engine.base.Engine.0x..d0 ROLLBACK Traceback (most recent call last): File saTest.py, line 27, in module session.commit() File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\session.py, line 483, in commit self.transaction = self.transaction.commit() File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\session.py, line 210, in commit self.session.flush() File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\session.py, line 681, in flush self.uow.flush(self, objects) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 216, in flush flush_context.execute() File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 432, in execute UOWExecutor().execute(self, head) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1051, in execute self.execute_save_steps(trans, task) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1070, in execute_save_steps self.execute_childtasks(trans, task, False) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1088, in execute_childtasks self.execute(trans, child, isdelete) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1051, in execute self.execute_save_steps(trans, task) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1070, in execute_save_steps self.execute_childtasks(trans, task, False) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1088, in execute_childtasks self.execute(trans, child, isdelete) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1051, in execute self.execute_save_steps(trans, task) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1070, in execute_save_steps self.execute_childtasks(trans, task, False) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1088, in execute_childtasks self.execute(trans, child, isdelete) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1051, in execute self.execute_save_steps(trans, task) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1070, in execute_save_steps self.execute_childtasks(trans, task, False) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1088, in execute_childtasks self.execute(trans, child, isdelete) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1051, in execute self.execute_save_steps(trans, task) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1070, in execute_save_steps self.execute_childtasks(trans, task, False) File c:\python25\lib\site-packages\SQLAlchemy-0.4.0-py2.5.egg\sqlalchemy\orm\unitofwork.py, line 1088, in execute_childtasks self.execute(trans, child, isdelete) File
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
On Nov 7, 2007, at 11:05 AM, jason kirtland wrote: Michael Bayer wrote: On Nov 7, 2007, at 6:01 AM, klaus wrote: This is strange. I had problems reproducing the bug for a long time and was now quite happy that I succeeded. Yes, I'm using PostgreSQL 8.1.5 and psycopg2. And I still see the behavior that I reported. That should narrow the problem down to something in my local setup... I'm sorry for wasting your time. are you on release 0.4.0 ? attached is the full script i was using, i moved the tables around so that I could use drop_all()/create_all(). Additionally, I just remembered that there are often dictionary- ordering related issues which will occur on linux but not OSX (which is my normal platform). So I'll try on linux later today...or if anyone else wants to run the attached script with PG, SA0.4 and a linux machine that would be helpful. I can reproduce this on Linux- 0.4.0 and trunk, any Python version. what happens if you change the create_engine to: engine = create_engine('sqlite://', echo=True, paramstyle=named) that doesnt reproduce for me on linux. i find it hard to believe its a bug within the PG dialect itselfbuilding PG on my linux box now. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Confusion about using Session() globally
Dear all, I'm quite confused to use Session like a global statement through different parts of a web program. The directory structure is the following: WebML/ +- webml.py (main program) +- globals.py +- managers/ +- __init__.py +- users_schema.py +- emails_schema.py +- ml_schema.py So, in the main program webml.py I have: from sqlalchemy import create_engine, MetaData, Table from sqlalchemy.orm import mapper, scoped_session, sessionmaker from managers import users_schema, ml_schema, emails_schema class HttpProtected: def __init__(self): engine=create_engine('postgres://[EMAIL PROTECTED]/webml',strategy='threadlocal') Session = scoped_session(sessionmaker(autoflush=True, transactional=True)) session=Session(bind=engine) self.m=ml_schema.MLManager() self.u=users_schema.UsersManager() self.e=emails_schema.EmailsManager() In managers/users_schema.py I have: from sqlalchemy import Table, MetaData from sqlalchemy.orm import relation, class_mapper, mapper import ml_schema class Users(object): pass class UsersManager(object): def __init__(self): metadata=MetaData('postgres://[EMAIL PROTECTED]/webml') users = Table('users', metadata, autoload=True) ml_users=Table('ml_users', metadata, autoload=True) usersmapper = mapper(Users, users, properties = { 'ml' : relation(ml_schema.ML, secondary = ml_users, backref='users') }) def saveNewUser(self, kw): session=Session() It's clear that saveNewUser doesn't works, because doesn't find Session(). I tried to put the Session initialization everywhere (except where it has to be :-)) without success. Where I have to put Session initialization to use it globally? Thanks in advance -- --- (o_ (o_//\ Coltivate Linux che tanto Windows si pianta da solo. (/)_ V_/_ +--+ | ENRICO MORELLI | email: [EMAIL PROTECTED] | | * * * *| phone: +39 055 4574269 | | University of Florence| fax : +39 055 4574253 | | CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY| +--+ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
By the way, on a larger database, drop_all() runs into an endless loop. However, I have no small script to reproduce this yet. Klaus On 7 Nov., 16:39, klaus [EMAIL PROTECTED] wrote: That's very kind. I'm on the trunk (0.4.1dev_r3747) and on linux. And I think your script (with a changed connection string, nothing else) fails on my machine. Here's the complete output: 2007-11-07 16:33:14,848 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:14,848 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'referer'} 2007-11-07 16:33:14,940 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:14,940 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'test'} 2007-11-07 16:33:14,941 INFO sqlalchemy.engine.base.Engine.0x..b4 DROP TABLE referer 2007-11-07 16:33:14,941 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,304 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,397 INFO sqlalchemy.engine.base.Engine.0x..b4 DROP TABLE test 2007-11-07 16:33:15,397 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,430 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,439 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:15,439 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'test'} 2007-11-07 16:33:15,440 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:15,440 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'referer'} 2007-11-07 16:33:15,441 INFO sqlalchemy.engine.base.Engine.0x..b4 CREATE TABLE test ( id SERIAL NOT NULL, data TEXT, PRIMARY KEY (id) ) 2007-11-07 16:33:15,442 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,893 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,894 INFO sqlalchemy.engine.base.Engine.0x..b4 CREATE TABLE referer ( id SERIAL NOT NULL, fk INTEGER, PRIMARY KEY (id), FOREIGN KEY(fk) REFERENCES test (id) ) 2007-11-07 16:33:15,894 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,958 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,963 INFO sqlalchemy.engine.base.Engine.0x..b4 INSERT INTO test (data) VALUES (%(data)s) 2007-11-07 16:33:15,963 INFO sqlalchemy.engine.base.Engine.0x..b4 [{'data': 1}, {'data': 2}, {'data': 3}, {'data': 4}, {'data': 5}, {'data': 6}, {'data': 7}, {'data': 8}, {'data': 9}, {'data': 10}, {'data': 11}, {'data': 12}, {'data': 13}, {'data': 14}, {'data': 15}, {'data': 30}, {'data': 44}, {'data': 55}] 2007-11-07 16:33:15,968 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,969 INFO sqlalchemy.engine.base.Engine.0x..b4 select nextval('referer_id_seq') 2007-11-07 16:33:15,969 INFO sqlalchemy.engine.base.Engine.0x..b4 None 2007-11-07 16:33:15,970 INFO sqlalchemy.engine.base.Engine.0x..b4 INSERT INTO referer (id, fk) VALUES (%(id)s, %(fk)s) 2007-11-07 16:33:15,970 INFO sqlalchemy.engine.base.Engine.0x..b4 {'fk': 2, 'id': 1L} 2007-11-07 16:33:15,971 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,976 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT testView.id AS testView_id, testView.data AS testView_data FROM (SELECT test.id AS id, test.data AS data FROM test WHERE test.id IN (%(test_id)s, %(test_id_1)s, %(test_id_2)s, % (test_id_3)s, %(test_id_4)s, %(test_id_5)s, %(test_id_6)s, % (test_id_7)s, %(test_id_8)s, %(test_id_9)s, %(test_id_10)s)) AS testView WHERE testView.id = %(param_1)s ORDER BY testView.id 2007-11-07 16:33:15,976 INFO sqlalchemy.engine.base.Engine.0x..b4 {'test_id_3': 5, 'test_id_10': 45, 'param_1': None, 'test_id_8': 30, 'test_id_9': 44, 'test_id_1': 3, 'test_id_2': 4, 'test_id': 2, 'test_id_4': 8, 'test_id_5': 10, 'test_id_6': 11, 'test_id_7': 13} None 2007-11-07 16:33:15,979 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT referer.id AS referer_id, referer.fk AS referer_fk FROM referer WHERE referer.id = %(param_1)s ORDER BY referer.id 2007-11-07 16:33:15,979 INFO sqlalchemy.engine.base.Engine.0x..b4 {'param_1': 1} 22007-11-07 16:33:15,982 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT testView.id AS testView_id, testView.data AS testView_data FROM (SELECT test.id AS id, test.data AS data FROM test WHERE test.id IN (%(test_id)s, %(test_id_1)s, %(test_id_2)s, % (test_id_3)s, %(test_id_4)s, %(test_id_5)s, %(test_id_6)s, % (test_id_7)s, %(test_id_8)s,
[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)
On Nov 7, 2007, at 10:23 AM, Werner F. Bruhin wrote: How can I define a column in the table as read-only? I have some computed by columns, e.g.: consumedvalue computed by (quantity*unitprice) These columns can not be updated, otherwise I get the following exception: ProgrammingError: (ProgrammingError) (-151, 'isc_dsql_prepare: \n attempted update of read-only column') read-only behavior is a class-level thing, so something like this: class MyClass(object): def mycol(self): return self._mycol mycol = property(mycol) mapper(MyClass, mytable, properties={ '_mycol':mytable.c.mycol, 'mycol':synonym('_mycol') }) Theres a ticket in trac which will make the above configuration slightly less verbose 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
That's very kind. I'm on the trunk (0.4.1dev_r3747) and on linux. And I think your script (with a changed connection string, nothing else) fails on my machine. Here's the complete output: 2007-11-07 16:33:14,848 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:14,848 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'referer'} 2007-11-07 16:33:14,940 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:14,940 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'test'} 2007-11-07 16:33:14,941 INFO sqlalchemy.engine.base.Engine.0x..b4 DROP TABLE referer 2007-11-07 16:33:14,941 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,304 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,397 INFO sqlalchemy.engine.base.Engine.0x..b4 DROP TABLE test 2007-11-07 16:33:15,397 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,430 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,439 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:15,439 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'test'} 2007-11-07 16:33:15,440 INFO sqlalchemy.engine.base.Engine.0x..b4 select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and lower(relname)=%(name)s 2007-11-07 16:33:15,440 INFO sqlalchemy.engine.base.Engine.0x..b4 {'name': 'referer'} 2007-11-07 16:33:15,441 INFO sqlalchemy.engine.base.Engine.0x..b4 CREATE TABLE test ( id SERIAL NOT NULL, data TEXT, PRIMARY KEY (id) ) 2007-11-07 16:33:15,442 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,893 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,894 INFO sqlalchemy.engine.base.Engine.0x..b4 CREATE TABLE referer ( id SERIAL NOT NULL, fk INTEGER, PRIMARY KEY (id), FOREIGN KEY(fk) REFERENCES test (id) ) 2007-11-07 16:33:15,894 INFO sqlalchemy.engine.base.Engine.0x..b4 {} 2007-11-07 16:33:15,958 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,963 INFO sqlalchemy.engine.base.Engine.0x..b4 INSERT INTO test (data) VALUES (%(data)s) 2007-11-07 16:33:15,963 INFO sqlalchemy.engine.base.Engine.0x..b4 [{'data': 1}, {'data': 2}, {'data': 3}, {'data': 4}, {'data': 5}, {'data': 6}, {'data': 7}, {'data': 8}, {'data': 9}, {'data': 10}, {'data': 11}, {'data': 12}, {'data': 13}, {'data': 14}, {'data': 15}, {'data': 30}, {'data': 44}, {'data': 55}] 2007-11-07 16:33:15,968 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,969 INFO sqlalchemy.engine.base.Engine.0x..b4 select nextval('referer_id_seq') 2007-11-07 16:33:15,969 INFO sqlalchemy.engine.base.Engine.0x..b4 None 2007-11-07 16:33:15,970 INFO sqlalchemy.engine.base.Engine.0x..b4 INSERT INTO referer (id, fk) VALUES (%(id)s, %(fk)s) 2007-11-07 16:33:15,970 INFO sqlalchemy.engine.base.Engine.0x..b4 {'fk': 2, 'id': 1L} 2007-11-07 16:33:15,971 INFO sqlalchemy.engine.base.Engine.0x..b4 COMMIT 2007-11-07 16:33:15,976 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT testView.id AS testView_id, testView.data AS testView_data FROM (SELECT test.id AS id, test.data AS data FROM test WHERE test.id IN (%(test_id)s, %(test_id_1)s, %(test_id_2)s, % (test_id_3)s, %(test_id_4)s, %(test_id_5)s, %(test_id_6)s, % (test_id_7)s, %(test_id_8)s, %(test_id_9)s, %(test_id_10)s)) AS testView WHERE testView.id = %(param_1)s ORDER BY testView.id 2007-11-07 16:33:15,976 INFO sqlalchemy.engine.base.Engine.0x..b4 {'test_id_3': 5, 'test_id_10': 45, 'param_1': None, 'test_id_8': 30, 'test_id_9': 44, 'test_id_1': 3, 'test_id_2': 4, 'test_id': 2, 'test_id_4': 8, 'test_id_5': 10, 'test_id_6': 11, 'test_id_7': 13} None 2007-11-07 16:33:15,979 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT referer.id AS referer_id, referer.fk AS referer_fk FROM referer WHERE referer.id = %(param_1)s ORDER BY referer.id 2007-11-07 16:33:15,979 INFO sqlalchemy.engine.base.Engine.0x..b4 {'param_1': 1} 22007-11-07 16:33:15,982 INFO sqlalchemy.engine.base.Engine.0x..b4 SELECT testView.id AS testView_id, testView.data AS testView_data FROM (SELECT test.id AS id, test.data AS data FROM test WHERE test.id IN (%(test_id)s, %(test_id_1)s, %(test_id_2)s, % (test_id_3)s, %(test_id_4)s, %(test_id_5)s, %(test_id_6)s, % (test_id_7)s, %(test_id_8)s, %(test_id_9)s, %(test_id_10)s)) AS testView WHERE testView.id = %(param_1)s ORDER BY testView.id 2007-11-07 16:33:15,982 INFO sqlalchemy.engine.base.Engine.0x..b4 {'test_id_3': 5, 'test_id_10': 45, 'param_1': None, 'test_id_8': 30, 'test_id_9': 44, 'test_id_1': 3, 'test_id_2': 4, 'test_id':
[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row - solved
Werner F. Bruhin wrote: Michael, Michael Bayer wrote: On Nov 6, 2007, at 12:20 PM, Werner F. Bruhin wrote: I insert a raw into a table and then retrieve again but columns which are filled by a db trigger don't return the updated values. The following is a code snippet and I wonder what I am missing. engine = sa.create_engine(url, encoding='utf8', echo=False) Session = sao.sessionmaker(autoflush=True, transactional=True) Session.configure(bind=engine) session = Session() botlot = db.Bottaglot() session.save(botlot) session.commit() print 'org' print botlot.bottaglotid print botlot.updated botlot2 = session.query(db.Bottaglot).get(botlot.bottaglotid) print 'reloaded' print botlot2.bottaglotid print botlot2.updated Both columns updated will show None instead of at least for botlot2 it should show the current date which was inserted into that column by a db trigger. set a PassiveDefault on the triggered column. that will indicate to the mapper that it should post-fetch the value after an insert. note that if the trigger is on a primary key column, it wont work since we need primary key values in order to post-fetch. PassiveDefault is great to know. However I still have a problem with the following. In a program I do something like this: botlot3 = session.query(db.Bottaglot).get(39) Then some other user and/or application changes data (I faked this by setting a debugger break point and used the db admin tool to change some data and committed it) in the database and commits, then when I do this: botlot4 = session.query(db.Bottaglot).get(39) Instead of using .get( I ended up using refresh, i.e. something like this: session.refresh(botlot3) Which re-queried the database and gave the values of the columns which were completed by the db trigger. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select entire column
Is this what you want? select([my_table.c.my_column], distinct=True) Barry - Original Message From: JamesT [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wednesday, November 7, 2007 2:05:13 AM Subject: [sqlalchemy] Select entire column I am looking to filter specific columns in a table, but I cannot find how to do this. I can filter by setting these columns to a value, but not just grabbing the entire column. In SQL, I want to do this: SELECT artist FROM artist_table, where the only column kept is artist. The reason I want to do this is so that I can run a distinct() on the Query object returned to get the distinct artists and also distinct genres. Here is a sample of what I am doing now without filtering to get the data I need: tuples = Session.query(Albums).add_entity(Songs).join('songs').all() __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problems with session transaction in SQLAlchemy 0.4
Michael Bayer ha scritto: I gleaned from your stack trace that youre using strategy=threadlocal (full test cases are much more helpful). anyway, its a bug, fixed in r3748. Thanks, now finally nadbapi works with SQLAlchemy 0.4. Sorry for not having provided full test cases, I was rather sure it was a problem in my code. One last thing. With SQLAlchemy 0.3.x, my Engine class can be used to execute implicit queries. Now with 0.4.x this does not works, since the engine should implement additionals methods: statement_compiler and _execute_clauseelement. What's the reason of this change? Regards Manlio Perillo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
On Nov 7, 2007, at 1:09 PM, jason kirtland wrote: Michael Bayer wrote: what happens if you change the create_engine to: engine = create_engine('sqlite://', echo=True, paramstyle=named) that doesnt reproduce for me on linux. i find it hard to believe its a bug within the PG dialect itselfbuilding PG on my linux box now. No change here. Tried other dialects too... nope, cant reproduce on linux here either with PG. param_1 is 2 then 1. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: insert to the database
On Nov 7, 2007, at 5:20 AM, lur ibargutxi wrote: Does anyone knows what am I doing wrong?? nopewould need to see an entire reproducing test case for that one. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3695 causes strange error
On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)
On Nov 7, 2007, at 12:58 PM, Werner F. Bruhin wrote: Mike, Michael Bayer wrote: On Nov 7, 2007, at 12:32 PM, Werner F. Bruhin wrote: Can you point out what I did wrong, please. id have to see a larger example, seems like something is marking the column as modified when it should not be. I put a little test case together, which does very little but I get the exception. oh sorry, I misread the source code in SA earlier...for an INSERT, we are going to insert None for all columns that are blank but dont have a default. so your two options here are to put another PassiveDefault on the column: Column(u'consumedvalue', Numeric(precision=18,length=2,asdecimal=True), PassiveDefault()), or you can leave the column out of the mapping entirely (but then reading it requires an extra query): mapper(Consumption, consumption_table, exclude_properties=['consumedvalue']) the consumedvalue column will post-fetch after an 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
Michael Bayer wrote: On Nov 7, 2007, at 6:01 AM, klaus wrote: This is strange. I had problems reproducing the bug for a long time and was now quite happy that I succeeded. Yes, I'm using PostgreSQL 8.1.5 and psycopg2. And I still see the behavior that I reported. That should narrow the problem down to something in my local setup... I'm sorry for wasting your time. are you on release 0.4.0 ? attached is the full script i was using, i moved the tables around so that I could use drop_all()/create_all(). Additionally, I just remembered that there are often dictionary- ordering related issues which will occur on linux but not OSX (which is my normal platform). So I'll try on linux later today...or if anyone else wants to run the attached script with PG, SA0.4 and a linux machine that would be helpful. I can reproduce this on Linux- 0.4.0 and trunk, any Python version. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
On Nov 7, 2007, at 6:01 AM, klaus wrote: This is strange. I had problems reproducing the bug for a long time and was now quite happy that I succeeded. Yes, I'm using PostgreSQL 8.1.5 and psycopg2. And I still see the behavior that I reported. That should narrow the problem down to something in my local setup... I'm sorry for wasting your time. are you on release 0.4.0 ? attached is the full script i was using, i moved the tables around so that I could use drop_all()/create_all(). Additionally, I just remembered that there are often dictionary- ordering related issues which will occur on linux but not OSX (which is my normal platform). So I'll try on linux later today...or if anyone else wants to run the attached script with PG, SA0.4 and a linux machine that would be helpful. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('postgres://scott:[EMAIL PROTECTED]/test', echo=True) metadata = MetaData(engine) table = Table(test, metadata, Column(id, Integer, primary_key=True), Column(data, String)) referer = Table(referer, metadata, Column(id, Integer, primary_key=True), Column(fk, Integer, ForeignKey(test.id))) metadata.drop_all() metadata.create_all() table.insert().execute([{data: 1}, {data: 2}, {data: 3}, {data: 4}, {data: 5}, {data: 6}, {data: 7}, {data: 8}, {data: 9}, {data: 10}, {data: 11}, {data: 12}, {data: 13}, {data: 14}, {data: 15}, {data: 30}, {data: 44}, {data: 55}]) test = table.select(table.c.id.in_([2, 3, 4, 5, 8, 10, 11, 13, 30, 44, 45])).alias(testView) referer.insert().execute([{fk: 2}]) class Test(object): pass mapper(Test, test) class Referer(object): pass mapper(Referer, referer, properties={ref: relation(Test)}) session = create_session() t = session.query(Test).get(2) print t r = session.query(Referer).get(1) print r.fk, r.ref
[sqlalchemy] Re: problems with session transaction in SQLAlchemy 0.4
On Nov 7, 2007, at 3:06 PM, Manlio Perillo wrote: Michael Bayer ha scritto: I gleaned from your stack trace that youre using strategy=threadlocal (full test cases are much more helpful). anyway, its a bug, fixed in r3748. Thanks, now finally nadbapi works with SQLAlchemy 0.4. Sorry for not having provided full test cases, I was rather sure it was a problem in my code. One last thing. With SQLAlchemy 0.3.x, my Engine class can be used to execute implicit queries. Now with 0.4.x this does not works, since the engine should implement additionals methods: statement_compiler and _execute_clauseelement. What's the reason of this change? do you mean connectionless execution ? i.e. engine.execute(statement) ? implicit is like table.select().execute(). both of these are supported in 0.4 even though some internals have changed. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Wrong SQL statement for mapped select involving in_
Michael Bayer wrote: what happens if you change the create_engine to: engine = create_engine('sqlite://', echo=True, paramstyle=named) that doesnt reproduce for me on linux. i find it hard to believe its a bug within the PG dialect itselfbuilding PG on my linux box now. No change here. Tried other dialects too... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)
On Nov 7, 2007, at 12:32 PM, Werner F. Bruhin wrote: Can you point out what I did wrong, please. id have to see a larger example, seems like something is marking the column as modified when it should not be. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
ohyoure *extending* abstractclauseprocessor ??? well yes, thats going to change things quite a bit. I think you should study ACP in its current form; what its doing now is faithfully calling convert_element() for *every* element in the expression, and also is not copying any elements before calling convert_element() - convert_element() always gets components from the original clause only. if convert_element() returns non-None, the resulting element is assembled into the output, and traversal *stops* for the remainder of that element. this is different behavior than it was before. the reason it stops for a replaced element is because its assumed that the replacement value is not part of the expression which is being copied, and therefore should not be copied or processed itself. if its that second part of the behavior thats breaking it for you, we can add an option to switch it off (comment out line 156, stop_on.add(newelem) to produce this). this new version of ACP can locate things besides just plain Table, Alias and Column objects; it can locate things like Joins embedded in a clause which match the target selectable. On Nov 7, 2007, at 10:45 AM, svilen wrote: On Wednesday 07 November 2007 16:57:08 Michael Bayer wrote: On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/aggregator/ (no it does not need dbcook) $ cd dbcook/misc/aggregator/tests $ PYTHONPATH=$PYTHONPATH:../.. python convertertest.py ... FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] i did print the interesting elements in my Converter.convert_element(), and the result is that a) order is slightly different - which i dont care b) 1 item is not traversed in r3727 e.g. r3626: Column tags.tabl Column tags.oid Column movies.id Column tags.tabl Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state r3627: Column tags.tabl Column tags.oid Column movies.id Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state the 2nd tags.tabl is missing, hence the assertFails ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How to define a column as read-only (e.g. computed by column in Firebird)
Mike, Michael Bayer wrote: On Nov 7, 2007, at 10:23 AM, Werner F. Bruhin wrote: How can I define a column in the table as read-only? I have some computed by columns, e.g.: consumedvalue computed by (quantity*unitprice) These columns can not be updated, otherwise I get the following exception: ProgrammingError: (ProgrammingError) (-151, 'isc_dsql_prepare: \n attempted update of read-only column') read-only behavior is a class-level thing, so something like this: class MyClass(object): def mycol(self): return self._mycol mycol = property(mycol) mapper(MyClass, mytable, properties={ '_mycol':mytable.c.mycol, 'mycol':synonym('_mycol') }) Theres a ticket in trac which will make the above configuration slightly less verbose in a future release. I don't have many of these, so no big deal that it is verbose. However I must not do something wrong in transposing the above as I still get the error. I must be a bit dense on this, here is what I have done: consumption_table = sa.Table(u'consumption', metadata, sa.Column(u'consumptionid', sa.Integer(), sa.Sequence('gen_consumption_consumptionid'), primary_key=True, nullable=False), sa.Column(u'quantity', sa.Integer()), sa.Column(u'unitprice', sa.Numeric(precision=18,length=2,asdecimal=True)), sa.Column(u'consumedvalue', sa.Numeric(precision=18,length=2,asdecimal=True)), ) class Consumption(object): def consumedvalue(self): return self._consumedvalue consumedvalue = property(consumedvalue) pass consumption = sao.mapper(Consumption, consumption_table, properties={ ... 'cellar': sao.relation(Cellar, backref='consumption'), '_consumedvalue': consumption_table.c.consumedvalue, 'consumedvalue': sao.synonym('_consumedvalue'), }) Can you point out what I did wrong, please. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
On Nov 7, 2007, at 11:03 AM, svilen wrote: also, i put a class ClauseVisitor( sql_util.AbstractClauseProcessor): def convert_element( me, e): return None in the beginning of the tests.sql.generative, and after ignoreing this or that error, here is similar thing: == FAIL: test_correlated_select (__main__.ClauseTest) -- Traceback (most recent call last): File sql/generative.py, line 235, in test_correlated_select self.assert_compile(Vis().traverse(s, clone=True), SELECT * FROM table1 WHERE table1.col1 = table2.col1 AND table1.col2 = :table1_col2) File /home/az/src/ver/sqlalchemy-trunk/test/testlib/testing.py, line 262, in assert_compile self.assert_(cc == result, \n' + cc + '\n does not match \n' + result + ') AssertionError: 'SELECT * FROM table1 WHERE table1.col1 = table2.col1' does not match 'SELECT * FROM table1 WHERE table1.col1 = table2.col1 AND table1.col2 = :table1_col2' here whole subexpr is gone that would be...correct ? replacing convert_element() to return None means the given clause will be copied with no changes. the original clause doesnt have the extra subexpression. On Wednesday 07 November 2007 17:45:04 svilen wrote: On Wednesday 07 November 2007 16:57:08 Michael Bayer wrote: On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. i did print the interesting elements in my Converter.convert_element(), and the result is that a) order is slightly different - which i dont care b) 1 item is not traversed in r3727 e.g. r3626: Column tags.tabl Column tags.oid Column movies.id Column tags.tabl Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state r3627: Column tags.tabl Column tags.oid Column movies.id Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state the 2nd tags.tabl is missing, hence the assertFails ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
On Wednesday 07 November 2007 16:57:08 Michael Bayer wrote: On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/aggregator/ (no it does not need dbcook) $ cd dbcook/misc/aggregator/tests $ PYTHONPATH=$PYTHONPATH:../.. python convertertest.py ... FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] i did print the interesting elements in my Converter.convert_element(), and the result is that a) order is slightly different - which i dont care b) 1 item is not traversed in r3727 e.g. r3626: Column tags.tabl Column tags.oid Column movies.id Column tags.tabl Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state r3627: Column tags.tabl Column tags.oid Column movies.id Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state the 2nd tags.tabl is missing, hence the assertFails ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] How to define a column as read-only (e.g. computed by column in Firebird)
How can I define a column in the table as read-only? I have some computed by columns, e.g.: consumedvalue computed by (quantity*unitprice) These columns can not be updated, otherwise I get the following exception: ProgrammingError: (ProgrammingError) (-151, 'isc_dsql_prepare: \n attempted update of read-only column') 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row
On Nov 7, 2007, at 5:19 AM, Werner F. Bruhin wrote: PassiveDefault is great to know. However I still have a problem with the following. In a program I do something like this: botlot3 = session.query(db.Bottaglot).get(39) Then some other user and/or application changes data (I faked this by setting a debugger break point and used the db admin tool to change some data and committed it) in the database and commits, then when I do this: botlot4 = session.query(db.Bottaglot).get(39) I expected to get the data from the database, however SA gets it from the session (I set echo=True) and I don't see a select being done between the first statement and the second. Searching in the documentation I see that it is documented that get is NOT querying the database if the key is present, however I haven't found how I can do a primary key query so that SA goes to the database. I'll keep searching in the doc, but would still appreciate any hints. Werner - theres three approaches which can be used individually or together for this type of thing. 1. When running the session within a transaction, either using session.begin() or creating your session with transactional=True, you let the database's normal transactional behavior handle transaction isolation issues (reading one value, making a change based on that value and commiting, but then someone else changed in the middle, is a transaction isolation issue). I would recommend running within a transaction at the very least. 2. If youd like to explicitly place a lock on the row, you can say session.query(Foo).with_lockmode('update').get(39). This will use a SELECT..FOR UPDATE in order to fetch the row and will then explicitly lock the row against concurrent access, until the next UPDATE occurs or the transaction is completed/rolled back. This is known as pessimistic locking, since it assumes that a contention issue will occur and prevents against it from happening. SELECT..FOR UPDATE should be run in a transaction. 3. Alternatively, optimistic locking can be used by setting the version_id_col option on your mapper() (i.e. mapper(, version_id_col=mytable.c.version_col) ); you add an integer valued column to your table which you reference via this setting. The mapper will place increasing numbers within the column upon each change to the row. When a flush() occurs, the mapper updates the row based not only on the primary key columns but also on the expected version number, and if no row was located in the update, it throws a ConcurrencyError. this is known as optimistic since it doesn't actually prevent the contention issue from happening, but instead when it does happen knows enough to abort the transaction. The Query can also fetch data from the database without using the cache - if you say query.load(39) it will fetch the row from the database unconditionally and re-populate the existing object if already present, but note that this removes any changes you've made to the object. However, this is not much of a guarantee of anything, since if you load() the object, make changes, then flush, theres still the possibility that the concurrent changes occured in between the load/flush steps. So the pessimistic/optimistic locking approaches are better solutions if concurrency issues are expected. For slower concurrency issues, such as you arent concerned about concurrency within a small period of time and are instead concerned about two users changing some data five minutes apart, I would note that an individual Session is typically meant for a single set of operations, then its closed. Holding onto a Session for a long time, across web requests, etc., is not really its primary usage model. Its more like something you check out, do some things with it, then check it back in. mike --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: data inserted by db trigger is not returned when I re-query the row
Mike, Michael Bayer wrote: On Nov 7, 2007, at 5:19 AM, Werner F. Bruhin wrote: PassiveDefault is great to know. However I still have a problem with the following. In a program I do something like this: botlot3 = session.query(db.Bottaglot).get(39) Then some other user and/or application changes data (I faked this by setting a debugger break point and used the db admin tool to change some data and committed it) in the database and commits, then when I do this: botlot4 = session.query(db.Bottaglot).get(39) I expected to get the data from the database, however SA gets it from the session (I set echo=True) and I don't see a select being done between the first statement and the second. Searching in the documentation I see that it is documented that get is NOT querying the database if the key is present, however I haven't found how I can do a primary key query so that SA goes to the database. I'll keep searching in the doc, but would still appreciate any hints. Werner - theres three approaches which can be used individually or together for this type of thing. 1. When running the session within a transaction, either using session.begin() or creating your session with transactional=True, you let the database's normal transactional behavior handle transaction isolation issues (reading one value, making a change based on that value and commiting, but then someone else changed in the middle, is a transaction isolation issue). I would recommend running within a transaction at the very least. 2. If youd like to explicitly place a lock on the row, you can say session.query(Foo).with_lockmode('update').get(39). This will use a SELECT..FOR UPDATE in order to fetch the row and will then explicitly lock the row against concurrent access, until the next UPDATE occurs or the transaction is completed/rolled back. This is known as pessimistic locking, since it assumes that a contention issue will occur and prevents against it from happening. SELECT..FOR UPDATE should be run in a transaction. 3. Alternatively, optimistic locking can be used by setting the version_id_col option on your mapper() (i.e. mapper(, version_id_col=mytable.c.version_col) ); you add an integer valued column to your table which you reference via this setting. The mapper will place increasing numbers within the column upon each change to the row. When a flush() occurs, the mapper updates the row based not only on the primary key columns but also on the expected version number, and if no row was located in the update, it throws a ConcurrencyError. this is known as optimistic since it doesn't actually prevent the contention issue from happening, but instead when it does happen knows enough to abort the transaction. The Query can also fetch data from the database without using the cache - if you say query.load(39) it will fetch the row from the database unconditionally and re-populate the existing object if already present, but note that this removes any changes you've made to the object. However, this is not much of a guarantee of anything, since if you load() the object, make changes, then flush, theres still the possibility that the concurrent changes occured in between the load/flush steps. So the pessimistic/optimistic locking approaches are better solutions if concurrency issues are expected. For slower concurrency issues, such as you arent concerned about concurrency within a small period of time and are instead concerned about two users changing some data five minutes apart, I would note that an individual Session is typically meant for a single set of operations, then its closed. Holding onto a Session for a long time, across web requests, etc., is not really its primary usage model. Its more like something you check out, do some things with it, then check it back in. Thanks for the detailed response. As mentioned earlier I found refresh which did the trick for this problem, but the query.load is really what I was looking for. 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Code Organisation
On Nov 7, 2007, at 6:34 AM, Koen Bok wrote: Hi there, We have a pretty large project by now and we run into import loops. So I decided to restructure the code, and I hoped some people with more experience can comment on this. The basic problem is this: We have the database object code, mappers and tables neatly organized in one module (db). The controller code imports this module to get access to these objects. All fine. But we have another object called Connection which is a singleton class that actually manages the connection to our database. It is basically a wrapper for create_engine and contextual_session. But next to that it keeps info about the current login state like the employee, location etc. The mapped database objects need this info on their turn to add the current user to a new object etc. So the Connection object depends on the Mapped Database Objects, but the Mapped Database Object depend on the Connection object too. Anyone got a good tip to solve this? Or designed something similar? if you really have an import cycle that cant be broken, you can place imports at the level of the functions which need themjust a thought --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: How does auto-reconnect work?
On Nov 7, 2007, at 12:51 AM, Hong Yuan wrote: Hi, In the release note of 0.3.7, the following is mentioned: - much improved auto-reconnect support But how can one configure this? I am using 0.3.10 with Postgresql. Very often, after some period of inactivity, the connection is closed and the whole application has to be closed and restarted for the database connection to work again. How is auto-reconnect suppposed to work? Is there any connection parameter to alter for instance? auto reconnect is for when an existing pooled connection loses its connection to the database. the connection upon next access will raise an error, and then be removed from the pool to be recycled. the feature is only so that stale connections don't hang around in the connection pool, requiring an app restart. However, note that various unfixed bugs in psycopg2 make this feature nearly unworkable on that platform since psycopg2 raises the connection lost errors at random times. however, youre dealing with a plain database timeout issue. for that, auto-reconnect isnt needed; you use the pool_timeout option documented at http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_options (similarly for 0.3). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Earn Money Online! No Registration Fees. Guaranteed Payments.
http://www.moneycosmos.com/?r=321740 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] IF you want earning easy money click on the link below!
http://www.freewebs.com/homebusiness1 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problems with session transaction in SQLAlchemy 0.4
Michael Bayer ha scritto: [...] One last thing. With SQLAlchemy 0.3.x, my Engine class can be used to execute implicit queries. Now with 0.4.x this does not works, since the engine should implement additionals methods: statement_compiler and _execute_clauseelement. What's the reason of this change? do you mean connectionless execution ? i.e. engine.execute(statement) ? implicit is like table.select().execute(). Right. both of these are supported in 0.4 even though some internals have changed. What is the interface that an engine must support in order to enable implicit execution for tables bound to the engine? I would like this interface to be public, so I can implement it for the Engine class in nadbapi. Thanks Manlio Perillo --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: problems with session transaction in SQLAlchemy 0.4
On Nov 7, 2007, at 4:26 PM, Manlio Perillo wrote: I would like this interface to be public, so I can implement it for the Engine class in nadbapi. ok, its public, rev 3751. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select entire column
I would like something to run on the Query class. It looks like you used to be able to run select on this class, but the function is deprecated. If I cannot, I will just use the execute function and run plain SQL. On Nov 7, 6:39 am, Barry Hart [EMAIL PROTECTED] wrote: Is this what you want? select([my_table.c.my_column], distinct=True) Barry - Original Message From: JamesT [EMAIL PROTECTED] To: sqlalchemy sqlalchemy@googlegroups.com Sent: Wednesday, November 7, 2007 2:05:13 AM Subject: [sqlalchemy] Select entire column I am looking to filter specific columns in a table, but I cannot find how to do this. I can filter by setting these columns to a value, but not just grabbing the entire column. In SQL, I want to do this: SELECT artist FROM artist_table, where the only column kept is artist. The reason I want to do this is so that I can run a distinct() on the Query object returned to get the distinct artists and also distinct genres. Here is a sample of what I am doing now without filtering to get the data I need: tuples = Session.query(Albums).add_entity(Songs).join('songs').all() __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection aroundhttp://mail.yahoo.com --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select entire column
One of the reasons that Query.select() is deprecated is that the way it was named led to this kind of confusion. The Query() class is used for ORM operations, and when it's used as mapped against a table, it's going to give you all the columns from the table by default. There are ways of defining the table mapper to only get one column, but I suspect that you are really barking up the wrong tree, and want to use the non-ORM or SQL expression part of SqlAlchemy. It's that library that Barry was referring to. Using that library, it's going to be really easy to specify only the columns that you want, and apply things like DISTINCT. Read the docs on SQL expressions and try what he's suggesting. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Select entire column
Thanks. I have it working in SQL expression language, using text in execute(). For better modularity and scalabililty, I will probably move it over to using the select(), join(), etc. functions that are built in. On Nov 7, 5:26 pm, Rick Morrison [EMAIL PROTECTED] wrote: One of the reasons that Query.select() is deprecated is that the way it was named led to this kind of confusion. The Query() class is used for ORM operations, and when it's used as mapped against a table, it's going to give you all the columns from the table by default. There are ways of defining the table mapper to only get one column, but I suspect that you are really barking up the wrong tree, and want to use the non-ORM or SQL expression part of SqlAlchemy. It's that library that Barry was referring to. Using that library, it's going to be really easy to specify only the columns that you want, and apply things like DISTINCT. Read the docs on SQL expressions and try what he's suggesting. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
OK i found some more things that i think is probably screwing you up. will keep you posted. On Nov 7, 2007, at 10:45 AM, svilen wrote: On Wednesday 07 November 2007 16:57:08 Michael Bayer wrote: On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/aggregator/ (no it does not need dbcook) $ cd dbcook/misc/aggregator/tests $ PYTHONPATH=$PYTHONPATH:../.. python convertertest.py ... FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] i did print the interesting elements in my Converter.convert_element(), and the result is that a) order is slightly different - which i dont care b) 1 item is not traversed in r3727 e.g. r3626: Column tags.tabl Column tags.oid Column movies.id Column tags.tabl Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state r3627: Column tags.tabl Column tags.oid Column movies.id Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state the 2nd tags.tabl is missing, hence the assertFails ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
On Nov 7, 2007, at 1:20 PM, svilen wrote: ahha. so i am replacing one whole subexpr with somthing, and the original subexpr is not traversed inside. if i comment the stop_on.add(), it attempts to traverse the result subexpr, not the original one. i want the original to be traversed. Something like doing onExit instead of current onEntry. if its too hard, i can probably traverse it twice, once just marking , 2nd time replaceing things? i'll try if youre replacing a subtree, why would you want to then traverse that part of the subtree which was replaced ? can you see why how i have it working now is reasonably straightforward ? perhaps a second call to ACP.traverse() should be called for those elements which were replaced. also r3754 fixes the issue that aliased selects, while being traversed, were not being copied properly. i still need to tweak the rules for aliases (alised tables, for example, remain immutable). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
ahha. so i am replacing one whole subexpr with somthing, and the original subexpr is not traversed inside. if i comment the stop_on.add(), it attempts to traverse the result subexpr, not the original one. i want the original to be traversed. Something like doing onExit instead of current onEntry. if its too hard, i can probably traverse it twice, once just marking , 2nd time replaceing things? i'll try if youre replacing a subtree, why would you want to then traverse that part of the subtree which was replaced ? because as i said, the needed work is onExit of the nodes, i.e. all room/building is traversed, now what u want to make out of it? thus i can traverse only once. can you see why how i have it working now is reasonably straightforward ? perhaps a second call to ACP.traverse() should be called for those elements which were replaced. i did change my code to traverse all twice, once doing nothing, and once replaceing; it works. speed there is not an issue. Still, if u can make the onEntry/onExit/both switchable, that would be usable. Maybe not immediately, but i'm sure once day you'll need it. Right now, it is onEntry, and the actual code/choice is not done clearly, it is spread between traverse() and _convert_element() and similar. Has anything changed on plain ClauseVisitor? coz' i have (more important) other code elsewhere, which also breaks at same r3727 - it's today topic of digging. also r3754 fixes the issue that aliased selects, while being traversed, were not being copied properly. i still need to tweak the rules for aliases (alised tables, for example, remain immutable). i dont really understand why u need the ACP being so different to plain visitor; i mean cant they share some skeleton part of traversing, while putting all the choices (visit* vs convert; onentry/onexit; stop/dont) in their own parts. After all, visitor pattern is twofold, a) Guide + b) Visitor; the Guide doing traversing, the Visitor noting things; choice where to go might be in visitor and/or in guide. some times (one extreme) the visitor is just one dumb functor; other cases (other extreme end) the visitor is very sofisticated and even does guiding/traversing. Here it looks more like second case, u have most of both sides put in the Visitor, and only small part (specific visit_* / copy_internals) left to the actual nodes. And to me, the skeleton is still same between ACP and ClauseVisitor. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: r3727 / AbstractClauseProcessor problem
try out r3754. On Nov 7, 2007, at 6:56 PM, Michael Bayer wrote: OK i found some more things that i think is probably screwing you up. will keep you posted. On Nov 7, 2007, at 10:45 AM, svilen wrote: On Wednesday 07 November 2007 16:57:08 Michael Bayer wrote: On Nov 7, 2007, at 2:03 AM, [EMAIL PROTECTED] wrote: - something changed in the traversing (AbstractClauseProcessor - r3727) and it does not find proper things... ACP has been entirely rewritten. if you can provide simple tests in the form that theyre present in test/sql/generative.py and/or test/sql/ selectable.py that would be helpful. I have a feeling its not missing things, its just doing it slightly differently. http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/aggregator/ (no it does not need dbcook) $ cd dbcook/misc/aggregator/tests $ PYTHONPATH=$PYTHONPATH:../.. python convertertest.py ... FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] FAIL: count tags per movie File tests/convertertest.py, line 73, in test1_count_tags_per_movie['oid']) ... AssertionError: ['oid'] != ['tabl', 'oid'] i did print the interesting elements in my Converter.convert_element(), and the result is that a) order is slightly different - which i dont care b) 1 item is not traversed in r3727 e.g. r3626: Column tags.tabl Column tags.oid Column movies.id Column tags.tabl Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state r3627: Column tags.tabl Column tags.oid Column movies.id Column tags.oid Column movies.id Column users.id Column userpics.uid Column userpics.state the 2nd tags.tabl is missing, hence the assertFails ciao svilen --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---