Python操作数据库篇

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