学习笔记 -- python使用MySQL

python操作数据库系列

1. python使用SQLite
2. python操作redis
3. python操作mysql

一、介绍

MySQL Connector是MySQL的官方驱动模块，兼容性较好。官网地址，通过下载安装即可。
当然也可以通过pip安装。

python -m pip install mysql-connector -i https://pypi.douban.com/simple

二、常用方法(一)

创建连接

# 第一种传参方式
import mysql.connector
conn = mysql.connector.connect(host="localhost", port=3306, user="root", password="1234", database="demo")
# 第二种传参方式
config = {
	"host": "localhost", 
	"port": 3306, 
	"user": "root", 
	"password": "1234", 
	"database": "demo"
}
conn = mysql.connector(**config)

游标的使用

cursor = conn.cursor()
cursor.execute(sql语句)  # 执行sql
cursor.executemany(sql语句, [(),()])  # 多次执行sql，实现重复修改
result = cursor.fetchone()

取数据库返回来的数据我们可以用以下三种方法：
fetchone() :这种方法每次只取一条数据
fetchmany(): 一次多条数据，括号内填入要读取的数据条数。不填则为1条数据，如果读数超过实际条数，只显示实际条数。
fetchall() :一次读取全部数据，如果管道内没有数据，则返回空元组或空列表。

防止注入攻击

案例
由于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 = "SELECT COUNT(*) FROM t_user WHERE username=%s AND AES_DECRYPT(UNHEX(password),'He1loworld')=%s" 
cursor.execute(sql,(username，password))
print(cursor.fetchone()[0])

事务的使用

事务只对DML语句有效，DDL语句是默认开始并执行事务的。

conn.start_transaction([事务的级别])  # 开始事务
conn.commit()  # 提交事务
conn.rollback()  # 回滚事务

三、数据库连接池

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

数据库连接语法

import mysql.connector.pooling
config = {
	"host": "localhost", 
	"port": 3306, 
	"user": "root", 
	"password": "1234", 
	"database": "demo"
}
# pool_size连接池最大连接数
pool = mysql.connector.MySQLConnectionPool(**config,pool_size=10)
conn = pool.get_connection()  # 从连接池取一个连接

四、常用写法

普通的连接

import mysql.connector
try:
    conn = mysql.connector.connect(host='localhost', port='3306', user='root', password='root123', database='demo')
    conn.start_transaction()
    cursor = conn.cursor()
    cursor.execute("sql语句")
    # result = cursor.fetchall()
    print(cursor)
    conn.commit()
except Exception as e:
    print(e)
    if "conn" in dir():
        conn.rollback()
finally:
    # 判断conn变量是否存在
    if "conn" in dir():
        conn.close()

连接池写法
使用连接池,连接在使用结束后会自动回收，所以不需要关闭连接

import mysql.connector.pooling
db_info = {
    "host": "localhost",
    "port": "3306",
    "user": "root",
    "password": "root123",
    "database": "demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(**db_info, pool_size=10)
    # 从连接池拿出空闲连接
    conn = pool.get_connection()
    conn.start_transaction()
    cursor = conn.cursor()
    sql = """"""
    cursor.execute(sql)
    conn.commit()
except Exception as e:
    if "conn" in dir():
        conn.rollback()
    print(e)

五、总结

• 掌握了数据库连接池技术
• 掌握了Python程序的CRUD操作
• 掌握了用预编译SQL抵御SQL注入攻击