Python操作Mysql数据库
安装pymysql
Linux操作系统：pip3 install pymysql
Windows操作系统:pip install pymysql
使用
说明：首先在虚拟机内的Ununtu系统里安装mysql和pymysql，创建数据库mydatabase,在该数据库内创建students数据表并添加数据，如图1所示
?图1
查询数据
# 导入
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.23.45', port=3306, user='root', password='abcdefg', database='mydatabase',
charset='utf8')
# 获取游标对象
cursor = conn.cursor()
sql = 'select * from students;'
# 执行sql语句 result受影响的行数
result = cursor.execute(sql)
print(result)
# 通过游标获取查询的结果
# print(cursor.fetchone())
# print(cursor.fetchone())
# print(cursor.fetchall())
# print(cursor.fetchall())
# print(cursor.fetchall())
eles = cursor.fetchall()
for ele in eles:
print(ele)
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
'''
运行结果:
3
(1, '张三', 30, Decimal('175.65'), '男', 10)
(2, '李四', 33, Decimal('180.00'), '女', 45)
(3, '王五', 25, Decimal('170.00'), '女', 66)
'''增加数据
# 导入
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.23.45', port=3306, user='root', password='abcdefg', database='mydatabase',
charset='utf8')
# 获取游标对象
cursor = conn.cursor()
sql= 'insert into students values(0,"赵六",18,161.9,"男",90);'
# 执行sql语句
result = cursor.execute(sql)
print(result)
# 提交(对于增删改需要加上commit提交)
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
'''
再执行查询运行结果如下:
4
(1, '张三', 30, Decimal('175.65'), '男', 10)
(2, '李四', 33, Decimal('180.00'), '女', 45)
(3, '王五', 25, Decimal('170.00'), '女', 66)
(4, '赵六', 18, Decimal('161.90'), '男', 90)
'''删除数据
# 导入
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.23.45', port=3306, user='root', password='abcdefg', database='mydatabase',
charset='utf8')
# 获取游标对象
cursor = conn.cursor()
# sql= 'insert into students values(0,"赵六",18,161.9,"男",90);'
sql = "delete from students where name='张三';"
# 执行sql语句
result = cursor.execute(sql)
print(result)
# 提交(对于增删改需要加上commit提交)
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
'''
再执行查询运行结果如下:
3
(2, '李四', 33, Decimal('180.00'), '女', 45)
(3, '王五', 25, Decimal('170.00'), '女', 66)
(4, '赵六', 18, Decimal('161.90'), '男', 90)
'''改：更新数据
# 导入
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.23.45', port=3306, user='root', password='abcdefg', database='mydatabase',
charset='utf8')
# 获取游标对象
cursor = conn.cursor()
# sql= 'insert into students values(0,"赵六",18,161.9,"男",90);'
# sql = "delete from students where name='张三';"
sql = "update students set age=18 where name='李四';"
# 执行sql语句
result = cursor.execute(sql)
print(result)
# 提交(对于增删改需要加上commit提交)
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
'''
再执行查询运行结果如下:
3
(2, '李四', 18, Decimal('180.00'), '女', 45)
(3, '王五', 25, Decimal('170.00'), '女', 66)
(4, '赵六', 18, Decimal('161.90'), '男', 90)
'''按条件查询
# 导入pymysql包
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.23.45', port=3306, user='root', password='abcdefg', database='mydatabase',
charset='utf8')
# 获取游标对象
cursor = conn.cursor()
# 不安全的方式
# 根据id查询学生信息
# find_name = input("请输入您要查询的学生姓名:")
# sql = "select * from students where name='%s'" % find_name
# # 显示所有的数据
# cursor.execute(sql)
# content = cursor.fetchall()
# for i in content:
# print(i)
# 安全的方式
# 根据id查询学生信息
find_name = input("请输入您要查询的学生姓名:")
sql = "select * from students where name=%s"
# 显示所有的数据
cursor.execute(sql, [find_name])
content = cursor.fetchall()
for i in content:
print(i)
# 关闭游标和连接
cursor.close()
conn.close()
'''
运行结果:
请输入您要查询的学生姓名:李四
(2, '李四', 12, Decimal('180.00'), '女', 45)
'''回滚
# 导入
import pymysql
# 创建连接
conn = pymysql.connect(host='192.168.23.45', port=3306, user='root', password='abcdefg', database='mydatabase',
charset='utf8')
# 获取游标对象
cursor = conn.cursor()
sql1 = "update students set age=100 where name='李四';"
# 执行sql语句
print(cursor.execute(sql1))
# 回滚
# 此时sql1语句不在执行
conn.rollback()
sql2 = 'select * from students;'
print(cursor.execute(sql2))
eles = cursor.fetchall()
for ele in eles:
print(ele)
# 提交(对于增删改需要加上commit提交)
conn.commit()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
'''
1
3
(2, '李四', 33, Decimal('180.00'), '女', 45)
(3, '王五', 25, Decimal('170.00'), '女', 66)
(4, '赵六', 18, Decimal('161.90'), '男', 90)
'''