如何在sql表中只删除或编辑一行?

2024-03-29 02:14:53 发布

您现在位置:Python中文网/ 问答频道 /正文

that is my app and I used search button to edit information easily我用python中的代码向本地数据库添加、删除、更新信息……它在添加信息时运行良好,但当我使用update时,数据库中的所有信息或行都会变得相同。另外,当删除所有数据时,可能是由于使用一个搜索栏将添加的数据带到要编辑或删除的位置

这是我的密码:

import sys
import os
import time

from PyQt5 import QtCore, QtGui, QtWidgets, uic
import mysql.connector
from mysql.connector import errorcode

FORM_CLASS, _ = uic.loadUiType(os.path.join(os.path.dirname(__file__),"mahmoudtarek.ui"))

class Main(QtWidgets.QMainWindow, FORM_CLASS):
    def __init__(self,parent=None):
        super(Main,self).__init__(parent)
        self.setupUi(self)
        self.InitUI()
        self.conn = None

        self.handle_buttons()
        self.handle_db_connections()

    def InitUI(self):
        ## changes in the run time
        pass

    def handle_buttons(self):
          ## all buttons in the app
        self.pushButton.clicked.connect(self.add_mahmoud_friends)
        self.pushButton_3.clicked.connect(self.update_mahmoud_friends)
        self.pushButton_2.clicked.connect(self.delete_mahmoud_friends)
        self.pushButton_6.clicked.connect(self.search_mahmoud_friends)


    def handle_db_connections(self):
        try:
            self.conn = mysql.connector.connect(
                host='127.0.0.1',
                database='mydb',
                user='root',
                password='134668691011',
                use_pure=True)  # use_pure is set to true

            if self.conn.is_connected():
                db_Info = self.conn.get_server_info()
                print("Connected to MySQL database using C extension... MySQL Server version on ", db_Info)
        except mysql.connector.Error as err:
            print("Error while connecting to MySQL using C extension", err)

    def add_mahmoud_friends(self):
        mth_friends = self.lineEdit.text()
        mth_search = self.lineEdit_4.text()
        if self.conn:
            c = self.conn.cursor()
            try:
                c.execute('''INSERT INTO ahmed (mth_friends,mth_search) values (%s,%s)''', (mth_friends,mth_search))
                self.conn.commit()
                self.lineEdit.setText('')
                self.lineEdit_4.setText('')
                self.statusBar.showMessage('ok mahmoud')


            except mysql.connector.Error as err:
                print("Error: ", err)

    def update_mahmoud_friends(self):
        mth_friends = self.lineEdit.text()
        mth_search = self.lineEdit_4.text()
        if self.conn:
            c = self.conn.cursor()
            try:
                c.execute('''UPDATE ahmed SET mth_friends = %s,mth_search = %s''', (mth_friends, mth_search))
                self.conn.commit()
                self.lineEdit.setText('')
                self.lineEdit_4.setText('')
                self.statusBar.showMessage('ok mahmoud')
                self.lineEdit_3.setText('')


            except mysql.connector.Error as err:
                print("Error: ", err)

    def delete_mahmoud_friends(self):
        c = self.conn.cursor()
        sql = '''DELETE FROM ahmed WHERE mth_search = %s'''
        mth_search = self.lineEdit_3.text()
        c.execute(sql, [(mth_search)])
        self.conn.commit()
        self.statusBar.showMessage("ok")
        self.lineEdit.setText('')
        self.lineEdit_4.setText('')
        self.lineEdit_3.setText('')


    def search_mahmoud_friends(self):
        if self.conn:
            c = self.conn.cursor()

            try:

                sql = '''SELECT * FROM ahmed WHERE mth_search = %s'''
                mth_search = self.lineEdit_3.text()
                c.execute(sql, [(mth_search)])
                data = c.fetchall()
                for row in data :
                    print(row)
                    self.lineEdit.setText(str(row[1]))
                    self.lineEdit_4.setText(str(row[2]))

            except mysql.connector.Error as err:
              print("Error: ", err)

    def closeEvent(self, event):
        if self.conn:
            self.conn.close()
        super(Main, self).closeEvent(event)


def main():
    app= QtWidgets.QApplication(sys.argv)
    window =Main()
    window.show()
    app.exec_()

if __name__ == '__main__':
    main()

我需要帮助,因为我还是一个初学者,如果有人知道这个问题,请给我写更正的代码。因为我找了太多…最后谢谢你


Tags: importselfsearchconnectorifdefmysqlerror
1条回答
网友
1楼 · 发布于 2024-03-29 02:14:53

UPDATE时,需要添加一个条件,否则表中的每一行都将用这些值更新。你知道吗

在代码中:

c.execute('''UPDATE ahmed SET mth_friends = %s,mth_search = %s''', (mth_friends, mth_search))

应该有这样的条件:

c.execute('''UPDATE ahmed SET mth_friends = %s,mth_search = %s WHERE {ADD CONDITION HERE}''', (mth_friends, mth_search))

如果要更新或删除单个行,则需要条件匹配主键或唯一键列。使用包含任何其他类型列的条件将更新或删除与该条件匹配的所有行。(感谢Raymond Nijland

相关问题 更多 >