On Thursday, January 18, 2018 at 9:31:49 PM UTC-5, Jaison Raj wrote: > > Hi guys, > > I am trying to write a row with 140 fields into the database, with the > following script: > > controller: > *WCEL={item1:value1,item2:value2,.......item140:value140}* > > *db.WCEL.update_or_insert(**(WCEL))* > > model: > *db = DAL('sqlite://storage.sqlite1')* > > *db.define_table('WCEL',* > * Field('item1'),* > * Field('item2),* > * ......* > * Field('item139'),* > * Field('item140')* > * )* > > but I am getting the following error: > <class 'sqlite3.OperationalError'> parser stack overflow > > > File "C:\web2py\gluon\packages\dal\pydal\adapters\base.py", line 412, in > execute > rv = self.cursor.execute(command, *args[1:], **kwargs) > OperationalError: parser stack overflow > > > Function argument list > > (self=<pydal.adapters.sqlite.SQLite object>, *args=('SELECT "WCEL"."id", > "WCEL."it.."."item140" = \'0\')) LIMIT 1 OFFSET 0;',), **kwargs={}) > > > > I tried the same set of 140 fields by importing from a csv, it was > successful. > > *db.WCEL.import_from_csv_file(open('WCEL.csv', 'r'))* >
The problem is not with writing the records but with attempting to check for existing records (.import_from_csv_file works because it does not check whether each record to be inserted matches an existing record). If you use .insert() instead of .update_or_insert(), you shouldn't have a problem. When you use .update_or_insert(), the DAL first attempts to select a record that matches all of the fields, and it does so with a query like: (db.mytable.field1 == 'value 1') & (db.mytable.field2 == 'value 2') & ... & (db.mytable.field140 == 'value 140') The problem is that when the DAL joins multiple queries, it places them in nested sets of parentheses -- with just 3 queries, you get SQL that looks something like: (((mytable.field1 = 'value 1') AND (mytable.field2 = 'value 2')) AND ( mytable.field3 = 'value 3')) With 140 fields, you get queries nested 140 levels deep in parentheses, which SQLite cannot handle. Of course, all of the nested parentheses are not really necessary, so if you want to first check for the existence of a record, one option is to avoid using .update_and_insert() and instead write your own code to check for record existence. You code would have to manually construct the "WHERE" clause of the SQL query. The logic then might look something like: record = db(manually_generated_raw_SQL).select(db.WCEL.id, limitby=(0, 1)). first() if not record: db.WCEL.insert(**WCEL) Anthony -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.