CODEWITHSUNDEEP
pythonsqldatabasesqlite
Dominico909
Dominico909

Reputation: 41

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...

Upvotes: 0

Views: 922

Answers (2)

nephlm
nephlm

Reputation: 553

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.

Upvotes: 1

robyschek
robyschek

Reputation: 2035

Formatting sql queries via string concatenation is very bad practice. Variable bindging should always be used:

c.execute("INSERT INTO test (playerNAME, playerID) VALUES (?, ?)", [line, x])

In your case the line probably contains spaces or any punctuation mark. The sqlite's error string is misleading, though.

Upvotes: 1

Related Questions