pysqlite2.dbapi2.OperationalError'> near "<": syntax error

[Peter]

I got the error above while trying to rewrite and tidy up some code that I had working but didn't do everything I wanted.
This is a lot neater but it doesn't work yet I think I am close!
 
I have a person table and a task table where task.person is type 'references person'

This is the code (not working and has never worked) so all I can say is I know it's wrong...

def generate_inv_details():

    session.inv_details = []
    session.inv_total = 0
    today = datetime.date.today()

    query = db( (db.task.person.belongs( session.company_persons)\        ## hard coded some ids in place of session.. but to no avail!
              & (db.task.start_time <= today )\                           ## this line worked ok in my earlier format so don't think its date related
              & (db.task.charge_to == 'COMPANY' )\
              & (db.task.task_status == 'BILLABLE')))

    rows=db(query).select( db.task.person,\
                           db.task.person.name,\                          ## tried removing this (may have changed the error code) but still did not work      
                           db.task.title,\
                           db.task.start_time,\
                           db.task.duration,\
                           db.task.task_type,\
                           db.task.task_status,\
                           db.task.charge_to,\
                           db.task.charge,\
                           db.task.payment_status )

    session.inv_details=rows

    for row in session.inv_details:
        session.inv_total += row.task.charge

 
Is this totally off the wall and not even close? 
Is there a problem with the format of the select statement? (which I suspect is the issue)  or
Is there a glaring problem elsewhere?

You will have to trust me when I say I have put hours of frustration and reading into this and I only post here as a last resort!

Many thanks for your consideration!

Peter

[Anthony]

First, note that the following is not doing what you think it is:

 rows=db(query).select(db.task.person, db.task.person.name, ...)

db.task.person is a Field object, and it's "name" attribute is just the string name of the field itself, so the above is equivalent to:

 rows=db(query).select(db.task.person, 'person', ...)

