Member Avatar for jcmeyer

I would like to copy a row in a database and change one value.

I think that this is close to what I am supposed to do.

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT (?, C2, C3, C4, C5) FROM DatabaseName WHERE C1=?", [newC1Value, copiedC1Value])

Although, I get an operational error:
sqlite3.OperationalError: near ",": syntax error

Thank you for any suggestions!

Edited by jcmeyer because: n/a
  • 6 Contributors
  • 11 Replies
  • 242 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by webstart

Dani AI

Generated

Short summary and practical notes based on the thread: the SQL pattern to copy a row while changing one value is an INSERT ... SELECT ... FROM ... WHERE ... — not a parenthesized SELECT list. The error “near ',' : syntax error” comes from writing the SELECT list inside parentheses (SQLite treats that as a single parenthesized expression and fails at the comma). Placeholders (either ? or named :name) work for values only — they cannot be used for table or column names.

A compact, safe pattern (uses named parameters and a connection context manager so commits/rollbacks are automatic):

with sqlite3.connect("my.db") as conn:
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO mytable (new_id_col, col_b, col_c) "
        "SELECT :new_id, col_b, col_c FROM mytable WHERE id_col = :src_id",
        {"new_id": new_value, "src_id": source_id},
    )

Readability and safety: as suggested, break SQL into a multi-line string for clarity. Never build SQL by concatenating Python strings with values (SQL injection and quoting bugs); use parameterized queries instead. Also, INSERT INTO ... SELECT does not require a fetch*() before commit() — commit (or the context manager) is sufficient.

Additional practical tips not covered in detail above: if the primary key is autoincrement, omit it from the INSERT/SELECT so SQLite generates a new key. If the column you change must remain unique, decide how to handle conflicts — check first, or use SQLite conflict clauses (INSERT OR IGNORE, INSERT OR REPLACE) with caution because REPLACE actually deletes then inserts. If copying paths or Windows filenames, parameterization avoids backslash/quoting problems (the later thread issue was about string types — in older Python 2 code convert to the appropriate str/unicode as needed).

Recommended Answers

All 11 Replies

Member Avatar for jice

I'd write

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT (C1, C2, C3, C4, C5) FROM DatabaseName WHERE C1='%s'", [newC1Value])
Member Avatar for jice

Sorry, my first post is not good. yours was the good one.
You can do this too :

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT (%s, C2, C3, C4, C5) FROM DatabaseName WHERE C1='%s'" % (newC1Value, copiedC1Value))
Member Avatar for jcmeyer

That is still not working for me. I think I might know why.

What is the line after this sql execute line? Is it simply

self.connection.commit()

With the SELECT in my sql line do I need to fetch anything first before I commit?

Member Avatar for jcmeyer

I figured it out.

self.cursor.execute("INSERT INTO DatabaseName (C1, C2, C3, C4, C5) SELECT ?, C2, C3, C4, C5 FROM DatabaseName WHERE C1=?", [newC1Value, copiedC1Value])
self.connection.commit()

The problem was the parentheses. I got rid of them in the SELECT and it works. Thanks for you help!

Edited by jcmeyer because: n/a
Member Avatar for sneekula

jcmeyer, could you do me a favor and test this in your code, it makes things a lot more readable, and may avoid errors like you exerienced:

cur_ex = """
INSERT INTO DatabaseName (C1, C2, C3, C4, C5) 
SELECT ?, C2, C3, C4, C5 FROM DatabaseName WHERE C1=?
"""
self.cursor.execute(cur_ex, [newC1Value, copiedC1Value])
self.connection.commit()

BTW, nice solution on your part.

Member Avatar for jcmeyer

Yes, sneekula's solution works as well. Thanks.

Member Avatar for gerard4143

Its been some time since I did any SQL but won't it be a more natural fit to use UPDATE WHERE to update a record

Member Avatar for jcmeyer

I was not trying to update a record. I wanted to copy a row, but change one value in it. In other words, I wanted to insert a new row into my database with all the exact same values of another row, but one, to prevent identical rows.

With UPDATE there are no new rows inserted into the database.

Member Avatar for webstart

Hello

         (u'D:\\data\\Example.xml',)

     I read the above string from a list and pass it to the below query, 
             sql = "DELETE FROM docs WHERE pathVariId = '"+myfile+"'"
             self.cursor.execute(sql)

    but I get the error as below
        sqlite3.OperationalError: near "D": syntax error

any suggestions to get rid of that '\' which is causing the error, thanks

Member Avatar for TrustyTony

You could try triple quote the outer double quotes for query. Path name seems Ok, it is only single back slash escaped.
Next time do not hijack old thread but make your own, include link to old thread if necessary.

Edited by TrustyTony
Member Avatar for webstart

Thank you, I did try with triple quote and '?' placeholder but the issue was with passing the string as unicode(myfile) instead of str(myfile)

Sorry for posting to the old thread as I came here while searching for the same error message. Thanks a lot for being patient, humble, putting your time and answering silly questions. which is far better than stackexchange ;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.