Re: [sqlalchemy] Re: multi-table comparators / properties - how?
On Jun 2, 2010, at 12:48 AM, SQLAlchemy User wrote: OK, I get the need for column_property, but the select is fairly complex and involves unions of the containing class (and it hasn't been defined yet). How do I get around this, and other questions below I think I got the SQL right (for a device with id = foo): SELECT v1.adminstate FROM ( (SELECT devices.adminstate, devices.processdate as insert_ts, devices.deviceid FROM devices WHERE devices.deviceid = foo) UNION (SELECT devicestates.adminstate, devicestates.insert_ts, devicestates.deviceid FROM devicestates WHERE devicestates.deviceid = foo)) AS v1 ORDER BY v1.insert_ts DESC LIMIT 1; You dont need to get into UNION here. My suggestion would be to avoid UNION at all costs, in fact, they perform poorly and are very cumbersome to work with.A correlated subquery here should do what you need: (i.e., when you say query(Device).filter(Device.curradminstate=='FOO'): select device.* from device where coalesce( (select devicestate.adminstate from devicestate where devicestate.insert_ts device.import_ts order by devicestate.insert_ts limit 1), device.adminstate ) = 'FOO' you'd need to verify the above works first (particularly the LIMIT inside the subquery, I know mysql can do it, not sure about PG, don't know your backend). property is: class Device(Base): ... device_subq = select([DeviceState.adminstate]).where(DeviceState.insert_ts device.import_ts).order_by(DeviceState.insert_ts).limit(1).as_scalar() Device.curradminstate = column_property( func.coalesce(device_subq, Device.adminstate) ) So, three questions come to mind: 1) how do I create a select statement that references a label (I did it using quotes - that is, select(['v1.adminstate'],...) but not sure whether that's the most correct way, and I'm running into difficulties later on in the order_by); 2) how do I reference Device from within the select statement that's a column_property for Device; and 3) am I on the right track here or (more likely) am I off-base and missing something simple (like func.max())? Thanks again - S. -- 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] SA-SID issue
Hi I am using Oracle with SQLalchemy in my application. 1. The SQLalchemy documentation gives the following as the connect string for Oracle sqlalchemy.url=oracle://user:passw...@localhost:1521/ SID_name 2. The above connect string does not seem to work in my application. I get the following error: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor 3. However, my application works without the SID_name as shown below: sqlalchemy.url=oracle://user:passw...@localhost:1521 4. I created the database schema in Oracle DBManager using the Create User menu option Could someone please tell me what I am doing wrong? I would appreciate any help. Thanks -- 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] SA-SID issue
the behavior here is exactly that which I described at http://groups.google.com/group/sqlalchemy/msg/ab479d4762c24c65 . The presence of the slash invokes the usage of cx_oracle makedsn(), in this case with a blank dsn, whereas when not, sends the hostname localhost as the DSN argument directly to cx_oracle.connect(). On Jun 2, 2010, at 10:43 AM, dhanil anupurath wrote: Hi I am using Oracle with SQLalchemy in my application. 1. The SQLalchemy documentation gives the following as the connect string for Oracle sqlalchemy.url=oracle://user:passw...@localhost:1521/ SID_name 2. The above connect string does not seem to work in my application. I get the following error: sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor 3. However, my application works without the SID_name as shown below: sqlalchemy.url=oracle://user:passw...@localhost:1521 4. I created the database schema in Oracle DBManager using the Create User menu option Could someone please tell me what I am doing wrong? I would appreciate any help. Thanks -- 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: Help with optimizing
On May 30, 8:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: OK well by re018792aea57 I've bumped the callcounts down a *little*, reducing compiler overhead within the flush(). Perhaps it will be a bigger difference for your app which seems to be heavy on flush() calls. As it turns out, that change didn't help much at all. :-/ However, performance might not be as big an issue as I suspected. It turns out the code I've been working with is now much faster since using SQLAlchemy. We are perhaps taking a bit more CPU time than we were before, but SQLAlchemy has given us much greater flexibility to write efficient queries. -- 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: Help with optimizing
On Jun 2, 2010, at 1:47 PM, Jason Baker wrote: On May 30, 8:39 pm, Michael Bayer mike...@zzzcomputing.com wrote: OK well by re018792aea57 I've bumped the callcounts down a *little*, reducing compiler overhead within the flush(). Perhaps it will be a bigger difference for your app which seems to be heavy on flush() calls. As it turns out, that change didn't help much at all. :-/ However, performance might not be as big an issue as I suspected. It turns out the code I've been working with is now much faster since using SQLAlchemy. We are perhaps taking a bit more CPU time than we were before, but SQLAlchemy has given us much greater flexibility to write efficient queries. so you're saying, once you ported your code to use SQLA's featureset fully, then you were OK, right ?Well yeah, that is how it works ! Better queries take the load off your app *and* the DB. Mailing list traffic piles up that much more since everyone's learning how to write bigger queries :). -- 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 have query sent as a single line
Is there anyway to have sqlalchemy send the queries to the database as a single line instead of multiline? The reason for this is we generate multiple GB of database logs each hour, and trying to find the rest of a given sqlalchemy query is quite a pain right now. It would be much easier for us if when we grep for a specific user or sql fragment if the entire sql line were returned instead of just the Select clause. Thanks for any help, Chris H. SQLAlchemy 0.5.8 PostgreSQL 8.3.x -- 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 have query sent as a single line
very easy. throw in a ConnectionProxy, override cursor_execute(), strip newlines from the statements. On Jun 2, 2010, at 2:02 PM, Chris H. wrote: Is there anyway to have sqlalchemy send the queries to the database as a single line instead of multiline? The reason for this is we generate multiple GB of database logs each hour, and trying to find the rest of a given sqlalchemy query is quite a pain right now. It would be much easier for us if when we grep for a specific user or sql fragment if the entire sql line were returned instead of just the Select clause. Thanks for any help, Chris H. SQLAlchemy 0.5.8 PostgreSQL 8.3.x -- 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] Am I really doing this right?
I have a lot of questions, so bear with me. I've been having some doubts about whether I'm really using sqlalchemy in a good way. -- Is there any use case for having more than one session active in the same thread? Or does everyone use threadlocal sessions? If you bind different tables to different engines in the same metadata, can one session service them all? If not, this would be a use case for multiple sessions, which would make threadlocal sessions inconvenient, unless you made a different threadlocal session for each engine. Is it a common practice to pass the current session into the constructor of an ORM model? At this point, it can't be in a session yet, so Session.object_session(self) wont help you. So what if the constructor needs to do some queries, perhaps to find-or-create some related models? Is this why pylons uses threadlocal sessions? -- For a specific example, say you have a constructor for a Thing that can be in a Category, and you want to pass the category name into the Thing constructor, and expect the constructor to find or create the associated Category and increment the number of Things in that category (because there are too many to count). You'd need a session to do those queries. I couldn't find a way to get the mapper to do this without a session, but maybe I'm overlooking some of the capabilities of lazy='dynamic'. Is connection pooling the sqlalchemy way really what we want? Say for example I have a variety of projects running on the same machine, all using sqlalchemy. Since the connection pool is in the engine instance, there is no way these projects would be sharing information about the connection pool, so how could you know how many connections your server is actually generating? The problem gets worse if you're running those applications with multiple processes or instances, because then you really have no idea how many connections there could be. This has already lead to some serious problems for me. Would it be better to use a connection pooling solution external to my python applications? One that had shared knowledge of all of them? If I'm running a web application with fastcgi, I'm already affected by this framentation of connection pools, right? Considering fastcgi uses multiple processes. Should I set my pool size to the size I expect only a single process to use? I wouldn't expect a single process to use more than one connection at a time, if no threading is going on. -- 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: multi-table comparators / properties - how?
On Jun 2, 6:43 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 2, 2010, at 12:48 AM, SQLAlchemy User wrote: OK, I get the need for column_property, but the select is fairly complex and involves unions of the containing class (and it hasn't been defined yet). How do I get around this, and other questions below I think I got the SQL right (for a device with id = foo): SELECT v1.adminstate FROM ( (SELECT devices.adminstate, devices.processdate as insert_ts, devices.deviceid FROM devices WHERE devices.deviceid = foo) UNION (SELECT devicestates.adminstate, devicestates.insert_ts, devicestates.deviceid FROM devicestates WHERE devicestates.deviceid = foo)) AS v1 ORDER BY v1.insert_ts DESC LIMIT 1; You dont need to get into UNION here. My suggestion would be to avoid UNION at all costs, in fact, they perform poorly and are very cumbersome to work with. A correlated subquery here should do what you need: (i.e., when you say query(Device).filter(Device.curradminstate=='FOO'): select device.* from device where coalesce( (select devicestate.adminstate from devicestate where devicestate.insert_ts device.import_ts order by devicestate.insert_ts limit 1), device.adminstate ) = 'FOO' you'd need to verify the above works first (particularly the LIMIT inside the subquery, I know mysql can do it, not sure about PG, don't know your backend). property is: class Device(Base): ... device_subq = select([DeviceState.adminstate]).where(DeviceState.insert_ts device.import_ts).order_by(DeviceState.insert_ts).limit(1).as_scalar() Device.curradminstate = column_property( func.coalesce(device_subq, Device.adminstate) ) Thanks so much - this worked perfectly. -- 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] Questions about session
In my code, I am currently adding to the session in various modules (this is the same session since I'm importing it from my most prominent module). Some sample code would be: ## BEGIN CODE 1 ### Session = sessionmaker(bind=engine) session = Session() def addToTable(): Very simple SQLAlchemy function that populates the Student, Project and Supervisor tables. for student in students.itervalues(): session.add(student) session.flush() for project in projects.itervalues(): session.add(project) session.flush() for supervisor in supervisors.itervalues(): session.add(supervisor) session.flush() session.commit() And then again in a function in the same module: def monteCarloBasic(trials): The Monte-Carlo simulation will generate allocations for the list of students by randomly arranging the order for each trial. In the case of a student having more than one project for a given rank, the algorithm with randomly select one of them since it is given that all such projects are equally desireable to the student. session_id = 1 ident = 1 for trial in xrange(trials): for id in randomiseStudentKeys(True): stud_id = id student = students[id] if student.preferences: temp_alloc = SimAllocation(ident, session_id, stud_id) ranks = sorted(student.preferences.keys()) for rank in ranks: # Now we begin to try giving him/her a project proj = random.choice(list(student.preferences[rank])) if not (proj.allocated or proj.blocked or proj.own_project): student.allocated_project = proj student.allocated_proj_ref = proj.proj_id student.allocated_rank = rank allocSuccessActions(proj) temp_alloc.alloc_proj = proj.proj_id# ... we can set the allocated project details temp_alloc.alloc_proj_rank = rank session.add(temp_alloc) break ident += 1 # Increment the primary key session.add(temp_alloc) session.flush() session_id += 1 resetData() # Resets all data session.commit() ### END CODE 1 ### Later on I'm using this session to run some calculations on my data. For example: ### BEGIN CODE 2 sid = 4545 project_id_list = list(students[sid].preferences) for project_id in project_id_list gotcha = session.query(SimAllocation).filter(SimAllocation.student_id == sid).filter(PP.SimAllocation.alloc_proj == project_id).count() ## END CODE 2 ### Simply, this line counts how many times a certain student was allocated each project from his list when using the Monte-Carlo simulation from ### CODE 1 ### above. +++ Questions +++ 1. Is this the correct way to use sessions or am I sort of abusing them? 2. When should I close a session? 3. I got the following error after trying to use copy.deepcopy() on one of my dictionaries. File Main.py, line 106, in module OPTM.preoptAlloc(some_best) File /Optimisation.py, line 48, in preoptAlloc sid = projs_preopt[sim.alloc_proj_ref].proj_sup File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ attributes.py, line 158, in __get__ return self.impl.get(instance_state(instance), instance_dict(instance)) File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ attributes.py, line 377, in get value = callable_() File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ state.py, line 185, in __call__ attr.impl.key in unmodified File /opt/local/Library/Frameworks/Python.framework/Versions/2.6/ lib/python2.6/site-packages/SQLAlchemy-0.5.8-py2.6.egg/sqlalchemy/orm/ mapper.py, line 1864, in _load_scalar_attributes attribute refresh operation cannot proceed % (state_str(state))) sqlalchemy.exc.UnboundExecutionError: Instance Project at 0x24c5c50 is not bound to a Session; attribute refresh operation cannot proceed Is this something to do with the way I've been using the
[sqlalchemy] Re: Questions about session
Also: I'm using [Python 2.6.5] and [SQLAlchemy 0.5.8] Previously I was just shallow copying my dictionaries, and there were no issues then with my simulations. My dictionaries contain objects such that my students dictionary is basically: students[stud_id] = Student(stud_id, name, preferences,...) Student is mapped to an SQLAlchemy table. This is similar for many of my objects. I was trying to run some code to optimise my allocations and there was no real involvement with SQLAlchemy -- in that I wasn't actually dealing with any SQLAlchemy code. I understand that the shallow copy in Python just copies the references whereas deepcopy copies the entire object. Does that mean the deepcopied object is outside the session or something? Some help would be much appreciated. I have a feeling that the answer lies somewhere within the way deepcopy and session work but my head just can't put two-and-two together right now :( On Jun 3, 6:24 am, Az azfarul.is...@gmail.com wrote: In my code, I am currently adding to the session in various modules (this is the same session since I'm importing it from my most prominent module). Some sample code would be: ## BEGIN CODE 1 ### Session = sessionmaker(bind=engine) session = Session() def addToTable(): Very simple SQLAlchemy function that populates the Student, Project and Supervisor tables. for student in students.itervalues(): session.add(student) session.flush() for project in projects.itervalues(): session.add(project) session.flush() for supervisor in supervisors.itervalues(): session.add(supervisor) session.flush() session.commit() And then again in a function in the same module: def monteCarloBasic(trials): The Monte-Carlo simulation will generate allocations for the list of students by randomly arranging the order for each trial. In the case of a student having more than one project for a given rank, the algorithm with randomly select one of them since it is given that all such projects are equally desireable to the student. session_id = 1 ident = 1 for trial in xrange(trials): for id in randomiseStudentKeys(True): stud_id = id student = students[id] if student.preferences: temp_alloc = SimAllocation(ident, session_id, stud_id) ranks = sorted(student.preferences.keys()) for rank in ranks: # Now we begin to try giving him/her a project proj = random.choice(list(student.preferences[rank])) if not (proj.allocated or proj.blocked or proj.own_project): student.allocated_project = proj student.allocated_proj_ref = proj.proj_id student.allocated_rank = rank allocSuccessActions(proj) temp_alloc.alloc_proj = proj.proj_id # ... we can set the allocated project details temp_alloc.alloc_proj_rank = rank session.add(temp_alloc) break ident += 1 # Increment the primary key session.add(temp_alloc) session.flush() session_id += 1 resetData() # Resets all data session.commit() ### END CODE 1 ### Later on I'm using this session to run some calculations on my data. For example: ### BEGIN CODE 2 sid = 4545 project_id_list = list(students[sid].preferences) for project_id in project_id_list gotcha = session.query(SimAllocation).filter(SimAllocation.student_id == sid).filter(PP.SimAllocation.alloc_proj == project_id).count() ## END CODE 2 ### Simply, this line counts how many times a certain student was allocated each project from his list when using the Monte-Carlo simulation from ### CODE 1 ### above. +++ Questions +++ 1. Is this the correct way to use sessions or am I sort of abusing them? 2. When should I close a session? 3. I got the following error after trying to use copy.deepcopy() on one of my dictionaries. File Main.py, line 106, in module OPTM.preoptAlloc(some_best) File /Optimisation.py, line 48, in preoptAlloc sid = projs_preopt[sim.alloc_proj_ref].proj_sup File
