作者:sumoly_ | 来源:互联网 | 2023-10-13 12:17
**
1,安装PyMySQL模块
**
语法为 pip install PyMySQL
**
2,集成环境里面操作MySQL数据库创建表
**
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写创建表的sql
sql = """create table python_student(sno int primary key auto_increment,sname varchar(30) not null,age int(2),score float(3,1))
"""try:# 执行创建表的sqlcur.execute(sql)print("创建表成功")
except Exception as e:print(e)print("创建表失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()
可打开Navicat查看创建完成的表
**
3,向创建的表中插入数据
**
1,插入单条数据
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写插入数据的sql
sql = "insert into python_student (sname,age,score) values (%s, %s, %s)"try:# 执行sqlcur.execute(sql, ("小强", 18, 99.5))con.commit()print("插入数据成功")
except Exception as e:print(e)con.rollback()print("插入数据失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()
2,插入多条数据
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写插入数据的sql
sql = "insert into python_student (sname,age,score) values (%s, %s, %s)"try:# 执行sqlcur.executemany(sql, [("小强", 18, 97.5),("小二", 19, 98.5),("小五", 20, 99.5)])con.commit()print("插入数据成功")
except Exception as e:print(e)con.rollback()print("插入数据失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()
**
4,操作mysql数据库查询所有数据
**
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写查询的sql
sql = "select * from python_student"try:# 执行sqlcur.execute(sql)# 处理结果集students = cur.fetchall()for student in students:# print(student)sno = student[0]sname = student[1]age = student[2]score = student[3]print("sno",sno,"sname",sname,"age",age,"score",score)
except Exception as e:print(e)print("查询所有数据失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()
**
5,查询mysql数据库的一条数据
**
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写查询的sql
sql = "select * from python_student where sname='小二'"try:# 执行sqlcur.execute(sql)# 处理结果集student = cur.fetchone()print(student)sno = student[0]sname = student[1]age = student[2]score = student[3]print("sno",sno,"sname",sname,"age",age,"score",score)
except Exception as e:print(e)print("查询所有数据失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()
**
6,操作mysql数据库修改数据
**
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写修改的sql
sql = 'update python_student set sname=%s where sno=%s'try:# 执行sqlcur.execute(sql, ("薛宝钗", 1))con.commit()print("修改成功")
except Exception as e:print(e)con.rollback()print("修改失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()
**
7,操作mysql数据库删除数据
**
# 导入pymysql
import pymysql# 创建连接
con = pymysql.connect(host="localhost", user="root", password="root", database="test", port=3306)# 创建游标对象
cur = con.cursor()# 编写删除的sql
sql = 'delete from python_student where sname=%s'try:# 执行sqlcur.execute(sql, ("薛宝钗"))con.commit()print("删除成功")
except Exception as e:print(e)con.rollback()print("删除失败")
finally:# 关闭游标连接cur.close()# 关闭数据库连接con.close()