Python-MySQL Connector模块

MySQL官方驱动模块

• MySQL Connector是MySQL官方的驱动模块，兼容性特别好
• 下载地址：https://dev.mysql.com/downloads/connector/python/
  

创建连接

方式一

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    port="3307",
    user="root",
    password="jinhua911love",
    database="demo"
)

con.close()

方式二

import mysql.connector

config = {
    "host":"localhost",
    "port":"3307",
    "user":"root",
    "password":"jinhua911love",
    "database":"demo"
}
con = mysql.connector.connect(**config)

游标(Cursor)

• MySQL Connector里面的游标用来执行SQL语句，而且查询的结果集也会保存在游标之中
  cursor = con.cursor()
cursor.execute( sql语句)

执行sql语句

cursor=con.cursor()
sql = "SELECT empno,ename,hiredate FROM t_emp;"
cursor.execute(sql)
for one in cursor:
    print(one[0], one[1], one[2])

7369 SMITH 1980-12-17
7499 ALLEN 1981-02-20
7521 WARD 1981-02-22
7566 JONES 1981-04-02
7654 MARTIN 1981-09-28
7698 BLAKE 1981-05-01
7782 CLARK 1981-06-09
7788 SCOTT 1982-12-09
7839 KING 1981-11-17
7844 TURNER 1981-09-08
7876 ADAMS 1983-01-12
7900 JAMES 1981-12-03
7902 FORD 1981-12-03
7934 MILLER 1982-01-23

Process finished with exit code 0

SQL注入攻击案例

username="1 OR 1=1";
password="1 OR 1=1";
sql="SELECT COUNT(*) FROM t_user WHERE 
username="+username+" AND
AES_DECRYPT(UNHEX(password) , 'HelloWorld' ) ="+password;
cursor.execute(sql);
print( cursor.fetchone()[0]);

SQL注入攻击的危害

• 由于SQL语句是解释型语言，所以在拼接SQL语句的时候，容易被注入恶意的SQL语句
  id = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE id=" + id;

SQL预编译机制

• 预编译SQL就是数据库提前把SQL语句编译成二进制，这样反复执行同一条SQL语句的效率就会提升
  

sql="INSERT INTO
t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s)";

SQL预编译机制抵御注入攻击

• SQL语句编译的过程中，关键字已经被解析过了，所以向编译后的SQL语句传入参数，都被当做字符串处理，数据库不会解析其中注入的SQL语句
  id = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE id=%s";

预防SQL注入攻击

username="1 OR 1=1"
password="1 OR 1=1"
sql = "SELECT COUNT(*) FROM t_user WHERE username=%s " \
      "AND AES_DECRYPT(UNHEX(password)),'HelloWorld')=%s;"
cursor=con.cursor()
cursor.execute(sql, (username,password))
print(cursor.fetchone()[0])

事务控制

• Connector为我们提供了非常简单的事务控制函数

con.start_transaction([事务隔离级别])
con.commit()
con.rollback()

异常处理

import mysql.connector

try:
    con = mysql.connector.connect(
        host="localhost",
        port="3307",
        user="root",
        password="jinhua911love",
        database="vega"
    )
    con.start_transaction()
    cursor = con.cursor()
    sql="INSERT INTO t_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) " \
        "VALUES(%s, %s, %s, %s, %s, %s, %s, %s);"
    cursor.execute(sql,(9600, "赵娜", "SALESMAN", None, "1985-02-01", 2500, None, 10))
    sql1="SELECT * FROM t_emp WHERE empno=%s;"
    cursor.execute(sql1,(9600))
    con.commit()

except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

finally:
    if "con" in dir():
        con.close()

数据库连接的昂贵之处

• 数据库连接是一种关键的、有限的、昂贵的资源，在并发执行的应用程序中体现得尤为突出。
• TCP连接需要三次握手，四次挥手，然后数据库还要验证用户信息
  

数据库连接池的意义

• 数据库连接池(Connection Pool)预先创建出一些数据库连接,然后缓存起来，避免了程序语言反复创建和销毁连接昂贵代价
  

数据库连接池的语法

import mysql.connector.pooling

config = {...}
pool = mysql.connector.pooling.MySQLConnectionPool(
    **config,
    pool_size=10
)
con = pool.get_connection()

实战

import mysql.connector.pooling

config = {
    "host":"localhost",
    "port":"3307",
    "user":"root",
    "password":"ll",
    "database":"demo"
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor=con.cursor()
    sql="UPDATE t_emp SET sal=sal+%s WHERE deptno=%s;"
    cursor.execute(sql,(200,20))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e) 

循环执行SQL语句

• 游标对象中的executemany()函数可以反复执行一条SQL语句

sql = "INSERT INTO t_dept(deptno, dname, loc) VALUES(%s, %s, %s)"
data = [[100,"A部门","北京"], [110,"B部门","上海"]]
cursor.executemany(sql, data)