0

I am trying to update a sqlite3 db once I get a true statement but I can't feed my variable, which is an int, to the query which is a str. Here is the database 

CREATE TABLE STATICIPS(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   IP           CHAR(50) NOT NULL,
   CITY           CHAR(50) NOT NULL,
   INCOMPLETE BOOL
, CMTSIP CHAR(50));

Here is the code

#!/usr/bin/python

import sqlite3
conn = sqlite3.connect('ipdb.sqlite')

cursor = conn.execute("SELECT ID, IP, CITY, INCOMPLETE, CMTSIP  from STATICIPS WHERE CITY='LS'")
for row in cursor:
 if (row[3] == 1):
    print row[1]
    searchfile = open("arp-ls.txt", "r")
    for line in searchfile:
        if row[1] + ' ' in line: 
            print line
            conn.execute("UPDATE STATICIPS set INCOMPLETE = 0 where ID = " + row[0])
            conn.commit
    searchfile.close()`

The row[0] is the id in the db which is an int. I get this when i run the code:

 Traceback (most recent call last):
   File "getinc.py", line 16, in <module>
     conn.execute("UPDATE STATICIPS set INCOMPLETE = 0 where ID = " + row[0])
 TypeError: cannot concatenate 'str' and 'int' objects

So my question is how do I make it that row[0] prints correctly in my query so I can update the sqlite entry of this specific ID?

alexbclay
  • 1,389
  • 14
  • 19
Jason Kack
  • 9
  • 3

2 Answers2

1

Don't use concatenation at all when constructing SQL queries. Use SQL parameters instead. These are placeholders in the query where the database will fill in the values for you.

This ensures that those values are properly escaped (avoiding SQL injection attacks), and allows the database to re-use queries for different values (giving you a performance boost).

In sqlite3, placeholders are question marks; you pass in the values in a sequence as a second argument to execute():

conn.execute("UPDATE STATICIPS set INCOMPLETE = 0 where ID = ?",
             [row[0]])

For the general case (so not using SQL queries), you'd convert that integer value to a string first. Either by using str(row[0]) or by using str.format() string templating.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
  • I get a display now but the update is not happening. INCOMPLETE remains at 1 instead of 0 even when i find a match in my file. `10.2.1.24 mib-2.3.1.1.2.10.1.10.2.1.24 "00 1A A0 E0 55 52 " 10.2.1.25 mib-2.3.1.1.2.10.1.10.2.1.25 "00 0F 34 E7 86 00 " 10.2.1.26 10.2.1.27 10.2.1.28 10.2.1.29` – Jason Kack Sep 14 '16 at 18:02
  • so the query isnt running properly or the if is wrong but i dont have a match in a file for an ip or have a match and the INCOMPLETE stays at 1 which is what i set them all to by default to start with. i want that when i get a match in my txt file that i set INCOMPLETE to 0 in the db – Jason Kack Sep 14 '16 at 18:05
  • found why it was not commiting. had forgotten () – Jason Kack Sep 14 '16 at 18:29
-1

You need to leverage backticks.

conn.execute("UPDATE STATICIPS set INCOMPLETE = 0 where ID = " + `row[0]`)
Fallenreaper
  • 10,222
  • 12
  • 66
  • 129
  • In Python 3, backticks have been removed altogether and the expression you show here is a syntax error. It is rarely a good idea to construct SQL queries this way anyway, use SQL parameters. – Martijn Pieters Sep 14 '16 at 17:47
  • @MartijnPieters Im not telling him how to program, just solving his question as his error was a concat error. He also did not mention which version of python. It works as expected in 2.7, maybe before you downvote, we should as least see if the OP is using 3 or 2.7 – Fallenreaper Sep 14 '16 at 17:49
  • We are not just answering for this OP; future visitors will also use answers here. Backticks have a better implementation in the `repr()` function, which has the added benefit of working in both Python 2 and 3. No-one actually uses backticks for this anymore and teaching this technique is not helpful in a world that is rapidly moving to Python 3 anyway. – Martijn Pieters Sep 14 '16 at 17:54
  • Yeah, the standard is to your answer. :) I was just trying to resolve the error and wasnt looking at the larger picture. My bad. – Fallenreaper Sep 14 '16 at 17:58