是否有sqlite函数检查单元格值是否存在

Question

我正在处理这个由3列组成的简单表格

• ID这只是所有行的序列号（PRIMARY KEY NOT NULL）

• PCode其中包含给定给产品的代码（INTEGER）

• PName其中包含产品名称（TEXT）

我想创建一个tkinter窗口，允许用户将新产品（代码和名称）添加到此表中。但它首先需要检查产品代码或名称是否已经存在，如果产品名称存在，则显示窗口错误，如果产品代码存在，则为其提供用户指定的名称。如果产品代码和名称不存在，请插入包含它们的新行。 如何使sqlite检查文本或整数是否已存在？多谢各位

Answer 1

Normall方法是使用SELECT检查数据库中是否已经存在值。如果结果为零，则可以添加新值

类似这样的东西（它可能还需要cursor、fetch()或fetchall()等等）

results = db.execute("SELECT ...") 

if results: 
    #... code ...

#or 

if len(results) > 0:
    #... code ...

---

可能您必须在中使用两个SELECT查询

results = db.execute("SELECT ... FROM ... WHERE PName = ... ") 

if results: 
    print("ERROR: PName exists with PCode", results[0])
else:
    results = db.execute("SELECT ... FROM ... WHERE PCode = ... ") 
    if results: 
         print("PCode exists with PName", results[1])
         db.execute("UPDATE ...") 
    else:
         db.execute("INSERT ...") 

---

编辑：

最小工作代码

import sqlite3

#  - functions  -

def drop_db(db):
    db.execute('''DROP TABLE IF EXISTS product''')
    db.commit()

def create_db(db):
    db.execute('''CREATE TABLE IF NOT EXISTS product (
        id INTEGER PRIMARY KEY,
        PCode INTEGERT,
        PName TEXT
    );''')
    db.commit()

def insert_example_data(db):    
    db.execute("INSERT INTO product (PCode, PName) VALUES (1, 'Phone')")
    db.execute("INSERT INTO product (PCode, PName) VALUES (2, 'Car')")
    db.commit()    

def test(db, code, name):
    print(' - test:', name, code, ' -')

    result = db.execute("SELECT * FROM product WHERE PName=?", (name,)).fetchone()

    if result:
        print('ERROR PName exists with PCode:', result[1], 'and ID:', result[0])
    else:
        result = db.execute("SELECT * FROM product WHERE PCode=?", (code,)).fetchone()
        if result:
            print('PCode exists with PName:', result[2], 'and ID:', result[0])
            print('UPDATE PName to:', name)
            db.execute('UPDATE product SET PName=? WHERE Pcode=?', (name, code))
            db.commit()
        else:
            print('INSERT')
            db.execute('INSERT INTO product (PCode, PName) VALUES (?, ?)', (code, name))
            db.commit()

def display(db, code=None, name=None):
    print(' - display', code, name, ' -')
    if code is None and name is None:
        print('** need code and/or name **')
        return

    if code is not None and name is None:
        results = db.execute("SELECT * FROM product WHERE PCode=?", (code,))
    if code is None and name is not None:
        results = db.execute("SELECT * FROM product WHERE PName=?", (name,))
    if code is not None and name is not None:
        results = db.execute("SELECT * FROM product WHERE PCode=? AND PName=?", (code, name,))

    for row in results:
        print(row)

#  - main  -

db = sqlite3.Connection("data.sqlite")

drop_db(db)
create_db(db)
insert_example_data(db)

test(db, 3, 'Car')
test(db, 7, 'Boat')
test(db, 7, 'House')

display(db, code=7)
display(db, name='Boat')
display(db, name='House')
display(db)

db.close()