Qt6 python Mysql を利用した訪問者登録とPDF印刷

Qt6Creatorでプロジェクトvisitappを作成

visitapp.pyproject visitapp.py form.ui を作成

form.ui は、pyside6-uic form.ui -o ui_form.py を実行して ui_form.py を作成します。

form.ui

visitapp.py

# This Python file uses the following encoding: utf-8
import sys
import datetime
import socket

from reportlab.lib import colors
from reportlab.lib.units import mm
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import A4, portrait
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.cidfonts import UnicodeCIDFont
from reportlab.platypus import Table, TableStyle
import webbrowser

from PySide6.QtWidgets import QApplication, QMainWindow,QTableWidgetItem

import mysql.connector as mydb
# コネクションの作成
connector = mydb.connect(
    host='192.168.10.71',
    user='aaaaa',
    password='bbbbb',
    database='wordpress_db',
    charset="utf8"
)

# Important:
# You need to run the following command to generate the ui_form.py file
#     pyside6-uic form.ui -o ui_form.py, or
#     pyside2-uic form.ui -o ui_form.py
from ui_form import Ui_visitapp

class visitapp(QMainWindow):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.ui = Ui_visitapp()
        self.ui.setupUi(self)
        self.ui.pushButton.clicked.connect(self.insert)
        self.ui.pushButton_2.clicked.connect(self.select)
        self.ui.pushButton_3.clicked.connect(self.delete)
        self.ui.pushButton_4.clicked.connect(self.exit)
        self.ui.pushButton_5.clicked.connect(self.pdfwrt)
        self.ui.tableWidget.cellClicked.connect(self.cell_clicked_event)

        s = socket.socket(socket.AF_INET, socket.SOCK_DGRAM)
        s.connect(("8.8.8.8", 80))
        ip= s.getsockname()[0]
        self.ui.lineEdit.setText(ip)

        dt_now = datetime.datetime.now()
        self.ui.dateTimeEdit.setDateTime(dt_now)

        self.ui.tableWidget.setColumnWidth(0, 100)
        self.ui.tableWidget.setColumnWidth(1, 200)
        self.ui.tableWidget.setStyleSheet(
            "QTableWidget::item:selected"
            "{"
            "background-color : #d9fffb;"
            "selection-color : #000000;"
            "}"
            )
        self.ui.label.setStyleSheet("background-color: yellow")
        self.ui.label_2.setStyleSheet("background-color: aqua")
        self.ui.label_3.setStyleSheet("background-color: aqua")
        self.ui.label_4.setStyleSheet("background-color: pink")
        self.ui.pushButton.setStyleSheet("background-color: yellow")
        self.ui.pushButton_2.setStyleSheet("background-color: green")
        self.ui.pushButton_3.setStyleSheet("background-color: red")
        self.ui.pushButton_4.setStyleSheet("background-color: pink")
        self.ui.pushButton_5.setStyleSheet("background-color: blue")

    def setTableWidth(self):
        width = self.ui.tableWidget.verticalHeader().width()
        width += self.ui.tableWidget.horizontalHeader().length()
        if self.ui.tableWidget.verticalScrollBar().isVisible():
           width += self.ui.tableWidget.verticalScrollBar().width()
           width += self.ui.tableWidget.frameWidth() * 2

    def resizeEvent(self, event):
        self.setTableWidth()
        super(visitapp, self).resizeEvent(event)

    def exit(self) -> None:
        sys.exit()
        #self.close()

    def dtset(self):
        dt_now = datetime.datetime.now()
        self.ui.dateTimeEdit.setDateTime(dt_now)

    def cell_clicked_event(self, row, column):
        if column == 0:
            ip = self.ui.tableWidget.item(row, 0).text()
            self.ui.lineEdit.setText(ip)
            dt = self.ui.tableWidget.item(row, 1).text()
            dte = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S')
            self.ui.dateTimeEdit.setDateTime(dte)
        elif column == 1:
            ip = self.ui.tableWidget.item(row, 0).text()
            self.ui.lineEdit.setText(ip)
            dt = self.ui.tableWidget.item(row, 1).text()
            dte = datetime.datetime.strptime(dt, '%Y-%m-%d %H:%M:%S')
            self.ui.dateTimeEdit.setDateTime(dte)
        else:
            pass


    def insert(self):
        self.dtset()
        cursor = connector.cursor()
        str1 = self.ui.lineEdit.text()
        str2 = self.ui.dateTimeEdit.text()
        sql = "insert into wp_homonsha (ip,dt) values (" + "'" + str1 + "','" + str2 + "')"
        cursor.execute(sql)
        connector.commit()
        cursor.close()
        #connector.close()
        self.select()

    def select(self):
        cursor = connector.cursor()
        if self.ui.lineEdit.text() == '':
           sql = 'SELECT * from wp_homonsha order by dt desc'
        else:
           sql = "select * from wp_homonsha where ip=" + "'" + self.ui.lineEdit.text() + "' order by dt desc"

        cursor.execute(sql)
        results = cursor.fetchall()
        # 実行結果
        self.ui.label.setText(str(cursor.rowcount) + ' 件')
        self.ui.tableWidget.setRowCount(cursor.rowcount)
        # データを表示
        row = 0
        for res in results:
            str0 = res[0]
            str1 = str(res[1])
            self.ui.tableWidget.setItem(row, 0, QTableWidgetItem(str0))
            self.ui.tableWidget.setItem(row, 1, QTableWidgetItem(str1))
            self.ui.tableWidget.resizeColumnToContents(0)
            self.ui.tableWidget.resizeColumnToContents(1)
            row += 1

        cursor.close()
        #connector.close()

    def delete(self):
        cursor = connector.cursor()
        str1 = self.ui.lineEdit.text()
        str2 = self.ui.dateTimeEdit.text()
        sql = "delete from wp_homonsha where ip=" + "'" + str1 + "'" + " and dt='" + str2 + "'"
        cursor.execute(sql)
        connector.commit()
        cursor.close()
        #connector.close()
        self.select()

    def pdfwrt(self):
        cursor = connector.cursor()
        if self.ui.lineEdit.text() == '':
           sql = 'SELECT * from wp_homonsha order by dt desc'
        else:
           sql = "select * from wp_homonsha where ip=" + "'" + self.ui.lineEdit.text() + "' order by dt desc"
        cursor.execute(sql)
        results = cursor.fetchall()
        cursor.close()

        # 白紙をつくる（A4縦）
        FILENAME = 'visitapp.pdf'
        c = canvas.Canvas(FILENAME, pagesize=portrait(A4))

        # フォント登録
        pdfmetrics.registerFont(UnicodeCIDFont('HeiseiMin-W3'))
        pdfmetrics.registerFont(UnicodeCIDFont("HeiseiKakuGo-W5"))

        page = 0

        hed0 = '訪問者ｉｐ'
        hed1 = '訪問日時'
        tb = [[hed0,hed1],]
        row = 0
        for res in results:
            str0 = res[0]
            str1 = str(res[1])
            tb.append([str0,str1])
            if row == 50:
                c.setFont('HeiseiKakuGo-W5', 12)
                c.drawString(10*mm, 290*mm, '＊　　　訪　問　者　一　覧　表　　　＊') # 書き出し(横位置, 縦位置, 文字)
                page += 1
                strpage = 'page.' + str(page)

                c.drawString(100*mm, 290*mm,strpage) # 書き出し(横位置, 縦位置, 文字)
                table = Table(tb,colWidths=40*mm, rowHeights=5*mm)       # テーブルサイズ　列　行
                table.setStyle(TableStyle([                              # tableの装飾
                                         ('FONT', (0, 0), (-1, -1), 'HeiseiMin-W3', 11),    #フォント,サイズ
                                         ('BOX', (0, 0), (-1, -1), 1, colors.black),        #罫線外側 box
                                         ('INNERGRID', (0, 0), (-1, -1), 1, colors.black),  #罫線内側 innergrid
                                         ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),            #フォントの場所垂直 top middle bottom
                                         ("ALIGN", (0,0), (-1,-1), "CENTER"),               #フォントの場所水平 left center right
                                         ('BACKGROUND',(0,0),(1,0),colors.yellow),
                                         ('BACKGROUND',(0,1),(-1,3),colors.lightskyblue),
                                         ('TEXTCOLOR',(0,0),(-1,-1),colors.black),
                                         ]))
                                                                                            #セル結合 span #横線 lineabove #縦線 linebefore
                c.setFont('HeiseiKakuGo-W5', 12)
                table.wrapOn(c, 10*mm, 20*mm) # table書き出し位置　横　縦
                table.drawOn(c, 10*mm, 20*mm)
                # Canvasに書き込み
                c.showPage()
                row = 0
                tb = [[hed0,hed1],]

            row += 1

        if row > 1:
           num = 1
           while num < 53-row:
               tb.append(['',''])
               num += 1
           c.setFont('HeiseiKakuGo-W5', 12)
           c.drawString(10*mm, 290*mm, '＊　　　訪　問　者　一　覧　表　　　＊') # 書き出し(横位置, 縦位置, 文字)
           page += 1
           strpage = 'page.' + str(page)
           c.drawString(100*mm, 290*mm,strpage) # 書き出し(横位置, 縦位置, 文字)
           table = Table(tb,colWidths=40*mm, rowHeights=5*mm)
           table.setStyle(TableStyle([                              # tableの装飾
                                    ('FONT', (0, 0), (-1, -1), 'HeiseiMin-W3', 11),
                                    ('BOX', (0, 0), (-1, -1), 1, colors.black),
                                    ('INNERGRID', (0, 0), (-1, -1), 1, colors.black),
                                    ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
                                    ]))
           table.wrapOn(c, 10*mm, 20*mm) # table位置
           table.drawOn(c, 10*mm, 20*mm)
           # Canvasに書き込み
           c.showPage()

        # ファイル保存
        c.save()

        # ブラウザーで表示
        webbrowser.open(FILENAME)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    form = visitapp()
    #form.setGeometry(700, 150, 800, 400)
    form.show()
    sys.exit(app.exec())

実行画面

登録でＩＰ、ＤＴ内容を wp_homonsha へ insert その後照会します。照会は、ＩＰの内容で select します。ＩＰが空の場合は、全件表示。消去は、セルクリックした項目からＩＰとＤＴを取得しその内容で delete します。その後照会します。ＰＤＦ作成は、ＩＰの内容で作成します。visitapp.pdfを作成、プレビューします。