Python Forum
UPDATE SQLITE TABLE - Copy a fields content to another field.
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
UPDATE SQLITE TABLE - Copy a fields content to another field.
#1
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()
Reply
#2
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()
ibreeden likes this post
Reply
#3
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.
Reply
#4
(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.
Reply
#5
Ok, so what would the WHERE clause be in order to update the row that the cursor's on?
Reply
#6
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?
Reply
#7
There is no unique key, there is a primary key on field1.

There are 15 columns made up of varchar's and integer's.
Reply
#8
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()
Reply
#9
Thanks, but that only updates the first row.

The remaining rows are not updated.
Reply
#10
Can you show the output?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Copy xml content from webpage and save to locally without special characters Nik1811 14 617 Mar-26-2024, 09:28 AM
Last Post: Nik1811
Thumbs Up Convert word into pdf and copy table to outlook body in a prescribed format email2kmahe 1 705 Sep-22-2023, 02:33 PM
Last Post: carecavoador
Question Using SQLAlchemy, prevent SQLite3 table update by multiple program instances Calab 3 704 Aug-09-2023, 05:51 PM
Last Post: Calab
  Compare fields from two csv files georgebijum 3 1,335 Apr-25-2022, 11:16 PM
Last Post: Pedroski55
  Display table field on multiple lines, 'wordwrap' 3python 0 1,747 Aug-06-2021, 08:17 PM
Last Post: 3python
  How to update values in a pyarrow table? xraphael75 1 3,626 Jan-25-2021, 02:14 PM
Last Post: xraphael75
Question Python + Google Sheet | Best way to update specific cells in a single Update()? Vokofe 1 2,628 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Bug ? when dataclass field name == field type Cyril 0 1,526 Oct-22-2020, 03:26 AM
Last Post: Cyril
  Unable to Update SQLite Table sambanerjee 5 2,867 Sep-30-2020, 12:21 PM
Last Post: Larz60+
  Json fields not being retrieved mrcurious2020 4 1,989 Sep-14-2020, 06:24 AM
Last Post: bowlofred

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020