python

Qt6 Python SQLServer 取引先照会

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作成','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ファイルを作成。

-python

PAGE TOP