Getting error <class 'sqlite3.OperationalError'> near "interval_time": syntax error
878 views
Skip to first unread message
Amit
Nov 14, 2012, 3:51:43 AM11/14/12
to web...@googlegroups.com
Hi,
I am getting error
Error snapshot
I am getting error
<class 'sqlite3.OperationalError'> near "interval_time": syntax error
Please see the code and traceback below:
In db.py:
db.define_table(
'status',
Field('serial_no',requires=IS_NOT_EMPTY()),
Field('name',requires=IS_NOT_EMPTY()),
Field('updated_on','datetime',default=request.now),
Field('interval_time','integer',default=0))
def save_status():
result = {}
result['serial_no'] = '123456'
result['name'] = 'MyDevice'
result['updated_on'] = request.now
result['interval_time'] = 1
db.status.insert(**result)
db.commit()In default.py:
from gluon.dal import Expression
def status():
tbl = db.status
max_created_on = tbl.updated_on.max()
query = Expression(db,"date_sub(now(),interval interval_time minute) > updated_on")
rows=db(query).select(tbl.serial_no,tbl.name,\
tbl.interval_time,max_created_on,\
groupby=tbl.serial_no|tbl.name)
# additional logic has to be added here when we get rows object successfully from db....
grid = SQLFORM.grid(db.status)
return locals()
def save_device_status():
save_status() TRACEBACK:
Ticket ID
127.0.0.1.2012-11-14.14-09-15.d938f195-3cd2-4ea2-9044-40a2e8d4226f
<class 'sqlite3.OperationalError'> near "interval_time": syntax error
Version
| web2py™ | (2, 1, 1, datetime.datetime(2012, 10, 15, 12, 44, 40), 'stable') |
|---|---|
| Python | Python 2.7.2: C:\Python27\python.exe |
Traceback
1. | Traceback (most recent call last): |
Error snapshot
<class 'sqlite3.OperationalError'>(near "interval_time": syntax error)
Frames
-
File D:\web2py2.1.1\web2py\applications\TestApplication\controllers\default.py in status at line 84 code arguments variables
Code listing
79.
80.
81.
82.
83.84.
85.
86.
87.
88.max_created_on = tbl.updated_on.max()
query = Expression(db,"date_sub(now(),interval interval_time minute) > updated_on")
rows=db(query).select(tbl.serial_no,tbl.name,\
tbl.interval_time,max_created_on,\groupby=tbl.serial_no|tbl.name)
grid = SQLFORM.grid(db.status)
return locals() -
File D:\web2py2.1.1\web2py\gluon\dal.py in log_execute at line 1687 code arguments variables
Function argument list
(self=<gluon.dal.SQLiteAdapter object>, *a=('SELECT status.serial_no, status.name, status.in...dated_on) GROUP BY status.serial_no, status.name;',), **b={})
Code listing
1682.
1683.
1684.
1685.
1686.1687.
1688.
1689.
1690.
1691.command = a[0]
if self.db._debug:
LOGGER.debug('SQL: %s' % command)
self.db._lastsql = command
t0 = time.time()ret = self.cursor.execute(*a, **b)
self.db._timings.append((command,time.time()-t0))
del self.db._timings[:-TIMINGSSIZE]
return retVariables
a ('SELECT status.serial_no, status.name, status.in...dated_on) GROUP BY status.serial_no, status.name;',) b {} self <gluon.dal.SQLiteAdapter object> ret undefined self.cursor <sqlite3.Cursor object> self.cursor.execute <built-in method execute of sqlite3.Cursor object>
I am suspecting something is wrong with Expression syntax but unable to catch it , please help me resolve this issue.
Thanks,
Amit
Niphlod
Nov 14, 2012, 5:06:48 AM11/14/12
to web...@googlegroups.com
Il giorno mercoledì 14 novembre 2012 09:51:43 UTC+1, Amit ha scritto:
Hi,
I am getting error<class 'sqlite3.OperationalError'> near "interval_time": syntax error
syntax error points to something around "interval_time"...... my bet is on date_sub() not supported by sqlite
http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions
Amit
Nov 14, 2012, 6:12:49 AM11/14/12
to web...@googlegroups.com
100% correct :) , now facing problem of replacing the query with sqlite compatible query :
MySql query were:
query = Expression(db,"date_sub(now(),interval interval_time minute) > updated_on")
where interval_time field is integer field which will be considered to take interval in minutes for e.g. if user wants to save the interval time is 1 hour then he has to enter it in minutes means 60 minutes.
Now problem is when i tried to replace the above query with sqlite compitible query like below and pass to the db to get the required records:
query = Expression(db,"date('now','-interval_time minutes') > updated_on")
it returns nothing.
So can you please help me out write the above query in Sqlite.
Thanks,
Amit
MySql query were:
query = Expression(db,"date_sub(now(),interval interval_time minute) > updated_on")
Now problem is when i tried to replace the above query with sqlite compitible query like below and pass to the db to get the required records:
query = Expression(db,"date('now','-interval_time minutes') > updated_on")
it returns nothing.
So can you please help me out write the above query in Sqlite.
Thanks,
Amit
--
Paolo Caruccio
Nov 14, 2012, 12:17:40 PM11/14/12
to web...@googlegroups.com
Please try:
query = Expression(db,"interval_time < (strftime('%M','now') - strftime('%M', updated_on))")On the page suggested by Niphlod we read:
Compute the number of seconds between two dates:
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');So by replacing '%s' with '%M' we obtain the difference in minutes between two dates.
Massimo Di Pierro
Nov 15, 2012, 3:31:14 PM11/15/12
to web...@googlegroups.com
This may work on sqlite but I am not sure. Anyway it is not portable. It should be possible to use .epoch()
import time
query = (db.status.updated_on.epoch() + db.status.interval_time*60) < time.time()
Reply all
Reply to author
Forward
0 new messages