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)
