Object type None error

[T.R.Rajkumar]

I get this error for a particular amcid = 1910, for others it is working ok. What might be the problem. The sp

usr_getconlabqty_amc_new is indeed returning row from db when checked in the backend. please elucidate.

Error ticket for "web_ocms"

Ticket ID

127.0.0.1.2020-10-14.10-26-27.939fd264-ebf8-4e08-aa76-2ca0d84a5703

<type 'exceptions.TypeError'> 'NoneType' object is not iterable

Version

web2py™	Version 2.14.6-stable+timestamp.2016.05.10.00.21.47
Python	Python 2.7.5: D:\Python\python.exe (prefix: D:\Python)

Traceback

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Traceback (most recent call last): File "D:\web2py\gluon\restricted.py", line 227, in restricted exec ccode in environment File "D:/web2py/applications/web_ocms/controllers/amc.py", line 407, in <module> File "D:\web2py\gluon\globals.py", line 417, in <lambda> self._caller = lambda f: f() File "D:/web2py/applications/web_ocms/controllers/amc.py", line 247, in schII rowsconlab = db.executesql("exec usr_getconlabqty_amc_new @amcid=?",placeholders=([amcid]),as_dict=True) File "D:\web2py\gluon\packages\dal\pydal\base.py", line 1028, in executesql fields = colnames or [f[0] for f in columns] TypeError: 'NoneType' object is not iterable

Error snapshot

<type 'exceptions.TypeError'>('NoneType' object is not iterable)

inspect attributes

Frames

• File D:\web2py\gluon\restricted.py in restricted at line 227 code arguments variables

• File D:\web2py\applications\web_ocms\controllers\amc.py in <module> at line 407 code arguments variables

• File D:\web2py\gluon\globals.py in <lambda> at line 417 code arguments variables

• File D:\web2py\applications\web_ocms\controllers\amc.py in schII at line 247 code arguments variables

• File D:\web2py\gluon\packages\dal\pydal\base.py in executesql at line 1028 code arguments variables

  Function argument list

  (self=<DAL uri="mssql2:******@172.16.164.60\SQL16S7INS1/ocms_nlc">, query='exec usr_getconlabqty_amc_new @amcid=?', placeholders=['1910'], as_dict=True, fields=None, colnames=None, as_ordered_dict=False)

  Code listing

  1023.
  1024.
  1025.
  1026.
  1027.
  1028.
  
  1029.
  1030.
  1031.
  1032.
              # Non-DAL legacy db query, converts cursor results to dict.
              # sequence of 7-item sequences. each sequence tells about a column.
              # first item is always the field name according to Python Database API specs
              columns = adapter.cursor.description
              # reduce the column info down to just the field names
              fields = colnames or [f[0] for f in columns]
  
              if len(fields) != len(set(fields)):
                  raise RuntimeError("Result set includes duplicate column names. Specify unique column names using the 'colnames' argument")
              #: avoid bytes strings in columns names (py3)
              if columns and not PY2:

  Variables

  fields	None
  colnames	None
  columns	None
  f	undefined

[T.R.Rajkumar]

this is the result of the sp for amcid = 1910

unitid    amcno    thsk    tsk    tssk    tusk    awg    da    vda    pfamt    sacamt    esiamt
ts2    AHO20DP052    9921.36600    8865.31945    8317.76000    31453.64245    8198133.44    0.00    1201554.58    5840151.72    532868.36    1460037.93

[Jim S]

Can you share the relevant code?

-Jim

[T.R.Rajkumar]

• 	def schII():     if not URL.verify(request, hmac_key=KEY): raise HTTP(403)     amcid = request.vars.amcid     rowsamcdet = db(db.amc_details.amc_id == amcid).select(db.amc_details.amcno)     if not rowsamcdet:         session.flash = "No jobs to make provn"         redirect(URL('new_contract'))     amcnod = db(db.amc_master.id == amcid).select(db.amc_master.amcno).first()     x = db(db.cont_overheads.amc_id == amcid).select(db.cont_overheads.id)     for i in x:         record = i['id']     #rows = db(db.cont_overheads.id == record).select()     fields = ['phhskn','phhskd','phhsk',               'phskn','phskd','phsk',               'phsskn','phsskd','phssk',               'phuskn','phuskd','phusk',               'alwhskn','alwhskd','alwhsk',               'alwskn','alwskd','alwsk',               'alwsskn','alwsskd','alwssk',               'alwuskn','alwuskd','alwusk',               'tax','insurance','sac','bonus',               'supchr','fringes']     form = SQLFORM(db.cont_overheads,record,keepvalues=True,fields=fields)     if form.process(session=None, formname='frmschII',dbio=False,onvalidation=schII_validation).accepted:         form.vars.amc_id = amcid         form.vars.update_uid = session.uname         form.vars.amcno = amcnod['amcno']         form.record.update_record(**dict(form.vars))         db.executesql("exec update_load_schII_amt_awg @amcid = ?",placeholders=([form.vars.amc_id]))         session.flash = 'Overheads updated.'         redirect(URL('new_contract'))     elif form.errors:         print form.errors         response.flash = 'form has errors'     else:         response.flash = 'please fill the form'     # Note: no form instance is passed to the view     ## record passed to set the id value in view.     rows = db(db.cont_overheads.id == record).select()

