时间戳

首页 > 解决方案 > 是否有一个 sqlite 函数来检查单元格值是否存在

python - 是否有一个 sqlite 函数来检查单元格值是否存在

问题描述

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

我想创建一个 tkinter 窗口，让用户将新产品（代码和名称）添加到此表中。但它首先需要检查该产品代码或名称是否已经存在，如果产品名称存在，则显示窗口错误，如果产品代码存在，则给它用户分配的名称。如果产品代码和名称尚不存在，请插入包含它们的新行。如何让 sqlite 检查文本或整数是否已经存在？谢谢你。

标签： pythonsqlite

解决方案

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

推荐阅读