Saturday, June 12, 2021

Setting up PyQT5 && "Mariadb 10.5 or MySQL 8.0.25" CRUD Application on Debian Bullseye/sid

Tuning remote connection of PyQT5 to Mariadb-Server as well as to MySQL seemed to me the most time consuming part of this small project. Obviously, the options for Mariadb and MySQL are mutually exclusive. Mariadb-Server seems to be native to Debian Bullseye due to setup MySQL 8.0.25 appears to be possible after purge preinstalled by default mariadb packages.

Tuning network configs on bare metal described in details utilizing `netstat -ant | grep 3306` output required to verify that  connection is established.

The very first time setup root account privileges to access the database server.

# mysql -u root -p

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 ;

MariaDB [(none)]> CREATE DATABASE db3 CHARACTER SET = 'utf8' COLLATE = 'utf8_bin';

MariaDB [db3]> CREATE TABLE persons ( id INT NOT NULL AUTO_INCREMENT,  name VARCHAR(30) CHARSET utf8,    spec VARCHAR(30) CHARSET utf8, ocenka INT,PRIMARY KEY (id) );

*****************************************************

VENV Setup for Mariadb Python Connection on Debian 11

 *****************************************************

  $ sudo apt install python3-venv

  $ python3 -m venv .env

  $ source .env/bin/activate

  $ sudo apt install libmariadb3 libmariadb-dev

  $ sudo apt install python3-dev

  $ pip3 install mariadb

  $ pip install PyQt5

Remote Connection to Mariadb on Debian Bullseye

Open port 3306

# firewall-cmd --zone=public --permanent --add-port=3306/tcp

1. Update /etc/mysql/mariadb.conf.d/50-server.cnf 

2. Update /etc/mysql/my.cnf

# cd /etc/mysql/mariadb.conf.d

# vi 50-server.cnf 

[server]

[mysqld]

# Comment out binding to localhost

#bind-address           = 127.0.0.1

save

# vi  /etc/mysql/my.cnf

    # add line to section below

    [mysqld]

    bind-address = 0.0.0.0

save

Restart mariadb service     

# systemctl restart mariadb

To get netstat on Debian ready to go 

$ sudo apt install net-tools 

When work on bare metal make sure that netstat reports a kind of :-

(.env) boris@boris-ms7c42:~/MARIADB$ netstat -ant  | grep 3306      

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      

tcp        0      0 192.168.0.19:3306       192.168.0.14:33312      ESTABLISHED

Code of PyQT5 module

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(125, 125, 550, 650)
self.setWindowTitle('Scores List')
self.tb = Tb(self)
self.btn = QPushButton('Refresh View', self)
self.btn.resize(150, 40)
self.btn.move(350, 20)
self.btn.clicked.connect(self.upd)
self.idp = QLineEdit(self)
self.idp.resize(150, 40)
self.idp.move(350, 70)
self.idp.setReadOnly(True)
self.fio = QLineEdit(self)
self.fio.resize(150, 40)
self.fio.move(350, 120)
self.spec = QLineEdit(self)
self.spec.resize(150, 40)
self.spec.move(350, 180)
self.oce = QLineEdit(self)
self.oce.resize(150, 40)
self.oce.move(350, 230)
self.btn = QPushButton('Add row', self)
self.btn.resize(150, 40)
self.btn.move(350, 280)
self.btn.clicked.connect(self.ins)
self.btn = QPushButton('Update row', self)
self.btn.resize(150, 40)
self.btn.move(350, 330)
self.btn.clicked.connect(self.change)
self.btn = QPushButton('Delete row', self)
self.btn.resize(150, 40)
self.btn.move(350, 390)
self.btn.clicked.connect(self.dels)

# connect to datatabase
def con(self):
self.conn =mariadb.connect(user = "root",
password = "**********",
host = "192.168.0.26",
port = 3306,
database = "db3")

self.cur = self.conn.cursor()

def upd(self):
self.conn.commit()
self.tb.updt()
self.idp.setText('')
self.fio.setText('')
self.spec.setText('')
self.oce.setText('')

# insert row updated by B.D.

def ins(self):
fio,spec,oce = self.fio.text(),self.spec.text(),self.oce.text()
try:
self.cur.execute("insert into persons (name,spec,ocenka) values (%s,%s,%s)",(fio,spec,oce))
except:
pass
self.upd()

# update row added by B.D.

def change(self):
fio,spec,oce = self.fio.text(),self.spec.text(), self.oce.text()
try:
ids = int(self.idp.text())
except:
return
self.cur.execute("update persons set name=%s,spec=%s,ocenka=%s where id=%s",(fio,spec,oce,ids,))
self.upd()

# delete record from table

