Dominico909 Dominico909 - 7 months ago 47
SQL Question

operation parameter must be str in sql/python

import sqlite3, time, datetime, random, random

class DatabaseManager(object):
def __init__(self, db):
self.conn = sqlite3.connect(db)
self.conn.execute('pragma foreign_keys = on')
self.conn.commit()
self.c = self.conn.cursor()


def query(self, arg):
self.c.execute(arg)
self.conn.commit()
return self.c

def fetch(self):
self.c.fetchall()
self.conn.commit()
return self.c

def __del__(self):
self.conn.close()



dbmgr = DatabaseManager("connect.db")



while 1 > 0:

def quantity():
x = 1
file = open("john.txt", "r")

for line in file:
x = random.randint(100,10000000)
func = "INSERT INTO test (playerNAME) VALUES (?, ?)", line, x
dbmgr.query(func)


You can ignore the while 1 > 0, that was part of the code I deleted to specifically ask my question. When I call the function quantity() (in another part of code I also deleted for the questions sake), I get an error that states:

Traceback (most recent call last):
File "C:\Users\senarsky.CAL\Documents\2016Sem2\Sqlite-connect.py", line 210, in
quantity()
File "C:\Users\senarsky.CAL\Documents\2016Sem2\Sqlite-connect.py", line 68, in quantity
dbmgr.query(func)
File "C:\Users\senarsky.CAL\Documents\2016Sem2\Sqlite-connect.py", line 14, in query
self.c.execute(arg)
ValueError: operation parameter must be str

I am not sure how to make the func line a string without using insecure sql formatting...

Answer

Enclose your query parameters in a tuple:

query = "INSERT INTO test (playerNAME) VALUES (?, ?)"
parameters = (line, x)

Modify the query method to accept both query and parameters:

def query(self, query, params=None):
    self.c.execute(query, params)
    self.conn.commit()
    return self.c

And then call as:

dbmgr.query(query, parameters)