python数据库使用二

数据库开发与驱动

数据库开发通常涉及与数据库进行通信，而驱动程序则负责封装数据库通信协议，简化开发者与数据库之间的交互过程。以下是关于MySQL数据库开发的一些内容：

CS模式与驱动程序

与MySQL通信通常采用典型的客户端-服务器（CS）模式。在这种模式下，客户端首先建立与服务器端的连接，然后通过该连接与数据库进行交互。在数据库编程中，客户端实际上是程序本身，负责向数据库发送查询和接收结果。

MySQL的驱动程序

MySQLdb: 最为著名的MySQL驱动库之一。它是对MySQL的C客户端库进行封装，主要支持Python 2，并且不再更新，不支持Python 3。
mysqlclient: 在MySQLdb的基础上增加了对Python 3的支持，是目前广泛使用的MySQL驱动之一。
MySQL官方Connector: MySQL官方提供的驱动程序，支持Python，并且与MySQL官方数据库保持同步更新，提供了良好的稳定性和性能。
MysqlExy: 这是另一个MySQL的驱动程序，可从MySQL官方网站下载。
pymysql: 一个纯Python实现的MySQL客户端库，提供了与MySQL数据库通信的功能，支持Python 2和Python 3，是一个轻量级的MySQL驱动选项。

驱动选择

选择合适的驱动程序取决于项目需求、Python版本以及开发者的个人偏好。一般来说，建议使用官方提供的MySQL Connector、mysqlclient或者pymysql驱动，以确保兼容性和稳定性。

import simplejson
import pymysql

with open('conn.json',encoding='utf-8') as f:
    conf = simplejson.load(f)

conn = None
cursor  = None

try: 
    conn = pymysql.connect(**conf)
    cursor = conn.cursor()
    for i in range(10):
        isql = """insert into student (name,age) values ('jerry-{}',{})""".format(i,i+10)
        x = cursor.execute(isql)
    print(type(x))
    print(x)
    conn.commit()

except Exception as e:
    print(e)
    conn.rollback() # 如果出现异常，回滚事务

finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

查询以字典的形式返回

import simplejson
import pymysql
from pymysql.cursors import DictCursor

with open('conn.json',encoding='utf-8') as f:
    conf = simplejson.load(f)

conn = None
cursor  = None

try: 
    conn = pymysql.connect(**conf)
    cursor = conn.cursor(DictCursor)
    isql = """select * from student"""
    x = cursor.execute(isql)
    print(cursor.fetchall())



finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

SQL 注入问题

避免SQL注入问题应该使用字符串拼接，而使用参数的传入

sql = 'SELECT * from student WhERE id= {}'.format('5 or 1=1')

运行的结果竟然是返回了全部数据
猜测后台数据库的查询语句使用拼接字符串等方式，从而经过设计为服务端传参，令其拼接出特殊字符串的SQL语句，返回攻击想要的结果;

永远不要相信客户端传来的数据是规范且安全的！！！

import simplejson
import pymysql
from pymysql.cursors import DictCursor

with open('conn.json',encoding='utf-8') as f:
    conf = simplejson.load(f)

conn = None
cursor  = None

try: 
    conn = pymysql.connect(**conf)
    cursor = conn.cursor(DictCursor)
    user_id = '1 or 3'
    #isql = '''select * from student where id={}'''.format(user_id) # 这样会导到SQL注入,列出所有学生
    #isql = """ select * from student where id=%s""" % user_id # 这样会导到SQL注入, 列出所有学生
    isql = """ select * from student where id=%s""" # 这样不会导到SQL注入
    x = cursor.execute(isql,user_id)
    print(cursor.fetchall())



finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

import simplejson
import pymysql
from pymysql.cursors import DictCursor

with open('conn.json',encoding='utf-8') as f:
    conf = simplejson.load(f)

conn = None
cursor  = None

try: 
    conn = pymysql.connect(**conf)
    cursor = conn.cursor(DictCursor)
    user_id = '1 or 3'
    isql = """select * from student where name like %(name)s and age > %(age)s"""
    x = cursor.execute(isql,{'name':'jerry-%', 'age': 15})
    print(cursor.fetchall())
    # [{'id': 4, 'name': 'jerry-9', 'age': 19}, {'id': 11, 'name': 'jerry-6', 'age': 16}, {'id': 12, 'name': 'jerry-7', 'age': 17}, {'id': 13, 'name': 'jerry-8', 'age': 18}, {'id': 14, 'name': 'jerry-9', 'age': 19}]


finally:
    if cursor:
        cursor.close()
    if conn:
        conn.close()

参数化查询在数据库开发中提高效率的主要原因之一是SQL语句缓存。下面是参数化查询提高效率的几个关键点：

SQL语句缓存

数据库服务器通常会对SQL语句进行编译和缓存。编译过程包括词法分析、语法分析、生成AST、优化和生成执行计划等步骤，这些过程消耗大量资源。
参数化查询的关键是将SQL语句中的变量部分使用占位符代替，因此即使参数中包含SQL指令，数据库服务器也不会将其视为指令执行，而只会对SQL语句部分进行编译。
服务端会先查找是否对同一条查询语句进行了缓存，如果缓存未失效，则不需要再次编译，从而降低了编译的成本，减少了内存消耗。
SQL语句字符串作为缓存的key，如果使用拼接方案，每次发送的SQL语句都不同，都需要重新编译并缓存。

防止注入攻击

参数化查询的另一个重要目的是有效地防止SQL注入攻击。通过使用参数化查询，可以将用户输入的数据视为参数而不是直接拼接到SQL语句中，从而有效消除了SQL注入的风险。

import simplejson
import pymysql
from pymysql.cursors import DictCursor

with open('conn.json',encoding='utf-8') as f:
    conf = simplejson.load(f)

print(conf)

try:
    conn = pymysql.connect(**conf)
    with conn:
        try:
            with conn.cursor(DictCursor) as cursor:
                isql = """select * from student where name like %(name)s and age > %(age)s"""
                print(isql)
                x = cursor.execute(isql,{'name':'jerry-%','age':15})
                print(cursor.fetchall())
# select * from student where name like %(name)s and age > %(age)s
# [{'id': 4, 'name': 'jerry-9', 'age': 19}, {'id': 11, 'name': 'jerry-6', 'age': 16}, {'id': 12, 'name': 'jerry-7', 'age': 17}, {'id': 13, 'name': 'jerry-8', 'age': 18}, {'id': 14, 'name': 'jerry-9', 'age': 19}]
            conn.commit()
        except:
            conn.rollback()

except Exception as e:
    print(e)