On 10/03/2016 07:41 PM, Andrew Kowalik wrote:
SQLAlchemy == 1.0.3
mysql-python == 1.2.5
I have been playing around with baked queries and have a couple
observations, one which is problem my lack of SQLA knowledge and the
other a possible feature.
1) .in_ and bindparam
I have a query that I am baking that uses in_ with a python list. For
example
baked_query += lambda q: q.filter(Model.attr1.in_([bindparam('attr1')])
that's not a supported argument form. in_() generates SQL like this:
col IN (?, ?, ?, ?)
as you can see, the physical structure of the SQL is dependent on how
many bound parameter items are in the list. So a single bound parameter
cannot accommodate for this. I *think* that some of the MySQL and
Postgresql drivers do attempt to receive a single list as a bound
parameter and expand within IN() in some of these cases but SQLAlchemy
knows nothing about that and it's not clear how those drivers accomplish
this without parsing the SQL statement.
If you are trying to work with the underlying driver's auto-expansion of
IN, I think at the very least you would not want to use
in_([bindparam()]), you'd want to use in_(bindparam()) and I'm not sure
SQLAlchemy allows that. You'd be better off building up a custom
construct for now short of patching SQLAlchemy.
Generating the query in python succeeds but fails in mysql as bindparam
is not being converted to a valid representation for the list. "operand
should contain 1 column(s)". I tried to manually convert the python list
into a string of comma separated values, the query runs but I am doing
something stupid so the query returns zero results.
So because the physical structure of the IN statement is affected by the
length of the argument list, this is not an appropriate target for
something that would be "baked". Unless the underlying database driver
is translating for a single bound parameter (and SQLAlchemy is letting
you do that), you're going to get weird errors.
2) Result class currently does not support count(). I have a use case
where I want to allow the possibility to paginate a baked query and with
it, gather the total number of records. Without supporting count I am
having to call _as_query() and then call count on the returned query.
Result sets don't work that way. Relational databases do not send out
the count of rows that would be expected separately, they simply start
sending out rows and you have no idea how many there will be (nor does
the database) until it has no more to send. So for count(), fetch the
entire result set and do a length. Obviously this does not work for
pagination. For pagination you at the very least need to do
LIMIT/OFFSET at the SQL level and depending on platform and specifics
you may want to use
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/WindowedRangeQuery.
Just some thoughts that I did not see brought up before in my searches.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
