Trying to insert value from txt file with sqlite in python

Question

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

you are missing the quotes around line: VALUES ('"+line+"', "+str(x)+")") — trans1st0r
– trans1st0r, Commented Apr 25, 2016 at 16:12
Never concat sql queries and strings. Use the parameterized version of execute. — DeepSpace
– DeepSpace, Commented Apr 25, 2016 at 16:22

nephlm · Accepted Answer · 2016-04-25 16:21:28Z

1

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.

edited Apr 25, 2016 at 16:21

answered Apr 25, 2016 at 16:14

nephlm

5634 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dominico909 Over a year ago

Could you explain what you mean by using parameters to sanitize...? I don't understand. Thanks for the answer by the way!

nephlm Over a year ago

If playerID is string type, rather than a numeric type you'd them around the second parameter as well. You can also just try the parametrized version as well which I added.

robyschek · Accepted Answer · 2016-04-25 16:20:46Z

1

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.

answered Apr 25, 2016 at 16:20

robyschek

2,04515 silver badges19 bronze badges

2 Comments

Dominico909 Over a year ago

Why is this method better practice?

robyschek Over a year ago

@Dominico909 Because your line may contain some sql and it will be executed. Consider : DROP TABLE blahbla

Collectives™ on Stack Overflow

Trying to insert value from txt file with sqlite in python

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related