sqlite3.OperationalError... what is wrong in my sqlite syntax?

Question

Python 3 + tkinter and sqlite3

I'm doing a mock application to save a piece of text in sqlite

here's the function:

 def saveNote(self,note_id):

    conn = db.connect(fname)
    c = conn.cursor()
    safeTitle=self.newNoteTitle.get()
    safeContents=self.newNoteText.get("1.0",tk.END)
    safeLink=self.newNoteLink.get()
    safeRemarks=self.newNoteRemarks.get()
    conn.execute('UPDATE notes SET (title,contents,remarks,link,created,last_modified,notebook_id) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?) WHERE notes_id=5', (safeTitle, safeContents, safeRemarks, safeLink, 1))
    conn.commit()
        self.master.destroy()

When executing the function, I get this error:

 conn.execute('UPDATE notes SET (title,contents,remarks,link,created,last_modified,notebook_id) VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, ?) WHERE notes_id=5', (safeTitle, safeContents, safeRemarks, safeLink, 1))
sqlite3.OperationalError: near "(": syntax error

I don't quite understand why the syntax is wrong...(I'm new to python)...can anyone help me spot the mistake?

Thanks

The SQLite syntax diagrams are very helpful: for example, sqlite.org/lang_update.html and sqlite.org/lang_insert.html — Robᵩ
– Robᵩ, Commented Sep 24, 2015 at 18:05
Thanks for the link but I've already read it and my syntax seems ok? — Dova Kin
– Dova Kin, Commented Sep 24, 2015 at 18:10
Your syntax is flawed. The word VALUES doesn't appear anywhere on this syntax diagram: sqlite.org/lang_update.html Neither does ( or ). — Robᵩ
– Robᵩ, Commented Sep 24, 2015 at 18:31

idjaw · Accepted Answer · 2015-09-24 19:04:44Z

2

I don't think your SQL statement is correct. From what you explained you are looking to insert data in to your table, so you want to actually use an INSERT statement and not UPDATE. I think you might want to do this instead:

INSERT INTO notes(title,contents,remarks,link,created,last_modified,notebook_id)
VALUES(INSERT_THE_VALUES_YOU_WANT_TO_INSERT_HERE)

If you are in fact looking to update existing data, then your syntax should look like this:

Just fill in the "" with the values you want to set

UPDATE notes
SET 
title = "",
contents = "",
remarkts = "",
link = "",
created = "",
last_modified = "",
notebook_id = ""
WHERE notes_id=5

To remove any confusion with the query I provided, you want to structure your query within your Python like this:

conn.execute('UPDATE notes SET title=?,contents=?,remarks=?,link=?,created=?,last_modified=?,notebook_id=?
WHERE notes_id=5', (safeTitle, safeContents, safeRemarks, safeLink, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1))

edited Sep 24, 2015 at 19:04

answered Sep 24, 2015 at 18:02

idjaw

26.8k10 gold badges68 silver badges84 bronze badges

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

9 Comments

Dova Kin Over a year ago

I'm actually updating an already existing note..hence why the "hardcoded" note_id (I was trying to debug it)

idjaw Over a year ago

Then your update syntax is incorrect. I'll update my answer.

Dova Kin Over a year ago

in that case I would not be using parameters, exposing my code to sql injections!

idjaw Over a year ago

I have no idea why you are jumping to sql injections. You are trying to interface to a db. You wrote SQL syntax that was incorrect and you were provided with the correct syntax. If you are worried about SQL injection, then you should write up data sanitization methods to check every input.

Dova Kin Over a year ago

What I mean is...your version is certainly correct, but it's "different", because it doesn't use any parameters. As I understand, the best practice is to parameterize queries to make them secure by default. Also, I would like to learn why that parameterized syntax is wrong instead of using an alternative.... The parameters I got from here: # example 1 -- simple placeholders db.execute('update players set name=?, score=?, active=? where jerseyNum=?', ('Smith, Steve', 42, True, 99))

|

Collectives™ on Stack Overflow

sqlite3.OperationalError... what is wrong in my sqlite syntax?

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related