[sqlalchemy] Locking strategy
Hi All I would like some advice / best practice on the following basic problem please. I'm new to SQL so am groping with some things that used to be basic. :-( I've looked around in SA and have only found a few small notes on locking. There is a for_update but when I try this it appears to be locked out for me as well! Perhaps the answer is here and I'm missing something? I want a standard ability to have a user who is editing a row on some GUI to have control of that row through a lock. Pessimistic locking. I've read various posts talking of having another table that stores the primary key and table name which is written to keep the lock table and therefore all processes must check this first.. but what if they don't? Is there an SA way to deal with this, or do I start inventing? This is the desired outcome; Code can lock a row and leave it locked until unlocked or the session ends. Other attempts to write to that row will receive a locked exception. Some way of enforcing access to the DB for writes must use locking - otherwise it's a strange opt in approach. i.e. it's only locked if you bother to check if it's locked! If what I read is true, and I need to create my own lock table, is there a nice generic way (i.e. I don't know or care what table schema is being locked via my handy lock table) of getting the primary key of the row in question to pass to the lock_row(yourKey). Should I use the primary key, or is there some other unique way of identifying it within a table? In this case, I'm running on MySQL, but I would prefer a DB agnostic solution for this project. Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Tutorial Issue
Hello, all! I'm following the tutorial here http://www.sqlalchemy.org/docs/orm/tutorial.html and I'm running into some problems at one moment, specifically at the Configuring delete/delete-orphan Cascade section. Here is a paste of exactly what I'm doing http://bpaste.net/show/9861/ . Am I making a mistake somewhere or is the tutorial incorrect? Lucian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Uninitialized coltype in PGDialect.get_columns() for custom column basetype
Sorry I was being a bit lazy and trying to avoid setting up the PG test environment. The problem affects a domain over a custom type. Here is a test that exhibits the behaviour (without assertions): class DomainOverCustomTypeReflectionTest(TestBase, AssertsExecutionResults): __only_on__ = 'postgresql' @classmethod def setup_class(cls): con = testing.db.connect() con.execute(CREATE TYPE testtype AS ENUM ('test')) con.execute('CREATE DOMAIN testdomain AS testtype') con.execute('CREATE TABLE testtable (question integer, answer testdomain)') @classmethod def teardown_class(cls): con = testing.db.connect() con.execute('DROP TABLE testtable') con.execute('DROP DOMAIN testdomain') con.execute('DROP TYPE testtype') def test_domain_is_reflected(self): metadata = MetaData(testing.db) table = Table('testtable', metadata, autoload=True) Thanks, N On Sep 29, 9:58 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 29, 2010, at 3:37 PM, Nikolaj wrote: Hi there, I use the earthdistance and cube modules for PostgreSQL (http:// www.postgresql.org/docs/8.4/interactive/earthdistance.html). These define some custom types and functions for doing great circle calculations. I ran into a problem with table introspection in PGDialect.get_columns(). The columns and domains fetched look like this (note that the 'earth' type defined by the earthdistance module has the base type of 'cube' from the cube module): rows = [ (u'id', u'integer', unextval('mytable_id_seq'::regclass), True, 1, 161772), (u'created_at', u'timestamp without time zone', None, True, 2, 161772), (u'name', u'character varying(255)', None, True, 3, 161772), (u'lat', u'numeric(10,7)', None, False, 4, 161772), (u'lng', u'numeric(10,7)', None, False, 5, 161772), (u'earth', u'earth', None, False, 6, 161772) ] domains = { u'earth': {'attype': u'cube', 'default': None, 'nullable': True}, u'information_schema.cardinal_number': {'attype': u'integer', 'default': None, 'nullable': True}, u'information_schema.character_data': {'attype': u'character varying', 'default': None, 'nullable': True}, u'information_schema.sql_identifier': {'attype': u'character varying', 'default': None, 'nullable': True}, u'information_schema.time_stamp': {'attype': u'timestamp', 'default': u('now'::text)::timestamp(2) with time zone, 'nullable': True} } The problem is that in the loop through the rows in PGDialect.get_columns(), the 'earth' column's attype is in the dictionary of domains, but the domain attype (cube) is not in self.ischema_names. So coltype is never initialized, and it ends up having the value of the previous for loop iteration, causing it to fail with TypeError: 'NUMERIC' object is not callable (because the previous iteration ran coltype = coltype(...)). The expected behaviour is obviously for `coltype` to become sqltypes.NULLTYPE. It can be solved by initializing coltype = None inside the for loop. Hi - can you please illustrate a short test for this. I dont work with PG domains myself so its not immediately clear to me what this means exactly, is it the case that this is a domain of a domain ? if so wouldnt we want cube to be in the domains list as well ? We have quite a few tests for reflecting types from domains in test/dialects/test_postgresql.py so a test would need to be added there once a solution is decided upon. Thanks, N -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Uninitialized coltype in PGDialect.get_columns() for custom column basetype
great, that test is perfect. I added ticket #1933, I just want to see if I can get the testtype to be available somehow before falling back to setting coltype=None solution. On Sep 30, 2010, at 9:22 AM, Nikolaj wrote: Sorry I was being a bit lazy and trying to avoid setting up the PG test environment. The problem affects a domain over a custom type. Here is a test that exhibits the behaviour (without assertions): class DomainOverCustomTypeReflectionTest(TestBase, AssertsExecutionResults): __only_on__ = 'postgresql' @classmethod def setup_class(cls): con = testing.db.connect() con.execute(CREATE TYPE testtype AS ENUM ('test')) con.execute('CREATE DOMAIN testdomain AS testtype') con.execute('CREATE TABLE testtable (question integer, answer testdomain)') @classmethod def teardown_class(cls): con = testing.db.connect() con.execute('DROP TABLE testtable') con.execute('DROP DOMAIN testdomain') con.execute('DROP TYPE testtype') def test_domain_is_reflected(self): metadata = MetaData(testing.db) table = Table('testtable', metadata, autoload=True) Thanks, N On Sep 29, 9:58 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 29, 2010, at 3:37 PM, Nikolaj wrote: Hi there, I use the earthdistance and cube modules for PostgreSQL (http:// www.postgresql.org/docs/8.4/interactive/earthdistance.html). These define some custom types and functions for doing great circle calculations. I ran into a problem with table introspection in PGDialect.get_columns(). The columns and domains fetched look like this (note that the 'earth' type defined by the earthdistance module has the base type of 'cube' from the cube module): rows = [ (u'id', u'integer', unextval('mytable_id_seq'::regclass), True, 1, 161772), (u'created_at', u'timestamp without time zone', None, True, 2, 161772), (u'name', u'character varying(255)', None, True, 3, 161772), (u'lat', u'numeric(10,7)', None, False, 4, 161772), (u'lng', u'numeric(10,7)', None, False, 5, 161772), (u'earth', u'earth', None, False, 6, 161772) ] domains = { u'earth': {'attype': u'cube', 'default': None, 'nullable': True}, u'information_schema.cardinal_number': {'attype': u'integer', 'default': None, 'nullable': True}, u'information_schema.character_data': {'attype': u'character varying', 'default': None, 'nullable': True}, u'information_schema.sql_identifier': {'attype': u'character varying', 'default': None, 'nullable': True}, u'information_schema.time_stamp': {'attype': u'timestamp', 'default': u('now'::text)::timestamp(2) with time zone, 'nullable': True} } The problem is that in the loop through the rows in PGDialect.get_columns(), the 'earth' column's attype is in the dictionary of domains, but the domain attype (cube) is not in self.ischema_names. So coltype is never initialized, and it ends up having the value of the previous for loop iteration, causing it to fail with TypeError: 'NUMERIC' object is not callable (because the previous iteration ran coltype = coltype(...)). The expected behaviour is obviously for `coltype` to become sqltypes.NULLTYPE. It can be solved by initializing coltype = None inside the for loop. Hi - can you please illustrate a short test for this. I dont work with PG domains myself so its not immediately clear to me what this means exactly, is it the case that this is a domain of a domain ? if so wouldnt we want cube to be in the domains list as well ? We have quite a few tests for reflecting types from domains in test/dialects/test_postgresql.py so a test would need to be added there once a solution is decided upon. Thanks, N -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to
[sqlalchemy] looks like bug ses.query(data).update()
I try to update counter for omr object ang got following: Traceback (most recent call last): File /home/vugluskr/tmp/z/sa.py, line 56, in module main() File /home/vugluskr/tmp/z/sa.py, line 52, in main q2.update({data.cnt: data.cnt + 1}) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py, line 2184, in update difference(to_evaluate)) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py, line 257, in expire_attributes impl = self.manager[key].impl KeyError: 'counter' Tests script and full output in attaches. Maybe I going wrong way and SA have more simpler way awailable to make query like: update tbl set counter = counter + 1 where ... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. INFO:sqlalchemy.engine.base.Engine.0x...9b4c:PRAGMA table_info(data) INFO:sqlalchemy.engine.base.Engine.0x...9b4c:() INFO:sqlalchemy.engine.base.Engine.0x...9b4c: CREATE TABLE data ( idnr INTEGER NOT NULL, counter INTEGER NOT NULL, PRIMARY KEY (idnr) ) INFO:sqlalchemy.engine.base.Engine.0x...9b4c:() INFO:sqlalchemy.engine.base.Engine.0x...9b4c:COMMIT INFO:sqlalchemy.engine.base.Engine.0x...9b4c:BEGIN INFO:sqlalchemy.engine.base.Engine.0x...9b4c:INSERT INTO data (counter) VALUES (?) INFO:sqlalchemy.engine.base.Engine.0x...9b4c:(0,) INFO:sqlalchemy.engine.base.Engine.0x...9b4c:COMMIT INFO:sqlalchemy.engine.base.Engine.0x...9b4c:BEGIN INFO:sqlalchemy.engine.base.Engine.0x...9b4c:SELECT data.counter AS data_counter, data.idnr AS data_idnr FROM data INFO:sqlalchemy.engine.base.Engine.0x...9b4c:() DEBUG:sqlalchemy.engine.base.Engine.0x...9b4c:Col ('data_counter', 'data_idnr') DEBUG:sqlalchemy.engine.base.Engine.0x...9b4c:Row (0, 1) INFO:sqlalchemy.engine.base.Engine.0x...9b4c:UPDATE data SET counter=(data.counter + ?) WHERE data.idnr = ? INFO:sqlalchemy.engine.base.Engine.0x...9b4c:(1, 1) Traceback (most recent call last): File /home/vugluskr/tmp/z/sa.py, line 56, in module main() File /home/vugluskr/tmp/z/sa.py, line 52, in main q2.update({data.cnt: data.cnt + 1}) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py, line 2184, in update difference(to_evaluate)) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py, line 257, in expire_attributes impl = self.manager[key].impl KeyError: 'counter' #!/usr/bin/env python #-*- coding:utf-8 -*- #import pdb import os, sys import time import logging #import re #import errno #import locale #import pprint # mutable inputs from sqlalchemy import * from sqlalchemy import orm from sqlalchemy.ext.declarative import declarative_base decl = declarative_base() class data(decl): __tablename__ = 'data' idnr= Column(Integer, primary_key=True) cnt = Column('counter', Integer, nullable=False, default=0) def __repr__(self): return 'tbl.%s(idnr=%s, cnt=%s)' % ( self.__class__.__name__, self.idnr, self.cnt) def main(): logging.basicConfig() logging.getLogger().setLevel(logging.DEBUG) log = logging.getLogger('sqlalchemy.engine') log.setLevel(logging.DEBUG) eng = create_engine('sqlite://') sm = orm.sessionmaker(bind=eng, autoflush=False, autocommit=False) decl.metadata.create_all(eng) ses = sm() e = data() ses.add(e) ses.commit() ses.close() ses = sm() q = ses.query(data) for e in q: q2 = ses.query(data).filter(data.idnr==e.idnr) q2.update({data.cnt: data.cnt + 1}) ses.commit() if __name__ == '__main__': main()
Re: [sqlalchemy] Locking strategy
On Sep 30, 2010, at 4:43 AM, Warwick Prince wrote: Hi All I would like some advice / best practice on the following basic problem please. I'm new to SQL so am groping with some things that used to be basic. :-( I've looked around in SA and have only found a few small notes on locking. There is a for_update but when I try this it appears to be locked out for me as well! Perhaps the answer is here and I'm missing something? I want a standard ability to have a user who is editing a row on some GUI to have control of that row through a lock. Pessimistic locking. I've read various posts talking of having another table that stores the primary key and table name which is written to keep the lock table and therefore all processes must check this first.. but what if they don't? Is there an SA way to deal with this, or do I start inventing? This is the desired outcome; Code can lock a row and leave it locked until unlocked or the session ends. Other attempts to write to that row will receive a locked exception. Some way of enforcing access to the DB for writes must use locking - otherwise it's a strange opt in approach. i.e. it's only locked if you bother to check if it's locked! If what I read is true, and I need to create my own lock table, is there a nice generic way (i.e. I don't know or care what table schema is being locked via my handy lock table) of getting the primary key of the row in question to pass to the lock_row(yourKey). Should I use the primary key, or is there some other unique way of identifying it within a table? In this case, I'm running on MySQL, but I would prefer a DB agnostic solution for this project. The usual approach to pessimistic locking on a simple scale is to use SELECTFOR UPDATE NOWAIT, which locks the selected rows in the current transaction. Other transactions which attempt to get to the row are blocked. The NOWAIT means it will raise immediately instead of blocking. If you've tried this and you seem to be locked out, then you need to get your transactions straight - you'd need to perform subsequent operations with the same transaction as that which began the for_update. However, if the lock we're talking about isn't just a matter of ensuring proper concurrency across transactions, and is more like a business-level lock - i.e. User XYZ is editing this document, then yes this is not a DB concurrency issue, its a datamodel one.You'd build the lock table as described, and build a system within your GUI that acquires a row from this table before allowing operations to proceed. How the application does that depends much on what kind of application it is and over what span such a lock is created.You'd have to trap user actions at the top level of activity which would be locked, sometimes Python decorators are good at this, such as : @requires_lock def user_do_something(x, y, z, ...): .. where @requires_lock would look at the current user, maybe look at the arguments of user_do_something(), determine if that user has acquired a locking object. Cheers Warwick -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Tutorial Issue
OK, was hard to spot, and I think maybe I should change this, there's a line where it does this: users_table = User.__table__ So the original users_table is replaced with the one that User.__table__ is mapped to. I'm going to change that remapping to read like this: mapper(User, User.__table__, properties={ ... 'addresses':relationship(Address, backref='user', cascade=all, delete, delete-orphan) ... }) Mapper at 0x...; User addresses_table = Address.__table__ mapper(Address, addresses_table) Mapper at 0x...; Address On Sep 30, 2010, at 6:22 AM, LucianU wrote: Hello, all! I'm following the tutorial here http://www.sqlalchemy.org/docs/orm/tutorial.html and I'm running into some problems at one moment, specifically at the Configuring delete/delete-orphan Cascade section. Here is a paste of exactly what I'm doing http://bpaste.net/show/9861/ . Am I making a mistake somewhere or is the tutorial incorrect? Lucian -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Locking strategy
On Sep 30, 2010, at 10:02 AM, Michael Bayer wrote: On Sep 30, 2010, at 4:43 AM, Warwick Prince wrote: Hi All I would like some advice / best practice on the following basic problem please. I'm new to SQL so am groping with some things that used to be basic. :-( I've looked around in SA and have only found a few small notes on locking. There is a for_update but when I try this it appears to be locked out for me as well! Perhaps the answer is here and I'm missing something? snip The usual approach to pessimistic locking on a simple scale is to use SELECTFOR UPDATE NOWAIT, which locks the selected rows in the current transaction. Other transactions which attempt to get to the row are blocked. The NOWAIT means it will raise immediately instead of blocking. If you've tried this and you seem to be locked out, then you need to get your transactions straight - you'd need to perform subsequent operations with the same transaction as that which began the for_update. However, if the lock we're talking about isn't just a matter of ensuring proper concurrency across transactions, and is more like a business-level lock - i.e. User XYZ is editing this document, then yes this is not a DB concurrency issue, its a datamodel one.You'd build the lock table as described, and build a system within your GUI that acquires a row from this table before allowing operations to proceed. How the application does that depends much on what kind of application it is and over what span such a lock is created.You'd have to trap user actions at the top level of activity which would be locked, sometimes Python decorators are good at this, such as : @requires_lock def user_do_something(x, y, z, ...): .. where @requires_lock would look at the current user, maybe look at the arguments of user_do_something(), determine if that user has acquired a locking object. Another option is to use database advisory locks (where available). http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html Advisory Locks Cheers, M -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] ...kill_hung_threads status...
Hi guys. I keep getting this message: [paste.httpserver.ThreadPool] kill_hung_threads status: 10 threads (1 working, 9 idle, 0 starting) ave time 0.03sec, max time 0.03sec, killed 0 workers Do I have to close a connection every time i'm querying the db? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] looks like bug ses.query(data).update()
this is a bug , created at http://www.sqlalchemy.org/trac/ticket/1935 , and a patch which fixes this issue is there. will try to get this committed soon. On Sep 30, 2010, at 9:57 AM, bogun.dmit...@gmail.com wrote: I try to update counter for omr object ang got following: Traceback (most recent call last): File /home/vugluskr/tmp/z/sa.py, line 56, in module main() File /home/vugluskr/tmp/z/sa.py, line 52, in main q2.update({data.cnt: data.cnt + 1}) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py, line 2184, in update difference(to_evaluate)) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py, line 257, in expire_attributes impl = self.manager[key].impl KeyError: 'counter' Tests script and full output in attaches. Maybe I going wrong way and SA have more simpler way awailable to make query like: update tbl set counter = counter + 1 where ... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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. update-output.txtsa.py -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ...kill_hung_threads status...
On Sep 30, 2010, at 10:26 AM, dobrysmak wrote: Hi guys. I keep getting this message: [paste.httpserver.ThreadPool] kill_hung_threads status: 10 threads (1 working, 9 idle, 0 starting) ave time 0.03sec, max time 0.03sec, killed 0 workers Do I have to close a connection every time i'm querying the db? I don't think that message is related to database connections. With normal connection pool usage connections aren't closed, they're just sitting in a pool, and as long as you stick to the plan at http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session , resources are released at the end of a request, or if an exception is raised (by removing within finally:). If you had requests that were hung, a database like PG would show idle in transaction for long spans of time in your process listing. Or you could use pg_stat_activity to see this. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] ...kill_hung_threads status...
On 9/30/10 16:26 , dobrysmak wrote: Hi guys. I keep getting this message: [paste.httpserver.ThreadPool] kill_hung_threads status: 10 threads (1 working, 9 idle, 0 starting) ave time 0.03sec, max time 0.03sec, killed 0 workers that's just the paste httpserver checking if any of your application threads are stuck. It's a purely diagnostic message that you can safely ignore. It has no relation to SQLAlchemy. Wichert. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Problems with inserting data
Hi. I've got autoloadable table: --- class ObjectList(object): pass t_object_list = sa.Table('object_list', meta.metadata, autoload=True, autoload_with=engine) orm.mapper(ObjectList, t_object_list) And when I'm trying insert data: ol = ObjectList() ol.type = data['type'], ol.name = data['name'], ol.address = data['address'], ol.gps_lat = data['gps_lat'], ol.gps_lng = data['gps_lng'], ol._class = data['class'], ol.has_parking = data['has_parking'], ol.has_canteen = data['has_canteen'], ol.has_cafe = data['has_cafe'], ol.has_restaurant = data['has_restaurant'], ol.has_fitness = data['has_fitness'], ol.has_beauty_salon = data['has_beauty_salon'], ol.has_tire_shop = data['has_tire_shop'], ol.has_car_washer = data['has_car_washer'], ol.has_bank = data['has_bank'], ol.has_minibank = data['has_minibank'], ol.has_payment_terminal = data['has_payment_terminal'], ol.has_another = data['has_another'] meta.Session.add(ol) meta.Session.commit() I've got error: ProgrammingError: (ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'), (\'1\',), (\'1\',), (\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',), (\'0\' at line 1') 'INSERT INTO object_list (type, name, address, gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe, has_restaurant, has_fitness, has_beauty_salon, has_tire_shop, has_car_washer, has_bank, has_minibank, has_payment_terminal, has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18 bound parameter sets Where is problems ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 make many-to-many links against the same class with an association table
Hi, I've two classes CourseSet and CanonicalCourse (with the same parent) which need to be connected in a many-to-many relationship. But these classes are mapped on the same table, thus definition of the association table seems redondant: assoc_course_set_canonical_table = Table( 'CM_COURSE_SET_CANON_ASSOC_T', Base.metadata, Column('CANON_COURSE', Integer, ForeignKey('CM_MEMBER_CONTAINER_T.MEMBER_CONTAINER_ID')), Column('COURSE_SET', Integer, ForeignKey('CM_MEMBER_CONTAINER_T.MEMBER_CONTAINER_ID')) ) But i can't define the relationship between. I've the error: Could not determine join condition between parent/child tables on relationship CourseSet.canonicalCourses. Specify a 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed as well. I must have miss something: i've tried several possibilities with primaryjoin and secondaryjoin but nothing work. Is it because i'm refering at the same foreign key in the associative table ? Thank you. Julien. PS: The code for my two classes ('#' comments the two lines with the problem) class CourseSet(AbstractContainer): parent_id = Column('PARENT_COURSE_SET', Integer, ForeignKey('CM_MEMBER_CONTAINER_T.ENTERPRISE_ID')) children = relationship(CourseSet, backref=backref('parent', remote_side=AbstractContainer.eid)) #canonicalCourses = relationship(CanonicalCourse, #secondary=assoc_course_set_canonical_table) @classproperty def __mapper_args__(self): args = dict() args.update(AbstractContainer.__mapper_args__) args.update({'polymorphic_identity': 'org.sakaiproject.coursemanagement.impl.CourseSetCmImpl'}) return args class CanonicalCourse(AbstractContainer): @classproperty def __mapper_args__(self): args = dict() args.update(AbstractContainer.__mapper_args__) args.update({'polymorphic_identity': 'org.sakaiproject.coursemanagement.impl.CanonicalCourseCmImpl'}) return args PS: the parents of these classes: class AbstractPersistent(object): version = Column('VERSION', Integer) last_modified_by = Column('LAST_MODIFIED_BY', String(255)) last_modified_date = Column('LAST_MODIFIED_DATE', Date) created_by = Column('CREATED_BY', String(255)) created_date = Column('CREATED_DATE', Date) class AbstractNamed(AbstractPersistent): eid = Column('ENTERPRISE_ID', String(255)) title = Column('TITLE', String(255)) description = Column('DESCRIPTION', String(255)) class AbstractContainer(Base,AbstractNamed): __tablename__ = 'CM_MEMBER_CONTAINER_T' id = Column('MEMBER_CONTAINER_ID',Integer,primary_key=True) discriminator = Column('CLASS_DISCR', String(100)) __mapper_args__ = {'polymorphic_on': discriminator } -- Trouble-a-cat limited -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] looks like bug ses.query(data).update()
2010/9/30 Michael Bayer mike...@zzzcomputing.com: this is a bug , created at http://www.sqlalchemy.org/trac/ticket/1935 , and a patch which fixes this issue is there. will try to get this committed soon. Thanks, patch fix issue. I try to update counter for omr object ang got following: Traceback (most recent call last): File /home/vugluskr/tmp/z/sa.py, line 56, in module main() File /home/vugluskr/tmp/z/sa.py, line 52, in main q2.update({data.cnt: data.cnt + 1}) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/query.py, line 2184, in update difference(to_evaluate)) File /usr/lib/python2.6/site-packages/sqlalchemy/orm/state.py, line 257, in expire_attributes impl = self.manager[key].impl KeyError: 'counter' Tests script and full output in attaches. Maybe I going wrong way and SA have more simpler way awailable to make query like: update tbl set counter = counter + 1 where ... -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] using funcs within a primary join
Hey, I am trying to create a funky relationship where I want to take a field in a table, take a portion of it and then use that portion to create a relation. Is this even possible? My relation is below reseller = relation(Reseller, uselist = False, primaryjoin = func.substring(credits.c.billing_code, 3, 25) == Reseller.resellerid) Any suggestions to make this happen? --Dan -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Problems with inserting data
dont send tuples as bind parameters for scalar attributes. one or more members of your data dictionary are tuples. On Sep 30, 2010, at 6:12 PM, phasma wrote: If add to sqlalchemy/orm/mapper.py at 1699 line this code: if isinstance(value, tuple): value = value[0] Insert works correctly. On Sep 30, 9:31 pm, phasma xpa...@gmail.com wrote: Hi. I've got autoloadable table: --- class ObjectList(object): pass t_object_list = sa.Table('object_list', meta.metadata, autoload=True, autoload_with=engine) orm.mapper(ObjectList, t_object_list) --- - And when I'm trying insert data: --- - ol = ObjectList() ol.type = data['type'], ol.name = data['name'], ol.address = data['address'], ol.gps_lat = data['gps_lat'], ol.gps_lng = data['gps_lng'], ol._class = data['class'], ol.has_parking = data['has_parking'], ol.has_canteen = data['has_canteen'], ol.has_cafe = data['has_cafe'], ol.has_restaurant = data['has_restaurant'], ol.has_fitness = data['has_fitness'], ol.has_beauty_salon = data['has_beauty_salon'], ol.has_tire_shop = data['has_tire_shop'], ol.has_car_washer = data['has_car_washer'], ol.has_bank = data['has_bank'], ol.has_minibank = data['has_minibank'], ol.has_payment_terminal = data['has_payment_terminal'], ol.has_another = data['has_another'] meta.Session.add(ol) meta.Session.commit() I've got error: ProgrammingError: (ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'), (\'1\',), (\'1\',), (\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',), (\'0\' at line 1') 'INSERT INTO object_list (type, name, address, gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe, has_restaurant, has_fitness, has_beauty_salon, has_tire_shop, has_car_washer, has_bank, has_minibank, has_payment_terminal, has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18 bound parameter sets Where is problems ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Problems with inserting data
at: ol.type = data['type'], print type(data['type']) returns str print ol.__dict__ returns: {u'has_tire_shop': (False,), u'gps_lat': ('2',), '_sa_instance_state': sqlalchemy.orm.state.InstanceState object at 0x7fcc6002b0d0, u'name': ('2',), u'has_fitness': (False,), u'has_another': '', u'has_cafe': (False,), u'has_restaurant': (False,), u'has_canteen': (False,), u'has_payment_terminal': (False,), u'has_bank': (False,), u'has_parking': (False,), u'address': ('2',), u'has_minibank': (False,), u'has_car_washer': (False,), u'gps_lng': ('2',), u'type': ('office',), u'_class': ('A',), u'has_beauty_salon': (False,)} On Oct 1, 2:19 am, Michael Bayer mike...@zzzcomputing.com wrote: dont send tuples as bind parameters for scalar attributes. one or more members of your data dictionary are tuples. On Sep 30, 2010, at 6:12 PM, phasma wrote: If add to sqlalchemy/orm/mapper.py at 1699 line this code: if isinstance(value, tuple): value = value[0] Insert works correctly. On Sep 30, 9:31 pm, phasma xpa...@gmail.com wrote: Hi. I've got autoloadable table: --- class ObjectList(object): pass t_object_list = sa.Table('object_list', meta.metadata, autoload=True, autoload_with=engine) orm.mapper(ObjectList, t_object_list) --- - And when I'm trying insert data: --- - ol = ObjectList() ol.type = data['type'], ol.name = data['name'], ol.address = data['address'], ol.gps_lat = data['gps_lat'], ol.gps_lng = data['gps_lng'], ol._class = data['class'], ol.has_parking = data['has_parking'], ol.has_canteen = data['has_canteen'], ol.has_cafe = data['has_cafe'], ol.has_restaurant = data['has_restaurant'], ol.has_fitness = data['has_fitness'], ol.has_beauty_salon = data['has_beauty_salon'], ol.has_tire_shop = data['has_tire_shop'], ol.has_car_washer = data['has_car_washer'], ol.has_bank = data['has_bank'], ol.has_minibank = data['has_minibank'], ol.has_payment_terminal = data['has_payment_terminal'], ol.has_another = data['has_another'] meta.Session.add(ol) meta.Session.commit() I've got error: ProgrammingError: (ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'), (\'1\',), (\'1\',), (\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',), (\'0\' at line 1') 'INSERT INTO object_list (type, name, address, gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe, has_restaurant, has_fitness, has_beauty_salon, has_tire_shop, has_car_washer, has_bank, has_minibank, has_payment_terminal, has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18 bound parameter sets Where is problems ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] how to make many-to-many links against the same class with an association table
you need to specify primaryjoin and secondary join when you do self ref using m2m. there's an example at the end of http://www.sqlalchemy.org/docs/orm/relationships.html#specifying-alternate-join-conditions-to-relationship , as well as some variants of that syntax which declarative introduces. On Sep 30, 2010, at 3:15 PM, Julien Iguchi-Cartigny wrote: Hi, I've two classes CourseSet and CanonicalCourse (with the same parent) which need to be connected in a many-to-many relationship. But these classes are mapped on the same table, thus definition of the association table seems redondant: assoc_course_set_canonical_table = Table( 'CM_COURSE_SET_CANON_ASSOC_T', Base.metadata, Column('CANON_COURSE', Integer, ForeignKey('CM_MEMBER_CONTAINER_T.MEMBER_CONTAINER_ID')), Column('COURSE_SET', Integer, ForeignKey('CM_MEMBER_CONTAINER_T.MEMBER_CONTAINER_ID')) ) But i can't define the relationship between. I've the error: Could not determine join condition between parent/child tables on relationship CourseSet.canonicalCourses. Specify a 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed as well. I must have miss something: i've tried several possibilities with primaryjoin and secondaryjoin but nothing work. Is it because i'm refering at the same foreign key in the associative table ? Thank you. Julien. PS: The code for my two classes ('#' comments the two lines with the problem) class CourseSet(AbstractContainer): parent_id = Column('PARENT_COURSE_SET', Integer, ForeignKey('CM_MEMBER_CONTAINER_T.ENTERPRISE_ID')) children = relationship(CourseSet, backref=backref('parent', remote_side=AbstractContainer.eid)) #canonicalCourses = relationship(CanonicalCourse, #secondary=assoc_course_set_canonical_table) @classproperty def __mapper_args__(self): args = dict() args.update(AbstractContainer.__mapper_args__) args.update({'polymorphic_identity': 'org.sakaiproject.coursemanagement.impl.CourseSetCmImpl'}) return args class CanonicalCourse(AbstractContainer): @classproperty def __mapper_args__(self): args = dict() args.update(AbstractContainer.__mapper_args__) args.update({'polymorphic_identity': 'org.sakaiproject.coursemanagement.impl.CanonicalCourseCmImpl'}) return args PS: the parents of these classes: class AbstractPersistent(object): version = Column('VERSION', Integer) last_modified_by = Column('LAST_MODIFIED_BY', String(255)) last_modified_date = Column('LAST_MODIFIED_DATE', Date) created_by = Column('CREATED_BY', String(255)) created_date = Column('CREATED_DATE', Date) class AbstractNamed(AbstractPersistent): eid = Column('ENTERPRISE_ID', String(255)) title = Column('TITLE', String(255)) description = Column('DESCRIPTION', String(255)) class AbstractContainer(Base,AbstractNamed): __tablename__ = 'CM_MEMBER_CONTAINER_T' id = Column('MEMBER_CONTAINER_ID',Integer,primary_key=True) discriminator = Column('CLASS_DISCR', String(100)) __mapper_args__ = {'polymorphic_on': discriminator } -- Trouble-a-cat limited -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] using funcs within a primary join
On Sep 30, 2010, at 4:47 PM, Dan wrote: Hey, I am trying to create a funky relationship where I want to take a field in a table, take a portion of it and then use that portion to create a relation. Is this even possible? My relation is below reseller = relation(Reseller, uselist = False, primaryjoin = func.substring(credits.c.billing_code, 3, 25) == Reseller.resellerid) Any suggestions to make this happen? Put viewonly=True on the relation, specify the foreign_keys and remote_side arguments. Though dramatically simpler, just use a @property. Also, here's a very old example that uses _local_remote_pairs to do it: http://www.sqlalchemy.org/trac/attachment/ticket/610/cast_primaryjoin_example.py -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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: Problems with inserting data
I feel I need to sleep. Thank you :) On Oct 1, 2:47 am, Michael Bayer mike...@zzzcomputing.com wrote: you're doing this: data = {} data['type'] = 'some type' class Foo(object): pass ol = Foo() ol.type = data['type'], print ol.type its a tuple, lose the ',' On Sep 30, 2010, at 6:42 PM, phasma wrote: at: ol.type = data['type'], print type(data['type']) returns str print ol.__dict__ returns: {u'has_tire_shop': (False,), u'gps_lat': ('2',), '_sa_instance_state': sqlalchemy.orm.state.InstanceState object at 0x7fcc6002b0d0, u'name': ('2',), u'has_fitness': (False,), u'has_another': '', u'has_cafe': (False,), u'has_restaurant': (False,), u'has_canteen': (False,), u'has_payment_terminal': (False,), u'has_bank': (False,), u'has_parking': (False,), u'address': ('2',), u'has_minibank': (False,), u'has_car_washer': (False,), u'gps_lng': ('2',), u'type': ('office',), u'_class': ('A',), u'has_beauty_salon': (False,)} On Oct 1, 2:19 am, Michael Bayer mike...@zzzcomputing.com wrote: dont send tuples as bind parameters for scalar attributes. one or more members of your data dictionary are tuples. On Sep 30, 2010, at 6:12 PM, phasma wrote: If add to sqlalchemy/orm/mapper.py at 1699 line this code: if isinstance(value, tuple): value = value[0] Insert works correctly. On Sep 30, 9:31 pm, phasma xpa...@gmail.com wrote: Hi. I've got autoloadable table: --- class ObjectList(object): pass t_object_list = sa.Table('object_list', meta.metadata, autoload=True, autoload_with=engine) orm.mapper(ObjectList, t_object_list) --- - And when I'm trying insert data: --- - ol = ObjectList() ol.type = data['type'], ol.name = data['name'], ol.address = data['address'], ol.gps_lat = data['gps_lat'], ol.gps_lng = data['gps_lng'], ol._class = data['class'], ol.has_parking = data['has_parking'], ol.has_canteen = data['has_canteen'], ol.has_cafe = data['has_cafe'], ol.has_restaurant = data['has_restaurant'], ol.has_fitness = data['has_fitness'], ol.has_beauty_salon = data['has_beauty_salon'], ol.has_tire_shop = data['has_tire_shop'], ol.has_car_washer = data['has_car_washer'], ol.has_bank = data['has_bank'], ol.has_minibank = data['has_minibank'], ol.has_payment_terminal = data['has_payment_terminal'], ol.has_another = data['has_another'] meta.Session.add(ol) meta.Session.commit() I've got error: ProgrammingError: (ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'), (\'1\',), (\'1\',), (\'1\',), (\'1\',), (\'A\',), (\'0\',), (\'0\',), (\'0\',), (\'0\' at line 1') 'INSERT INTO object_list (type, name, address, gps_lat, gps_lng, _class, has_parking, has_canteen, has_cafe, has_restaurant, has_fitness, has_beauty_salon, has_tire_shop, has_car_washer, has_bank, has_minibank, has_payment_terminal, has_another) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)' (('office',), ('1',)) ... and a total of 18 bound parameter sets Where is problems ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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.