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