Are you supposed to be able to use a sub-select in the WHERE
clause of an UPDATE through SQLAlchemy? It generated this SQL for me:
UPDATE passwords
SET password=?
WHERE passwords.user_id = SELECT users.id FROM users WHERE users.name = ?
Note the lack of parenthesis around the sub-select. Test case
for this, along with the output with echo=True, included below. I
just upgraded to the latest code from SVN, 1347. Python 2.4.1,
testing against SQLite 3.1.2. When I put the parenthesis around the
sub-select, fill in the parameters, and submit that same query to
SQLite it works as expected. I've only tested in SQLite, but I
suspect this bug will occur with other RDBMSes.
(Also note that I'm doing this because SQLite doesn't support
something like UPDATE ... WHERE users.id == passwords.user_id AND
users.name == 'bob'. Apparently you cannot do a join like that in
SQLite's UPDATE statement.)
Thanks,
Dale Sedivec
######################################################################
# Test case (requires SQLite):
from sqlalchemy import *
engine = create_engine("sqlite://filename=:memory:", echo=True)
users = Table("users", engine,
Column("id", Integer, primary_key=True),
Column("name", String, unique=True, nullable=False))
users.create()
passwords = Table("passwords", engine,
Column("user_id", Integer, ForeignKey("users.id"),
primary_key=True),
Column("password", String, nullable=False))
passwords.create()
users.insert().execute(name="bob")
bob_id = users.select(users.c.name == "bob").execute().fetchone().id
passwords.insert().execute(user_id=bob_id, password="foo")
subselect = select([users.c.id], users.c.name == "bob")
passwords.update(passwords.c.user_id == subselect).execute(password="XXX")
# Never get here because of exception thrown by the update above.
row = passwords.select(passwords.c.user_id == bob_id).execute().fetchone()
assert row.password == "XXX"
######################################################################
# Output:
#
# [2006-04-28 02:30:16,635] [engine]:
# CREATE TABLE users(
# id INTEGER NOT NULL PRIMARY KEY,
# name TEXT NOT NULL
# )
#
# [2006-04-28 02:30:16,636] [engine]: None
# [2006-04-28 02:30:16,639] [engine]: CREATE UNIQUE INDEX ux_name ON users
(name)
# [2006-04-28 02:30:16,639] [engine]: None
# [2006-04-28 02:30:16,646] [engine]:
# CREATE TABLE passwords(
# user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(id),
# password TEXT NOT NULL
# )
#
# [2006-04-28 02:30:16,647] [engine]: None
# [2006-04-28 02:30:16,652] [engine]: INSERT INTO users (name) VALUES (?)
# [2006-04-28 02:30:16,653] [engine]: ['bob']
# [2006-04-28 02:30:16,658] [engine]: SELECT users.id, users.name
# FROM users
# WHERE users.name = ?
# [2006-04-28 02:30:16,659] [engine]: ['bob']
# [2006-04-28 02:30:16,663] [engine]: INSERT INTO passwords (user_id, password)
VALUES (?, ?)
# [2006-04-28 02:30:16,663] [engine]: [1, 'foo']
# [2006-04-28 02:30:16,668] [engine]: UPDATE passwords SET password=? WHERE
passwords.user_id = SELECT users.id
# FROM users
# WHERE users.name = ?
# [2006-04-28 02:30:16,669] [engine]: ['XXX', 'bob']
# Traceback (most recent call last):
# File "./update_bug.py", line 21, in ?
# passwords.update(passwords.c.user_id == subselect).execute(password="XXX")
# File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 473, in execute
# File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 378, in execute
# File "build/bdist.linux-i686/egg/sqlalchemy/sql.py", line 355, in execute
# File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 646, in
execute_compiled
# File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 641, in proxy
# File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 692, in execute
# File "build/bdist.linux-i686/egg/sqlalchemy/engine.py", line 712, in
_execute
# sqlalchemy.exceptions.SQLError: (OperationalError) near "SELECT": syntax
error 'UPDATE passwords SET password=? WHERE passwords.user_id = SELECT
users.id \nFROM users \nWHERE users.name = ?' ['XXX', 'bob']
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
