Hello Michael,
Thanks a lot for your help :)
I've followed your directions. it works.
Regarding the reserved column names (now I remember I saw this
yesterday) ... it does not happen because I'm restricting the field
names which appear in the SET clause, so that there's no collision
between what appears in the WHERE clause and what appears in the SET clause.
This is the entire recipe, for the records:
from sqlalchemy import create_engine, MetaData, bindparam
engine = create_engine('postgresql://localhost/sample'
<postgresql://localhost/sample%27>)
metadata = MetaData()
metadata.bind = engine
metadata.reflect()
t = metadata.tables['company_valuation_measures']
print(str(
t.update().values(trailing_pe='dummy').where(t.c.symbol==bindparam('symbol')).where(t.c.date==bindparam('date'))
))
It prints
UPDATE company_valuation_measures SET trailing_pe=%(trailing_pe)s WHERE
company_valuation_measures.symbol = %(symbol)s AND
company_valuation_measures.date = %(date)s
Cheers
Richard Gomes
http://rgomes.info
+44(77)9955-6813
On 11/07/13 19:06, Michael Bayer wrote:
On Jul 11, 2013, at 11:30 AM, Richard Gomes <[email protected]
<mailto:[email protected]>> wrote:
hello,
I've previously defined inserts and updates by hand in my
application, which is working fine, not using SQLAlchemy at the moment.
At this point, I'd like to employ SQLAlchemy to generate these
inserts and updates for me. And that's all.
I mean: just generate the queries for me. I'm /*not*/ going to
execute via SQLAlchemy at this point.
I did the test below:
engine = create_engine('postgresql://localhost/sample'
<postgresql://localhost/sample%27>)
metadata = MetaData()
metadata.bind = engine
t = metadata.tables['company_valuation_measures']
print(str(
t.update().values(trailing_pe=1.0).where(t.c.symbol=='dummy').where(t.c.date=='dummy')
))
I obtained:
UPDATE company_valuation_measures
SET trailing_pe=%(trailing_pe)s
WHERE company_valuation_measures.symbol = %(symbol_1)s AND
company_valuation_measures.date = %(date_1)s
The trouble is: field names are 'symbol' and 'date', not 'symbol_1',
not 'date_1'.
Could someone point out what I'm doing wrong?
"symbol_1" and "date_1" are generated bound parameter names, which
SQLAlchemy will match up to those "dummy" names which you passed in.
since you aren't interested in the automatic linkage of "dummy" to
binds and you're looking for binds that have a specific name, you can
use bindparam():
t.update().values(...).where(t.c.symbol==bindparam('symbol')).where(t.c.date==bindparam('date'))
however, the update() construct might complain on this as
insert()/update() both reserve the column name binds for the
SET/VALUES clause. you may need to name them something else (like
"symbol_where" or something like that).
--
You received this message because you are subscribed to a topic in the
Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/DtqNcKvr0Yo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
[email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
