pyMySQL How do I get the row id

[JayCee]

Hello everyone,

Greetings from Brazil! I have a simple question: How do I get the row id of records? I need that so I can navigate through the rows using a form I designed in PyQT5, without having to depend on the user ID column in the table.

The method I tried below works ONLY if the record IDs start with 1 and are in sequeced increments of 1 (1,2,3 4, 5, 6, 7, ...), but when rows are deleted from a table,it leaves gaps in the user ID (for example: 2, 3, 5, 7, 9, ...), the code below doesn't work perfectly.

Thank you very much for your time and help. I really appreciate it.

Best regards.

# -*- coding: utf-8 -*-
# Form implementation generated from reading ui file 'ui/insert.ui'
# Created by: PyQt5 UI code generator 5.14.1
# WARNING! All changes made in this file will be lost!


import sys
import pymysql
from PyQt5 import QtCore, QtWidgets
from pymysql import Error

rowNo = 1

connection = pymysql.connect(
    host='localhost',
    user='bremi691_ead',
    password='dspm7356',
    db='bremi691_ead'
)

cursor = connection.cursor()
sql = "SELECT id, nome, email, senha, data_cadastro, niveis_acesso_id, validacao, ativo, como_chegou FROM usuarios ORDER BY nome ASC"
cursor.execute(sql)


class Ui_Dialog(object):

    def setupUi(self, Dialog):
        Dialog.setObjectName("Dialog")
        Dialog.resize(448, 300)

        self.lineEdit_name = QtWidgets.QLineEdit(Dialog)
        self.lineEdit_name.setGeometry(QtCore.QRect(130, 50, 241, 21))
        self.lineEdit_name.setInputMethodHints(QtCore.Qt.ImhUppercaseOnly)
        self.lineEdit_name.setObjectName("lineEdit_name")

        self.lineEdit_email = QtWidgets.QLineEdit(Dialog)
        self.lineEdit_email.setGeometry(QtCore.QRect(130, 90, 191, 21))
        self.lineEdit_email.setInputMethodHints(QtCore.Qt.ImhEmailCharactersOnly)
        self.lineEdit_email.setObjectName("lineEdit_email")

        self.lineEdit_pwd = QtWidgets.QLineEdit(Dialog)
        self.lineEdit_pwd.setGeometry(QtCore.QRect(130, 130, 131, 21))
        self.lineEdit_pwd.setInputMethodHints(QtCore.Qt.ImhSensitiveData | QtCore.Qt.ImhUppercaseOnly)
        self.lineEdit_pwd.setObjectName("lineEdit_pwd")

        self.lineEdit_market = QtWidgets.QLineEdit(Dialog)
        self.lineEdit_market.setGeometry(QtCore.QRect(130, 170, 131, 21))
        self.lineEdit_market.setInputMethodHints(QtCore.Qt.ImhUppercaseOnly)
        self.lineEdit_market.setObjectName("lineEdit_market")

        self.pushButton_first = QtWidgets.QPushButton(Dialog)
        self.pushButton_first.setGeometry(QtCore.QRect(70, 240, 61, 28))
        self.pushButton_first.setObjectName("pushButton_first")
        self.pushButton_first.clicked.connect(ShowFirst)

        self.pushButton_previous = QtWidgets.QPushButton(Dialog)
        self.pushButton_previous.setGeometry(QtCore.QRect(150, 240, 61, 28))
        self.pushButton_previous.setObjectName("pushButton_previous")
        self.pushButton_previous.clicked.connect(ShowPrevious)

        self.pushButton_next = QtWidgets.QPushButton(Dialog)
        self.pushButton_next.setGeometry(QtCore.QRect(230, 240, 61, 28))
        self.pushButton_next.setObjectName("pushButton_next")
        self.pushButton_next.clicked.connect(ShowNext)

        self.pushButton_last = QtWidgets.QPushButton(Dialog)
        self.pushButton_last.setGeometry(QtCore.QRect(310, 240, 61, 28))
        self.pushButton_last.setObjectName("pushButton_last")
        self.pushButton_last.clicked.connect(ShowLast)

        self.retranslateUi(Dialog)

    def retranslateUi(self, Dialog):
        _translate = QtCore.QCoreApplication.translate
        Dialog.setWindowTitle(_translate("Dialog", "Dialog"))
        self.pushButton_first.setText(_translate("Dialog", "<<"))
        self.pushButton_previous.setText(_translate("Dialog", "<"))
        self.pushButton_next.setText(_translate("Dialog", ">"))
        self.pushButton_last.setText(_translate("Dialog", ">>"))

        ShowFirst(self)


def ShowFirst(self):
    try:
        cursor.execute(sql)
        row = cursor.fetchone()
        if row:
            ui.lineEdit_name.setText(row[1])
            ui.lineEdit_email.setText(row[2])
            ui.lineEdit_pwd.setText(row[3])
            ui.lineEdit_market.setText(row[8])
    except Error as e:
        print("Error in accessing table")


def ShowPrevious(self):
    global rowNo
    rowNo -= 1
    sql = "SELECT id, nome, email, senha, data_cadastro, niveis_acesso_id, validacao, ativo, como_chegou FROM usuarios WHERE id=" + str(rowNo) + " ORDER BY nome ASC"
    cursor.execute(sql)
    row = cursor.fetchone()

    if row:
        ui.lineEdit_name.setText(row[1])
        ui.lineEdit_email.setText(row[2])
        ui.lineEdit_pwd.setText(row[3])
        ui.lineEdit_market.setText(row[8])
    else:
        rowNo += 1


def ShowNext(self):
    global rowNo
    rowNo += 1
    sql = "SELECT id, nome, email, senha, data_cadastro, niveis_acesso_id, validacao, ativo, como_chegou FROM usuarios WHERE id=" + str(rowNo) + " ORDER BY nome ASC"
    cursor.execute(sql)
    row = cursor.fetchone()

    if row:
        ui.lineEdit_name.setText(row[1])
        ui.lineEdit_email.setText(row[2])
        ui.lineEdit_pwd.setText(row[3])
        ui.lineEdit_market.setText(row[8])
    else:
        rowNo -= 1


def ShowLast(self):
    cursor.execute(sql)
    for row in cursor.fetchall():
        ui.lineEdit_name.setText(row[1])
        ui.lineEdit_email.setText(row[2])
        ui.lineEdit_pwd.setText(row[3])
        ui.lineEdit_market.setText(row[8])


if __name__ == "__main__":
    app = QtWidgets.QApplication(sys.argv)
    Dialog = QtWidgets.QDialog()
    ui = Ui_Dialog()
    ui.setupUi(Dialog)
    Dialog.show()
    sys.exit(app.exec_())

[ndc85430]

Perhaps you could just fetch all the rows once and since you're ordering them, the row number would just be the index in the list of results. If the data set is large so that that's impractical, then probably there's a way to return those values as part of the query. That's more of an SQL question, though (and I'm not sure about the specifics of MySQL).

Also, you shouldn't be creating SQL statements by joining strings together. It's bad practice because it's vulnerable to SQL injection. You should be using the parameter substitution to do it safely - see the examples in the docs: https://pymysql.readthedocs.io/en/latest...mples.html - you use %s in the string and then pass the value to be used to execute.

---

You should also avoid global variables - they lead to code that's difficult to follow and maintain.

[JayCee]

Thanks for the feed-back. You made very good points and I will sure follow your suggestions!

[JayCee]

I SOLVED THE PROBLEM. Check the link below to see my solution:

https://python-forum.io/Thread-PyQt-Tuto...navigation

Thanks.