.select() will extract the table names from both the query and the fields passed to .select() -- this is because we allow an empty query, like db().select(db.mytable.ALL). However, .count() only extracts the table names from the query, not from the "distinct" argument to .count(). Because your query is just a string and not a DAL Query object, the DAL can't figure out what the table is (and it doesn't attempt to do so by inspecting the "distinct" argument).
Rather than building a string query, why don't you generate a regular DAL query object? You can join separate queries with the & and | operators. Anthony On Thursday, April 18, 2013 12:54:52 AM UTC-4, Stelios Koroneos wrote: > > 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
