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_())
$ 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
. . . .
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()
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)
> );
