title: incremental select using id from SQLite in Twisted
body: <p>I am trying to select data from a table in SQLite one row ONLY at a time for each call to the function, and I want the row to increment on each call (self.count is initialized elsewhere and 'line' is irrelevant here) I am using an adbapi connection pool in Twisted to connect to the DB. Here is the code I have tried:</p>
  
  <pre><code>def queryBTData4(self,line):
      self.count=self.count+1
      uuId=self.count
      query="SELECT co2_data, patient_Id FROM btdata4 WHERE uid=:uid",{"uid": uuId}          
      d = self.dbpool.runQuery(query)
      return d
  </code></pre>
  
  <p>This code works if I just set uid=1 or any other number in the DB (I used autoincrement for uid when I created the DB) but when I try to assign a value to uid (i.e. self.count via uuId) it reports that the operator has to be string or unicode.(I have tried both but it does not seem to help) However, I know that the query statement above works just fine in a previous program when I use a cursor and the execute command but I cannot see why it does not work here. I have tried all sorts of combinations and searched for a solution but have not found anything yet that works.(I have also tried the statement with brackets and other forms)</p>
  
  <p>Thanks for any help or advice.</p>
  
  <p>Here is the entire code:</p>
  
  <pre><code>from twisted.internet import protocol, reactor
  from twisted.protocols import basic
  from twisted.enterprise import adbapi
  import sqlite3, time
  
  
  class ServerProtocol(basic.LineReceiver):
      def __init__(self):
          self.conn = sqlite3.connect('biomed2.db',check_same_thread=False)
          self.dbpool =  adbapi.ConnectionPool("sqlite3" , 'biomed2.db',  check_same_thread=False)
  
  
      def connectionMade(self):
          self.sendLine("conn made")
          factory = protocol.ClientFactory()
          factory.protocol = ClientProtocol
          factory.originator = self
          reactor.connectTCP('localhost', 1234, factory)
  
      def lineReceived(self, line):
          self._received = line
          self.insertBTData4(self._received)
          self.sendLine("line recvd") 
  
      def forwardLine(self, recipient):
          recipient.sendLine(self._received)      
  
  
      def insertBTData4(self,data):
          print "data in insert is",data
          chx=data
          PID=2
          device_ID=5
          query="INSERT INTO btdata4(co2_data,patient_Id, sensor_Id) VALUES ('%s','%s','%s')" % (chx, PID, device_ID) 
          dF = self.dbpool.runQuery(query)
          return dF
  
  class ClientProtocol(basic.LineReceiver):
      def __init__(self):
          self.conn = sqlite3.connect('biomed2.db',check_same_thread=False)
          self.dbpool =  adbapi.ConnectionPool("sqlite3" , 'biomed2.db',  check_same_thread=False)
          self.count=0
  
  
      def connectionMade(self):
          print "server-client made connection with client"
          self.factory.originator.forwardLine(self)
          #self.transport.loseConnection()
  
  
      def lineReceived(self, line):
          d=self.queryBTData4(self)
          d.addCallbacks(self.sendData,self.printError )
  
  
      def queryBTData4(self,line):
          self.count=self.count+1
          query=("SELECT co2_data, patient_Id FROM btdata4 WHERE uid=:uid",{"uid": uuId})          
          dF = self.dbpool.runQuery(query)
          return dF
  
      def sendData(self,line):
          data=str(line)
          self.sendLine(data) 
  
      def printError(self,error):
          print "Got Error: %r" % error
          error.printTraceback()
  
  
  def main():
      factory = protocol.ServerFactory()
      factory.protocol = ServerProtocol
      reactor.listenTCP(4321, factory)
      reactor.run()
  
  if __name__ == '__main__':
      main()
  </code></pre>
  
  <p>The DB is created in another program, thus:</p>
  
  <pre><code>import sqlite3, time, string
  conn = sqlite3.connect('biomed2.db')
  
  c = conn.cursor()
  c.execute('''CREATE TABLE btdata4
               (uid INTEGER PRIMARY KEY, co2_data integer, patient_Id integer, sensor_Id   integer)''')
  </code></pre>
  
  <p>The main program takes data into the server socket and inserts into DB. On the client socket side, data is removed from the DB one line at a time and sent to an external server. The program also has the ability to send data from the server side to the client side if required but I am not doing so here at the moment.</p>
  
  <p>In queryBTData(), every time the function is called the count increments and I assign that value to uuId, which I then pass to the query. I have had this query statement working in a program where I do not use the adbapi but it does not seem to work here. I hope this is clear enough but if not please let me know and I will try again.</p>
  
  <p>EDIT:</p>
  
  <p>I have modified the program to take one row from the DB at a time (see queryBTData() below) but have come across another problem.</p>
  
  <pre><code>def queryBTData4(self,line):
      self.count=self.count+1
      xuId= self.count
      #xuId=10
      return self.dbpool.runQuery("SELECT co2_data FROM btdata4 WHERE uid = ?",xuId)
      #return self.dbpool.runQuery("SELECT co2_data FROM btdata4 WHERE uid = 10")
  </code></pre>
  
  <p>When the count gets to 10 I get an error (which I will post below) which states that: "Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied" </p>
  
  <p>I have tried setting xuId to 10 (see commented out line xuId=10) but I still get the same error. However, if I switch the return statements (to commented out return) I do indeed get correct row with no error. I have tried converting xuId to unicode but that makes no difference, I still get the same error. Basically, if I I set uid in the return statement to 10 or more (commented out return) it works, but if I set uid to xuId (i.e. uid=?,xuId) in the first return, it only works when xuId is below 10. The API documentation, as far as I can tell, gives no clue as to why this occurs.(I have also disabled the insert into DB to eliminate this and checked the SQLite3_ limit, which is 999)</p>
  
  <p>Here are the errors I am getting when using the first return statement.</p>
  
  <pre><code>Got Error: &lt;twisted.python.failure.Failure &lt;class 'sqlite3.ProgrammingError'&gt;&gt;
  Traceback (most recent call last):
  File "c:\python26\lib\threading.py", line 504, in __bootstrap
  self.__bootstrap_inner()
  File "c:\python26\lib\threading.py", line 532, in __bootstrap_inner
  self.run()
  File "c:\python26\lib\threading.py", line 484, in run
  self.__target(*self.__args, **self.__kwargs)
  --- &lt;exception caught here&gt; ---
  File "c:\python26\lib\site-packages\twisted\python\threadpool.py", line 207, i
  n _worker
  result = context.call(ctx, function, *args, **kwargs)
  File "c:\python26\lib\site-packages\twisted\python\context.py", line 118, in c
  allWithContext
  return self.currentContext().callWithContext(ctx, func, *args, **kw)
  File "c:\python26\lib\site-packages\twisted\python\context.py", line 81, in ca
  llWithContext
  return func(*args,**kw)
  File "c:\python26\lib\site-packages\twisted\enterprise\adbapi.py", line 448, i
  n _runInteraction
  result = interaction(trans, *args, **kw)
  File "c:\python26\lib\site-packages\twisted\enterprise\adbapi.py", line 462, i
  n _runQuery
  trans.execute(*args, **kw)
  sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current sta
  tement uses 1, and there are 2 supplied.
  </code></pre>
  
  <p>Thanks.</p>