Python练手小项目——简易版基础SQL模板代码生成器

1、效果图

2、代码

源码-ui.py：

from tkinter import *
from tkinter import scrolledtext, messagebox
from tkinter.ttk import Combobox
import pymysql




def init():
    # 创建窗口：实例化一个窗口对象
    window = Tk()

    # 窗口大小
    window.geometry("900x550")

    #  窗口标题
    window.title("基础SQL语句代码生成器")

    frame = Frame(window, relief=SUNKEN, borderwidth=2, width=450, height=250)
    frame.pack(side=TOP, fill=BOTH, expand=1)

    titleLabel = Label(frame, text="数据库连接", fg="red", font=("Arial Bold", 14))
    titleLabel.place(x=400, y=20, width=150, height=30)

    ipLabel = Label(frame, text="IP：", fg="red", font=("Arial Bold", 10))
    ipLabel.place(x=250, y=60, width=50, height=30)
    ipVar = StringVar()
    ipVar.set("localhost")
    ipEntryBox = Entry(frame, width=50, textvariable=ipVar)
    ipEntryBox.focus()  # 自动聚焦
    # 设置放置的位置
    ipEntryBox.place(x=300, y=60, width=300, height=30)

    portLabel = Label(frame, text="端口：", fg="red", font=("Arial Bold", 10))
    portLabel.place(x=250, y=100, width=50, height=30)
    portVar = IntVar()
    portVar.set(3306)
    portEntryBox = Entry(frame, width=50, textvariable=portVar)
    # 设置放置的位置
    portEntryBox.place(x=300, y=100, width=300, height=30)

    dbLabel = Label(frame, text="数据库：", fg="red", font=("Arial Bold", 10))
    dbLabel.place(x=250, y=140, width=50, height=30)
    dbVar = StringVar()
    dbVar.set("hello")
    dbEntryBox = Entry(frame, width=50, textvariable=dbVar)
    # 设置放置的位置
    dbEntryBox.place(x=300, y=140, width=300, height=30)

    userLabel = Label(frame, text="用户名：", fg="red", font=("Arial Bold", 10))
    userLabel.place(x=250, y=180, width=50, height=30)
    userVar = StringVar()
    userVar.set("root")
    userEntryBox = Entry(frame, width=50, textvariable=userVar)
    userEntryBox.place(x=300, y=180, width=300, height=30)

    passwordLabel = Label(frame, text="密码：", fg="red", font=("Arial Bold", 10))
    passwordLabel.place(x=250, y=220, width=50, height=30)
    passwordVar = StringVar()
    passwordVar.set("root")
    passwordEntryBox = Entry(frame, width=50, textvariable=passwordVar)
    passwordEntryBox.place(x=300, y=220, width=300, height=30)

    # tipsLabel = Label(frame, text="---", fg="red", font=("Arial Bold", 10))
    # tipsLabel.place(x=400, y=320, width=100, height=30)
    tbCombo = Combobox(frame)
    tbCombo['values'] = ["请选择表名"]
    tbCombo.current(0)
    tbCombo.place(x=200, y=320, width=100, height=30)

    conn = None

    def toConn():
        global conn
        ip = ipEntryBox.get()
        port = int(portEntryBox.get())
        db = dbEntryBox.get()
        userName = userEntryBox.get()
        password = passwordEntryBox.get()
        if ip is None or ip == "":
            messagebox.showwarning('提示', '请输入ip')
            return
        if port is None or port == "":
            messagebox.showwarning('提示', '请输入port')
            return
        if db is None or db == "":
            messagebox.showwarning('提示', '请输入数据库名')
            return
        if userName is None or userName == "":
            messagebox.showwarning('提示', '请输入用户名')
            return
        if password is None or password == "":
            messagebox.showwarning('提示', '请输入密码')
            return

        conn = connMySQL(db=db, host=ip, port=port, user=userName, pwd=password, encode="utf8")
        titleLabel.configure(text="连接已开启！")

        cur = conn.cursor()
        cur.execute("show tables;")
        data = cur.fetchall()
        arr = ["请选择表名"]
        for item in data:
            arr.append(item[0])
        tbCombo['values'] = tuple(arr)

    connBtn = Button(frame, text="开始连接", command=toConn)
    connBtn.place(x=200, y=280, width=100, height=30)

    textArea = scrolledtext.ScrolledText(frame, width=100, height=12, wrap="word", undo=True)
    textArea.place(x=120, y=360)

    def toGen():
        global conn
        if conn is None:
            messagebox.showwarning('提示', '请先连接')
            return
        else:
            db = dbEntryBox.get()
            tb = tbCombo.get()
            sql = "select column_name from information_schema.columns where table_schema='{0}' and table_name='{1}';".format(
                db, tb)
            cur = conn.cursor()
            cur.execute(sql)
            data = cur.fetchall()

            add_sql = concatInsertSQL(db, tb, data)
            textArea.insert(INSERT, add_sql)

            del_sql = concatDeleteSQL(db, tb)
            textArea.insert(INSERT, del_sql)

            update_sql = concatUpdateSQL(db, tb)
            textArea.insert(INSERT, update_sql)

            select_sql = concatSelectSQL(db, tb, data)
            textArea.insert(INSERT, select_sql)

    genBtn = Button(frame, text="开始生成", command=toGen)
    genBtn.place(x=350, y=280, width=100, height=30)


    def resetTxt():
        ipVar.set("localhost")
        portVar.set(3306)
        dbVar.set("hello")
        userVar.set("root")
        passwordVar.set("root")
        titleLabel.configure(text="数据库连接")
        tbCombo['values'] = ("请选择表名",)
        tbCombo.current(0)
        textArea.delete(1.0, END)

    resetBtn = Button(frame, text="重置", command=resetTxt)
    resetBtn.place(x=650, y=280, width=100, height=30)


    def toCloseConn():
        global conn
        if conn is not None:
            closeMySQL(conn)
            resetTxt()

    closeConnBtn = Button(frame, text="断开连接", command=toCloseConn)
    closeConnBtn.place(x=500, y=280, width=100, height=30)


    # 进入消息循环
    window.mainloop()


