Running PyQT5 CRUD (PostgreSQL 13.2 ) Application you might experience problem with importing psycopg2 . Posting below briefly provides the way to solve the issue and also describes in details database setup which is tuned specifically for Fedora 34 .
Setting up PostgreSQL 13.2 on Fedora 34 Server
$ sudo dnf module -y install postgresql:13/server
$ sudo postgresql-setup --initdb
auth-method "ident" by "md5"# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.0.XX/32 md5
# IPv6 local connections:
host all all ::1/128 md5
Edit /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
$ sudo -u postgres psql
[sudo] password for boris:
psql (13.2)
Type "help" for help.
postgres=# create database db1 encoding 'UTF8';
CREATE DATABASE
postgres=# grant all privileges on database db1 to postgres;
GRANT
postgres=# alter user postgres with encrypted password '*******';
postgres=# SELECT datname FROM pg_database;
datname
-----------
postgres
template1
template0
db1
(4 rows)
postgres=# \c db1
db1=# create table students ( id SERIAL PRIMARY KEY, name VARCHAR,ocenka INT);
$ pip install psycopg2-binary
Setup PyQT5 in PyCharm per http://lxer.com/module/newswire/view/298983/index.html
Imports in Python module should look like :
import sys
import psycopg2
from PyQt5.QtWidgets import QTableWidget, QApplication, QMainWindow, QTableWidget
from PyQt5.QtWidgets import QTableWidgetItem, QWidget, QPushButton, QLineEdit
from PyQt5 import QtGui
=======================
Connection to PostgreSQL :
===============================
# connect to database
def con(self):
self.conn = psycopg2.connect(user = "postgres",
password = "*******",
host = "192.168.0.XX",
port = "5432",
database = "db1")
self.cur = self.conn.cursor()
The template of PyQT5 code below belongs OldProgramer@YandexZen. However, been provided to auditorium as is without details of PostgresSQL setup on particular Linux flavor (tuning pg_hba.conf, postgresql.conf) it doesn't provide the straight forward ability for testing. Moreover in original version of code names of database and table "students" where the same , what potentially might confuse even experienced DBA.
#!/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(100, 100, 500, 600)
self.setWindowTitle('Persons List')
self.tb = Tb(self)
self.btn = QPushButton('Refresh', 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('Delete Row', self)
self.btn.resize(150, 40)
self.btn.move(300, 260)
self.btn.clicked.connect(self.dels)
def con(self):
self.conn = psycopg2.connect(user = "postgres",
password = "*********",
host = "192.168.0.XX",
port = "5432",
database = "db1")
self.cur = self.conn.cursor()
def upd(self):
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 dels(self):
try:
ids = int(self.idp.text())
except:
return
self.cur.execute("delete from students where id=%s",(ids,))
self.upd()
class Tb(QTableWidget):
def __init__(self, wg):
self.wg = wg
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)
def updt(self):
self.clear()
self.setRowCount(0);
self.setHorizontalHeaderLabels(['id', 'Name', 'Score'])
self.wg.cur.execute("select * from students order by name")
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()
def cellClick(self, row, col):
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_())
Configuring another project to run PyQT5 with PostgreSQL database requires injection PyQT5 and psycopg2-binary in it's own venv. Now output has been sorted by "id".
No comments:
Post a Comment