CODEWITHSUNDEEP
pythonpython-3.xsqlitepyqtpyqt5
Sator
Sator

Reputation: 776

PyQt5.QtSql.QSqlError while inserting lineEdit input into database

I want to insert the QLineEdit input into a database

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

I get this error

Parameter count mismatch

When I insert a list instead of the LineEdit input the function works fine.

I checked that the table exists in the database also the column
output: ['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")

Upvotes: 1

Views: 315

Answers (1)

eyllanesc
eyllanesc

Reputation: 244132

sqlite is a database that has a particular characteristic: If the database does not exist when you try to open then it will be created so the open() method will always return True so it can generate silent problems.

In this case it is always advisable to use the full path, this can be explicitly: "/full/path/of/database" or built based on the location of the script, for example if the database is in the same script folder then you can use the following code:

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

In your case you are probably using some IDE that when running the script uses a working directory different from the location of the script.

Upvotes: 1

Related Questions