python使用pymysql模块操作MySQL_Python

实例一：插入数据

python使用pymysql模块操作MySQL

				?

									import pymysql

									import tkinter as tk

									conn = pymysql.connect(host='localhost', user='root', passwd='root', db='okzl', charset='utf8')

									master = tk.Tk()

									master.title("插入供应商信息")

									master.geometry('350x300')

									tk.Label(master, text='cName').place(x=30,y=10)

									tk.Label(master, text='address').place(x=30,y=40)

									tk.Label(master, text='linkman').place(x=30,y=70)

									tk.Label(master, text='linkPhone').place(x=30,y=100)

									tk.Label(master, text='credit').place(x=30,y=130)

									tk.Label(master, text='remark').place(x=30,y=160)

									in1=tk.Entry(master, width=30).place(x=100,y=10)

									in2=tk.Entry(master, width=30).place(x=100,y=40)

									in3=tk.Entry(master, width=30).place(x=100,y=70)

									in3=tk.Entry(master, width=30).place(x=100,y=100)

									in3=tk.Entry(master, width=30).place(x=100,y=130)

									in3=tk.Entry(master, width=30).place(x=100,y=160)

									def insert():

									    cur = conn.cursor()  # 伸出手

									    sql1 = "insert into pro(cName,address,linkman,linkPhone,credit,remark) values(%s,%s,%s,%s,%s,%s)"

									    temp2 = ( )

									    cur.execute(sql1, temp2)

									    conn.commit()

									    cur.close()

									tk.Button(master,text='插入',width=8,command=insert).place(x=140,y=220)

									master.mainloop()

									conn.close()

python使用pymysql模块操作MySQL

成功插入数据

实例二：获取某个表全部数据

				?

									import pymysql

									conn = pymysql.connect(host='localhost', user='root', passwd='root', db='okzl', charset='utf8')

									cur = conn.cursor()

									cur.execute('select * from pro')

									data = cur.fetchall()

									cur.close()

									print(data)

									conn.close()

python使用pymysql模块操作MySQL

实例三：根据cName模糊搜索

				?

									import pymysql

									import tkinter as tk

									conn = pymysql.connect(host='localhost', user='root', passwd='root', db='okzl', charset='utf8')  # 连接数据库

									master = tk.Tk()

									master.title("搜索某客户信息")

									master.geometry('350x300')

									e = tk.Entry(master)

									e.pack(padx=20, pady=20)

									def tosearch():

									    cur = conn.cursor()

									    temp2 = (e.get(), "%" + e.get() + "%")

									    cur.execute("select * from pro where cName like %s or cName like %s ", temp2)

									    data = cur.fetchall()

									    cur.close()

									    print(data)

									tk.Button(master, text='搜索', width=8, command=tosearch).pack(padx=20, pady=50)

									master.mainloop()

									conn.close()

python使用pymysql模块操作MySQL

实例四：修改数据

根据数据库自动给数据生成的id来确认目标和修改数据

python使用pymysql模块操作MySQL

				?

									import pymysql

									import tkinter as tk

									conn = pymysql.connect(host='localhost', user='root', passwd='root', db='okzl', charset='utf8')

									master = tk.Tk()

									master.title("修改供应商信息")

									master.geometry('350x300')

									tk.Label(master, text='cName').place(x=30,y=10)

									tk.Label(master, text='address').place(x=30,y=40)

									tk.Label(master, text='linkman').place(x=30,y=70)

									tk.Label(master, text='linkPhone').place(x=30,y=100)

									tk.Label(master, text='credit').place(x=30,y=130)

									tk.Label(master, text='remark').place(x=30,y=160)

									tk.Label(master, text='目标id').place(x=30,y=190)

									in1=tk.Entry(master, width=30)

									in1.place(x=100,y=10)

									in2=tk.Entry(master, width=30)

									in2.place(x=100,y=40)

									in3=tk.Entry(master, width=30)

									in3.place(x=100,y=70)

									in4=tk.Entry(master, width=30)

									in4.place(x=100,y=100)

									in5=tk.Entry(master, width=30)

									in5.place(x=100,y=130)

									in6=tk.Entry(master, width=30)

									in6.place(x=100,y=160)

									in7=tk.Entry(master, width=30)

									in7.place(x=100,y=190)

									def update():

									    cur = conn.cursor()  # 伸出手

									    sql1 = "update pro set cName=%s, address=%s,linkman=%s,linkPhone=%s,credit=%s,remark=%s where id=%s"

									    temp2 = (in1.get(),in2.get(),in3.get(),in4.get(),in5.get(),in6.get(),in7.get())

									    cur.execute(sql1, temp2)

									    conn.commit()

									    cur.close()

									tk.Button(master,text='确认修改',width=8,command=update).place(x=140,y=220)

									master.mainloop()

									conn.close()

python使用pymysql模块操作MySQL

实例五：删除数据

这里是根据id删除

				?

									sql1 = "delete from pro where id=%s"

									temp1 = str(n)

									cur.execute(sql1, temp1)

									conn.commit()

									cur.close()