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_())
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
$ python3 -m venv .env
$ source .env/bin/activate
$ pip install mysql-connector-python==8.0.25
$ pip install PyQt5
import sysRuntime snapshots
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_())
Transform your business with Genex & effectively tackle unprecedented challenges & opportunities. The MySQL databases powered by Oracle and Percona, which provide the best platform for building and running your dream MySQL applications, are well-versed in all versions of the service, eliminating the need for complicated, expensive & separate data integrations.
ReplyDeletehttps://genexdbs.com/