PyQt5桌面程序记录

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'pyqt5_demo.ui'
#
# Created by: PyQt5 UI code generator 5.15.0
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(944, 633)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.export_button = QtWidgets.QPushButton(self.centralwidget)
        self.export_button.setGeometry(QtCore.QRect(810, 240, 80, 30))
        font = QtGui.QFont()
        font.setFamily("Microsoft YaHei UI")
        font.setPointSize(12)
        self.export_button.setFont(font)
        self.export_button.setStyleSheet("")
        self.export_button.setIconSize(QtCore.QSize(22, 22))
        self.export_button.setObjectName("export_button")
        self.select_button = QtWidgets.QPushButton(self.centralwidget)
        self.select_button.setGeometry(QtCore.QRect(690, 240, 80, 30))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(12)
        font.setBold(False)
        font.setItalic(False)
        font.setWeight(50)
        self.select_button.setFont(font)
        self.select_button.setStyleSheet("background-color:rgb(85, 170, 127);\n"
"font: 12pt \"微软雅黑\";\n"
"border-radius:3px;")
        self.select_button.setIconSize(QtCore.QSize(22, 22))
        self.select_button.setAutoRepeat(False)
        self.select_button.setAutoDefault(False)
        self.select_button.setDefault(False)
        self.select_button.setFlat(False)
        self.select_button.setObjectName("select_button")
        self.groupBox = QtWidgets.QGroupBox(self.centralwidget)
        self.groupBox.setGeometry(QtCore.QRect(560, 0, 381, 171))
        self.groupBox.setStyleSheet("background-color:#dbfcff")
        self.groupBox.setTitle("")
        self.groupBox.setObjectName("groupBox")
        self.label_6 = QtWidgets.QLabel(self.groupBox)
        self.label_6.setGeometry(QtCore.QRect(253, 90, 110, 20))
        font = QtGui.QFont()
        font.setFamily("MV Boli")
        self.label_6.setFont(font)
        self.label_6.setObjectName("label_6")
        self.date_name = QtWidgets.QLineEdit(self.groupBox)
        self.date_name.setGeometry(QtCore.QRect(80, 82, 140, 30))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(10)
        self.date_name.setFont(font)
        self.date_name.setStyleSheet("background-color:#ffffff;\n"
"border-radius:3px;")
        self.date_name.setObjectName("date_name")
        self.city_name = QtWidgets.QComboBox(self.groupBox)
        self.city_name.setGeometry(QtCore.QRect(250, 20, 91, 31))
        font = QtGui.QFont()
        font.setFamily("Microsoft YaHei UI")
        font.setPointSize(10)
        self.city_name.setFont(font)
        self.city_name.setStyleSheet("background-color:#ffffff;\n"
"border-radius:3px;")
        self.city_name.setObjectName("city_name")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.city_name.addItem("")
        self.label_2 = QtWidgets.QLabel(self.groupBox)
        self.label_2.setGeometry(QtCore.QRect(200, 20, 41, 31))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(12)
        font.setBold(False)
        font.setWeight(50)
        self.label_2.setFont(font)
        self.label_2.setObjectName("label_2")
        self.label_5 = QtWidgets.QLabel(self.groupBox)
        self.label_5.setGeometry(QtCore.QRect(30, 81, 41, 30))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(12)
        font.setBold(False)
        font.setWeight(50)
        self.label_5.setFont(font)
        self.label_5.setObjectName("label_5")
        self.label = QtWidgets.QLabel(self.groupBox)
        self.label.setGeometry(QtCore.QRect(30, 20, 41, 31))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(12)
        font.setBold(False)
        font.setWeight(50)
        self.label.setFont(font)
        self.label.setObjectName("label")
        self.status_name = QtWidgets.QComboBox(self.groupBox)
        self.status_name.setGeometry(QtCore.QRect(80, 20, 91, 31))
        font = QtGui.QFont()
        font.setFamily("Microsoft YaHei UI")
        font.setPointSize(10)
        self.status_name.setFont(font)
        self.status_name.setStyleSheet("background-color:#ffffff;\n"
"border-radius:3px;")
        self.status_name.setObjectName("status_name")
        self.status_name.addItem("")
        self.status_name.addItem("")
        self.status_name.addItem("")
        self.status_name.addItem("")
        self.status_name.addItem("")
        self.status_name.addItem("")
        self.tableView = QtWidgets.QTableView(self.centralwidget)
        self.tableView.setGeometry(QtCore.QRect(0, 30, 560, 560))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(10)
        self.tableView.setFont(font)
        self.tableView.setStyleSheet("background-color:#f6f8fa;\n"
"border-radius:5px;")
        self.tableView.setObjectName("tableView")
        self.tableView.horizontalHeader().setVisible(True)
        self.tableView.horizontalHeader().setCascadingSectionResizes(False)
        self.textBrowser = QtWidgets.QTextBrowser(self.centralwidget)
        self.textBrowser.setGeometry(QtCore.QRect(562, 480, 380, 110))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(10)
        self.textBrowser.setFont(font)
        self.textBrowser.setObjectName("textBrowser")
        self.groupBox_2 = QtWidgets.QGroupBox(self.centralwidget)
        self.groupBox_2.setGeometry(QtCore.QRect(0, 0, 560, 31))
        self.groupBox_2.setStyleSheet("background-color:#55aa7f;")
        self.groupBox_2.setTitle("")
        self.groupBox_2.setObjectName("groupBox_2")
        self.label_4 = QtWidgets.QLabel(self.groupBox_2)
        self.label_4.setGeometry(QtCore.QRect(240, 6, 80, 20))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(12)
        font.setBold(False)
        font.setWeight(50)
        self.label_4.setFont(font)
        self.label_4.setObjectName("label_4")
        self.groupBox_3 = QtWidgets.QGroupBox(self.centralwidget)
        self.groupBox_3.setGeometry(QtCore.QRect(562, 450, 380, 31))
        self.groupBox_3.setStyleSheet("background-color:#55aa7f;")
        self.groupBox_3.setTitle("")
        self.groupBox_3.setObjectName("groupBox_3")
        self.label_7 = QtWidgets.QLabel(self.groupBox_3)
        self.label_7.setGeometry(QtCore.QRect(160, 3, 80, 20))
        font = QtGui.QFont()
        font.setFamily("微软雅黑")
        font.setPointSize(12)
        font.setBold(False)
        font.setWeight(50)
        self.label_7.setFont(font)
        self.label_7.setObjectName("label_7")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 944, 23))
        self.menubar.setObjectName("menubar")
        self.menu = QtWidgets.QMenu(self.menubar)
        self.menu.setObjectName("menu")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.action1 = QtWidgets.QAction(MainWindow)
        self.action1.setObjectName("action1")
        self.actionhelle1 = QtWidgets.QAction(MainWindow)
        self.actionhelle1.setCheckable(True)
        font = QtGui.QFont()
        font.setFamily("Microsoft YaHei UI")
        font.setPointSize(12)
        font.setBold(False)
        font.setWeight(50)
        font.setStyleStrategy(QtGui.QFont.PreferDefault)
        self.actionhelle1.setFont(font)
        self.actionhelle1.setObjectName("actionhelle1")
        self.actionhello2 = QtWidgets.QAction(MainWindow)
        font = QtGui.QFont()
        font.setFamily("Microsoft YaHei UI")
        font.setPointSize(11)
        self.actionhello2.setFont(font)
        self.actionhello2.setObjectName("actionhello2")
        self.menu.addAction(self.actionhelle1)
        self.menu.addAction(self.actionhello2)
        self.menubar.addAction(self.menu.menuAction())

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "房源查询（融易拍）"))
        self.export_button.setText(_translate("MainWindow", "导出"))
        self.select_button.setText(_translate("MainWindow", "搜索"))
        self.label_6.setText(_translate("MainWindow", "示例：2020/01/01"))
        self.city_name.setItemText(0, _translate("MainWindow", "全部"))
        self.city_name.setItemText(1, _translate("MainWindow", "罗湖"))
        self.city_name.setItemText(2, _translate("MainWindow", "福田"))
        self.city_name.setItemText(3, _translate("MainWindow", "南山"))
        self.city_name.setItemText(4, _translate("MainWindow", "宝安"))
        self.city_name.setItemText(5, _translate("MainWindow", "龙岗"))
        self.city_name.setItemText(6, _translate("MainWindow", "盐田"))
        self.city_name.setItemText(7, _translate("MainWindow", "龙华"))
        self.label_2.setText(_translate("MainWindow", "区域"))
        self.label_5.setText(_translate("MainWindow", "时间"))
        self.label.setText(_translate("MainWindow", "状态"))
        self.status_name.setItemText(0, _translate("MainWindow", "全部"))
        self.status_name.setItemText(1, _translate("MainWindow", "正在竞拍"))
        self.status_name.setItemText(2, _translate("MainWindow", "即将开始"))
        self.status_name.setItemText(3, _translate("MainWindow", "流拍"))
        self.status_name.setItemText(4, _translate("MainWindow", "撤拍"))
        self.status_name.setItemText(5, _translate("MainWindow", "成交"))
        self.label_4.setText(_translate("MainWindow", "结果输出"))
        self.label_7.setText(_translate("MainWindow", "日志输出框"))
        self.menu.setTitle(_translate("MainWindow", "菜单"))
        self.action1.setText(_translate("MainWindow", "1"))
        self.actionhelle1.setText(_translate("MainWindow", "helle1"))
        self.actionhello2.setText(_translate("MainWindow", "hello2"))

