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:
# 执行创建表的sql
cur.execute(sql)
print("创建表成功")
except Exception as e:
print(e)
print("创建表失败")
finally:
# 关闭游标连接
cur.close()
# 关闭数据库连接
con.close()
3.向创建的表中插入数据
# 导入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:
# 执行sql
cur.execute(sql, ("小强", 18, 99.5))
con.commit()
print("插入数据成功")
except Exception as e:
print(e)
con.rollback()
print("插入数据失败")
finally:
# 关闭游标连接
cur.close()
# 关闭数据库连接
con.close()
插入多条数据
# 导入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:
# 执行sql
cur.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:
# 执行sql
cur.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:
# 执行sql
cur.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:
# 执行sql
cur.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:
# 执行sql
cur.execute(sql, ("薛宝钗"))
con.commit()
print("删除成功")
except Exception as e:
print(e)
con.rollback()
print("删除失败")
finally:
# 关闭游标连接
cur.close()
# 关闭数据库连接
con.close()
