SQL_Client

pymysql

插入

import pymysql
import simplejson
import random

with open('j.json',mode='rt',encoding='utf-8') as f: # 创建单独的json文件导入
    conf = simplejson.load(f) # 反序列化

conn = None
cursor = None
try:
    conn = pymysql.connect(**conf) # 返回Connection类，有三个方法开始事务、变更事务和回滚事务
    conn.ping()
    cursor = conn.cursor() # 获取游标Cursor，操作数据库必须使用游标
    print(type(cursor))

    for i in range(10):
        sql = "insert into tdb_cates (id,cate_name,parent_id) values ({},'tom-{}',{})".format(i+1,i+1,random.randint(20,30)) # 注意，tom-{},虽然为字符串，但是Server端收到的不会带''，抛异常，所以改为'tom-{}'
        result = cursor.execute(sql)
    conn.commit() # 多次操作，一次提交

finally:
    if conn: # socket，必须归还
        conn.close()

    if cursor: 
        cursor.close() # self.connection=connection变更为self.connection = None,connection是socket对象，确保socket对象引用计数为0

查询

Cursor

import pymysql
import simplejson

with open('j.json',mode='rt',encoding='utf-8') as f: # 创建单独的json文件导入
    conf = simplejson.load(f) # 反序列化

conn = None
cursor = None
try:
    conn = pymysql.connect(**conf) # 返回Connection类，有三个方法开始事务、变更事务和回滚事务
    conn.ping()
    cursor = conn.cursor() # 缺省cursor=Cursor,结果集为元组，记录也为元组

    # 查询
    sql = "select * from tdb_cates"
    rows = cursor.execute(sql)

    print(cursor.fetchone(),'one') # 拿取一个数据
    print(cursor.rownumber) # rownumber返回当前游标指针的位置
    print(cursor.fetchmany(3),'many') # 拿取三个数据
    print(cursor.rownumber) # rownumber返回当前游标指针的位置
    print(cursor.fetchall(),'all') # 拿取剩下所有的数据
    print(cursor.rownumber,cursor.rowcount) # rowcount,返回所有行数

    for data in cursor.fetchall()[0:3]: # 无结果，需要将游标指针回调
        print(data)

    cursor.rownumber = 0 # rownumber,除去返回游标指针位置，还可以调整指针位置
    for data in cursor.fetchall()[0:3]:
        print(data)

    cursor.rownumber = -1 # rownumber,支持正负索引
    print(cursor.fetchone())

    cursor.rownumber = 20 # rownumber,正索超界，返回None，不抛异常；
    print(cursor.fetchone())

    cursor.rownumber = -11 # rownumber，负索引超界，抛IndexError；
    print(cursor.fetchone())

finally:
    if conn: # socket，必须归还
        conn.close()

    if cursor:
        cursor.close()

DictCursor

import pymysql
import simplejson
from pymysql.cursors import DictCursor

with open('j.json',mode='rt',encoding='utf-8') as f: # 创建单独的json文件导入
    conf = simplejson.load(f) # 反序列化

conn = None
cursor = None
try:
    conn = pymysql.connect(**conf) # 返回Connection类，有三个方法开始事务、变更事务和回滚事务
    conn.ping()
    cursor = conn.cursor(cursor=DictCursor) # cursor=DictCursor，通过Minxi混合类实现，结果集为列表，记录为字典

    # 查询
    sql = "select * from tdb_cates"
    rows = cursor.execute(sql)

    print(cursor.fetchone(),'one') # 拿取一个数据
    print(cursor.rownumber) # rownumber返回当前游标指针的位置
    print(cursor.fetchmany(3),'many') # 拿取三个数据
    print(cursor.rownumber) # rownumber返回当前游标指针的位置
    print(cursor.fetchall(),'all') # 拿取剩下所有的数据
    print(cursor.rownumber,cursor.rowcount) # rowcount,返回所有行数

finally:
    if conn: # socket，必须归还
        conn.close()

    if cursor:
        cursor.close()

注入攻击

import pymysql
import simplejson
from pymysql.cursors import DictCursor

with open('j.json',mode='rt',encoding='utf-8') as f: # 创建单独的json文件导入
    conf = simplejson.load(f) # 反序列化

conn = None
cursor = None
try:
    conn = pymysql.connect(**conf) # 返回Connection类，有三个方法开始事务、变更事务和回滚事务
    conn.ping()
    cursor = conn.cursor(cursor=DictCursor) # cursor=DictCursor，通过Minxi混合类实现，结果集为列表，记录为字典

    # 查询
    sql = "select * from tdb_cates where id = {}".format('6 or 1=1') # 注入攻击，本来是查id=6,因为注入了'6 or 1=1',使得数据库返回所有信息，极度危险
    rows = cursor.execute(sql)

    print(cursor.fetchall(),'all') # 拿取剩下所有的数据

