python3通过ssh操作mysql

• Z时代
• 2024-01-10
• 分类：综合

python"># coding=utf-8
import pymysql
import configparser
from tkinter import *
from tkinter import ttk, messagebox
from sshtunnel import SSHTunnelForwarder
config = configparser.ConfigParser()
if not config.read("sys.ini"):
    config["ssh"] = {
        "host": "",
        "user": "",
        "passwd": ""
    }
    config["db"] = {
        "host": "",
        "user": "",
        "passwd": ""
    }
    with open("sys.ini", "w") as configfile:
        config.write(configfile)
ssh_host = config["ssh"]["host"]
ssh_user = config["ssh"]["user"]
ssh_pwd = config["ssh"]["passwd"]
db_host = config["db"]["host"]
db_user = config["db"]["user"]
db_pwd = config["db"]["passwd"]
server = False
def connect_ssh():
    server = SSHTunnelForwarder(
        ssh_address_or_host=(ssh_host, 22),
        ssh_username=ssh_user,
        ssh_password=ssh_pwd,
        remote_bind_address=("127.0.0.1", 3306)
    )
    return server
def search_btn():
    try:
        if not ssh_host or not ssh_user or not ssh_pwd:
            raise Exception("没有配置远程主机")
        val = entry1.get()
        if not val:
            raise Exception("没有输入")
        global server
        if not server:
            server = connect_ssh()
        if not server.is_active:
            server.start()
            global port
            port = server.local_bind_port
        for item in tree.get_children():
            tree.delete(item)
        db = pymysql.connect(host=db_host, port=port, user=db_user, passwd=db_pwd, db="oil")
        sql = "select id,mobile,fleet_name,pay_name from oil_fleet_account " 
              " where mobile like "%{}%" or fleet_name like "%{}%" or pay_name like "%{}%"" 
              " order by id limit 15".format(val, val, val)
        cursor = db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        if not data:
            raise Exception("没有数据")
        for v in data:
            tree.insert("", END, values=v)
        db.close()
    except Exception as e:
        messagebox.showerror("错误", e)
        return
def edit_pwd(id, str, tl):
    db = pymysql.connect(host=db_host, port=port, user=db_user, passwd=db_pwd, db="oil")
    cursor = db.cursor()
    sql = "UPDATE oil_fleet_account SET pay_name = "{}" WHERE id = {}".format(str, id)
    try:
        change_row = cursor.execute(sql)
        db.commit()
    except Exception as e:
        db.rollback()
        db.close()
        messagebox.showerror("错误", e)
        return
    db.close()
    search_btn()
    messagebox.showinfo("提示", change_row)
    tl.destroy()
def click_item(event):
    if not tree.selection():
        return
    for item in tree.selection():
        item_text = tree.item(item, "values")
    tl = Toplevel()
    tl.title("修改密码")
    Label(tl, text="编号：" + item_text[0]).place(x=0, y=0)
    Label(tl, text="手机：" + item_text[1]).place(x=0, y=30)
    Label(tl, text="名称：" + item_text[2]).place(x=0, y=60)
    Label(tl, text="密码：").place(x=0, y=90)
    entry2 = Entry(tl, relief=SOLID)
    entry2.place(x=40, y=90)
    btn1 = Button(tl, text="修改", command=lambda: edit_pwd(item_text[0], entry2.get(), tl), relief=GROOVE)
    btn1.place(x=80, y=120)
window = Tk()
window.title("修改车队密码")
window.geometry("600x500")
window.resizable(width=False, height=False)
entry1 = Entry(window, relief=SOLID)
btn = Button(window, text="查询", command=search_btn, relief=GROOVE)
tree = ttk.Treeview(window, show="headings", selectmode="browse")
tree["columns"] = ("id", "mobile", "fleet_name", "pay_name")
tree.bind("<Double-1>", click_item)
tree.column("id", width=50)
tree.column("mobile", width=100)
tree.column("fleet_name", width=150)
tree.heading("id", text="编号")
tree.heading("mobile", text="手机")
tree.heading("fleet_name", text="名称")
tree.heading("pay_name", text="简称")
tree.place(x=20, y=60, height=400, width=560)
entry1.place(x=20, y=20, height=29, width=480)
btn.place(x=520, y=20)
window.mainloop()

以上是 python3通过ssh操作mysql 的全部内容， 来源链接： utcz.com/z/518135.html