Python/Sqlite3 : Exception has occurred: sqlite3.OperationalError

Question

I'm trying to create a function that will handle API error messages but I get this error message in Python:

Exception has occurred: sqlite3.OperationalError
near "Test4": syntax error

The server response is:

{"message":"Failed to validate one or more request parameters","validationErrors":["Budget name must be unique. 'Test4 - X4574747-PHONE' already exits"]}

And my code is:

def error():
    if "message" in r.json():
        logText = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) + " : " + r.json()['message']
        c.execute("INSERT INTO log VALUES ('"+ logText +"')")
        conn.commit()
        if "validationErrors" in r.json():
            logText = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) + " : " + r.json()['validationErrors'][0]
            c.execute("INSERT INTO log VALUES ('"+ logText +"')")
            conn.commit()
        os._exit(1)

I can't put my finger on what causes this error. Any help would be appreciated. Thank you.

Can you show your logText value in case of validationErrors. I guess there is a problem with quotes. Btw best way to insert str into str with quotes is using %r. Like sql = "insert into log values(%r)" % logText. — sashaaero
– sashaaero, Commented Dec 11, 2018 at 4:28
@sashaaero No, the best way is to use parameterized queries: c.execute("INSERT INTO log VALUES (?)", [logText]) — Dan D.
– Dan D., Commented Dec 11, 2018 at 4:31
In sql yes, but I'm talking about strings generally. But we are working with sql here, so you are right. — sashaaero
– sashaaero, Commented Dec 11, 2018 at 4:31
The logText value is : 2018-12-10 23:31:26 : Budget name must be unique. 'Test4 - X4574747-PHONE' already exits — Creek Barbara
– Creek Barbara, Commented Dec 11, 2018 at 4:33
Yes, you are closing ' quote before Test4. Use the way Dan D. showed. — sashaaero
– sashaaero, Commented Dec 11, 2018 at 4:34

sashaaero · Accepted Answer · 2018-12-11 04:42:34Z

logText = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime()) + " : " + r.json()['validationErrors'][0]
c.execute("INSERT INTO log VALUES ('"+ logText +"')")

You are sending this SQL INSERT INTO log VALUES ('2018-12-10 23:31:26 : Budget name must be unique. 'Test4 - X4574747-PHONE' already exits') and as you see you close ' quote before Test4 and that's why SQL doesn't understand what's going on after closing quote.

Use c.execute("INSERT INTO log VALUES (?)", [logText])

Dan's code works, but I don't understand it.

? means pass argument from given arguments list. Which is [logText]. It is better to use this way to avoid SQL injections.

See here

Thank you for the explanation and link. And thanks to Dan too for the input

Collectives™ on Stack Overflow

Python/Sqlite3 : Exception has occurred: sqlite3.OperationalError

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related