Python Forum
pyMySQL How do I get the row id
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
pyMySQL How do I get the row id
#1
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_())
Reply
#2
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.
Reply
#3
Thanks for the feed-back. You made very good points and I will sure follow your suggestions!
Reply
#4
I SOLVED THE PROBLEM. Check the link below to see my solution:

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

Thanks.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  pymysql can't do SELECT * Pedroski55 3 553 Sep-11-2021, 10:18 PM
Last Post: Pedroski55
  pymysql won't handle some diacritic characters awarren2001AD 0 575 Apr-16-2020, 08:58 AM
Last Post: awarren2001AD
  pyMySQL - ROW_NUMBER in SQL statement JayCee 1 1,112 Apr-12-2020, 08:40 PM
Last Post: JayCee
  PyMySQL return a single dictionary Valon1981 2 819 Feb-20-2020, 04:07 PM
Last Post: Valon1981
  pymysql: formating ouput of query wardancer84 18 4,830 Oct-04-2018, 01:54 PM
Last Post: wardancer84
  pymysql ifnull aland 4 2,337 Sep-06-2018, 05:58 AM
Last Post: buran
  get last row id in pymysql tony1812 1 8,363 Sep-26-2017, 11:25 PM
Last Post: Larz60+
  pymysql question tony1812 1 1,891 Sep-26-2017, 12:41 AM
Last Post: Larz60+
  pymysql on macos seirra tony1812 5 2,860 Sep-17-2017, 03:24 PM
Last Post: Larz60+
  PyMySQL Dean Stackhouse 1 3,408 Oct-05-2016, 07:26 PM
Last Post: micseydel

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020