Python - SQLite:更新具有相同标识值的两条记录 [英] 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()

脚本会在数据库中检查是否有关于 IP "192.168.10.111" 的任何记录.如果 db 已经有关于 IP "192.168.10.111" 的记录，脚本会将数据从 dict DISK 更新到 db 的表磁盘.如果数据库没有关于 IP "192.168.10.111" 的记录，脚本将创建记录以将 **DISK"" 插入数据库.

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 命令运行良好，但 UPDATE 命令不像我想要的那样工作.INSERT 命令运行后，在表 disk 中，我将有关于磁盘 C 和磁盘 D 的两条记录，其列Client_IP (192.168.10.111) 的值相同.更新后，IP192.168.10.111"的两条记录在每一列上都得到相同的值，这是非常错误的.一条记录必须包含C盘信息，另一条记录包含D盘信息.

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.

我怎样才能使 UPDATE 正常工作?dict DISK 的长度取决于计算机上安装的设备数量.所以我需要使用 for 循环来更新而不是静态更新.

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

推荐答案

根据您的 UPDATE 和 INSERTSELECT 查询列值不正确> 稍后查询.不是IP，不应该是CLIENT_IP吗?此外，您需要更改字符串查询格式.删除列名中的单引号.最后，您可以使用简单的列表推导式和 cursor.executemany 来缩短代码:

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

这篇关于Python - SQLite:更新具有相同标识值的两条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！