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
|
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()
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()
|