Re: [sqlalchemy] further restricting a query provided as raw sql
Michael Bayer wrote: Are the innards of in_ exposed anywhere for public consumption or should I avoid? from sqlalchemy.sql import column column(anything_you_want).in_(['a', 'b', 'c']) Thanks, that's exactly what I was looking for... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
Chris Withers wrote:
Michael Bayer wrote:
we have the in_() construct. It should be in the ORM and SQL expression
tutorials:
t1 = Table('mytable', metadata, Column('foo', String))
select([t1]).where(t1.c.foo.in_(['a', 'b', 'c']))
However, that requires table/column objects which I don't have.
Are the innards of in_ exposed anywhere for public consumption or should
I avoid?
from sqlalchemy.sql import column
column(anything_you_want).in_(['a', 'b', 'c'])
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
Michael Bayer wrote:
we have the in_() construct. It should be in the ORM and SQL expression
tutorials:
t1 = Table('mytable', metadata, Column('foo', String))
select([t1]).where(t1.c.foo.in_(['a', 'b', 'c']))
However, that requires table/column objects which I don't have.
Are the innards of in_ exposed anywhere for public consumption or should
I avoid?
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
Michael Bayer wrote: Any serious ideas or should I just go sulk in the corner? you have to use IN, and you have to generate the SQL every time based on the number of values you'd like to put into the IN. Does SA have any helper code anywhere for this? Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
Chris Withers wrote:
Michael Bayer wrote:
Any serious ideas or should I just go sulk in the corner?
you have to use IN, and you have to generate the SQL every time based on
the number of values you'd like to put into the IN.
Does SA have any helper code anywhere for this?
we have the in_() construct. It should be in the ORM and SQL expression
tutorials:
t1 = Table('mytable', metadata, Column('foo', String))
select([t1]).where(t1.c.foo.in_(['a', 'b', 'c']))
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
I can't reconcile your comment with mine... ...I want to limit the results returned to those matching a set of ids. This isn't about storing schemaless content ;-) Any serious ideas or should I just go sulk in the corner? Chris Michael Bayer wrote: yeah man , this is why we're all moving to mongodb :) On Apr 15, 2010, at 10:46 AM, Chris Withers wrote: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
you have to rewrite your SQL to support the number of values in the IN clause
for each parameter set.
On Apr 15, 2010, at 9:54 AM, Chris Withers wrote:
Hi All,
I have a big set of queries provided as raw sql like:
SELECT somestuff FROM somewhere
WHERE some_date = :from_date AND some_date = :to_date
That's fine, I just blat it at session.execute and provide from_date and
to_date in the params dict.
However, they now wany to supply something like:
SELECT somestuff FROM somewhere
WHERE some_date = :from_date AND some_date = :to_date
AND somefield in :values
Now, I have values as a sequence of strings that I need to provide,
but supplying in the params dict_ doesn't work:
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py,
line 753, in execute
clause, params or {})
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 874, in _execute_clauseelement
return self.__execute_context(context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor, context)
File
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) near ?: syntax error
u'AND somefield in ?' [('1', '2', '3')]
What should I do?
cheers,
Chris
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
yeah man , this is why we're all moving to mongodb :) On Apr 15, 2010, at 10:46 AM, Chris Withers wrote: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] further restricting a query provided as raw sql
Excerpts from Chris Withers's message of Thu Apr 15 11:46:05 -0300 2010: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris My answer will be generic since I don't know id SA provide a better way to deal with it (I haven't face this situation yet). You will have to provide the values not as binded parameters but hardcoded instead: SELECT somestuff FROM somewhere WHERE some_date = :from_date AND some_date = :to_date AND somefield in (%s) % (,.join([str(x) for x in a])) if you don't like this kind of hack, depending on your database, you can create a temp table, insert all the values in it and join with your real table. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] further restricting a query provided as raw sql
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Mariano Mara Sent: 15 April 2010 16:20 To: sqlalchemy Subject: Re: [sqlalchemy] further restricting a query provided as raw sql Excerpts from Chris Withers's message of Thu Apr 15 11:46:05 -0300 2010: Michael Bayer wrote: you have to rewrite your SQL to support the number of values in the IN clause for each parameter set. Hmm :'( While my code knows the number of values, they don't, and it may vary from when they write the SQL to when that SQL gets executed by my code... Chris My answer will be generic since I don't know id SA provide a better way to deal with it (I haven't face this situation yet). You will have to provide the values not as binded parameters but hardcoded instead: SELECT somestuff FROM somewhere WHERE some_date = :from_date AND some_date = :to_date AND somefield in (%s) % (,.join([str(x) for x in a])) if you don't like this kind of hack, depending on your database, you can create a temp table, insert all the values in it and join with your real table. ...and make sure that you properly escape all your values to prevent SQL injection. I guess another option would be to detect when lists are passed in, then replace ':values' with ':value0, :value1, :value2' etc. in the SQL. As long as you can be sure that the string ':values' doesn't appear anywhere else in the SQL, this wouldn't be too bad. Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
