tkinter+pymysql实现gui数据库查询功能

首先引用tkinter和pymysql库;

import tkinter
import pymysql

构造一个格式化时间戳的类

#遇到日期特殊处理
class ComplexEncoder(json.JSONEncoder):
    def default(self,obj):
        if isinstance(obj,datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj,date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self,obj)

定义一个函数，实现sql查询的功能，使用entry和combobox获取用户的输入，使用open函数将查询结果导出并存储到D盘；

#定义查询函数
def interface_table_cx_sql():
    try:
        db = pymysql.connect(host=ip_down_menu.get(),port=int(port_down_menu.get()),user=user_down_menu.get(),password=pw_down_menu.get(),database=db_down_menu.get(), charset='utf8' )
        tkinter.messagebox.showinfo('消息框', '数据库连接成功', parent=win)
    except:
        tkinter.messagebox.showerror('消息框', 'error 数据库连接失败', parent=win)
    cursor = db.cursor()    #创建一个游标

    sql = "select * from " + interface_table_down_menu.get() 

    try:
    # 执行SQL语句
        cursor.execute(sql)
        results = cursor.fetchall()
        cols = cursor.description
        data = format_data(results,cols)
        cursor.close()
        db.close()
        data_json=json.dumps(data,cls=ComplexEncoder,indent=1,ensure_ascii=False)
        with open('D:/接口库全表查询结果.txt','w') as file:
            file.write(data_json)
        tkinter.messagebox.showinfo('消息框', '数据已导入D盘 接口库全表查询结果.txt', parent=win)
    except:
        tkinter.messagebox.showerror('消息框', '错误！！请检查输入信息', parent=win)

定义一个format_data函数将导出的数据格式化为json格式;

def format_data(results,cols):
    keys = []
    for column in cols:
        keys.append(column[0])
    key_number = len(keys)

    json_data = []
    for row in results:
        item = dict()
        for q in range(key_number):
            item[keys[q]] = row[q]
        json_data.append(item)

    return json_data

以下为主程序:

win = tkinter.Tk()
win.title(test')
# 设置窗口大小
winWidth = 800
winHeight = 600

# 获取屏幕分辨率
screenWidth = win.winfo_screenwidth()
screenHeight = win.winfo_screenheight()
 
x = int((screenWidth - winWidth) / 2)
y = int((screenHeight - winHeight) / 2)

# 设置窗口初始位置在屏幕居中
win.geometry("800x600")
# 设置窗口图标
win.iconbitmap("D:/ztshouhou for mysql/image/34.ico")
#p1 = PhotoImage(file = 'D:/project/34.jpg')
#win.iconphoto(False, '34.jpg') 
# 设置窗口宽高固定
win.resizable(0, 0)
#创建一个菜单项，类似于导航栏
menubar = Menu(win)
#提示1
tishi1 = Label(win,text = '数据库IP和端口号',font = 16)
tishi1.grid(row=0)
'''
下拉菜单
'''
down_menu_ip = tkinter.StringVar()    #创建变量，便于取值
down_menu_port = tkinter.StringVar()    #创建变量，便于取值
down_menu_user = tkinter.StringVar()    #创建变量，便于取值
down_menu_pw = tkinter.StringVar()    #创建变量，便于取值
down_menu_db = tkinter.StringVar()    #创建变量，便于取值
down_menu_table = tkinter.StringVar()    #创建变量，便于取值
down_menu_interface_table = tkinter.StringVar()    #创建变量，便于取值
 
ip_down_menu = ttk.Combobox(win, textvariable=down_menu_ip)     # #创建下拉菜单
ip_down_menu.grid(row=1,column=0)     # #将下拉菜单绑定到窗体
ip_file = open("D:/ztshouhou for mysql/ip.config",'r+')
for x in ip_file:
    ip_down_menu["value"] = x 
ip_file.close()
ip_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即********

port_down_menu = ttk.Combobox(win, textvariable=down_menu_port)     # #创建下拉菜单
port_down_menu.grid(row=2,column=0)     # #将下拉菜单绑定到窗体
port_file = open("D:/ztshouhou for mysql/port.config",'r+')
for x in port_file:
    port_down_menu["value"] = x 
port_file.close()
port_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即********

user_down_menu = ttk.Combobox(win, textvariable=down_menu_user)     # #创建下拉菜单
user_down_menu.grid(row=4,column=0)     # #将下拉菜单绑定到窗体
user_file = open("D:/ztshouhou for mysql/user.config",'r+')
for x in user_file:
    user_down_menu["value"] = x 
user_file.close()
user_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即********

pw_down_menu = ttk.Combobox(win, textvariable=down_menu_pw)     # #创建下拉菜单
pw_down_menu.grid(row=5,column=0)     # #将下拉菜单绑定到窗体
pw_down_menu["value"] = ('**********************','11111111')    # #给下拉菜单设定值
pw_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即********

db_down_menu = ttk.Combobox(win, textvariable=down_menu_db)     # #创建下拉菜单
db_down_menu.grid(row=7,column=0)     # #将下拉菜单绑定到窗体
db_file = open("D:/ztshouhou for mysql/db.config",'r+')
for x in db_file:
    db_down_menu["value"] = x 
db_file.close()
db_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即********

table_down_menu = ttk.Combobox(win, textvariable=down_menu_table)     # #创建下拉菜单
table_down_menu.grid(row=7,column=1)     # #将下拉菜单绑定到窗体
table_file = open("D:/ztshouhou for mysql/table.config",'r+')
for x in table_file:
    table_down_menu["value"] = x
table_file.close()
table_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即null

interface_table_down_menu = ttk.Combobox(win, textvariable=down_menu_interface_table)     # #创建下拉菜单
interface_table_down_menu.grid(row=7,column=2)     # #将下拉菜单绑定到窗体
interface_table_file = open("D:/ztshouhou for mysql/interface_table.config",'r+')
for x in interface_table_file:
    interface_table_down_menu["value"] = x
interface_table_file.close()
interface_table_down_menu.current(0)    # #设定下拉菜单的默认值为第1个，即null
'''