时间戳

首页 > 解决方案 > 在 python 代码中添加到数据库时如何修复错误？

python - 在 python 代码中添加到数据库时如何修复错误？

问题描述

我有一个名为“地址簿”的数据库，并且在数据库中创建了代码表，这部分没有问题，但是在添加数据时出现错误消息框显示“添加到数据库时发生错误。”。我无法修复它。我该如何解决这种情况？

try:
  import tkinter as tk
  from tkinter import filedialog
  from tkinter import messagebox
  import sqlite3
  from PIL import Image, ImageTk
  print("Kutuphanler import edildi.")
except:
  !pip install pysqlite3
  !pip install PILLOW
  print("\n\nHucreyi yeniden calistiriniz.")
def openConn(dbName:str):
    # Veritabanina baglanma
    conn = sqlite3.connect(dbName)
    #Veritabaninda  islemi yapabilmek icin cursor tanimlama
    cur = conn.cursor()
    return conn, cur

#Veritabani olusturma
def createTable(dbName:str, tableName:str):
    try:
        conn, cur = openConn(dbName)
        sqlQuery = "CREATE TABLE  {}(firstName text, middleName text, surname text, DOB text, address text, city text, country text, zipcode text, imPath text)".format(tableName)
        cur.execute(sqlQuery)
        conn.close()
        return True
    except:
        tk.messagebox.showerror("Hata", "Veritabani olusturulurken hata meydana geldi.")
        return False

#Her bir girdinin uygun formatta gosterilmesi
def strResult(result):
    return "{} {} {}, {} - {}, {}, {}, {}".format(result[0],result[1],result[2],result[3],result[4],result[5],result[6],result[7])


#Tum entry alanlarinin temizlenmesi
def clearAllEntries():
    #Olusturdugunuz tum entry widgetlarin iceriklerini temizleyiniz
    myEntry.delete(0, 'end')
    return True

#Tum Verilerin okunmasi
def printAllRecords(dbName:str, tableName:str):
    try:
        conn, cur = openConn(dbName)
        sqlQuery = "SELECT *, oid FROM {}".format(tableName)
        resultSet = cur.execute(sqlQuery)

        for result in resultSet:
            strResult = strEntry(result)
            tempLabel = tk.Label(formApp, text = strResult)
        return True
    except:
        tk.messagebox.showerror("Hata","Verilerin okunmasi esnasinda hata olustu")
        return False

#Veritabanina entry degerinin eklenmesi
def insertEntry(dbName:str, tableName:str,entryValues:list):
    try:
        conn, cur = openConn(dbName)
        sqlQuery= "INSERT INTO {} VALUES({},{},{},{},{},{},{},{},{}) ".format(tableName, entryValues[0], entryValues[1], entryValues[2], entryValues[3], entryValues[4], entryValues[5], entryValues[6], entryValues[7], entryValues[8])
        cur.execute(sqlQuery)
        conn.commit()
        conn.close()
        clearAllEntries()
        printAllRecords()
        return True
    except:
        tk.messagebox.showerror("Hata","An error occurred while adding to the database.")
        return False


def openImageFile():
    global img, photo
    imPath = filedialog.askopenfilename(initialdir="C:\\", title = "Open an image", filetypes = (("PNG file","*.png"), ("JPEG File","*.jpeg"), ("JPG File","*.jpg"), ("All File Types","*.*")) )

    if imPath:
        img=ImageTk.PhotoImage(Image.open(imPath))
        photo.configure(image=img)

和按钮和条目列表

entry_list=[firstName_entry,middleName_entry,surname_entry,date_entry,address_entry,city_entry,country_entry,zipCode_entry]

    btnSubmit = tk.Button(appWin, text ="Insert Item", command=lambda: insertEntry(dbName=DB_NAME, tableName=TABLE_NAME, entryValues = [entry_list]))

我不明白 InsertEntry 函数有问题吗？还是在按钮部分？

标签： pythondatabasetkinter

解决方案

错误应该在这一行：

sqlQuery= "INSERT INTO {} VALUES({},{},{},{},{},{},{},{},{}) ".format(tableName, entryValues[0], entryValues[1], entryValues[2], entryValues[3], entryValues[4], entryValues[5], entryValues[6], entryValues[7], entryValues[8])

例如，如果entryValues[0]（根据您的表定义，它是firstName）具有 value John，那么最终的 SQL 语句将如下所示：

INSERT INTO sometable VALUES(John,...)

但预期的 SQL 语句应该是：

INSERT INTO sometable VALUES("John",...)

在 SQL 语句中使用占位符，如下所示：

sqlQuery = "INSERT INTO {} VALUES (?,?,?,?,?,?,?,?,?)".format(tableName)
cur.execute(sqlQuery, (entryValues[0].get(), entryValues[1].get(), entryValues[2].get(),
                       entryValues[3].get(), entryValues[4].get(), entryValues[5].get(),
                       entryValues[6].get(), entryValues[7].get(), entryValues[8].get()))
# or shorter version
# cur.execute(sqlQuery, [e.get() for e in entryValues])

注意entryValues是列表Entry，所以需要用来get()获取值。

此外，以下行

btnSubmit = tk.Button(appWin, text="Insert Item",
                      command=lambda: insertEntry(dbName=DB_NAME, tableName=TABLE_NAME, 
                                                  entryValues=[entry_list]))

应该

btnSubmit = tk.Button(appWin, text="Insert Item",
                      command=lambda: insertEntry(dbName=DB_NAME, tableName=TABLE_NAME, 
                                                  entryValues=entry_list)) # without []

推荐阅读