I thought this could be of interest ot the mailing list:
def select(self, table, index=None, *args, **kwargs):
'''
Helper function which eases generation of SELECT statements
using indexes.
Arguments
---------
table -- an instance of sqlalchemy.schema.Table
index -- an optional Primary Key or Alternative Key
Returns
-------
``sqlalchemy.sql.expression.Select``
Example
-------
>>> url = 'postgresql://localhost/sample'
>>> import dbaware
>>> db = dbaware.api.dbaware()
>>> conn = db.connection(url)
>>> table = db.metadata(conn, table='eod')
>>> sql = db.select(table, index=table.primary_key,
for_update=True)
>>> print(type(sql))
<class 'sqlalchemy.sql.expression.Select'>
>>> print(str(sql))
SELECT eod.o, eod.h, eod.l, eod.c, eod.v, eod.i, eod.ac
FROM eod
WHERE symbol = %(symbol)s AND date = %(date)s FOR UPDATE
'''
import sqlalchemy
if index is not None:
fields = list()
for col in table.columns:
if not index.columns.contains_column(col):
fields.append(col)
expr = None
for field in index.columns:
col = sqlalchemy.sql.expression.column(field.name)
bind = sqlalchemy.sql.expression.bindparam(field.name)
eq = (col == bind)
if expr is None:
expr = eq
else:
expr = sqlalchemy.sql.expression.and_(expr, eq)
result = sqlalchemy.select(fields, *args, **kwargs).where(expr)
else:
result = sqlalchemy.select([table], *args, **kwargs)
return result
def update(self, table, index=None, *args, **kwargs):
'''
Helper function which eases generation of UPDATE statements
using indexes.
Arguments
---------
table -- an instance of sqlalchemy.schema.Table
index -- an optional Primary Key or Alternative Key
Returns
-------
``sqlalchemy.sql.expression.Update``
Example
-------
>>> url = 'postgresql://localhost/sample'
>>> import dbaware
>>> db = dbaware.api.dbaware()
>>> conn = db.connection(url)
>>> table = db.metadata(conn, table='eod')
>>> sql = db.update(table, index=table.primary_key)
>>> print(type(sql))
<class 'sqlalchemy.sql.expression.Update'>
>>> print(str(sql))
UPDATE eod SET o=%(o)s, h=%(h)s, l=%(l)s, c=%(c)s, v=%(v)s,
i=%(i)s, ac=%(ac)s WHERE symbol = %(symbol)s AND date = %(date)s
'''
import sqlalchemy
if index is not None:
import collections
fields = collections.OrderedDict()
for col in table.columns:
if not index.columns.contains_column(col):
fields[col.name] = 'dummy'
expr = None
for field in index.columns:
col = sqlalchemy.sql.expression.column(field.name)
bind = sqlalchemy.sql.expression.bindparam(field.name)
eq = (col == bind)
if expr is None:
expr = eq
else:
expr = sqlalchemy.sql.expression.and_(expr, eq)
result = sqlalchemy.update(table, *args,
**kwargs).values(fields).where(expr)
else:
result = sqlalchemy.update(table, *args, **kwargs)
return result
Richard Gomes
http://rgomes.info
+44(77)9955-6813
On 11/07/13 20:12, Richard Gomes wrote:
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 <rgomes.i...@gmail.com
<mailto:rgomes.i...@gmail.com>> 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
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
