Qt6 Python SQLServer 取引先照会
Qt6Creatorでプロジェクト ShTorihikisakiを作成
ShTorihikisaki.pyproject ShTorihikisaki.py form.ui を作成
form.ui は、pyside6-uic form.ui -o ui_form.py を実行して ui_form.py を作成します。pymssqlを利用してSQLServerへ接続します。
form.ui
ShTorihikisaki.py
# This Python file uses the following encoding: utf-8
import sys
import io
from datetime import date, datetime
from typing import Any, List
from PySide6.QtGui import QStandardItemModel
from PySide6.QtCore import (
Qt,
QModelIndex,
QAbstractTableModel
)
from PySide6.QtWidgets import (
QApplication,
QMainWindow,
QTableView,
QMessageBox,
QHeaderView
)
# 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_ShTorihikisaki
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 #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 ShTorihikisaki(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_ShTorihikisaki()
self.ui.setupUi(self)
self.ui.lineEdit.returnPressed.connect(self.edit_2)
self.ui.lineEdit_2.returnPressed.connect(self.edit_1)
self.ui.label.setAlignment(Qt.AlignCenter)
self.ui.label_2.setAlignment(Qt.AlignCenter)
self.ui.lineEdit.setAlignment(Qt.AlignRight)
self.ui.lineEdit_2.setAlignment(Qt.AlignRight)
self.ui.pushButton.clicked.connect(self.view)
self.ui.pushButton_2.clicked.connect(self.exit)
self.ui.pushButton_3.clicked.connect(self.copySelection)
self.ui.pushButton_4.clicked.connect(self.csvwrt)
self.ui.label.setStyleSheet("background-color: pink")
self.ui.label_2.setStyleSheet("background-color: violet")
self.ui.pushButton.setStyleSheet("background-color: aqua")
self.ui.pushButton_2.setStyleSheet("background-color: green")
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 copySelection(self):
selection = self.ui.tableView.selectedIndexes()
if selection:
rows = sorted(index.row() for index in selection)
columns = sorted(index.column() for index in selection)
rowcount = rows[-1] - rows[0] + 1
colcount = columns[-1] - columns[0] + 1
table = [[''] * colcount for _ in range(rowcount)]
for index in selection:
row = index.row() - rows[0]
column = index.column() - columns[0]
table[row][column] = index.data()
stream = io.StringIO()
csv.writer(stream).writerows(table)
QApplication.clipboard().setText(stream.getvalue())
return
def exit(self) -> None:
sys.exit()
#self.close()
def edit_1(self):
self.ui.lineEdit.focusPreviousChild()
def edit_2(self):
self.ui.lineEdit_2.focusNextChild()
def view(self):
sttstr = self.ui.lineEdit.text()
endstr = self.ui.lineEdit_2.text()
try:
sttint = int(sttstr)
endint = int(endstr)
except:
QMessageBox.warning(self,'エラー','数値エラー')
self.edit_1()
return
else:
if sttint > endint:
QMessageBox.warning(self,'エラー','範囲エラー')
self.edit_1()
return
server = '192.168.10.111'
username = 'sa'
password = 'xxxxxxxx'
database = 'kakei'
try:
conn = pymssql.connect( server , username , password, database )
tablenm = "取引先マスター"
cursor = conn.cursor()
sql = "select * from " + tablenm
sql += " where 取引先コード >= " + str(sttint) + " and 取引先コード <= " + str(endint)
sql += " order by 取引先コード"
cursor.execute(sql)
results = cursor.fetchall()
# データを表示
coldata = ['コード','取引先名','取引先区分']
rowdata = []
row = 0
for res in results:
i=0
rowone = []
while i <= 2:
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 Exception as e:
QMessageBox.warning(self,'エラー','接続エラー' + e.args)
else:
pass
return coldata, results
def csvwrt(self):
results = self.view()
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[1]:
i=0
resdta = []
while i < 3:
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 = ShTorihikisaki()
widget.show()
sys.exit(app.exec())
実行画面
Ubuntuで実行 照会で select を実行しTableView 表示。copy で選択部分をクリップボードへコピー。csv作成でcsvファイルを作成。
