Qt6 Python SQLServerからデータ照会とPDF作成(QTableView)
Qt6Creatorでプロジェクトsqlserverappを作成
sqlserverapp.pyproject sqlserverapp.py form.ui を作成
form.ui は、pyside6-uic form.ui -o ui_form.py を実行して ui_form.py を作成します。pymssqlを利用してSQLServerへ接続します。pdf作成は、reportlabを利用します。
form.ui
sqlservercon.py
# This Python file uses the following encoding: utf-8
import sys
from datetime import date, datetime
from typing import Any, List
from PySide6.QtCore import (
Qt,
QModelIndex,
QAbstractTableModel
)
from PySide6.QtWidgets import (
QApplication,
QMainWindow,
QTableView,
QMessageBox,
)
6
# 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_sqlserverapp
from reportlab.lib import colors
from reportlab.lib.units import mm
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import A4, landscape, portrait
from reportlab.lib.pagesizes import A3
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
import pymssql
import csv
class SimpleTableModel(QAbstractTableModel):
def __init__(self, list, headers = [], parent = None):
QAbstractTableModel.__init__(self, parent)
self.list = list
self.headers = headers
def rowCount(self, parent):
return len(self.list)
def columnCount(self, parent):
return len(self.list[0])
def flags(self, index):
return Qt.ItemIsEditable | Qt.ItemIsEnabled | Qt.ItemIsSelectable
def data(self, index, role):
if role == Qt.EditRole:
row = index.row()
column = index.column()
return self.list[row][column]
elif role == Qt.DisplayRole:
row = index.row()
column = index.column()
value = self.list[row][column]
# Perform per-type checks and render accordingly.
if isinstance(value, datetime):
# Render time to YYY-MM-DD.
return value.strftime("%Y-%m-%d")
elif isinstance(value, int):
# Render int to 0 dp
return "%d" % value
elif isinstance(value, float):
# Render float to 2 dp
return "%.2f" % value
elif isinstance(value, str):
# Render strings with quotes
return "%s" % value
# Default (anything not captured above: e.g. int)
else:
return value
elif role == Qt.TextAlignmentRole:
row = index.row()
column = index.column()
value = self.list[row][column]
if isinstance(value, int) or isinstance(value, float):
# Align right, vertical middle.
return int(Qt.AlignRight) + int(Qt.AlignVCenter) #,Qt.AlignHCenter,Qt.AlignRight,Qt.AlignCenter,Qt.AlignTop,Qt.AlignVCenter,Qt.AlignBottom
else:
return int(Qt.AlignLeft) + int(Qt.AlignVCenter)
def setData(self, index, value, role = Qt.EditRole):
if role == Qt.EditRole:
row = index.row()
column = index.column()
self.list[row][column] = value
self.dataChanged.emit(index, index)
return True
return False
def headerData(self, section, orientation, role):
if role == Qt.DisplayRole:
if orientation == Qt.Horizontal:
if section < len(self.headers):
return self.headers[section]
else:
return "not implemented"
else:
return "%d" % (section + 1)
class sqlserverapp(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_sqlserverapp()
self.ui.setupUi(self)
self.ui.pushButton.clicked.connect(self.select)
self.ui.pushButton_2.clicked.connect(self.csvwrt)
self.ui.pushButton_3.clicked.connect(self.pdfwrt)
self.ui.pushButton_4.clicked.connect(self.exit)
self.ui.radioButton.setChecked(True)
self.ui.label.setStyleSheet("background-color: pink")
self.ui.pushButton.setStyleSheet("background-color: yellow")
self.ui.pushButton_2.setStyleSheet("background-color: pink")
self.ui.pushButton_3.setStyleSheet("background-color: aqua")
self.ui.pushButton_4.setStyleSheet("background-color: green")
#self.ui.lineEdit_2''.setEchoMode(QlineEdit.Password)
rowdata = ['']
coldata = ['']
model = SimpleTableModel(rowdata, coldata)
self.ui.tableView.setModel(model) # create model and set
self.ui.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents) #列幅の自動調整 0:Interactive 1:Stretch 2:Fixed 3:ResizeToContents
def exit(self) -> None:
sys.exit()
#self.close()
def select(self):
server = self.ui.lineEdit.text()
username = self.ui.lineEdit_2.text()
password = self.ui.lineEdit_3.text()
database = self.ui.lineEdit_4.text()
try:
conn = pymssql.connect( server , username , password, database )
tablenm = self.ui.lineEdit_5.text()
wherenm = self.ui.lineEdit_6.text()
ordernm = self.ui.lineEdit_7.text()
cursor = conn.cursor()
colsql = "select count(*) from sys.columns where object_id=object_id('" + tablenm + "')"
cursor.execute(colsql)
results = cursor.fetchall()
for res in results:
colcount = res[0]
self.ui.label_3.setText(str(colcount) + ' 項目')
#self.ui.tableView.columnCountChanged()
namsql = " SELECT t.name as tblnam,c.name as colname,type_name(user_type_id) as typname,max_length as maxleng ,CASE WHEN is_nullable = 1 THEN 'YES' ELSE 'NO' END as nullok"
namsql += " FROM sys.objects t INNER JOIN sys.columns c ON t.object_id = c.object_id"
namsql += " WHERE t.type = 'U' AND t.name='" + tablenm + "'"
namsql += " ORDER BY c.column_id"
cursor.execute(namsql)
results = cursor.fetchall()
coldata = []
i=0
for res in results:
coldata.append(res[1])
i += 1
if wherenm == '':
sql = "SELECT * from " + tablenm
else:
sql = "select * from " + tablenm + " where " + wherenm
pass
if ordernm == '':
sql += ''
else:
sql += " order by " + ordernm
pass
cursor.execute(sql)
results = cursor.fetchall()
# 実行結果
self.ui.label_2.setText(str(cursor.rowcount) + ' 件')
# データを表示
rowdata = []
row = 0
for res in results:
i=0
rowone = []
while i < colcount:
try:
strint = int(str(res[i]))
strfloat = float(str(res[i]))
if strint == strfloat:
rowone.append(strint)
else:
rowone.append(strfloat)
except:
rowone.append(str(res[i]))
else:
pass
i += 1
row += 1
rowdata.append(rowone)
model = SimpleTableModel(rowdata, coldata)
self.ui.tableView.setModel(model) # create model and set
cursor.close()
conn.close()
except:
QMessageBox.warning(self,'エラー','接続エラー')
else:
pass
return coldata, colcount, results, tablenm
def pdfwrt(self):
results = self.select()
tablenm = results[3]
try:
#用紙サイズ
ans = 1
if self.ui.radioButton.isChecked() == True:
ans = 1 #A4タテ
elif self.ui.radioButton_2.isChecked() == True:
ans = 2 #A4ヨコ
elif self.ui.radioButton_3.isChecked() == True:
ans = 3 #A3ヨコ
else:
pass
# 白紙をつくる
FILENAME = 'sqlservercon.pdf'
if ans == 1:
c = canvas.Canvas(FILENAME, pagesize=portrait(A4))
gyosuu = 50 #a4縦 50
elif ans == 2:
c = canvas.Canvas(FILENAME,pagesize=landscape(A4))
gyosuu = 34 #a4横 34
elif ans == 3:
c = canvas.Canvas(FILENAME,pagesize=landscape(A3))
gyosuu = 50 #a3横 34
else:
pass
# フォント登録
pdfmetrics.registerFont(UnicodeCIDFont('HeiseiMin-W3'))
pdfmetrics.registerFont(UnicodeCIDFont("HeiseiKakuGo-W5"))
page = 0
#テーブル項目名のセット
tb = []
hedcol = results[0]
tb.append(hedcol,) #tb[[a,b,c,....],]
row = 0
for res in results[2]:
rowdta = []
i = 0
for res_col in results[0]:
rowdta.append(str(res[i]))
i += 1
tb.append(rowdta)
if row == gyosuu:
c.setFont('HeiseiKakuGo-W5', 12)
if ans == 1:
c.drawString(10*mm, 290*mm, "＊ " + tablenm + "一覧表 ＊") #a4 縦 書き出し(横位置, 縦位置, 文字)
elif ans == 2:
c.drawString(10*mm, 200*mm, "＊ " + tablenm + "一覧表 ＊") #a4 横 書き出し(横位置, 縦位置, 文字)
elif ans == 3:
c.drawString(10*mm, 290*mm, "＊ " + tablenm + "一覧表 ＊") #a3 横 書き出し(横位置, 縦位置, 文字)
else:
pass
page += 1
strpage = 'page.' + str(page)
if ans == 1:
c.drawString(170*mm, 290*mm,strpage) # a4 縦 書き出し(横位置, 縦位置, 文字)
elif ans == 2:
c.drawString(270*mm, 200*mm,strpage) # a4 横 書き出し(横位置, 縦位置, 文字)
elif ans == 3:
c.drawString(370*mm, 290*mm,strpage) # a3 横 書き出し(横位置, 縦位置, 文字)
else:
pass
table = Table(tb,colWidths=40*mm, rowHeights=5*mm) # テーブルサイズ 列 行
table.setStyle(TableStyle([ # tableの装飾
('FONT', (0, 0), (-1, -1), 'HeiseiMin-W3', 11), #フォント,サイズ FONTL
('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), "LEFT"), #フォントの場所水平 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)
if ans == 1:
table.wrapOn(c, 10*mm, 20*mm) # table a4 縦 書き出し位置 横 縦
table.drawOn(c, 10*mm, 20*mm)
elif ans == 2:
table.wrapOn(c, 10*mm, 10*mm) # table a4 横 書き出し位置 横 縦
table.drawOn(c, 10*mm, 10*mm)
elif ans == 3:
table.wrapOn(c, 10*mm, 20*mm) # table a3 横 書き出し位置 横 縦
table.drawOn(c, 10*mm, 20*mm)
else:
pass
# Canvasに書き込み
c.showPage()
row = -1
tb = []
tb.append(hedcol,)
row += 1
if row > 0:
num = 1
if ans == 1:
kasan = 2
elif ans == 2:
kasan = 2
elif ans == 3:
kasan = 2
else:
pass
while num < gyosuu + kasan - row:
tb.append(['','',''])
num += 1
c.setFont('HeiseiKakuGo-W5', 12)
if ans == 1:
c.drawString(10*mm, 290*mm, "＊ " + tablenm + "一覧表 ＊") #a4 縦 書き出し(横位置, 縦位置, 文字)
elif ans == 2:
c.drawString(10*mm, 200*mm, "＊ " + tablenm + "一覧表 ＊") #a4 横 書き出し(横位置, 縦位置, 文字)
elif ans == 3:
c.drawString(10*mm, 290*mm, "＊ " + tablenm + "一覧表 ＊") #a3 横 書き出し(横位置, 縦位置, 文字)
else:
pass
page += 1
strpage = 'page.' + str(page)
if ans == 1:
c.drawString(170*mm, 290*mm,strpage) # a4 縦 書き出し(横位置, 縦位置, 文字)
elif ans == 2:
c.drawString(270*mm, 200*mm,strpage) # a4 横 書き出し(横位置, 縦位置, 文字)
elif ans == 3:
c.drawString(370*mm, 290*mm,strpage) # a3 横 書き出し(横位置, 縦位置, 文字)
else:
pass
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'),
]))
if ans == 1:
table.wrapOn(c, 10*mm, 20*mm) # table a4 縦 書き出し位置 横 縦
table.drawOn(c, 10*mm, 20*mm)
elif ans == 2:
table.wrapOn(c, 10*mm, 10*mm) # table a4 横 書き出し位置 横 縦
table.drawOn(c, 10*mm, 10*mm)
elif ans == 3:
table.wrapOn(c, 10*mm, 20*mm) # table a3 横 書き出し位置 横 縦
table.drawOn(c, 10*mm, 20*mm)
else:
pass
# Canvasに書き込み
c.showPage()
# ファイル保存
c.save()
# ブラウザーで表示
webbrowser.open(FILENAME)
except:
QMessageBox.warning(self,'エラー','PDF作成エラー')
else:
pass
def csvwrt(self):
results = self.select()
try:
with open('test.csv', 'w', newline='') as f:
writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC) #数値以外のフィールドをダブルクオートで囲む
writer.writerow(results[0]) #カラム名配列
for res in results[2]:
i=0
resdta = []
while i < results[1]:
resdta.append(res[i])
i += 1
writer.writerow(resdta)
QMessageBox.warning(self,'CSV作成','ｃｓｖファイルを作成しました。')
except:
QMessageBox.warning(self,'エラー','CSV作成エラー')
else:
pass
if __name__ == "__main__":
app = QApplication(sys.argv)
widget = sqlserverapp()
widget.show()
sys.exit(app.exec())
実行画面
Ubuntuで実行 接続で select を実行しテーブル表示。CSV作成で select を実行し csvファイルを作成。ＰＤＦ作成で select を実行し指定サイズで pdfファイルを作成。TableWidgetより処理が速くなりました。
 Qt6Creatorでプロジェクトsqlserverappを作成 sqlserverapp.pyprojec){kind=link}