Jason Kack Jason Kack - 2 months ago 16
Python Question

printing a int variable in a sqlite3 update query

I am trying to update a sqlite3 db once i get a true statement but i cant 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 correxctly in my query so i can update the sqlite entry of this specific ID?

Answer

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.