UPDATE SQLITE TABLE - Copy a fields content to another field.

UPDATE SQLITE TABLE - Copy a fields content to another field. - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: UPDATE SQLITE TABLE - Copy a fields content to another field. (/thread-33574.html)

Pages: 1 2

UPDATE SQLITE TABLE - Copy a fields content to another field. - andrewarles - May-07-2021

Hi, I am trying to go through filtered records and copy the contents, line by line, of field1 to field2.

Why is the field2 being filled with the same value and not it's corresponding field1 value?

Thanks.

import sqlite3
conn = sqlite3.connect("test.sqb")
cursor = conn.cursor()

sql = "SELECT * FROM report WHERE type LIKE 'E%'"

cursor.execute(sql)
data = cursor.fetchall()

for row in data:
    field1 = row[0]
    cursor.execute("UPDATE report SET field2='" + field1 + "'")
    conn.commit()
conn.close()

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - Larz60+ - May-07-2021

print value of data, row, field1, and the actual query
I can't run this, so there may be some typos.

import sqlite3
conn = sqlite3.connect("test.sqb")
cursor = conn.cursor()
 
sql = "SELECT * FROM report WHERE type LIKE 'E%'"
 
cursor.execute(sql)
data = cursor.fetchall()

for row in data:
    print(f"\nrow: {row}")
    field1 = row[0]
    sqlstmt = f"UPDATE report SET field2='{field1}'"
    print(f"row[0]: {row[0]}\nsqlstmt: {sqlstmt}")
    cursor.execute(sqlstmt)
    # cursor.execute("UPDATE report SET field2='" + field1 + "'")
    conn.commit()
conn.close()

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - andrewarles - May-08-2021

Thanks for the reply.

The variable in being passed to the sqlstmt, and the sqlstmt is being printed correctly with its corresponding value, but when updating the db, all rows are still being updated with a unique value in field2, which corresponds to the value of the last row.

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - ibreeden - May-08-2021

(May-08-2021, 07:36 AM)andrewarles Wrote: but when updating the db, all rows are still being updated with a unique value in field2, which corresponds to the value of the last row.

This is exactly what can be expected.

cursor.execute("UPDATE report SET field2='" + field1 + "'")

... contains no "where" clause, so all records are updated. You only see the results of the last update.

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - andrewarles - May-08-2021

Ok, so what would the WHERE clause be in order to update the row that the cursor's on?

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - ibreeden - May-08-2021

We would need to know the structure of the table. What are the columns? And most important: does the table have a unique key? On which column?

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - andrewarles - May-08-2021

There is no unique key, there is a primary key on field1.

There are 15 columns made up of varchar's and integer's.

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - ibreeden - May-08-2021

Allright then. I cannot test it but I think you have to do something like this.

import sqlite3
conn = sqlite3.connect("test.sqb")
cursor = conn.cursor()
  
sql = "SELECT * FROM report WHERE type LIKE 'E%'"
  
cursor.execute(sql)
data = cursor.fetchall()
 
for row in data:
    print(f"\nrow: {row}")
    field1 = row[0]
    sqlstmt = f"UPDATE report SET field2='{field1}' where field1 = '{field1}'"
    print(f"row[0]: {row[0]}\nsqlstmt: {sqlstmt}")
    cursor.execute(sqlstmt)

conn.commit()
cursor.close()
conn.close()

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - andrewarles - May-08-2021

Thanks, but that only updates the first row.

The remaining rows are not updated.

RE: UPDATE SQLITE TABLE - Copy a fields content to another field. - ibreeden - May-08-2021

Can you show the output?