• rowsconlab = db.executesql("exec usr_getconlabqty_amc_new @amcid=?",placeholders=([amcid]),as_dict=True)

• rowsrate = get_amc_rate(amcid)     return dict(record=record,rowsconlab=rowsconlab,rows=rows,rowsrate=rowsrate)

the error occurs in the  line marked red.

[Carlos Correia]

EXEC is not a SQL statement.

-- 
Com os melhores cumprimentos,

Carlos Correia
=========================
MEMÓRIA PERSISTENTE
GSM:  917 157 146 (Signal, WhatsApp)
e-mail: ge...@memoriapersistente.pt
URL: http://www.memoriapersistente.pt
XMPP (Jabber): car...@memoriapersistente.pt
GnuPG: wwwkeys.eu.pgp.net

[Jim S]

Instead of 

rowsconlab = db.executesql("exec usr_getconlabqty_amc_new @amcid=?",placeholders=([amcid]),as_dict=True)

can you try:

rowsconlab = db.executesql("exec usr_getconlabqty_amc_new @amcid=?",placeholders=[amcid],as_dict=True)

I removed the parentheses around [amcid]

-Jim

[T.R.Rajkumar]

• 	I get this error for a particular amcid = 1910, for others it is working ok. What might be the problem. The sp usr_getconlabqty_amc_new is indeed returning row from db when checked in the backend. please elucidate.

Actually the code is working fine, but for a particular id 1910 it reports error None cannot be iterated. The database does return the result but in dal/base.py as_dict check function the variable columns is not intialized as the execulte sql is returning None, I think so. But for all other amcids it is working fine. How to debug this.

 

 

[Jim S]

What is the value of rowsconlab immediately following the executesql statement for id 1910 and for one that works?

-Jim

[T.R.Rajkumar]

The print rowsconlab for id other than 1910 is

web2py Web Framework
Created by Massimo Di Pierro, Copyright 2007-2020
Version 2.14.6-stable+timestamp.2016.05.10.00.21.47
Database drivers available: sqlite3, imaplib, pyodbc, pymysql, pg8000
please visit:
        http://127.0.0.1:8000/
starting browser...
[{'thsk': '0.00000', 'tsk': '0.00000', 'unitid': 'ts2', 'da': '0.00', 'vda': '0.
00', 'sacamt': '0.00', 'amcno': 'PLT17CS051', 'tusk': '0.00000', 'awg': '0.00',
'esiamt': '0.00', 'pfamt': '0.00', 'tssk': '0.00000'}]

for id 1910 there is nothing printed in the cmd window.

but the sp does return result below for id 1910

unitid    amcno    thsk    tsk    tssk    tusk    awg    da    vda    pfamt    sacamt    esiamt

ts2    AHO20DP057    10.00000    0.00000    0.00000    10.00000    2800.00    0.00    460.00    2042.56    186.78    510.64

for reasons unknow  to me web2py dal is not reading the result, resulting in None type object not iterable error.

[Jim S]

Ok, I'm at a loss for what the problem could be.

To further troubleshoot I'd probably open a web2py shell for your app and try different things with a good amcid and with 1910. 

python web2py.py -S application_name    (http://web2py.com/books/default/chapter/29/04/the-core#Command-line-options)

I'd try

-hard coding the stored procedure call with the id instead of using variable substitution

-depending on how complex the stored procedure is, start issuing those sql statements and see if you can determine where something is going wrong

-when you say the sp does return values for that id, are you testing via python (not using pydal) or in a database tool?  If not in Python, can you test without using pydal and see what the results are?

-Jim

[T.R.Rajkumar]

Dear Jim S
     I found the error to be in the database in one of the tables. When I corrected the form worked nicely. It was all a programming error on my part. Thank you so much for your valuable time and effort in helping me out. Web2py rocks.

[Jim Steil]

Very happy that you found the solution.

Jim

On Mon, Oct 19, 2020, 1:04 AM T.R.Rajkumar <t.r.raj...@gmail.com> wrote:

Dear Jim S
     I found the error to be in the database in one of the tables. When I corrected the form worked nicely. It was all a programming error on my part. Thank you so much for your valuable time and effort in helping me out. Web2py rocks.

--
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 a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/IVY_TyXGsjE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/e311ffea-aa0c-45a2-962e-3423fcfe938ao%40googlegroups.com.