Re: [sqlalchemy] too many SQL variables in "in_"
I had a mystical appearence!!! I will put in a dictionary the parameters of searching used in the GUI. If I would to sort the record, the script will re-do the search, which at this time will use only a few parametrs, and I'll put an ORDER_BY in the query. The script will write dinamically as a string the comand in sqlalchemy sintax, that thank to eval() comand will be run. If I have all the records of the table present on my GUI, I will only perform a query as id > 0 with ORDER BY statement. I will try and I will inform you if I have success o complete defeat. :) Thanks a lot to put me on the right path. Luca -- 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/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
> On 13 Sep 2014, at 11:25, pyArchInit ArcheoImagineers > wrote: > > Il giorno giovedì 11 settembre 2014 18:39:24 UTC+2, Jonathan Vanasco ha > scritto: > i once thought about extending SqlAlchemy to handle this issue behind the > scenes, but each database treats `IN()` differently. for example: oracle > maxes out at a number of elements, while mysql maxes out based on the size of > the overall statement (which is configured on the server). it's too much > work to limit this in sqlalchemy, as these limits change across servers. [ i > forget what postgres maxed out on, i think it was a hard number too.] > > the workaround I used was to just build a query-base, and then run multiple > selects with a single `IN` within a for-loop which appends to a list. i > found that performance to be much better than chaining multiple `IN()` with > `OR` > > My problem is to find a dataset of more than 999 records and sort all through > ORDER BY statement. How can I use multiple selects and order all records? You can try a different approach, such as creating a (temporary table) which you fill with all your ids, and then do a SQL statement with something like WHERE id IN (SELECT id FROM temp_table); That bypasses any limits in the IN operator. Wichert. -- 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/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
Il giorno giovedì 11 settembre 2014 18:39:24 UTC+2, Jonathan Vanasco ha scritto: > > i once thought about extending SqlAlchemy to handle this issue behind the > scenes, but each database treats `IN()` differently. for example: oracle > maxes out at a number of elements, while mysql maxes out based on the size > of the overall statement (which is configured on the server). it's too > much work to limit this in sqlalchemy, as these limits change across > servers. [ i forget what postgres maxed out on, i think it was a hard > number too.] > > the workaround I used was to just build a query-base, and then run > multiple selects with a single `IN` within a for-loop which appends to a > list. i found that performance to be much better than chaining multiple > `IN()` with `OR` > My problem is to find a dataset of more than 999 records and sort all through ORDER BY statement. How can I use multiple selects and order all records? I try to explain my problem: I have a gui where I display all the records on my table. Then I make a search and I find 1500 records. Then I want to order the current dataset (1500 records) by some parameter, so I pass to my function the list of 1500 records ID which will be found the records through the IN() query with the ORDER BY statement. Now I don't understand how I can find more than 999 records in sqlite and sort the records through ORDER BY. What strategy I can use? Suggestions? Thanks a lot! Luca -- 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/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
i once thought about extending SqlAlchemy to handle this issue behind the scenes, but each database treats `IN()` differently. for example: oracle maxes out at a number of elements, while mysql maxes out based on the size of the overall statement (which is configured on the server). it's too much work to limit this in sqlalchemy, as these limits change across servers. [ i forget what postgres maxed out on, i think it was a hard number too.] the workaround I used was to just build a query-base, and then run multiple selects with a single `IN` within a for-loop which appends to a list. i found that performance to be much better than chaining multiple `IN()` with `OR` -- 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/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
On Sep 11, 2014, at 1:53 AM, pyArchInit ArcheoImagineers wrote: > Hi, > if I create many "or" request with a little number of id (list populated with > 10 values), the script return to me this message: Expression tree is too > large (maximum depth 1000) > > So, It's possibile there is a limit for sqlite? This is a big problem for > using sqlalchemy/sqlite. > > I cannot linking many selection because I want to order the query, so I > suppose I must to do a single select query. It's correct? I'm assuming pysqlite is raising that. which would be the end of the line for that approach, you need to break it up into individual SELECT statements or write your data to a temp table and JOIN to that. -- 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/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
Hi, if I create many "or" request with a little number of id (list populated with 10 values), the script return to me this message: Expression tree is too large (maximum depth 1000) So, It's possibile there is a limit for sqlite? This is a big problem for using sqlalchemy/sqlite. I cannot linking many selection because I want to order the query, so I suppose I must to do a single select query. It's correct? Best regards and thanks a lot Luca -- 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/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
Hi Michael,
I use my method for every tables of my DB so I pass the mapper class, the
name of id, etc, etc. and through a string I build the cmd to pass to the
eval function.
I'll try your method.. Thanks a lot.
Best regards
Luca
Il giorno martedì 9 settembre 2014 22:58:59 UTC+2, Michael Bayer ha scritto:
>
>
> On Sep 9, 2014, at 4:45 PM, pyArchInit ArcheoImagineers <
> pyarc...@gmail.com > wrote:
>
> Hi Michael and thanks a lot.
>
>
> Il giorno venerdì 5 settembre 2014 18:23:31 UTC+2, Michael Bayer ha
> scritto:
>>
>> you batch out the values to be used in the IN, then one of two choices:
>> my preference is to run separate SELECT statements, using IN with each
>> batch. If you really can’t do that, you can combine the batches of IN
>> groups with an OR: “x IN (batch1) OR x IN (batch2) …”
>>
>
>
>
>
> O spent 2 day to find a solutions:
> I make write to python this string:
>
> cmd_str = "session.query(MAPPCLASS).filter(or_(MAPPCLASS.id_invmat.in_([1,
> 2, 3, 4])).(MAPPCLASS.id_invmat.in_([5, 6, 7,
> 8]))).order_by(asc(MAPPCLASS.id_invmat)).all()"
>
> But I receive this error..
>
> Neither 'BinaryExpression' object nor 'Comparator' object has an attribute
> ‘or_'
>
>
> there’s be somewhere you’re calling .or_(), which is incorrect,
> or_() is standalone.
>
> not sure what “cmd_str” is about, you can just do this directly:
>
> args = []
> or_args = []
> while args:
> chunk = args[0:1000]
> or_args.append(MAPCLASS.id.in_(chunk))
>
> session.query(MAPCLASS).filter(or_(*or_args))
>
>
>
>
>
>
> a little suggestion to the right sintax to pass to the eval?
>
>
>>
>> keep in mind when you send enormous strings to your database, that places
>> a burden on the query system. Oracle (which I assume you’re using) also
>> caches these queries
>>
>
>
> I'm using sqlite DB.
>
> Best regards and thanks a lot.
> Luca
>
>
>>
>>
>>
>>
>> On Sep 5, 2014, at 2:30 AM, mando wrote:
>>
>> Hi to all,
>>
>> I wrote a method like this to reuse the code for many tables at the same
>> time[0]
>>
>> But, with more than 1000 records sqlite doesn't accepts the amount of id
>> inside .in_(id_list)
>>
>> How can I filter, split or can manage it?
>>
>> Thanks a lot and best regards,
>> Luca
>>
>>
>> [0]
>> def query_sort(self,id_list, op, to, tc, idn):
>> self.order_params = op #sorting parameters
>> self.type_order = to #asc or desc
>> self.table_class = tc #the name of the mapper class
>> self.id_name = idn #the name of the id
>> filter_params = self.type_order + "(" + self.table_class + "." +
>> self.order_params[0] + ")"
>> for i in self.order_params[1:]:
>> filter_temp = self.type_order + "(" + self.table_class + "." + i + ")"
>>
>> filter_params += ", "+ filter_temp
>>
>> Session = sessionmaker(bind=self.engine, autoflush=True, autocommit=True)
>> session = Session()
>>
>> cmd_str = "session.query(" + self.table_class + ").filter(" +
>> self.table_class + "." + self.id_name + ".in_(id_list)).order_by(" +
>> filter_params + ").all()"
>>
>> return eval(cmd_str)
>>
>> --
>> 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+...@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/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+...@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/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
On Sep 9, 2014, at 4:45 PM, pyArchInit ArcheoImagineers
wrote:
> Hi Michael and thanks a lot.
>
>
> Il giorno venerdì 5 settembre 2014 18:23:31 UTC+2, Michael Bayer ha scritto:
> you batch out the values to be used in the IN, then one of two choices: my
> preference is to run separate SELECT statements, using IN with each batch.
> If you really can't do that, you can combine the batches of IN groups with an
> OR: "x IN (batch1) OR x IN (batch2) ..."
>
>
>
>
> O spent 2 day to find a solutions:
> I make write to python this string:
>
> cmd_str = "session.query(MAPPCLASS).filter(or_(MAPPCLASS.id_invmat.in_([1, 2,
> 3, 4])).(MAPPCLASS.id_invmat.in_([5, 6, 7,
> 8]))).order_by(asc(MAPPCLASS.id_invmat)).all()"
>
> But I receive this error..
>
> Neither 'BinaryExpression' object nor 'Comparator' object has an attribute
> 'or_'
there's be somewhere you're calling .or_(), which is incorrect,
or_() is standalone.
not sure what "cmd_str" is about, you can just do this directly:
args = []
or_args = []
while args:
chunk = args[0:1000]
or_args.append(MAPCLASS.id.in_(chunk))
session.query(MAPCLASS).filter(or_(*or_args))
>
> a little suggestion to the right sintax to pass to the eval?
>
>
> keep in mind when you send enormous strings to your database, that places a
> burden on the query system. Oracle (which I assume you're using) also caches
> these queries
>
>
> I'm using sqlite DB.
>
> Best regards and thanks a lot.
> Luca
>
>
>
>
>
> On Sep 5, 2014, at 2:30 AM, mando wrote:
>
>> Hi to all,
>>
>> I wrote a method like this to reuse the code for many tables at the same
>> time[0]
>>
>> But, with more than 1000 records sqlite doesn't accepts the amount of id
>> inside .in_(id_list)
>>
>> How can I filter, split or can manage it?
>>
>> Thanks a lot and best regards,
>> Luca
>>
>>
>> [0]
>> def query_sort(self,id_list, op, to, tc, idn):
>> self.order_params = op #sorting parameters
>> self.type_order = to #asc or desc
>> self.table_class = tc #the name of the mapper class
>> self.id_name = idn #the name of the id
>>
>> filter_params = self.type_order + "(" + self.table_class + "."
>> + self.order_params[0] + ")"
>> for i in self.order_params[1:]:
>> filter_temp = self.type_order + "(" + self.table_class
>> + "." + i + ")"
>>
>> filter_params += ", "+ filter_temp
>>
>> Session = sessionmaker(bind=self.engine, autoflush=True,
>> autocommit=True)
>> session = Session()
>>
>> cmd_str = "session.query(" + self.table_class + ").filter(" +
>> self.table_class + "." + self.id_name + ".in_(id_list)).order_by(" +
>> filter_params + ").all()"
>>
>> return eval(cmd_str)
>>
>> --
>> 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+...@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/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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
Hi Michael and thanks a lot.
Il giorno venerdì 5 settembre 2014 18:23:31 UTC+2, Michael Bayer ha scritto:
>
> you batch out the values to be used in the IN, then one of two choices:
> my preference is to run separate SELECT statements, using IN with each
> batch. If you really can’t do that, you can combine the batches of IN
> groups with an OR: “x IN (batch1) OR x IN (batch2) …”
>
O spent 2 day to find a solutions:
I make write to python this string:
cmd_str = "session.query(MAPPCLASS).filter(or_(MAPPCLASS.id_invmat.in_([1,
2, 3, 4])).(MAPPCLASS.id_invmat.in_([5, 6, 7,
8]))).order_by(asc(MAPPCLASS.id_invmat)).all()"
But I receive this error..
Neither 'BinaryExpression' object nor 'Comparator' object has an attribute
'or_'
a little suggestion to the right sintax to pass to the eval?
>
> keep in mind when you send enormous strings to your database, that places
> a burden on the query system. Oracle (which I assume you’re using) also
> caches these queries
>
I'm using sqlite DB.
Best regards and thanks a lot.
Luca
>
>
>
>
> On Sep 5, 2014, at 2:30 AM, mando > wrote:
>
> Hi to all,
>
> I wrote a method like this to reuse the code for many tables at the same
> time[0]
>
> But, with more than 1000 records sqlite doesn't accepts the amount of id
> inside .in_(id_list)
>
> How can I filter, split or can manage it?
>
> Thanks a lot and best regards,
> Luca
>
>
> [0]
> def query_sort(self,id_list, op, to, tc, idn):
> self.order_params = op #sorting parameters
> self.type_order = to #asc or desc
> self.table_class = tc #the name of the mapper class
> self.id_name = idn #the name of the id
> filter_params = self.type_order + "(" + self.table_class + "." +
> self.order_params[0] + ")"
> for i in self.order_params[1:]:
> filter_temp = self.type_order + "(" + self.table_class + "." + i + ")"
>
> filter_params += ", "+ filter_temp
>
> Session = sessionmaker(bind=self.engine, autoflush=True, autocommit=True)
> session = Session()
>
> cmd_str = "session.query(" + self.table_class + ").filter(" +
> self.table_class + "." + self.id_name + ".in_(id_list)).order_by(" +
> filter_params + ").all()"
>
> return eval(cmd_str)
>
> --
> 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+...@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/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] too many SQL variables in "in_"
you batch out the values to be used in the IN, then one of two choices: my
preference is to run separate SELECT statements, using IN with each batch. If
you really can't do that, you can combine the batches of IN groups with an OR:
"x IN (batch1) OR x IN (batch2) ..."
keep in mind when you send enormous strings to your database, that places a
burden on the query system. Oracle (which I assume you're using) also caches
these queries.
On Sep 5, 2014, at 2:30 AM, mando wrote:
> Hi to all,
>
> I wrote a method like this to reuse the code for many tables at the same
> time[0]
>
> But, with more than 1000 records sqlite doesn't accepts the amount of id
> inside .in_(id_list)
>
> How can I filter, split or can manage it?
>
> Thanks a lot and best regards,
> Luca
>
>
> [0]
> def query_sort(self,id_list, op, to, tc, idn):
> self.order_params = op #sorting parameters
> self.type_order = to #asc or desc
> self.table_class = tc #the name of the mapper class
> self.id_name = idn #the name of the id
>
> filter_params = self.type_order + "(" + self.table_class + "."
> + self.order_params[0] + ")"
> for i in self.order_params[1:]:
> filter_temp = self.type_order + "(" + self.table_class
> + "." + i + ")"
>
> filter_params += ", "+ filter_temp
>
> Session = sessionmaker(bind=self.engine, autoflush=True,
> autocommit=True)
> session = Session()
>
> cmd_str = "session.query(" + self.table_class + ").filter(" +
> self.table_class + "." + self.id_name + ".in_(id_list)).order_by(" +
> filter_params + ").all()"
>
> return eval(cmd_str)
>
> --
> 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/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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
