将lineEdit输入插入数据库时出现PyQt5.QtSql.QSqlError

Question

我想将QLineEdit输入插入数据库

import sys
from PyQt5 import QtWidgets as qtw
from PyQt5 import QtCore as qtc
from PyQt5 import QtGui as qtg

from PyQt5 import QtSql as qsql


class Secondwindow(qtw.QWidget):
    '''
    description einfügen
    '''

    # Attribut Signal

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        # your code will go here

        # Messung starten
        self.connectdb_button = qtw.QPushButton("Connect to Database ?")
        hlaout_layout = qtw.QHBoxLayout()
        hlaout_layout.addStretch(1)
        hlaout_layout.addWidget(self.connectdb_button)
        hlaout_layout.addStretch(1)

        # input /nested layout
        input1_label = qtw.QLabel("input 1 ")
        self.input_1 = qtw.QLineEdit()
        input1_hlayout = qtw.QHBoxLayout()
        input1_hlayout.addStretch(1)
        input1_hlayout.addWidget(input1_label)
        input1_hlayout.addWidget(self.input_1)
        input1_hlayout.addStretch(1)
        input1_hlayout.setAlignment(qtc.Qt.AlignHCenter)

        input2_label = qtw.QLabel("input 2 ")
        self.input_2 = qtw.QLineEdit()
        input2_hlayout = qtw.QHBoxLayout()
        input2_hlayout.addStretch(1)
        input2_hlayout.addWidget(input2_label)
        input2_hlayout.addWidget(self.input_2)
        input2_hlayout.addStretch(1)
        input2_hlayout.setAlignment(qtc.Qt.AlignHCenter)

        input3_label = qtw.QLabel("input 3 ")
        self.input_3 = qtw.QLineEdit()
        input3_hlayout = qtw.QHBoxLayout()
        input3_hlayout.addStretch(1)
        input3_hlayout.addWidget(input3_label)
        input3_hlayout.addWidget(self.input_3)
        input3_hlayout.addStretch(1)
        input3_hlayout.setAlignment(qtc.Qt.AlignHCenter)

        input4_label = qtw.QLabel("input 4 ")
        self.input_4 = qtw.QLineEdit()
        input4_hlayout = qtw.QHBoxLayout()
        input4_hlayout.addStretch(1)
        input4_hlayout.addWidget(input4_label)
        input4_hlayout.addWidget(self.input_4)
        input4_hlayout.addStretch(1)
        input4_hlayout.setAlignment(qtc.Qt.AlignHCenter)

        input5_label = qtw.QLabel("input 5 ")
        self.input_5 = qtw.QLineEdit()
        input5_hlayout = qtw.QHBoxLayout()
        input5_hlayout.addStretch(1)
        input5_hlayout.addWidget(input5_label)
        input5_hlayout.addWidget(self.input_5)
        input5_hlayout.addStretch(1)
        input5_hlayout.setAlignment(qtc.Qt.AlignHCenter)

        # select button
        self.select_button = qtw.QPushButton("start query ")
        select_buttonlayout = qtw.QHBoxLayout()
        select_buttonlayout.setAlignment(qtc.Qt.AlignHCenter)
        select_buttonlayout.addStretch(1)
        select_buttonlayout.addWidget(self.select_button)
        select_buttonlayout.addStretch(1)

        # hauptlayout
        haupt_layout = qtw.QFormLayout()

        haupt_layout.addRow(self.connectdb_button)
        haupt_layout.setVerticalSpacing(20)
        haupt_layout.addRow(input1_hlayout)
        haupt_layout.setVerticalSpacing(20)
        haupt_layout.addRow(input2_hlayout)
        haupt_layout.setVerticalSpacing(20)
        haupt_layout.addRow(input3_hlayout)
        haupt_layout.setVerticalSpacing(20)
        haupt_layout.addRow(input4_hlayout)
        haupt_layout.setVerticalSpacing(20)
        haupt_layout.addRow(input5_hlayout)
        haupt_layout.setVerticalSpacing(30)
        haupt_layout.addRow(select_buttonlayout)

        self.setLayout(haupt_layout)

        self.show()

        # Funktionalität
        self.connectdb_button.clicked.connect(self.connect_to_db)

        self.select_button.clicked.connect(self.query_data)

    def connect_to_db(self):
        self.database = qsql.QSqlDatabase.addDatabase('QSQLITE')
        self.database.setDatabaseName('qtdatabase.db')
        self.database.open()
        if self.database.isOpen():
            qtw.QMessageBox.about(self, 'connectet', "connection to db successful")

        # einfügen siehe pdf
        # if not self.db.open():
        #     error = self.db.lastError().text()
        # qtw.QMessageBox.critical(
        #     None, 'DB Connection Error',
        #     'Could not open database file: '
        #     f'{error}')
        # sys.exit(1)

    def query_data(self):
        mein_input = []

        item1 = self.input_1.text()
        item2 = self.input_2.text()
        item3 = self.input_3.text()
        item4 = self.input_4.text()
        item5 = self.input_5.text()

        mein_input.append(item1)
        mein_input.append(item2)
        mein_input.append(item3)
        mein_input.append(item4)
        mein_input.append(item5)

        self.query = qsql.QSqlQuery()
        self.query.prepare("INSERT INTO userinput(firstcolumns) VALUES(?)")

        self.query.addBindValue(mein_input)

        if not self.query.execBatch():
            print(self.query.lastError().text())

        self.close()
        # if not self.database.isOpen():
        #     qtw.QMessageBox.about(self, 'Erfolg', "Data inserted successfully")

if __name__ == '__main__':
    app = qtw.QApplication(sys.argv)
    w = Secondwindow()
    sys.exit(app.exec_())

我得到这个错误

Parameter count mismatch

当我插入一个列表而不是LineEdit输入时，该函数工作正常

我检查了数据库中是否存在该表以及列
输出：['firstcolumns']

import sqlite3

# connect to database query starten
try:
    db_connection = sqlite3.connect("qtdatabase.db")

    cursor = db_connection.cursor()

    column_abfrage = '''SELECT * FROM userinput;'''

    cursor.execute(column_abfrage)
    cursor.close()

    names = list(map(lambda x: x[0], cursor.description))

    print(names)

except sqlite3.Error as error:
    print(error)

finally:
    if (db_connection):
        db_connection.close()
        print("db connection closed")

Answer 1

sqlite是一个具有特定特征的数据库：如果在尝试打开时该数据库不存在，则将创建该数据库，因此open()方法将始终返回True，以便生成静默问题

在这种情况下，建议始终使用完整路径，可以显式地使用：“/full/path/of/database”或基于脚本的位置构建，例如，如果数据库位于同一脚本文件夹中，则可以使用以下代码：

import os

# ...

current_dir = os.path.dirname(os.path.realpath(__file__))
db_path = os.path.join(current_dir, "qtdatabase.db")
self.database = qsql.QSqlDatabase.addDatabase('QSQLITE')
self.database.setDatabaseName(db_path)
if self.database.open():
    qtw.QMessageBox.about(self, 'connectet', "connection to db successful")

在您的例子中，您可能正在使用某些IDE，在运行脚本时，该IDE使用与脚本位置不同的工作目录