Greetinfs to all.
I am facing a problem with count on a db that i am using the following to
create the query.
class CDRI():
def __init__(self,cdri_db):
self.db=cdri_db
def
_generate_querry(self,device_id,date_from=None,date_to=None,source=None,destination=None):
if not date_from :
pass
if not date_to :
pass
if source :
condition_source="src like \'%s\'" % source
else :
condition_source='src<>""'
if destination :
condition_destination="dst like \'%s\'" % destination
else :
condition_destination='dst<>""'
condition_deviceid="device_id=%s" %device_id
condition=(condition_deviceid+" and "+ condition_source +"
and "+ condition_destination )
return condition
def
get_calls(self,device_id,date_from=None,date_to=None,source=None,destination=None):
self.querry_rules=self._generate_querry(device_id,
date_from, date_to, source, destination)
self.rows=self.db(self.querry_rules).select(self.db.CDR.ALL)
return self.rows
def
get_calls_count(self,device_id,date_from=None,date_to=None,source=None,destination=None):
self.querry=self._generate_querry(device_id, date_from,
date_to, source, destination)
print self.querry
result=self.db(self.querry).count(self.db.CDR)
return result
if __name__ == '__main__':
cdri=CDRI(db_cdri)
rows=cdri.get_calls(device_id='2',source="210%")
for row in rows:
print row.id,row.calldate,row.src,row.dst,row.billsec
print "Total entries found
",cdri.get_calls_count(device_id=2,source="210%")
What its basically doing is that it prepares the query rules as string
(based on a number of parameters) and then feeds it to the DAL select or
count
The query looks like this
device_id=2 and src like '210%' and dst<>""
and works fine with select
but when i try to do a count i get this
raceback (most recent call last):
File
"/home/stelios/eclipse/eclipse-3.7.1/plugins/org.python.pydev_2.7.3.2013031601/pysrc/pydevd.py",
line 1397, in <module>
debugger.run(setup['file'], None, None)
File
"/home/stelios/eclipse/eclipse-3.7.1/plugins/org.python.pydev_2.7.3.2013031601/pysrc/pydevd.py",
line 1090, in run
pydev_imports.execfile(file, globals, locals) #execute the script
File "/home/stelios/workspace/CDRi/cdri_data.py", line 70, in <module>
print "Total entries found
",cdri.get_calls_count(device_id=2,source="210%")
File "/home/stelios/workspace/CDRi/cdri_data.py", line 56, in
get_calls_count
result=self.db(str(self.querry)).count(self.db.CDR)
File "/home/stelios/workspace/CDRi/web2py/gluon/dal.py", line 9850, in
count
return db._adapter.count(self.query,distinct)
File "/home/stelios/workspace/CDRi/web2py/gluon/dal.py", line 1710, in
count
self.execute(self._count(query, distinct))
File "/home/stelios/workspace/CDRi/web2py/gluon/dal.py", line 1768, in
execute
return self.log_execute(*a, **b)
File "/home/stelios/workspace/CDRi/web2py/gluon/dal.py", line 1762, in
log_execute
ret = self.cursor.execute(*a, **b)
File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in
execute
self.errorhandler(self, exc, value)
File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in
defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1064, 'You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near \'WHERE device_id=2 and src like \'210%\' and
dst<>""\' at line 1')
I traced the problem and found out that the SQL generated does not include
the table name in the count
I was under the impression that both select,count etc could operate with
the same "prepared" query.
If this is not a bug what is the appropriate way to handle such a case ?
Thanks for your time
Stelios
--
---
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/groups/opt_out.
