sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) too many SQL variables
Closed this issue · 3 comments
When trying to export a database to tardis the sql query seems to get too long. This happens when querying for all levels with a level id that is in the list of selected levels.
Solutions would be to batch these queries or to query for all and then perform the selection in python instead of on the database level.
@mishinma How did you create the database inside carsus-db? How did you circumvent this problem or did it not appear back then?
@yeganer Hm, I didn't encounter that error and I am not sure where the root of the problem is. The levels query was tested io/tests/test_output (on test data). Creating HDFStores from the database took a while (about 20 mins) but was working.
What do you mean by "the list of selected levels"? For the output you can select atoms with selected_atoms and chianti ions with chianti_ions. What do you provide for this variables? I can try to reproduce the error.
I used selected_atoms='H-Zn' and chianti_ions=H-He. Basically the settings from the two scripts in carsus-db/scripts.
The root of the problem is the Line.lower_level_id.in_(...) operation which checks against a list of ~50 000 Levels. This are too many Parameters for pysqlite (apparently the allow 999) causing the query to fail.
Yesterday, with the help of @wkerzendorf I found the solution to this problem by doing a join instead of in_. And I'm currently in the progress of updating the relevant parts of code to use mostly SQL syntax instead of pandas magic.
Good finding! It's strange that I didn't get the error though, because I used the same settings. But yeah, doing a join is much better.
I think doing things in SQL is a very good idea. I was a bit afraid to do it all in SQL from scratch.