Generated update command is alway schosing alternative field names

[Richard Gomes]

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')
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?

Thanks

-- Richard

[Michael Bayer]

"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).

[Simon King]

SQLAlchemy uses bind parameters when executing SQL - ie. the values
don't get substituted into the SQL string, but get passed to the
underlying DBAPI module separately. This is generally what you want,
as bind parameters avoid potential SQL-injection security holes.

There is a recipe on the wiki for getting the SQL string with the
parameters inserted, but you should read the warning at the top
carefully and fully understand the dangers:

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings

Hope that helps,

Simon

[Richard Gomes]

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')
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

--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Richard Gomes]

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