finally:
    if conn: # socket，必须归还
        conn.close()

    if cursor:
        cursor.close()

参数化查询 - 避免注入攻击

import pymysql
import simplejson
from pymysql.cursors import DictCursor

with open('j.json',mode='rt',encoding='utf-8') as f: # 创建单独的json文件导入
    conf = simplejson.load(f) # 反序列化

conn = None
cursor = None
try:
    conn = pymysql.connect(**conf) # 返回Connection类，有三个方法开始事务、变更事务和回滚事务
    conn.ping()
    cursor = conn.cursor(cursor=DictCursor) # cursor=DictCursor，通过Minxi混合类实现，结果集为列表，记录为字典

    # 查询
    sql = "select * from tdb_cates where id = %s" # 注入攻击，本来是查id=6,因为注入了'6 or 1=1',使得数据库返回所有信息，极度危险
    userid = '2 or 1 = 1'
    rows = cursor.execute(sql,args=(userid))
    print(cursor.fetchall()) # 拿取剩下所有的数据
    print('---------')
    sql = "select * from tdb_cates where cate_name = %(name)s" # 注意cate_name的书写格式
    username = 'tom-3'
    cursor.execute(sql,args={'name':username}) # execute的第二参数,参数化查询，可以是元组、列表或者字典
    print(cursor.fetchall())

finally:
    if conn: # socket，必须归还
        conn.close()

    if cursor:
        cursor.close()

上下文管理

• 源码阅读 – Connection类

# Connection类
class Connection(object):
    ...
        def __enter__(self):
        """Context manager that returns a Cursor"""
        warnings.warn(
            "...",
            DeprecationWarning)
        return self.cursor() # 返回Cursor游标对象

    def __exit__(self, exc, value, traceback):
        """On successful exit, commit. On exception, rollback"""
        if exc: # 如果出现异常，事务回滚
            self.rollback()
        else: # 未出现异常，事务提交
            self.commit()

• 源码阅读 – Cursor类

# Cursor类
class Cursor(object):
    ...
        def __enter__(self):
        return self # 返回Cursor实例自己

    def __exit__(self, *exc_info):
        del exc_info # 删除本地变量exc_info
        self.close() # 将self.connectin重新赋值为None
    
    def close(self):
        """..."""
        conn = self.connection
        if conn is None:
            return
        try:
            while self.nextset():
                pass
        finally:
            self.connection = None

上下文使用方法

import pymysql
import simplejson
from pymysql.cursors import DictCursor

with open('j.json',mode='rt',encoding='utf-8') as f: # 创建单独的json文件导入
    conf = simplejson.load(f) # 反序列化

conn = None
cursor = None

try:
    conn = pymysql.connect(**conf)  # 返回Connection类
    with conn as cursor: # 调用__enter__,返回游标对象。
        with cursor: # 返回游标自己
            sql = "select * from tdb_cates where id = %s"
            userid = '2 or 1 = 1'
            rows = cursor.execute(sql,args=(userid))
            print(cursor.fetchall())

            print('---------')
            sql = "select * from tdb_cates where cate_name = %(name)s"
            username = 'tom-3'
            cursor.execute(sql,args={'name':username}) # execute的第二参数,参数化查询，可以是元组、列表或者字典
            print(cursor.fetchall())
        # 离开,调用self.close()
    # 离开，提交事务或者回滚事务

finally: # 保证Client端socket对象的归还
    if conn: # socket，必须归还
        conn.close()

mysqlclient

Django中，ORM使用前提，就是安装mysqlclient

• pymysql兼容MySQLdb,而mysqlclient是对MySQLdb的封装，使用原则请参考pymysql

import MySQLdb
import simplejson
from MySQLdb.cursors import DictCursor

with open('j.json',mode='rt',encoding='utf-8') as f:
    config = simplejson.load(f)

conn = MySQLdb.connect(**config,autocommit=False) # pymysql语法兼容MySQLdb，使用原则借鉴pymysql
print(type(conn),conn)

try:
    cursor = conn.cursor(cursorclass=DictCursor) # 此处有所差异
    with cursor:
        data = cursor.execute('select * from employees where emp_no = 10010')
        print(data)
        print(cursor.fetchall())
finally:
    if conn:
        conn.close()