which makes the second argument redundant with the first. Also, when using a non-Field object as a positional argument to .select(), accessing field values in the resulting Row objects will require using the full "tablename.fieldname" format (looks like you figured that out, as your later code uses row.task.charge rather than row.charge -- the latter would work if you didn't have that db.task.person.name in the select).

If you want the "name" of the person selected (presumably db.task.person is a reference to a db.person table), then you will need to do a join (alternatively, you could just fetch the references stored in the db.task.person field and later fetch the names via recursive selects, but that approach is much less efficient if you are fetching multiple records).

Anyway, regarding the main error, please show the full traceback as well as your table definition.

And as an aside, no need for the line continuation backslashes inside of parentheses (i.e., you can/should remove all of the ones in the code you have shown).

Anthony

[Peter Errity]

Thanks for your response Anthony!

I have obviously misunderstood a reference I read somewhere saying that reference fields were given special attributes and gave an example something like db.fieldname.name I assumed the trailing .name came from the referenced table attributes.

So I have modified the code ...

def generate_inv_details():

    session.inv_details = []
    session.inv_total = 0
    today = datetime.date.today()

    query = db( (db.task.person.belongs( session.company_persons))

              & (db.task.start_time <= today )

              & (db.task.charge_to == 'COMPANY' )
              & (db.task.task_status == 'BILLABLE')

              & (db.person.id==db.task.person))

    rows=db(query).select( db.task.person,
                           db.person.name,

                           db.task.title,
                           db.task.start_time,
                           db.task.duration,
                           db.task.task_type,
                           db.task.task_status,
                           db.task.charge_to,
                           db.task.charge,
                           db.task.payment_status )

    session.inv_details=rows

    for row in session.inv_details:
        session.inv_total += row.task.charge 

•     added the join (I hope correctly) in the query
  
•     used full "tablename.fieldname" format to reference the person.name in the select statement
•     removed the newline slashes

The relevant table excerpts...

db.define_table('person',

    Field( 'name'          , requires=IS_NOT_EMPTY() , comment="*"),
    Field( 'status'        , requires=IS_IN_SET(PERSON_STATUS) , comment="*") ,
    Field( 'type'          , requires=IS_IN_SET(PERSON_TYPES)  , comment="*") ,
    Field( 'company'       , 'reference company' , comment="*"),
    Field( 'role'          , 'string' , comment='for CONTACTS not CLIENTS'),
    Field( 'address'       , comment="Use ',' to seperate address lines"),
    Field( 'mobile_phone'  , label='Mobile' , requires=IS_EMPTY_OR(is_phone), comment="Can use '-' to separate prefix for clarity" ),
    Field( 'office_phone'  , label='Office' , requires=IS_EMPTY_OR(is_phone), comment="Can use '-' to separate prefix for clarity" ),
    Field( 'home_phone'    , label='Home'   , requires=IS_EMPTY_OR(is_phone), comment="Can use '-' to separate prefix for clarity" ),
    Field( 'email'         , requires=IS_EMPTY_OR(IS_EMAIL()) ),
    Field( 'date_of_birth' , 'date' ),
    Field( 'referrer_ref'  , 'string' , default=None),
    Field( 'notes'         , 'text' ),
    Field( 'created_by'    , db.auth_user , default=me , writable=False , readable=False ),
    Field( 'created_on'    , 'datetime' , default=request.now , writable=False , readable=False ),
    format=' %(type) %(name)s Referrer Ref %(referrer_ref)s')

db.define_table('task',

    Field( 'task_type'      , requires=IS_IN_SET(TASK_TYPES) , comment="*" ),
    Field( 'task_status'    , requires=IS_IN_SET(TASK_STATUS) , comment="*"),
    Field( 'start_time'     , 'datetime' , default=request.now ,requires=IS_NOT_EMPTY() , comment="*"),
    Field( 'duration'       , default='1Hr' ),
    Field( 'title'          , default="Session ##" , requires=IS_NOT_EMPTY() , comment="*"),
    Field( 'person'         , 'reference person' , comment="*"),
    Field( 'description'    , 'text' ),
    Field( 'charge'         , 'double' , default=0.0),
    Field( 'charge_to'      , default='COMPANY'  , requires=IS_IN_SET(CHARGE_TO)      , comment="* [COMPANY for Invoicing, CONTACT for Receipting]"),
    Field( 'payment_status' , default='BILLABLE' , requires=IS_IN_SET(PAYMENT_STATUS) , comment="* [BILLABLE for Invoicing or Receipting]"),
    Field( 'invoice_number' , 'integer' , default=None , writable=False ),
    Field( 'invoice_date'   , 'date'    , default=None , writable=False ),
    Field( 'created_by'     , db.auth_user , default=me , writable=False , readable=False ),
    Field( 'created_on'     , 'datetime' , default=request.now , writable=False , readable=False ),
    format= '%(person)s %(start_time)s  %(task_type)s  %(task_status)s  %(charge)s')

 

with the modified code I am still getting the same error

Traceback

Traceback (most recent call last):
  File "/home/peter/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/peter/web2py/applications/PAPAIM/controllers/default.py", line 566, in <module>
  File "/home/peter/web2py/gluon/globals.py", line 417, in <lambda>
    self._caller = lambda f: f()
  File "/home/peter/web2py/gluon/tools.py", line 4241, in f
    return action(*a, **b)
  File "/home/peter/web2py/applications/PAPAIM/controllers/default.py", line 347, in preview_invoice
    generate_inv_details()
  File "/home/peter/web2py/applications/PAPAIM/models/extras.py", line 25, in generate_inv_details
    db.task.payment_status )
  File "/usr/local/lib/python2.7/dist-packages/pydal/objects.py", line 2020, in select
    return adapter.select(self.query, fields, attributes)
  File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/sqlite.py", line 123, in select
    return super(SQLiteAdapter, self).select(query, fields, attributes)
  File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1296, in select
    return self._select_aux(sql,fields,attributes)
  File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1253, in _select_aux
    self.execute(sql)
  File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1388, in execute
    return self.log_execute(*a, **b)
  File "/usr/local/lib/python2.7/dist-packages/pydal/adapters/base.py", line 1382, in log_execute
    ret = self.get_cursor().execute(command, *a[1:], **b)
OperationalError: near "<": syntax error

[Anthony]

Replace .select() with ._select() and print/output the result so we can see the exact SQL generated.

[Peter]

Error after changing

.select()    to    ._select()

Traceback

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

Traceback (most recent call last): File "/home/peter/web2py/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/home/peter/web2py/applications/PAPAIM/controllers/default.py", line 566, in <module> File "/home/peter/web2py/gluon/globals.py", line 417, in <lambda> self._caller = lambda f: f() File "/home/peter/web2py/gluon/tools.py", line 4241, in f return action(*a, **b) File "/home/peter/web2py/applications/PAPAIM/controllers/default.py", line 347, in preview_invoice generate_inv_details

() File "/home/peter/web2py/applications/PAPAIM/models/extras.py", line 31, in generate_inv_details session.inv_total += row.task.charge AttributeError: 'str' object has no attribute 'task'

Because this error highlighted   

 session.inv_total += row.task.charge 

as a problem, I changed it to

session.inv_total += 1

the code now runs through and outputs this using generic view in which the session information provides..

 

