Monday, May 31, 2021

Setting up PyQT5 && Mariadb CRUD Application on Fedora Server 34

Code posted below is actually a kind of POC and may be freely modified any way you like. It is testing standard connection setup Python 3.9.5 to native Fedora's Mariadb-Server running on Server Edition Fedora 34 with Deepin Desktop installed. Here I have to notice that KDE Plasma 5.20 Desktop instance on the same server seems to be a better choice for PyQT5  windowing running CRUD application with any of Mariadb-Server 10.5 or PostgreSQL 13.2 .

First grant remote access to database for root :-

[boris@fedora33server ~]$ mysql -u root -p

Enter password: 

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 18

Server version: 10.5.10-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%' IDENTIFIED BY '*******' WITH GRANT OPTION;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> FLUSH PRIVILEGES ;

Query OK, 0 rows affected (0.000 sec)

Code itself

#!/usr/bin/python3

import sys

import mariadb 

from PyQt5.QtWidgets import QTableWidget, QApplication, QMainWindow, QTableWidget 

from PyQt5.QtWidgets import QTableWidgetItem, QWidget, QPushButton, QLineEdit

from PyQt5 import QtGui

class MyWidget(QWidget):

    def __init__(self):

        super().__init__()

        self.con()

        self.setGeometry(100, 100, 500, 600)

        self.setWindowTitle('Scores List')

        self.tb = Tb(self)

        self.btn = QPushButton('Refresh View', self)

        self.btn.resize(150, 40)

        self.btn.move(300, 10)

        self.btn.clicked.connect(self.upd)

        self.idp = QLineEdit(self)

        self.idp.resize(150, 40)

        self.idp.move(300, 60)

        self.idp.setReadOnly(True)

        self.fio = QLineEdit(self)

        self.fio.resize(150, 40)

        self.fio.move(300, 110)

        self.oce = QLineEdit(self)

        self.oce.resize(150, 40)

        self.oce.move(300, 160)

        self.btn = QPushButton('Add row', self)

        self.btn.resize(150, 40)

        self.btn.move(300, 210)

        self.btn.clicked.connect(self.ins)

        self.btn = QPushButton('Update row', self)

        self.btn.resize(150, 40)

        self.btn.move(300, 260)

        self.btn.clicked.connect(self.change)

        self.btn = QPushButton('Delete row', self)

        self.btn.resize(150, 40)

        self.btn.move(300, 310)

        self.btn.clicked.connect(self.dels)

# local connect to mariadb datatabase 

    def con(self):

        self.conn = mariadb.connect(user = "root",

                              password = "**********",

                              host = "192.168.0.XX",

                              port = 3306,

                              database = "db1")

        self.cur = self.conn.cursor()

    def upd(self):   # insert record into table

        self.conn.commit()

        self.tb.updt()

        self.idp.setText('')

        self.fio.setText('')

        self.oce.setText('')

    def ins(self):

        fio, oce = self.fio.text(), self.oce.text()

        try:

            self.cur.execute("insert into students (name, ocenka) values (%s,%s)",(fio,oce))

        except:

            pass

        self.upd()

  def change(self):   # update record in table

        fio, oce = self.fio.text(), self.oce.text()

        try:

            ids = int(self.idp.text()) 

        except:

            return

        self.cur.execute("update students set name=%s,ocenka=%s where id=%s",(fio,oce,ids,))

        self.upd()

# delete record from table 

    def dels(self):

        try:

            ids = int(self.idp.text()) # row identifier

        except:

            return

        self.cur.execute("delete from students where id=%s",(ids,))

        self.upd()

class Tb(QTableWidget):

    def __init__(self, wg):

        self.wg = wg  # remember window to show table 

        super().__init__(wg)

        self.setGeometry(10, 10, 280, 500)

        self.setColumnCount(3)

        self.verticalHeader().hide();

        self.updt() # обновить таблицу

        self.setEditTriggers(QTableWidget.NoEditTriggers)  

        self.cellClicked.connect(self.cellClick)  # catch mouse click on table 

    def updt(self):

        self.clear()

        self.setRowCount(0);

        self.setHorizontalHeaderLabels(['id', 'Name', 'Score']) 

        self.wg.cur.execute("select * from students order by id")

        rows = self.wg.cur.fetchall()

        i = 0

        for elem in rows:

            self.setRowCount(self.rowCount() + 1)

            j = 0

            for t in elem: # fill in the line

                self.setItem(i, j, QTableWidgetItem(str(t).strip()))

                j += 1

            i += 1

        self.resizeColumnsToContents()

# Reaction on mouse click inside table 

    def cellClick(self, row, col): # row - row number, col - column number 

        self.wg.idp.setText(self.item(row, 0).text())

        self.wg.fio.setText(self.item(row, 1).text().strip())

        self.wg.oce.setText(self.item(row, 2).text().strip())

app = QApplication(sys.argv)

ex = MyWidget()

ex.show()

sys.exit(app.exec_())




Initial setup on Server F34 already running mariadb-server to 
          run PyQT5 python script via CLI:-

$ mkdir WORK
$ cd WORK
$ sudo dnf install mariadb-connector-c-devel
$ sudo dnf install python-devel
$ sudo dnf install qt5-qtbase-devel
$ sudo dnf install qt5-designer
$ sudo dnf groupinstall "C Development Tools and Libraries"
$ python3 -m venv .envs
$ source .envs/bin/activate
(.envs) [boris@fedora34server WORK] pip3 install mariadb
(.envs) [boris@fedora34server WORK] pip install PyQt5
(.envs) [boris@fedora34server WORK] python  MyProgQT5MDB.py



Setup for MySQL 8.0.25

$ python3 -m venv .env
$ source .env/bin/activate
$ pip install mysql-connector-python
$ ~boris/PYMSQL/.env/bin/python3 -m pip install -- upgrade pip
$ pip install PyQt5
$ python MyProgQT5MDB.py
 
 Code updates
 
  import mysql.connector
  . . . . 
 
   
# connect to datatabase
   def con(self):
       self.conn = mysql.connector.connect(user = "root",
                             password = "*********",
                             host = "127.0.0.1",
                             port = 3306,
                             database = "db1")
       self.cur = self.conn.cursor()
 
Database setup
 
[boris@sever33fedora PYMSQL]$ mysql -u root -p
Enter password:  
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.25 MySQL Community Server - GPL

mysql> CREATE DATABASE db1 CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
mysql > use db1;
mysql> CREATE TABLE students (
>     id INT NOT NULL AUTO_INCREMENT,
>     name VARCHAR(30) CHARSET utf8,
>     ocenka INT,
>     PRIMARY KEY (id)
 > );

No comments:

Post a Comment