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()