#!/usr/bin/env python
# -*- coding:utf-8 -*-
"""
@file: call_pyqt5_demo.py
@desc: None
@Author: Chenzq
@Wechat: 15690833097
@contact: czq181020@gmail.com
"""
import PyQt5.sip
# 导入程序运行必须模块
import sys, os
from threading import Thread
from time import strftime, localtime, time
if hasattr(sys, 'frozen'):
    os.environ['PATH'] = sys._MEIPASS + ';' + os.environ['PATH']

# PyQt5中使用的基本控件都在PyQt5.QtWidgets模块中
from PyQt5.QtWidgets import QApplication, QMainWindow, QDialog, QMessageBox
from PyQt5.QtGui import QStandardItemModel, QStandardItem
from pandas import DataFrame, ExcelWriter
from pymysql import connect
# 导入designer工具生成的login模块
from pyqt5_demo import Ui_MainWindow


class MyMainForm(QMainWindow, Ui_MainWindow, QDialog):
    def __init__(self, parent=None):
        super(MyMainForm, self).__init__(parent)
        self.setupUi(self)
        # 链接数据库
        self.db = connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            passwd='123456',
            db='dbname',
            charset='utf8'
        )
        self.cursor = self.db.cursor()

        # 添加登录按钮信号和槽。注意display函数不加小括号()
        self.select_button.clicked.connect(self.display)
        # 添加退出按钮信号和槽。调用close函数
        self.export_button.clicked.connect(self._export_excel)

        # 筛选房源状态
        self.status = ''
        # 刷选房源区域
        self.city = ''
        # 刷选房源日期
        self.date = ''
        self.datas = []

    def display(self):
        # 利用line Edit控件对象text()函数获取界面输入
        # username = self.lineEdit.text()
        # password = self.lineEdit_2.text()
        self.status = self.status_name.currentText()
        self.city = self.city_name.currentText()
        self.date = self.date_name.text()
        t = Thread(target=self._select)
        t.setDaemon(True)
        t.start()
        # self._select()

    def _select(self):
        # 查询
        self.export_log(msg='状态：' + self.status + '，' + '区域：' + self.city + '，' + '日期：' + self.date)
        if not self.date:
            self.date = '0'
        if not all([self.status, self.city]):
            self.export_log('时间与区域不能为空?')
        v_dict = {
            "statusName": repr(self.status),
            "countyName": repr(self.city),
        }
        if self.status == '全部' and self.city == '全部':
            check_sql = """
                SELECT countyName,statusName,startTime,buildingName,areaSize,currentPrice,startPrice,unitPrice,
                communityName,bTypeName,taxesName,fzID, jiaofu, biz_tag FROM houselist WHERE startTime LIKE '%{}%' ORDER BY editDate DESC
            """.format(self.date)
        elif self.status == '全部' and self.city != '全部':
            check_sql = """
                SELECT countyName,statusName,startTime,buildingName,areaSize,currentPrice,startPrice,unitPrice,
                communityName,bTypeName,taxesName,fzID,jiaofu, biz_tag FROM houselist WHERE countyName={} AND startTime LIKE '%{}%' 
                ORDER BY editDate DESC 
            """.format(v_dict.get('countyName'), self.date)
        elif self.status != '全部' and self.city == '全部':
            check_sql = """
                SELECT countyName,statusName,startTime,buildingName,areaSize,currentPrice,startPrice,unitPrice,
                communityName,bTypeName,taxesName,fzID,jiaofu, biz_tag FROM houselist WHERE statusName={} AND startTime LIKE '%{}%'
                ORDER BY editDate DESC 
            """.format(v_dict.get('statusName'), self.date)
        elif self.status != '全部' and self.city != '全部':
            check_sql = """
                SELECT countyName,statusName,startTime,buildingName,areaSize,currentPrice,startPrice,unitPrice,
                communityName,bTypeName,taxesName,fzID,jiaofu, biz_tag FROM houselist WHERE statusName={} AND countyName={} 
                AND startTime LIKE '%{}%' ORDER BY editDate DESC
            """.format(v_dict.get('statusName'), v_dict.get('countyName'), self.date)
        try:
            self.cursor.execute(check_sql)
            self.datas = self.cursor.fetchall()
        except Exception as e:
            print(e)
            self.export_log(msg='请尝试退出重启?')
        else:
            global data_dict
            data_dict = {
                "区域": [],
                "开始时间": [],
                "标的物": [],
                "面积": [],
                "㎡": [],
                "起拍": [],
                "当前价": [],
                "学区": [],
                "板块": [],
                "市场": [],
                "市场价": [],
                "折扣率": [],
                "清场交付": [],
                "均价": [],
                "URL": [],

                # "类型": [],
                # "状态": [],
                # "评估价": [],
                # "小区": [],
                # "税费": [],
            }
            url = "https://www.51paimaifang.com/detail/{}.html"
            ali = "https://sf-item.taobao.com/sf_item/{}.htm"
            jd = "https://paimai.jd.com/{}"

            self.model = QStandardItemModel(len(self.datas), 3)
            # 设置表头
            self.model.setHorizontalHeaderLabels(['区域', '小区', '标的物'])

            for row, data in enumerate(self.datas):
                data_dict.get('区域').append(data[0])
                data_dict.get('开始时间').append(data[2])
                data_dict.get('标的物').append(data[3])
                data_dict.get('面积').append(data[4])
                data_dict.get('㎡').append('㎡')
                data_dict.get('起拍').append('起拍')
                data_dict.get('当前价').append(data[5])
                data_dict.get('学区').append(' ')
                data_dict.get('板块').append(data[13])
                data_dict.get('市场').append('市场')
                data_dict.get('市场价').append(' ')
                data_dict.get('折扣率').append(' ')
                data_dict.get('清场交付').append(data[12])
                data_dict.get('均价').append(data[7])
                fzID = data[11]
                house_id = fzID[-1]
                if house_id == '1':
                    data_dict.get('URL').append(ali.format(fzID[:-1]))
                elif house_id == '2':
                    data_dict.get('URL').append(jd.format(fzID[:-1]))
                else:
                    data_dict.get('URL').append(url.format(data[11]))

                # data_dict.get('类型').append(data[9])
                # data_dict.get('状态').append(data[1])
                # data_dict.get('评估价').append(data[6])
                # data_dict.get('小区').append(data[8])
                # data_dict.get('税费').append(data[10])
                for column in range(0, 3):
                    if column == 0:
                        val = data[0]
                    if column == 1:
                        val = data[8]
                    if column == 2:
                        val = data[3]
                    item = QStandardItem(val)
                    # 设置每个位置的文本值
                    self.model.setItem(row, column, item)

            # 水平方向标签拓展剩下的窗口部分，填满表格
            self.tableView.horizontalHeader().setStretchLastSection(True)
            # 水平方向，表格大小拓展到适当的尺寸
            # self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
            # 关联QTableView控件和Model
            self.tableView.setModel(self.model)

            self.export_log('结果共：' + str(len(self.datas)) + '条')

    def _export_excel(self):
        # 导出搜索结果
        try:
            isinstance(data_dict, dict)
        except NameError as e:
            self.export_log(msg='无查询结果')
        else:
            if data_dict:
                # urls = data_dict.get('URL')
                # for url in  urls:
                #     r = url.rsplit('/')
                #     print(r)
                #
                try:
                    today_time = strftime('%Y-%m-%d', localtime(time()))
                    df1 = DataFrame(data_dict)
                    writer = ExcelWriter(self.city + '&' + self.status + today_time + '.xlsx')
                    df1.to_excel(writer, sheet_name='Data1', startcol=0, index=False)
                    writer.save()
                    self.export_log(msg='文件名 ' + self.city + '&' + self.status + today_time + '.xlsx')
                except PermissionError as e:
                    self.export_log(msg='请关闭 ' + self.city + '&' + self.status + today_time + '.xlsx')
            else:
                self.export_log(msg='无结果')
        self.export_log(msg='导出完成')

    def closeEvent(self, event):
        """
        重写closeEvent方法，实现dialog窗体关闭时执行一些代码
        :param event: close()触发的事件
        :return: None
        """
        reply = QMessageBox.question(self,
                                     '关闭程序',
                                     "是否要退出程序？",
                                     QMessageBox.Yes | QMessageBox.No,
                                     QMessageBox.No)
        if reply == QMessageBox.Yes:
            self.cursor.close()
            self.db.close()
            self.export_log(msg='断开数据库')
            event.accept()
        else:
            event.ignore()

    def export_log(self, msg):
        # 利用text Browser控件对象setText()函数设置界面显示,append追加输出数据
        self.textBrowser.append(msg)


if __name__ == "__main__":
    # 固定的，PyQt5程序都需要QApplication对象。sys.argv是命令行参数列表，确保程序可以双击运行
    app = QApplication(sys.argv)
    # 初始化
    myWin = MyMainForm()
    # 设置窗口logo图标
    # myWin.setWindowIcon(QIcon('fangzi.ico'))

    # 将窗口控件显示在屏幕上
    myWin.show()
    # 程序运行，sys.exit方法确保程序完整退出。
    sys.exit(app.exec_())