def connMySQL(db, host="localhost", user="root", pwd="root", port=3306, encode="utf8"):
    return pymysql.connect(host=host, user=user, password=pwd, port=port, database=db, charset=encode)


def closeMySQL(conn):
    conn.close()


def concatInsertSQL(db, tb, data):
    tb_cols = []
    for item in data:
        tb_cols.append(item[0])
    add_sql = "insert into " + str(db) + "." + str(tb) + "("
    for col in tb_cols:
        add_sql += col + ","
    add_sql = add_sql[:-1]
    add_sql += ") values(null" + ("," * (len(tb_cols) - 1)) + "); \n\n\n"
    return add_sql


def concatDeleteSQL(db, tb):
    return "delete from " + str(db) + "." + str(tb) + " where id= ; \n\n\n"


def concatUpdateSQL(db, tb):
    return "update " + str(db) + "." + str(tb) + " \nset  \nwhere   ;\n\n\n"


def concatSelectSQL(db, tb, data):
    tb_cols = []
    for item in data:
        tb_cols.append(item[0])
    select_sql = "select \n  "
    for col in tb_cols:
        select_sql += col + ", "
    select_sql = select_sql[:-2]
    select_sql += "\nfrom " + str(db) + "." + str(tb) + "\nwhere  ;\n\n\n"
    return select_sql



if __name__ == '__main__':
    init()

3、打包

cd 项目目录 

# -F 打包成单独的文件；-D 打包成多个文件
pyinstaller -F -p ./ -i ./icon.ico ./ui.py --noconsole

最终的exe文件在dist目录下。