Expecting Debian Bullseye release to happen pretty shortly (by the end of September 2021) PostgreSQL 13.2 setup has been verified on Debian Bullseye/sid as Oracle's Virtual Box 6.1.22 Guest running in EFI mode along with ongoing hackery of PyQT5 code mentioned in my recent post at Lxer.com
Setting up PostgreSQL 13.12
$ sudo apt -y install gnupg2
$ sudo apt install postgresql-13 postgresql-client-13
$ sudo systemctl start postgresql@13-main
$ sudo systemctl status postgresql@13-main
$ sudo systemctl enable postgresql@13-main
$ sudo -u postgres psql
postgres=# create database db3 encoding 'UTF8';
CREATE DATABASE
postgres=# grant all privileges on database db3 to postgres;
GRANT
postgres=# alter user postgres with encrypted password '*******';
postgres=# \c db3
db3=# create table persons ( id SERIAL PRIMARY KEY, name VARCHAR(30), spec VARCHAR(25) ,ocenka INT );
^D
$ sudo su -
# vi /etc/postgresql/13/main/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.0.0/24 md5
save
# vi /etc/postgresql/13/main/posgesql.conf
listen_addresses = '*'
save
# systemctl restart postgresql@13-main
Setup Python venv on Debian 11 and prepare venv for PyQT5 CRUD Application
$ sudo apt-get install python3-venv
$ python3 -m venv .env
$ source .env/bin/activate
$ pip install PyQT5
$ pip install psycopg2-binary
Another hack of code mentioned in http://lxer.com/module/newswire/view/300978/index.html
due to adding new field to tables "persons" named "spec" VARCHAR(25) what causes redesign of main front window by adding one more area for editing and inserting data into newly added field "spec". All code updated either added by myself has been colored blue
Final draft of PyQT5 CRUD module:-
#!/usr/bin/python3
import sys
import psycopg2
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 =psycopg2.connect(user = "postgres",
password = "*******",
host = "192.168.0.26",
port = "5432",
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_())
No comments:
Post a Comment