CODEWITHSUNDEEP
pythonpython-3.xsqlite
Alex Rebell
Alex Rebell

Reputation: 374

Counting of rows

Please tell me how I can calculate how many rows have been updated and added to the table from the CSV file. My code with which I update or add data:

#!/usr/bin/env python
import sqlite3
import csv
import time

database_name = "/root/Test/test.db"
csv_file_name = "/root/test.csv"
print("[!] Updating the database...")
con = sqlite3.connect(database_name)
cur = con.cursor()
with open(csv_file_name, 'r') as f:
    dr = csv.DictReader(f, delimiter=';')
    test = [(i['column_1'], i['column_2'], i['column_3'], i['column_4'], 
             i['column_5'], i['column_2'], i['column_3'],
             i['column_4'], i['column_5'],) for i in dr]

cur.executemany("INSERT INTO mytable (column_1, column_2, column_3, column_4, column_5, "
                "VALUES (?, ?, ?, ?, ?) "
                "ON CONFLICT(column_1) DO UPDATE SET "
                "column_2 = ?, column_3 = ?, column_4 = ?, column_5 = ?,", test)
con.commit()
con.close()
time.sleep(1)
print("[+] The database is updated!")

Upvotes: 1

Views: 115

Answers (2)

Leo Arad
Leo Arad

Reputation: 4472

You can do it by counting the length of the table before the update and after like

#!/usr/bin/env python
import sqlite3
import csv
import time

database_name = "/root/Test/test.db"
csv_file_name = "/root/test.csv"
print("[!] Updating the database...")
con = sqlite3.connect(database_name)
table_count_before = con.execute("select COUNT(*) as count from mytable").fetchall()
cur = con.cursor()
with open(csv_file_name, 'r') as f:
    dr = csv.DictReader(f, delimiter=';')
    test = [(i['column_1'], i['column_2'], i['column_3'], i['column_4'],
             i['column_5'], i['column_2'], i['column_3'],
             i['column_4'], i['column_5'],) for i in dr]


cur.executemany("INSERT INTO mytable (column_1, column_2, column_3, column_4, column_5, "
                "VALUES (?, ?, ?, ?, ?) "
                "ON CONFLICT(column_1) DO UPDATE SET "
                "column_2 = ?, column_3 = ?, column_4 = ?, column_5 = ?,", test)
con.commit()
table_count_after = con.execute("select COUNT(*) as count from mytable").fetchall()
print(f"Update {table_count_after[0].count - table_count_before[0].count} rows")
con.close()
time.sleep(1)
print("[+] The database is updated!")

OR

#!/usr/bin/env python
import sqlite3
import csv
import time

database_name = "/root/Test/test.db"
csv_file_name = "/root/test.csv"
print("[!] Updating the database...")
con = sqlite3.connect(database_name)
table_count_before = con.execute("select * from mytable").fetchall()
cur = con.cursor()
with open(csv_file_name, 'r') as f:
    dr = csv.DictReader(f, delimiter=';')
    test = [(i['column_1'], i['column_2'], i['column_3'], i['column_4'],
             i['column_5'], i['column_2'], i['column_3'],
             i['column_4'], i['column_5'],) for i in dr]


cur.executemany("INSERT INTO mytable (column_1, column_2, column_3, column_4, column_5, "
                "VALUES (?, ?, ?, ?, ?) "
                "ON CONFLICT(column_1) DO UPDATE SET "
                "column_2 = ?, column_3 = ?, column_4 = ?, column_5 = ?,", test)
con.commit()
table_count_after = con.execute("select * from mytable").fetchall()
print(f"Update {len(table_count_after) - len(table_count_before)} rows")
con.close()
time.sleep(1)
print("[+] The database is updated!")

Upvotes: 1

Muhammad Junaid Haris
Muhammad Junaid Haris

Reputation: 452

To see the number of rows updated and added you can use,

cur.rowcount

Upvotes: 1

Related Questions