Re: Binding and custom types...

Douglas Mayle wrote:
> Hello all,
>     I was wondering if someone could help me figure out an issue I'm 
> having using psycopg2 by way of SQLAlchemy because of my bind 
> parameter.  I'm running something like this:
> 
> cursor.execute('UPDATE almanacs SET location=%(location)s, 
> modified=CURRENT_TIMESTAMP WHERE almanacs.id = %(almanacs_id)s',
> {'almanacs_id': 2, 'location': 
> "ST_TRANSFORM('SRID=900913;01010000007f2ef1fb0b804640cdfc51710f0059c0', 
> 2163)"})
> 
> 
> What's coming includes this: SET 
> location=E'ST_TRANSFORM(''SRID=900913;010100000033c4b12e6e5544408f554acff48652c0'', 
> 2163)'
> 
> Where I need it to be SET 
> location=ST_TRANSFORM('SRID=900913;010100000033c4b12e6e5544408f554acff48652c0', 
> 2163)
> 
> In other words, it's a SQL function, not a text string.  Is there anyway 
> to call execute so that it won't escape my string?
> 
> Thanks,
> Douglas Mayle

Depending on what version of SQLAlchemy you're using, the func 
expression might be what you're looking for:
http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/expressions.html#sqlalchemy.sql.expression.func

Maybe something like:

cursor.execute('UPDATE almanacs SET location=%(location)s,
modified=CURRENT_TIMESTAMP WHERE almanacs.id = %(almanacs_id)s',
{'almanacs_id': 2, 'location':
sqlalchemy.sql.expression.func.st_transform('SRID=900913;01010000007f2ef1fb0b804640cdfc51710f0059c0', 

2163)})

Jeremy