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
  Display table field on multiple lines, 'wordwrap' 3python 0 360 Aug-06-2021, 08:17 PM
Last Post: 3python
  how to filter two fields in json using python python_student 4 1,089 Mar-15-2021, 05:58 PM
Last Post: python_student
  How to update values in a pyarrow table? xraphael75 1 1,451 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 818 Dec-16-2020, 05:26 AM
Last Post: Vokofe
  Bug ? when dataclass field name == field type Cyril 0 599 Oct-22-2020, 03:26 AM
Last Post: Cyril
  Unable to Update SQLite Table sambanerjee 5 1,245 Sep-30-2020, 12:21 PM
Last Post: Larz60+
  Json fields not being retrieved mrcurious2020 4 897 Sep-14-2020, 06:24 AM
Last Post: bowlofred
  Fetch Oracle DB rows & print it in HTML file with table's col headers in table format tssr_2001 1 1,393 Sep-04-2020, 01:39 PM
Last Post: ibreeden
  How to create db table with SQLite and SQLAlchemy?? marcello86 1 1,224 Sep-02-2020, 03:05 PM
Last Post: marcello86
  copy content of text file with three delimiter into excel sheet vinaykumar 0 797 Jul-12-2020, 01:27 PM
Last Post: vinaykumar

Forum Jump:

User Panel Messages

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