python3 ssh mysql_python3通过ssh操作mysql

# 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('', 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()

来源：oschina

链接：https://my.oschina.net/qjd/blog/4340309