CODEWITHSUNDEEP
pythonpython-2.7sqlite
Francis
Francis

Reputation: 71

Python - SQLite : Update two record with same identify value

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

Upvotes: 1

Views: 143

Answers (1)

Ajax1234
Ajax1234

Reputation: 71451

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()])

Upvotes: 2

Related Questions