Python增删改查到Mysql

这是我的数据表：

增删改查在代码里(haichong是我操作的数据库其中的一个表)：

import pymysql
import datetime,string,random
conn=pymysql.connect("localhost",user="你的mysql用户名",password="密码",db="你的数据库名称")
# print(conn)
# print(type(conn))

#获取游标
cursor=conn.cursor()


#获取当前时间
t=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# print(t)
#增
def insert():
    sql="insert into haichong values(%s,%s,%s,%s)"
    insert1=cursor.execute(sql,("apple2",0.85,t,52))
    print("影响行数：",insert1)

#增加多条
def insertmany():
    sql = "insert into haichong values(%s,%s,%s,%s)"
    insert2 = cursor.executemany(sql, [("apple3", 0.85, t, 52),
                                       ("daxianghuijia",0.365,t,1),
                                       ("chacie",0.999,t,2)])
    print("影响行数：", insert2)


#查
#可查全部用where较麻烦，不够fetchall简单
def queryone():
    sql="select * from haichong"
    cursor.execute(sql)
    while True:
        #res只是当前条数据
        res=cursor.fetchone()
        #查全部
        if res is None:
            #表示结果一取完
            break
        print(res)

def querymany():
    sql = "select * from haichong"
    cursor.execute(sql)
    #restuple返回一个元组的元组,可查全部
    restuple=cursor.fetchmany(5)
    #遍历元组取出每条数据
    for res in restuple:
        print(res)

#可查全部比fetchmany方便
def queryall():
    sql = "select * from haichong"
    cursor.execute(sql)
    restuple=cursor.fetchall()
    # 遍历元组取出每条数据
    # for res in restuple:
    #     print(res)
    print("共%d条数据"%len(restuple))


#改+查一条
def update():
    sql="update haichong set amount=210 where categories='a1'"
    update=cursor.execute(sql)
    print('修改后受影响的行数为：', update)
    # 查询一条数据
    cursor.execute('select * from haichong where categories="a1";')
    print(cursor.fetchone())

#改多条
def updatemany():
    sql="update haichong set amount=%s where categories=%s"
    updatemany=cursor.executemany(sql,[(556,"a1"),(777,"apple3")])
    print('修改后受影响的行数为：', updatemany)

#删一条
def deleteone():
    sql='delete from haichong where categories="a1"'
    deleteone=cursor.execute(sql)
    print("删了"+str(deleteone)+"条")

#删多条
def deletemany():
    sql="delete from haichong where categories=%s"
    deletemany=cursor.executemany(sql,[('apple2'),('apple3')])
    print("删了" + str(deletemany) + "条")


#把100条数据添加到数据库（一条一条加）
def exertise100_onebyone():
    words = list(string.ascii_letters)
    sql="insert into haichong values(%s,%s,%s,%s)"
    for i in range(3):
        random.shuffle(words)
        #生成随机两位小数
        a=random.uniform(0,1)
        a=round(a,2)
        num=cursor.execute(sql,("".join(words[:5]),a,t,random.randint(1,20)))
        print("增加了"+str(num)+"条数据")

    print(words)

if __name__=="__main__":
    exertise100_onebyone()
    cursor.close()
    conn.commit()
    conn.close()
    print("执行成功")