Python - SQLite : Update two record with same identify value

Question

i have a script like below :

import psutil

import sqlite3

DISK = {'1': ['C:\\', 'C:\\', 'NTFS', 'rw,fixed', '75.0Gb', '54.0Gb', '20.0Gb', '72.2%'], '2': ['D:\\', 'D:\\', 'NTFS', 'rw,fixed', '399.0Gb', '208.0Gb', '191.0Gb', '52.2%']}

conn = sqlite3.connect("Test.db")
c = conn.cursor()
result = c.execute("SELECT * FROM clientinfo WHERE IP = ?", ("192.168.10.111",))

if (len(result.fetchall()) > 0):
    for x in DISK :
        c.execute("UPDATE disk SET Device = ?, 'Mount Point' = ?, 'fstyle' = ?, 'opts' = ?, 'total' = ?, 'used' = ?, 'free' = ?, 'percent' = ? WHERE Client_IP = ?", (DISK[x][0], DISK[x][1], DISK[x][2], DISK[x][3], DISK[x][4], DISK[x][5], DISK[x][6], DISK[x][7], "192.168.10.111"))
else :
    for x in DISK :
    c.execute("INSERT INTO disk('Client_IP', 'Device', 'Mount Point', 'fstyle', 'opts', 'total', 'used', 'free', 'percent') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", ("192.168.10.111", DISK[x][0], DISK[x][1], DISK[x][2], DISK[x][3], DISK[x][4], DISK[x][5], DISK[x][6], DISK[x][7]))

conn.commit()
conn.close()

Script will check in db if there is any record about IP "192.168.10.111". If db already have record about IP "192.168.10.111", Script will update data from dict DISK to table disk of db. If db don't have record about IP "192.168.10.111", Script will create record to insert **DISK"" to database.

INSERT command work well but the UPDATE command don't work like i want. After INSERT command run, in table disk i will have two record about disk C and disk D with same value of column Client_IP (192.168.10.111). After UPDATE, two record of IP "192.168.10.111" get same value on every column ehich is very wrong. One record must be contain information about disk C and another record cotain disk D information.

How can i make the UPDATE work right ? length of dict DISK depend on how many mounted devices the computer has. So i need to use for loop to UPDATE but not static UPDATE.

Please tell me how to fix this, Many thanks, Francis

it is Client_IP in table disk. please note that the SELECT is select from different table which is clientinfo not disk. — Francis
– Francis, Commented May 7, 2018 at 15:03
When you have multiple rows with the same Client_IP value, then this column does not identify the rows. — CL.
– CL., Commented May 7, 2018 at 18:32
When you get the two rows, how exactly do you determine which one is the correct one? Which column do you use to identify the one with the correct disk? — CL.
– CL., Commented May 13, 2018 at 16:11

Ajax1234 · Accepted Answer · 2018-05-06 19:03:57Z

2

Your SELECT query column value is incorrect based on your UPDATE and INSERT queries later on. Instead of IP, should't it be CLIENT_IP? Also, you need to change your string query formatting. Remove the single quotes form your column names. Lastely, you can shorten your code by using a simple list comprehension and cursor.executemany:

import sqlite3
DISK = {'1': ['C:\\', 'C:\\', 'NTFS', 'rw,fixed', '75.0Gb', '54.0Gb', '20.0Gb', '72.2%'], '2': ['D:\\', 'D:\\', 'NTFS', 'rw,fixed', '399.0Gb', '208.0Gb', '191.0Gb', '52.2%']}

conn = sqlite3.connect("Test.db")
c = conn.cursor()
if not list(c.execute('SELECT * FROM lientinfo WHERE CLIENT_IP = ?',  ("192.168.10.111",))):
  c.executemany("INSERT INTO disk (Client_IP, Device, Mount Point, fstyle, opts, total, used, free, percent) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", [["192.168.10.111", *i] for i in DISK.values()])
else:
   c.executemany('UPDATE disk SET Device = ?, Mount Point = ?, fstyle = ?, opts = ?, total = ?, used = ?, free = ?, percent = ? WHERE Client_IP = ?', [[*i, "192.168.10.111"] for i in DISK.values()])

edited May 6, 2018 at 19:03

answered May 6, 2018 at 18:58

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Francis Over a year ago

Hi Ajax1234, the SELECT is right cause clientinfo is a different table which only have one column contain client IP. The INSERT and UPDATE is for table disk. But it doesn't matter, the problem is i can run your code. There is a Syntax error about *i . Please kindly note that i\m using Python 2.7.

Collectives™ on Stack Overflow

Python - SQLite : Update two record with same identify value

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related