Python数据库编程

数据库编程就是针对数据库的操作，通过编写程序的方式，让程序做为数据库的客户端进行数据库操作。

通过使用程序代码的方式去连接数据库服务器，通过和服务器进行交互完成对数据库的增删改查的方式，就称为数据库编程。

从图中可以看出，在 Python 中操作数据库，要经过五个步骤，分别是连接数据库，获取游标，数据库操作，关闭游标，关闭数据库连接。

在 Pyhton 中，使用 Pymysql 模块来对数据库进行编程。

1.1pymysql 操作

1. 导入模块

from pymysql import connect

2. Connection 对象

目的：用于建立代码与数据库的连接

3. 创建连接对象 conn=connect(参数列表)

• 参数host：连接的mysql主机，如果本机是‘localhost’
• 参数port：连接的mysql主机的端口，默认是3306
• 参数database：数据库的名称
• 参数user：连接的用户名
• 参数password：连接的密码
• 参数charset：通信采用的编码方式，推荐使用utf8

4. 关闭连接 conn.close()

5. 提交数据 conn.commit()

6. 撤销数据 conn.rollback()

7. 通过连接获取游标 cur = conn.cursor()返回Cursor对象

用于执行sql语句并获得结果

8. Cursor游标对象 目的: 执行sql语句

9. 获取Cursor对象 cur = conn.cursor()

10. 使用游标执行SQL语句 cur.execute(operation , [parameters])

11. 执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete语句

12. 获取结果集中的一条 cur.fetchone() 返回一个元组

13. 获取结果集中的一条 cur.fetchmany(2) 返回一个元组 ，元组中包含两条数据

14. 获取结果集中的所有 cur.fetchall() 执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回.

15. 关闭游标 cur.close()

1.2 示例代码

在这里默认已经拥有了一个名为python_db的数据库，数据库包含students,classes两个数据表，students字段包括id, name, age, height, gender, cls_id, is_delect， classes字段包括id, name.

1.2.1 查询数据

# 导入pymysql模块
from pymysql import *

# 与数据库建立连接
conn = connect(host = 'localhost', port = 3306, database = 'python_db', user = 'root', password = '', charset = 'utf8')

# 通过连接获取游标
cur = conn.cursor()

# 编辑mysql命令串
sql_str = '''select * from students;'''

# 通过游标向数据库服务器发送命令
cur.execute(sql_str)

# 接收数据库服务器返回的数据
result = cur.fetchall()

# 循环输出数据信息
for t in result:
    print(t)

# 关闭游标
cur.close()

# 关闭数据库连接
conn.close()

1.2.2 增删改操作

# 导入pymysql模块
from pymysql import *

# 建立连接
conn = connect(host = 'localhost', port = 3306, database = 'students', user = 'root', password = '', charset = 'utf8')

# 通过连接获取游标
cur = conn.cursor()

# 增加命令串：
sql_str = '''insert into students values (0, '新来的', 20, 180, '男', 1, 1);'''

# 修改命令串：
#sql_str = '''update students set name = '小明' where name = '新来的';'''

# 删除命令串：
#sql_str = '''delete from students where name = '小明';'''

# 通过游标向数据库服务器发送命令
row_count = cur.execute(sql_str)

# 在执行增删改数据库操作时需要对操作进行提交，否则不成功
conn.commit()

# 关闭游标
cur.close()

# 关闭连接
conn.close()

1.2.3 回滚（取消操作）

# 导入pymysql模块
from pymysql import *

# 与mysql数据库建立连接
conn = connect(host = 'localhost', port = 3306, database = 'python_db', user = 'root', password = '', charset = 'utf8')

# 通过连接获取游标
cur = conn.cursor()

# 创建sql命令串
sql_str = '''insert into students values (0, '小华',  20, 180, '男', 1, 1);'''

# 插入10条相同的数据
for i in range(10):
    # 执行sql语句
    row_count = cur.execute(sql_str)

# 在执行增删改操作时，如果不想提交前面的修改操作，可以使用 rollback 回滚取消操作
conn.rollback()

# 关闭游标
cur.close()

# 关闭数据库连接
cur.close()

1.3 参数化列表防止SQL注入

SQL注入:

产生原因: 后台对用户提交的带有恶意的数据和 SQL 进行字符串方式的拼接，得到了脱离原意的 SQL 语句，从而影响了 SQL 语句的语义，最终产生数据泄露的现象。

如何防止: SQL 语句的参数化, 将 SQL 语句的所有数据参数存在一个列表\元组\字典中传递给 execute 函数的第二个参数。

注意:

• 此处不同于python的字符串格式化，必须全部使用%s占位.

# 导入模块
from pymysql import *

# 与mysql数据库建立连接
conn = connect(host = 'localhost', port = 3306, database = 'python_db', user = 'root', password = '', charset = 'utf8')

# 获取游标
cur = conn.cursor()

# 接收外部传入的查询指令条件
find_name = input('输入要查询的姓名：')

# 创建sql命令
sql_str = '''select * from student from name = %s'''% find_name

# 执行select语句，execute方法在内部实现了防SQL注入的功能，但具体如何实现并不清楚，隐藏了细节
# 这里使用了元组的方式将find_name 传入sql_str中
row_count = cur.execute(sql_str, (find_name, ))

# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么(sql_str, (数值1, 数值2....))，此时sql语句中有多个%s即可
# 打印受影响的行数
print(row_count)

# 获取查询的结果
result = cur.fetchall()

# 打印查询的结果
print(result)

# 关闭游标
cur.close()

# 关闭数据库连接
conn.close()