此文章已同步更新至我的个人博客https://simonting.gitee.io
前言
帮老同学写的一个python脚本,具体需求主要是绘制一个面板，连接数据库，输入公司名称关键字，查询满足要求的数据，并且带有导出excel功能。数据库中的数据来源于https://blog.csdn.net/King__Cheung/article/details/109484246 这篇博客中通过python脚本调用企查查接口所获取到的数据。
一、代码
#!/usr/bin/python3
# -*- coding: UTF-8 -*-
# pip3 install PyMySQL
import tkinter as tk
from tkinter import messagebox
import pymysql
import csv
import os
window = tk.Tk()
# 标题
window.title('天眼查小工具')
# 窗口大小
window.geometry('900x500+500+250')
# 请输入关键字提示文字
label1 = tk.Label(window, text="请输入关键字:")
# 下方提示文字
label2 = tk.Label(window, text="输入关键字点击查询按钮即可搜索，点击导出数据即可导出为excel文件。")
# 关键字输入框
company_name_input = tk.Entry(window, show=None, font=('微软雅黑', 14))
# 查询结果显示文本框
text = tk.Text(window, width=71, height=14, font=('微软雅黑', 15))
# 滚动条
scroll = tk.Scrollbar()
# 连接数据库
db = pymysql.connect("localhost", "root", "zhangting", "holder")
cursor = db.cursor()
success_text = "程序启动成功。\n数据库连接成功。\n输入关键字开始查询吧..."
text.insert("insert", success_text)
export_res = ()
export_file_name = "data.csv"
# 清空
def reset():
global export_res
export_res = ()
company_name_input.delete(0, "end")
text.delete(1.0, "end")
# 查询
def search():
text.delete(1.0, "end")
key_word = company_name_input.get()
key_word = str(key_word).lstrip().rstrip()
get_data(key_word)
# 导出
def export():
if len(export_res) == 0:
messagebox.showwarning('提示', '内容为空，无法导出！')
return
create_csv_file('data-1')
csv_file = open(export_file_name, 'a+', newline='')
try:
writer = csv.writer(csv_file)
header = ('股东', '公司', 'Account UserName', 'Primary UserName', 'Account Segment', 'AM Name')
writer.writerow(header)
for res in export_res:
writer.writerow(res)
abspath = os.path.abspath(export_file_name)
messagebox.showinfo('提示', '文件导出完成！\n文件地址：' + abspath + "\n")
finally:
csv_file.close()
# 生成csv文件,且防止重名
def create_csv_file(file_name):
if os.path.exists(file_name + '.csv'):
index = int(file_name.split('-', 1)[1]) + 1
create_csv_file('data-' + str(index))
else:
global export_file_name
export_file_name = str(file_name) + '.csv'
# 查询按钮
button_search = tk.Button(window, text="查询", bg="LightBlue", font=('微软雅黑', 12), width=6, height=1, command=search)
# 清空按钮
button_reset = tk.Button(window, text="清空", bg="LightCyan", font=('微软雅黑', 12), width=6, height=1, command=reset)
# 导出按钮
button_export = tk.Button(window, text="导出数据", font=('微软雅黑', 12), width=8, height=1, command=export)
# 查询所有数据（包含直接关联与非直接关联的数据）
def execute_sql(key_word):
if len(key_word) != 0:
cursor.execute(
'select share,name,account_username,primary_username,account_segment'
',am_name from holder where share in (select share from holder where'
' name like \'%' + key_word + '%\')')
return cursor.fetchall()
# 查询直接关联的数据
def search_direct(key_word):
if len(key_word) != 0:
cursor.execute(
'select share,name,account_username,primary_username,account_segment'
',am_name from holder where name like \'%' + key_word + '%\'')
return cursor.fetchall()
# 查询数据、数据处理
def get_data(key_word):
content = '请输入关键字！'
if len(key_word) != 0:
result = execute_sql(key_word)
result = dig_data(result)
# 直接关联的数据
direct_result = search_direct(key_word)
# 求出非直接关联的公司
indirect_result = set(result).difference(set(direct_result))
indirect_result = tuple(indirect_result)
global export_res
export_res = result
if len(result) != 0:
content = "共查询到" + str(len(result)) + "条与“" + key_word + "”相关的信息：\n股东" \
+ " - " + "公司" + " - " + "Account UserName" \
+ " - " + "Primary UserName" + " - " + "Account Segment" \
+ " - " + "AM Name" + "\n"
content += "------------------------------\n与“" + key_word + "”直接关联的数据为：\n"
i = 1
for res in direct_result:
content += str(i) + "、" + res[0] + " - " + res[1] + " - " + res[2] \
+ " - " + res[3] + " - " + res[4] + " - " \
+ res[5] + "\n"
i += 1
content += "------------------------------\n与“" + key_word + "”非直接关联的数据为：\n"
if len(indirect_result) != 0:
for res in indirect_result:
content += str(i) + "、" + res[0] + " - " + res[1] + " - " + res[2] \
+ " - " + res[3] + " - " + res[4] + " - " \
+ res[5] + "\n"
i += 1
else:
content += "无数据\n"
else:
content = "未查询到任何与“" + key_word + "”相关的结果！"
text.insert("insert", content)
db.commit()
# 数据挖掘
def dig_data(result):
all_company = ""
for res in result:
all_company += res[1]
data = ()
for res in result:
share = str(res[0])
if share.endswith("公司"):
data += execute_sql(share)
return tuple(set(result + data))
# 控件布局
def main():
# "请输入关键字"提示文字坐标
label1.place(x=10, y=20)
# 关键字输入框坐标
company_name_input.place(x=100, y=17)
# 关键字输入框的长度与宽度
company_name_input.place(width=590, height=30)
# 查询按钮坐标
button_search.place(x=720, y=12)
# 清空按钮坐标
button_reset.place(x=800, y=12)
# 查询结果显示文本框坐标
text.place(x=12, y=60)
# 滚动条的位置
scroll.pack(side=tk.RIGHT, fill=tk.Y)
scroll.config(command=text.yview)
text.config(yscrollcommand=scroll.set)
# 导出按钮坐标
button_export.place(x=780, y=455)
# 下方提示文字坐标
label2.place(x=10, y=465)
window.mainloop()
if __name__ == '__main__':
main()
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
功能截图
查询功能：
- 1、 支持向下挖掘一层
- 2、支持直接关联数据与非直接关联数据分开显示
导出功能：
导出文件名支持自动重命名，不会覆盖之前导出的文件。
内容为空，无法导出：
导出的excel示例：
数据库数据示例：
