[sqlalchemy] outerjoin constructor throws exception from 0.3.9. Bug or user error?
Hi,
I have the following two tables (in MySql):
CREATE TABLE `A` (
`xkey` varchar(200) NOT NULL,
`yval` int(11) default NULL,
PRIMARY KEY (`xkey`)
) ENGINE=InnoDB;
and
CREATE TABLE `B` (
`xkey` varchar(200) NOT NULL default '',
`s` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`xkey`,`s`)
) ENGINE=InnoDB;
The following statement worked fine till 0.3.8 , and from 0.3.9 throws
the exception:
oj=outerjoin(A, B, and_(A.c.xkey==B.c.xkey, B.c.s=='yes'))
Traceback (most recent call last):
File join.py, line 13, in ?
oj=outerjoin(A, B, and_(A.c.xkey==B.c.xkey, B.c.s=='yes'))
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 117, in outerjoin
return Join(left, right, onclause, isouter = True, **kwargs)
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 2256, in __init__
self._init_primary_key()
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 2287, in _init_primary_key
if p.references(c) or (c.primary_key and not p.primary_key):
AttributeError: '_BindParamClause' object has no attribute
'primary_key'
Note, if B.s were not an enum but a varchar, it would have worked.
1) Is this a usage error or a bug?
2) If the latter. How can i work around it?
TIA
--~--~-~--~~~---~--~~
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] minimizing number of connections created
Hi,
the following code creates 2 connections.
Is there way to make it 1, either by making the Table share
connection1 or
by releasing the connection Table used after it loaded the metadada?
TIA
connect_s = 'mysql://%s:[EMAIL PROTECTED]/%s' % (user, pw, server, db)
engine = sqlalchemy.create_engine(connect_s)
connection1 = engine.connect() #connection proxy
# engine.echo=True # for debug
md = sqlalchemy.BoundMetaData(engine)
table = sqlalchemy.Table('name', md, autoload = True)
--~--~-~--~~~---~--~~
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] Newbie question: Too many connections
Hi, I keep getting The error: DBAPIError: (Connection failed) (OperationalError) (1040, 'Too many connections'). At no time do I really need more than a few connections so I am doing something basically wrong. From reading the doc I concluded that pooling in the simple cases is automatic. I must have misunderstood. Questions: 1) What is the suggested method to debug such a problem? E.g. retrieve the number of open connections. 2) When is a connection closed? For that matter , when is it open and when taken from the pool? TIA --~--~-~--~~~---~--~~ 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] Outerjoin with a subset of columns
Hi, Note the following code: oj=outerjoin(s,f) r1=select([oj]).execute().fetchall() r2=select([oj.left.c.id, oj.right.c.status]).execute().fetchall() The first select executes a LEFT OUTER JOIN and returns all columns. I wanted only 2 specific columns, so I tried the 2nd select. That does not execute a LEFT OUTER JOIN, though. How do I achieve my goal? TIA --~--~-~--~~~---~--~~ 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: Outerjoin with a subset of columns
It certainly did help . many thanks On Aug 15, 4:41 pm, King Simon-NFHD78 [EMAIL PROTECTED] wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of mc Sent: 15 August 2007 14:23 To: sqlalchemy Subject: [sqlalchemy] Outerjoin with a subset of columns Hi, Note the following code: oj=outerjoin(s,f) r1=select([oj]).execute().fetchall() r2=select([oj.left.c.id, oj.right.c.status]).execute().fetchall() The first select executes a LEFT OUTER JOIN and returns all columns. I wanted only 2 specific columns, so I tried the 2nd select. That does not execute a LEFT OUTER JOIN, though. How do I achieve my goal? You want to use the 'from_obj' parameter for select. Something like: select([s.c.id, f.c.status], from_obj=[oj]) Hope that helps, Simon --~--~-~--~~~---~--~~ 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: right outer join (newbie)
I don't need the ROJ either, just didn't understand how it knows by itself to make it a LEFT :-) On Aug 2, 6:52 pm, Jonathan Ellis [EMAIL PROTECTED] wrote: There are good reasons to support full outer join. Some are suggested in the comments to that one article. :) I don't miss right joins though, I find left join more natural. On 8/1/07, Michael Bayer [EMAIL PROTECTED] wrote: we currently dont have a right outer join. but this is not by accident...theres really no use case for a right outer join, which are more easily written as left outer joins. some good articles on whats wrong with RIGHT outer join as well as FULL outer join: http://weblogs.sqlteam.com/jeffs/archive/2006/03/14/9289.aspx http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer- Joins.aspx?x=1 On Aug 1, 2007, at 5:56 PM, mc wrote: Hi, I read in the docs about joins and saw an example for left outer join, though I didn't understand where the left was specified. How is a right outer join specified? --~--~-~--~~~---~--~~ 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: Newbie question about transactions
Thanks again.
Let me see if I understood this correctly.
What I tried to do initially is use implicit connections that I have
no control over. The transaction was started on my explicit connection
but the inserts used a different connection from the pool and
therefore were not aware of the transaction. Right?
On Jul 29, 12:12 am, Michael Bayer [EMAIL PROTECTED] wrote:
discussion starts here:
http://www.sqlalchemy.org/docs/dbengine.html#dbengine_connections
covers some options available to you including using a thread local
connection.
On Jul 28, 2007, at 5:07 PM, mc wrote:
Thanks.
Can you point me to the place in the doc where this is discussed?
I was under the impression that I had one connection only in this
scenario.
TIA
On Jul 28, 11:09 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 28, 2007, at 2:54 PM, mc wrote:
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
the statements must be executed relative to the connection which
contains the transaction:
cn.execute(insert(t), id=332)
--~--~-~--~~~---~--~~
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] Newbie question about transactions
In the following code, I intended that nothing will be inserted into
the table because the transaction fails and is rolled back. What
actually happens is that the first insert is not rolled back because
it s committed by itself.
What is the correct way to achieve my goal?
TIA
from sqlalchemy import *
db=create_engine('mysql://user:pw@localhost/db')
cn= db.connect()
cn.execute(DROP TABLE IF EXISTS test)
cn.execute(CREATE TABLE test (`id` bigint(20) NOT NULL , PRIMARY KEY
(`id`))
ENGINE=InnoDB)
db.echo=True
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
--~--~-~--~~~---~--~~
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: Newbie question about transactions
Thanks.
Can you point me to the place in the doc where this is discussed?
I was under the impression that I had one connection only in this
scenario.
TIA
On Jul 28, 11:09 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jul 28, 2007, at 2:54 PM, mc wrote:
md=BoundMetaData(db)
t=Table('test', md, autoload = True)
trans = cn.begin()
try:
insert(t).execute(id=332)
insert(t).execute(id=332)
trans.commit()
except:
trans.rollback()
raise
the statements must be executed relative to the connection which
contains the transaction:
cn.execute(insert(t), id=332)
--~--~-~--~~~---~--~~
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: last_inserted_ids()
Thanks On Jul 9, 7:44 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 9, 2007, at 3:40 AM, mc wrote: I have a followup question: You posted above a function for retrieving the values by using another select query. The fact is that regardless of the differences between SQLalchemy 0.3.6 to 0.3.9, MySQL returns the value of the non-primary-key autoincrement column, otherwise 0.3.6 would not have that data without querying the MySQL server again (at least there is no query according to the debug log). Will there be another method to get it? result.cursor.lastrowid, as always this might change to result.lastrowid in 0.4 since the cursor is closed immediately after INSERT --~--~-~--~~~---~--~~ 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: last_inserted_ids()
I am using 0.3.6 and reading the 0.3 doc. The problem is that actually I need it the way it works for me now, i.e. returning the auto_increment fields and not my primary key fields. Will upgrading sqlalchemy break this (undocumented) feature? On Jul 4, 12:58 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 3, 4:01 pm, mc [EMAIL PROTECTED] wrote: What is the correct documentation for it? The docs say it returns the primary key but for now I see it returns an auto increment field (which is not part of the key). I doubt thats true. but also, this function has been reworked to be a lot stronger in 0.3.9; try the trunk. --~--~-~--~~~---~--~~ 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: Creating where clauses programatically
I must be missing something Doesn't that evaluate the == expressions and result in an expression like and_(True, True, False, ...) ? On Jul 4, 1:04 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 3, 4:06 pm, mc [EMAIL PROTECTED] wrote: I managed to avoid this issues because for select and insert I can use dictionaries as arguments of execute(). For update, I must create a where clause from a list of conditions I have as a dictionary. How do I do this programatically? and_(*[table.c[key]==mydict[key] for key in mydict]) --~--~-~--~~~---~--~~ 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 lock using sqlalchemy
Thank you very much, Michael. It was very clear and helpful and help set my mind straight on the issue. One of the things that confused me was the term optimistic locking (used elsewhere, not by yourself). In the optimistic approach, there is no locking at all. On Jul 1, 11:28 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jul 1, 2007, at 11:36 AM, mc wrote: Thanks. You say you are not a fan. What is the preferred way to solve the problem I described? optimistically. i would ensure that appropriate constraints are placed upon the table such that two conflicting INSERT statements would result in one of them raising an exception; when the second client encounters this exception, it starts over again and re-SELECTs the row from the table. The case for the optimistic approach is one of how often will a confict reasonably take place ? I think conficts on INSERT, for all the use cases I can think of (such as inserting unique keywords), are exceedingly rare, since they correspond usually to end-user activities, where two users come up with the same new information at the exact same time. Even if you did have thousands of users hammering an application where many are expected to come up with the exact same information (such as, everyone is going to tag their photos with kids and pets which get added to a table of unique keywords), once a fair degree of all that unique data is inserted, then you'd no longer have conflicts, and the pessimistic locking would then add tremendous and almost always unnecessary latency to an applciation that has thousands of users hammering it. If I were running a really big website, id even try pre-populate the table with expected values before going live. on the other hand, if end users are not the issue, and you are instead writing an application that expects to have INSERT conflicts because it spawns a huge number of worker threads that are all operating upon the same data, locking the whole table for each INSERT will completely defeat the purpose of having worker threads, and you might as well not use them. --~--~-~--~~~---~--~~ 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] last_inserted_ids()
What is the correct documentation for it? The docs say it returns the primary key but for now I see it returns an auto increment field (which is not part of the key). --~--~-~--~~~---~--~~ 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] Creating where clauses programatically
I managed to avoid this issues because for select and insert I can use dictionaries as arguments of execute(). For update, I must create a where clause from a list of conditions I have as a dictionary. How do I do this programatically? --~--~-~--~~~---~--~~ 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 lock using sqlalchemy
Thanks. You say you are not a fan. What is the preferred way to solve the problem I described? On Jul 1, 3:22 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 30, 7:05 pm, mc [EMAIL PROTECTED] wrote: My problem is as follows: I select for something. If that row does not exists, I need to insert it. I want to avoid the situation where some other client inserts the row after I have selected (and found out it does not exist) but before I insert it myself. In direct SQL, I would LOCK the table for WRITE before the select, and release the locks after the insert. Can that be done through SQLAlchemy? im not a fan of pessimistic locking but you can issue LOCK TABLE on the connection easily enough. --~--~-~--~~~---~--~~ 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 lock using sqlalchemy
Thank's all for the replies but I am not sure it solves my specific
problem.
As I understand, FOR UPDATE locks the rows that were selected.
My problem is as follows:
I select for something.
If that row does not exists, I need to insert it.
I want to avoid the situation where some other client inserts the row
after I have selected (and found out it does not exist) but
before I insert it myself.
In direct SQL, I would LOCK the table for WRITE before the select, and
release the locks after the insert.
Can that be done through SQLAlchemy?
On Jun 29, 4:38 pm, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 29, 2007, at 4:55 AM, Andreas Jung wrote:
At least the select() method has an optional parameter 'lockmode'.
You might check the docs and the release notes.
lockmode is specific to the ORM. these days it looks like
query.with_lockmode('read').filter(...).. it results in FOR UPDATE
statements.
--~--~-~--~~~---~--~~
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 lock using sqlalchemy
Hi, I need to lock a table for WRITE. The scenario is that I have a SELECT followed by an INSERT, where the INSERT depends on the outcome of the SELECT. I have to make sure that no other process is modifying the table after my SELECT but before my INSERT. 1) How do i do it via SQLAlchemy ? 2) Is there any other method (not via locks) to achieve my goal? TIA --~--~-~--~~~---~--~~ 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: flush and transactions, doesn't roll back
Thanks, that was it. On Jun 25, 5:25 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 25, 2007, at 4:09 AM, mc wrote: I have the following piece of code: transaction = session.create_transaction() try: # Do some work here that might fail p=P() p.ID=333 session.save(p) session.flush() # Do some more work here that might fail p1=P() p1.ID=333 session.save(p1) session.flush() # Success, commit everything transaction.commit() except: # Make sure the transaction is rolled back ... transaction.rollback() # ... then propagate the error upwards to be handled elsewhere raise The 2nd flush fails (because there is already an ID - 333) and then rollback is called, but the first row entered remains in the database. Wasn't rollback supposed to remove it ensure you are using InnoDB tables if youre using MySQL. --~--~-~--~~~---~--~~ 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] flush and transactions, doesn't roll back
I have the following piece of code: transaction = session.create_transaction() try: # Do some work here that might fail p=P() p.ID=333 session.save(p) session.flush() # Do some more work here that might fail p1=P() p1.ID=333 session.save(p1) session.flush() # Success, commit everything transaction.commit() except: # Make sure the transaction is rolled back ... transaction.rollback() # ... then propagate the error upwards to be handled elsewhere raise The 2nd flush fails (because there is already an ID - 333) and then rollback is called, but the first row entered remains in the database. Wasn't rollback supposed to remove it? --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
