python pymysql基础操作

# -*- coding: utf-8 -*-
"""
Created on Sun Jan  2 17:20:35 2022

@author: shiyu
"""

import pymysql
import tkinter



#查看版本，创建数据库
def button_1(s,pos):
    b=tkinter.Button(win,text=s,command=lambda:fun_1())
    b.grid(row=pos[0],column=pos[1],sticky='w')
def fun_1():
    db=pymysql.connect(host='localhost',user='root',password='Wang1997#',\
                       port=3306)
    cursor=db.cursor()
    cursor.execute('SELECT VERSION()')
    data=cursor.fetchone()
    print('data version:',data)
    try:
        cursor.execute("create database spiders default character set utf8")
    except:
        print('database exists')
    db.close()

#创建表
def button_2(s,pos):
    b=tkinter.Button(win,text=s,command=lambda:fun_2())
    b.grid(row=pos[0],column=pos[1],sticky='w')
def fun_2():
    db=pymysql.connect(host='localhost',user='root',password='Wang1997#',\
                       port=3306,db='spiders')
    cursor=db.cursor()
    sql='create table if not exists students (id varchar(255) not null,\
        name varchar(255) not null,age int not null,primary key(id))'
    cursor.execute(sql)
    db.close()
    print('success!')

#表中插入数据
def button_3(s,pos):
    b=tkinter.Button(win,text=s,command=lambda:fun_3())
    b.grid(row=pos[0],column=pos[1],sticky='w')
def fun_3():
    db=pymysql.connect(host='localhost',user='root',password='Wang1997#',\
                       port=3306,db='spiders')
    cursor=db.cursor()
    id='20120001'
    user='Bob'
    age=20
    sql='insert into students(id,name,age) values(%s,%s,%s)'
    try:
        cursor.execute(sql,(id,user,age))
        db.commit()
        print('success!')
    except:
        db.rollback()
        print('exists!')
    db.close()

#表中插入数据,字典动态插入
def button_4(s,pos):
    b=tkinter.Button(win,text=s,command=lambda:fun_4())
    b.grid(row=pos[0],column=pos[1],sticky='w')
def fun_4():
    db=pymysql.connect(host='localhost',user='root',password='Wang1997#',\
                       port=3306,db='spiders')
    cursor=db.cursor()
    data={
        'id':'20120001',
        'name':'Bob',
        'age':20
    }
    table='students'
    keys=','.join(data.keys())
    values=','.join(['%s']*len(data))
    sql='insert into {table}({keys}) values({values})'.format(table=table,\
            keys=keys,values=values)
    try:
        cursor.execute(sql,tuple(data.values()))
        db.commit()
        print('success!')
    except:
        db.rollback()
        print('exists!')
    db.close()
    

#更新表数据
def button_5(s,pos):
    b=tkinter.Button(win,text=s,command=lambda:fun_5())
    b.grid(row=pos[0],column=pos[1],sticky='w')
def fun_5():
    db=pymysql.connect(host='localhost',user='root',password='Wang1997#',\
                       port=3306,db='spiders')
    cursor=db.cursor()
    sql='update students set age=%s where name=%s'
    try:
        cursor.execute(sql,(25,'Bob'))
        db.commit()
        print('success!')
    except:
        db.rollback()
        print('failed!')
    db.close()

#插入时重复则更新
def button_6(s,pos):
    b=tkinter.Button(win,text=s,command=lambda:fun_6())
    b.grid(row=pos[0],column=pos[1],sticky='w')
def fun_6():
    db=pymysql.connect(host='localhost',user='root',password='Wang1997#',\
                       port=3306,db='spiders')
    cursor=db.cursor()
    data={
        'id':'20120001',
        'name':'Bob',
        'age':21
    }
    table='students'
    keys=','.join(data.keys())
    values=','.join(['%s']*len(data))
    
    sql='insert into {table} ({keys}) values ({values}) on duplicate key \
        update'.format(table=table,keys=keys,values=values)
    update=','.join([" {key}=%s".format(key=key) for key in data])
    sql+=update
    try:
        if cursor.execute(sql,tuple(data.values())*2):
            print('success!')
            db.commit()
    except:
        print('failed')
        db.rollback()
    db.close()


win =tkinter.Tk()
win.geometry('600x450')
win.title('mysql')

button_1('创建数据库',(0,0))
button_2('创建表',(0,1))
button_3('表插入',(0,2))
button_4('表插入动态',(0,3))
button_5('更新表数据',(0,4))
button_6('插入时重复则更新',(0,5))

win.mainloop()