Thank you, Mike!
I’ve tried to implement what we talked about above, but had a few problems.
First, I was unable to use MySQLdb
<http://mysql-python.sourceforge.net/MySQLdb.html> because of compilation
errors; instead I’ve used pymysql <https://github.com/PyMySQL/PyMySQL> for
a while now. When I tried to run the test code from your previous answer
<https://groups.google.com/d/msg/sqlalchemy/7N6zH3T94Rc/BHg0V8kbBgAJ> I
received a SQL syntax error near WHERE. It looks like the FROM is missing.
As for “fails” I was mistaken. The conditional insert should always
succeed, but may not insert a row into the table. While this maintains data
consistency, it’s not quite what I need. It’ll do for now though.
My current working solution is the following hack which I need to revisit
again later:
# Create a proxy object to return to the caller.
token = Token(
id=uuid4(),
user=user_id,
client_sig=sigstring,
)
# If I don't expunge then the token is automatically added/inserted?
# I thought I'd always have to dbsession.add().
dbsession.expunge(token)
# Now issue the SQL statement much like the on from my initial question.
sql = (
"INSERT INTO tokens(id, user_id, client_sig) "
"SELECT '" + token.id + "', '" + token.user_id + "', '" +
token.client_sig + "' "
"FROM dual "
"WHERE NOT EXISTS "
"(SELECT * FROM tokens WHERE user_id='" + token.user_id + "' AND
client_sig='" + token.client_sig + "')"
)
dbsession.execute(sql)
# Return the proxy object to the Pyramid view function.
return token
I realize that this is a poor solution because the returned proxy object
may in rare cases not represent what's committed to the db. However, that's
easier to handle than committing a duplicate. I would also prefer SQLA
functions over raw SQL code.
Jens
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
