Dominico909 Dominico909 - 7 months ago 13
SQL Question

Trying to insert value from txt file with sqlite in python

def quantity():
i = 0
x = 1

file = open("john.txt", "r")

while i < 5000:

for line in file:
c.execute("INSERT INTO test (playerNAME, playerID) VALUES ("+line+", "+str(x)+")")
conn.commit()

x = random.randint(100,10000000000000000)
i += 1


I try to iterate through the John.txt file and insert each value into a table. The first word in the txt file is "abc123". When I run this code there is an error: sqlite3.OperationalError: no such column: abc123

I can get the code to enter the random numbers into playerID but I can't get the txt file query to work...

Answer

You need single quotes around the string.

c.execute("INSERT INTO test (playerNAME, playerID) VALUES ('"+line+"', "+str(x)+")")

Otherwise it tries to interpret it as a sql expression and looks for the named column.

More generally you should use parameters or sanitize the incoming data from the file for safety against sql insertion. Even if you trust this particular file. It's a good habit.

c.execute("INSERT INTO test (playerName, playerID) VALUES (?, ?)", (line, x))

Details are here and here is why it's important.