pymysql基础操作，增加，删除，更改，查询

​

pymysql基础操作

---

点击查看建表操作

---

增加数据

import pymysql  # 1.导模块

def add_stu():
    '''增加'''
    # 2.连接   host-主机名或地址，port-端口号，user-用户名，passwd-密码，db-创建的数据库名，charset-字符编码
    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='*****',db='*****',charset='utf8')
    print(conn)
    # 3. 获取游标
    cur = conn.cursor()
    # 4. 执行sql
    sql01 ='insert into students values(default,%s,%s,%s,now(),%s,%s)'
    #添加数据信息
    num = cur.executemany(sql01, [('张三', '男', 23, '1234554648', '河北'),('李四', '男', 23, '1008611', '河北')])

    print(f'影响行数:{num}')
    conn.commit() # 必须手动提交到数据库!
    # 5. 释放资源
    cur.close()
    conn.close()

if __name__ =="__main__":
    add_stu()

---

删除数据

import pymysql  #导入

# 1.选择删除的姓名
a= input("请输入你想删除的姓名：")
def del_stu(name):
    '''删除'''
    # 2.连接
    conn = pymysql.connect(host='localhost',port=3306,user='root',passwd='******',db='******',charset='utf8')
    print(conn)
    # 3. 获取游标
    cur = conn.cursor()
    # 4. 执行sql
    sql = 'delete from students where name =%s'
    num = cur.execute(sql,(name))
    print(f'影响行数:{num}')
    conn.commit() # 必须手动提交到数据库!
    # 5. 释放资源
    cur.close()
    conn.close()

if __name__ =="__main__":
    del_stu(name=a)  

---

更改

import pymysql

def update_stu():
     '''更改'''
    # 2.连接
    conn = pymysql.connect(host='',port=3306,user='root',passwd='******',db='******',charset='utf8')
    print(conn)
    # 3. 获取游标
    cur = conn.cursor()
    # 4. 执行sql
    sql = 'update students set name=%s,sex=%s,age=%s,birthday=%s where id=%s'
    num = cur.execute(sql,('贺六','女',23,'2000-11-23',1))
    print(f'影响行数:{num}')
    conn.commit() # 必须手动提交到数据库!
    # 5. 释放资源
    cur.close()
    conn.close()

if __name__ == '__main__':
    update_stu()

---

查询

import pymysql

def get_one():
    # 1. 连接
    conn = pymysql.connect(host='', port=3306, user='root', passwd='******', db='******', charset='utf8')
    # 2. 打开游标
    cur = conn.cursor()
    # 3. 执行
    sql ='select * from students where sex = %s'
    cur.execute(sql,('男')) #查询男的
    #result = cur.fetchone()
    result = cur.fetchall()
    print(result)
    for stu in result:
        print(f'id:{stu[0]},名字:{stu[1]}')

    # 4.关闭资源
    cur.close()
    conn.close()

if __name__ == '__main__':
    get_one()