def dels(self):
try:
ids = int(self.idp.text()) # rowid obtained
except:
return
self.cur.execute("delete from persons 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(20, 20, 320, 500)
self.setColumnCount(4)
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','Speciality','Score'])
self.wg.cur.execute("select * from persons 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:
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.spec.setText(self.item(row, 2).text().strip())
self.wg.oce.setText(self.item(row, 3).text().strip())

app = QApplication(sys.argv)
ex = MyWidget()
ex.show()
sys.exit(app.exec_())
Runtime snapshots































































*******************************************************
Setting up MySQL 8.0.25 via purge preinstalled mariadb packages
*******************************************************
$ sudo apt update
$ sudo apt upgrade
$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.17-1_all.deb
$ sudo apt install ./mysql-apt-config_0.8.17-1_all.deb







$ sudo apt update
***********************************
Upgrade mysql-common up to 8.0.25
***********************************
$ sudo apt upgrade  
$ sudo apt install mysql-server
************************************************
Failure due to preinstalled conflicting mariadb packages
************************************************
Cleaning up conflicting packages 
$ sudo apt purge mariadb-client-core-10.5 mariadb-server-core-10.5
*************************
Reinstall MySQL 8.0.25
*************************
$ sudo apt install mysql-server 
$ sudo systemctl start mysql.service
sudo systemctl enable mysql.service









************************************************
DATABASE SETUP for "root" on MySQL 8.0.25 
Community Edition
************************************************
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

******************************
NETWORK CONFIG SETUP
******************************
# vi /etc/mysql/mysql.conf.d/mysqld.cnf
d-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 0.0.0.0
:wq
# systemctl restart mysql
 
VENV SETUP

$ python3 -m venv .env
$ source .env/bin/activate
$ pip install mysql-connector-python==8.0.25
$ pip install PyQt5
 
Code for MySQL 8.0.25 connectivity
import sys

import mysql.connector
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(125, 125, 550, 650)
self.setWindowTitle('Scores List')
self.tb = Tb(self)
self.btn = QPushButton('Refresh View', self)
self.btn.resize(150, 40)
self.btn.move(350, 20)
self.btn.clicked.connect(self.upd)
self.idp = QLineEdit(self)
self.idp.resize(150, 40)
self.idp.move(350, 70)
self.idp.setReadOnly(True)
self.fio = QLineEdit(self)
self.fio.resize(150, 40)
self.fio.move(350, 120)
self.spec = QLineEdit(self)
self.spec.resize(150, 40)
self.spec.move(350, 180)
self.oce = QLineEdit(self)
self.oce.resize(150, 40)
self.oce.move(350, 230)
self.btn = QPushButton('Add row', self)
self.btn.resize(150, 40)
self.btn.move(350, 280)
self.btn.clicked.connect(self.ins)
self.btn = QPushButton('Update row', self)
self.btn.resize(150, 40)
self.btn.move(350, 330)
self.btn.clicked.connect(self.change)
self.btn = QPushButton('Delete row', self)
self.btn.resize(150, 40)
self.btn.move(350, 390)
self.btn.clicked.connect(self.dels)

# connect to datatabase
def con(self):
self.conn =mysql.connector.connect(user = "root",
password = "*******",
host = "192.168.0.27",
port = 3306,
database = "db3")

self.cur = self.conn.cursor()

def upd(self):
self.conn.commit()
self.tb.updt()
self.idp.setText('')
self.fio.setText('')
self.spec.setText('')
self.oce.setText('')

# insert row updated by B.D.

def ins(self):
fio,spec,oce = self.fio.text(),self.spec.text(),self.oce.text()
try:
self.cur.execute("insert into persons (name,spec,ocenka) values (%s,%s,%s)",(fio,spec,oce))
except:
pass
self.upd()

# update row added by B.D.

def change(self):
fio,spec,oce = self.fio.text(),self.spec.text(), self.oce.text()
try:
ids = int(self.idp.text())
except:
return
self.cur.execute("update persons set name=%s,spec=%s,ocenka=%s where id=%s",(fio,spec,oce,ids,))
self.upd()

# delete record from table

def dels(self):
try:
ids = int(self.idp.text()) # rowid obtained
except:
return
self.cur.execute("delete from persons 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(20, 20, 320, 500)
self.setColumnCount(4)
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','Speciality','Score'])
self.wg.cur.execute("select * from persons 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:
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.spec.setText(self.item(row, 2).text().strip())
self.wg.oce.setText(self.item(row, 3).text().strip())

app = QApplication(sys.argv)
ex = MyWidget()
ex.show()
sys.exit(app.exec_())
 Runtime snapshots




Runtime on bare metal Server side (Mariadb-Server)


Client Side


Two PyQT5 sessions opened from Virthost (Server F34) with each one of Debian 11 VBOX Guests attached to Virthost via the same Linux bridge,i.e. both Guests are on the same subnet with
Virthost 192.168.0.0/24.


 Needless to say that KVM Guests of Debian 11  would be much better and faster for testing the same configuration via common
Linux Bridge to Fedora 34 Server running KVM-Hypervisor.

No comments:

Post a Comment