company_id	:	1
company_persons	:	id 28 37 46 51 52 63 70 81 87 95
flash	:	None
inv_details	:	SELECT task.person, person.name, task.title, task.start_time, task.duration, task.task_type, task.task_status, task.charge_to, task.charge, task.payment_status FROM task, person WHERE <Set (((((task.person IN (1)) AND (task.start_time <= '2016-09-19 00:00:00')) AND (task.charge_to = 'COMPANY')) AND (task.task_status = 'BILLABLE')) AND (person.id = task.person))>;
inv_total	:	366

The length of company persons (10) matches the actual number of db.person  entries that reference db.company.id=1
however the inv_total (366)  which is now a simple count of tasks found is wrong - it appears none of the filters have been applied ?

This may be totally spurious but for what it's worth...
I don't know much SQL but using SQLite manager in Firefox I stripped down the SQL from the inv_details extract provided above and ran it as

Original stripped of its 'Set<((()))>'

SELECT task.person, person.name, task.title, task.start_time, task.duration, task.task_type, task.task_status, task.charge_to, task.charge, task.payment_status
FROM task, person
WHERE  ((task.person IN (28,37,46,51,52,63,70,81,87,95))
AND (task.start_time <= '2016-09-19 00:00:00')
AND (task.charge_to = 'COMPANY')
AND (task.task_status = 'BILLABLE')
AND (person.id = task.person));

returns nothing or never ends?
 
 
whereas

SELECT task.id ,task.start_time
FROM task
WHERE ( task.start_time <= '2016-09-19 00:00:00') 
AND (task.person IN(28,37,46,51,52,63,70,81,87,95)
AND (task.charge_to="COMPANY")
AND (task.payment_status="BILLABLE"));
 
returns 19 rows matchinig the criteria

[Peter]

This SQL in Firefox/SQLite manager

SELECT task.id, task.start_time,person.name,  task.charge_to, task.payment_status, task.charge
FROM task JOIN person ON person.id = task.person

WHERE ( task.start_time <= '2016-09-19 00:00:00')
AND (task.person IN(28,37,46,51,52,63,70,81,87,95)
AND (task.charge_to='COMPANY')
AND (task.payment_status='BILLABLE'));

provides the result I'm looking for...see attachment
(incidentally the data is not real - was auto populated)

[Anthony]

OK, got it. You are passing a Set object where you should have a Query object. You have:

query = db(...)

and then you have:

rows = db(query).select(...)

which is equivalent to:

rows = db(db(...)).select(...)

which is not allowed. When the Set object is passed to db(), it is converted to a string representation, which starts with "<Set", so the final SQL that is generated ends up looking like, "...WHERE <Set...". The "<" in that part of the SQL is generating the SQL syntax error in the database.

So, just change the code so the initial line defining the query is not wrapped in db(), or if you want to leave that line as is, then just call .select() directly on the Set object.

Anthony

[Peter]

The double db reference was definitely part of the problem but wasn't the whole story.

I made a mistake in one of the field names...
specifically

AND (task.task_status = 'BILLABLE')         

should have been
AND (task.payment_status="BILLABLE")); 
I actually had it correct in the SQL code above because I didn't use copy and paste for that.
 
Then I looked over some of joecodeswell has references for info about joins

 https://joecodeswell.wordpress.com/web2py-notes/#Validators

(cant get the editor to remove the formatting on this or create the link as I did above?)

Then I took a break from the small screen and went to the big screen and found a youtube video
by Giovanni Barillari called pyDal a pure database abstraction layer
it's still sitting there paused at 22:52 where it hints at the final part of my solution.

So after much playing around I got to this

def generate_inv_details():

    session.inv_details = []
    session.inv_total = 0

    session.inv_record_count=0

    today = datetime.date.today()

    query=db(db.task.person.belongs(session.company_persons))

    rows = query( (db.task.start_time <= today)
                    & (db.task.charge_to=='COMPANY')
                    & (db.task.payment_status=='BILLABLE'))\
                        .select(db.task.person,
                                db.person.id,
                                db.person.name,
                                db.person.referrer_ref,

                                db.task.title,
                                db.task.start_time,
                                db.task.duration,
                                db.task.task_type,
                                db.task.task_status,
                                db.task.charge_to,
                                db.task.charge,

                                db.task.payment_status,
                                left=db.person.on(db.person.id==db.task.person)

                )
    session.inv_details=rows

    for row in session.inv_details:

        session.inv_record_count += 1
        session.inv_total += row.task.charge

and IT WORKS!     I can call anything I want from the person table.


Many thanks for your responses Anthony, without you guys I might as well be looking into a hedge!


[edit] That single backslash after the select statement is needed probably because I forced the '.select' onto a new line to satisfy my borderline aspergers.