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 [email protected].
For more options, visit https://groups.google.com/d/optout.
