python - mysql 数据库增删改查

今天药忘吃喽~ 2022-12-20 12:47 184阅读 0赞

# 建表 #

在mysql数据库中建立一张表userlist:  
(不知道为什么保存的时候height, weight的值会变)  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center]

# 数据库连接 #

# connect to mysql
    conn = sql.connect(
        host = 'localhost',
        port = 3306,
        user = 'root',
        passwd = 'toor',
        db = 'pizzaordersys',
    )
    
    #get cursor
    cur = conn.cursor()

# 插入数据 #

tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) #获取当前时间
    insert = "insert into userlist values(id,%s,%s,%s,%s,%s,%s);"  # '%s'的写法是错误的, 不要忘记加分号
    result = cur.execute(insert, (name, gender, height, weight, IBW, tm))  #都是前面的数据
    conn.commit()  #对表有改动必须要commit()才行
    
    if result != 0:
        print("successful!")

![在这里插入图片描述][20201112163316478.png_pic_center]  
数据库中也增加了一条数据:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 1](不知道为什么weight的类型会自动补零而且还改不过来)

# 查找数据 #

## (一)显示所有数据 ##

#show all information
    select = "select * from userlist;"
    result = cur.execute(select)
    if result!=0:
        print("select - successful!")
        for row in cur.fetchall():
            print(row)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 2]

## (二)根据条件显示部分数据 ##

ids = list(map(int,input("input ids:").split()))
    print(ids)
    
    for i in ids:
        select = "select * from userlist where id in (%s);"
        result = cur.execute(select,i)
        print(cur.fetchone())

![在这里插入图片描述][2020111216453892.png_pic_center]

# 改动 #

name ="xixi"
    newname ="apple"
    
    update = "update userlist set name = %s where name= %s;"
    result = cur.execute(update,(newname,name))
    
    if result!=0:
        print("update-successful!")

结果:  
所有原来name为’xixi’的都被改成了’apple’

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 3]

# 删除 #

name ="popo"
    
    delete = "delete from userlist where name = %s;"
    result = cur.execute(delete,name)
    conn.commit()
    
    if result!=0:
        print("delete - successful!")

删除前:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 4]删除后:  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 5]

# 关闭连接 #

#close the connection
    cur.close()
    conn.close()

# 其他有用的知识 #

参考:  
[python连接数据库][python]  
[十分钟教你python+MySQL数据库操作][python_MySQL]

cur.fetchone()    #获取下一个查询结果
    
    cur.fetchmany(4)  # 获取指定个数个查询结果
    
    cur.fetchall()     # 获取所有的查询结果
    
    cur.rowcount   # sql语句影响的行数

表中插入多条数据:

除了用一条条用execute( )插入之外，我们还可以用executemany（）的方式批量插入，也就是val中包含的是一个元组列表，包含我们想要插入的数据。

# 往表里插入一些记录 
    sql="INSERT INTO customers(name,address,sex,age,sl) VALUES(%s, %s,%s,%s,%s)"
    val = ("John", "Highway 21","M",23,5000)  
    mycursor.execute(sql, val)  
    val = ("Jenny", "Highway 29","F",30,12500)  
    mycursor.execute(sql, val)  
    val=[("Tom","ABC 35","M",35,14000),  
         ("Tom1","Highway 29","M",28,6700),  
         ("Lily","Road 11","F",30,8000),  
         ("Martin","Road 24","M",35,14000),  
         ("Sally","Fast 56","M",32,15000)]  
    mycursor.executemany(sql, val)  
    con.commit()

另一个插入数据的例子:

# store user details into the database ibw
        tm = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        store = "insert into userlist values(id,%s,%s,%s,%s,%s,%s);"
    
        height = heightInch*2.54
        result = cur.execute(store, (name, gender, height, weightKilo, IBW, tm))
        conn.commit()
    
        if result!=0:
            print("your information has been updated in our database. Thank you!")

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/c626e4b6dbdc470cbbe07b283bff4ffd.png
[20201112163316478.png_pic_center]: /images/20221120/b4f46bc270e842eb804be24112dc4687.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/bde2030b30564d9fa992be3170896bd8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/11804666f6bd47c4959aefe769a011eb.png
[2020111216453892.png_pic_center]: /images/20221120/acda6b0a00454f879ada4039e3f8bf79.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/095cdcb0635246b587186e4ea1fa872a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/555a57220bb742f394050638aba9edf4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NoYXJvbm1vbW8_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221120/5d1500575c3c49da99c31479105bd00e.png
[python]: https://blog.csdn.net/weixin_42213622/article/details/86523400
[python_MySQL]: https://www.cnblogs.com/dengfaheng/p/10959127.html