MySQL与Python交互

01_python连接数据库

import mysql.connector

con = mysql.connector.connect(
    host="localhost",port="3306",
    user="root",password="wq123",
    database="demo"
)
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])
con.close()

02_mysql注入攻击

import mysql.connector
config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "database":"vege"
}
con = mysql.connector.connect(**config)

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" %(username,password);

cursor = con.cursor()
cursor.execute(sql)
print(cursor.fetchone()[0])
con.close

03_预防注入攻击

import mysql.connector
config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "database":"vege"
}
con = mysql.connector.connect(**config)

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])
con.close

04_事务控制-异常处理

import mysql.connector
try:
    con = mysql.connector.connect(
        host="localhost",
        port=3306,
        user="root",
        password="wq123",
        database="demo"
    )
    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-12-01",2500,None,10))    
    con.commit()

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

05_数据库连接池

import mysql.connector.pooling

config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "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)

06_删除记录

import mysql.connector.pooling
config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "database":"demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    # con.start_transaction()
    cursor = con.cursor()
    # sql = "DELETE e,d FROM t_emp e JOIN t_dept d "\
    #       "ON e.deptno = d.deptno WHERE d.deptno=20"
    sql = "TRUNCATE TABLE t_emp" # 两种删除数据的方法，区别在于注释增减
    cursor.execute(sql)
    # con.commit()
except Exception as e:
    # if "con" in dir():
    #     con.rollback()
    print(e)

07_循环执行sql语句

import mysql.connector.pooling

config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "database":"demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s)"
    data = [
        [100,"A","北京"],[101,"B","上海"],
    ]
    cursor.executemany(sql,data)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

08_mysql _connector综合使用

"""
使用insert语句，把部门平均底薪超过公司平均底薪的部门里的员工
信息导入到t_emp_new表里面，并且让这些员工隶属于sales部门
"""

import mysql.connector.pooling

config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "database":"demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    # 删除测试数据表
    sql = "DROP TABLE t_emp_new"
    cursor.execute(sql)
    # 创建新的数据表
    sql = "CREATE TABLE t_emp_new LIKE t_emp"
    cursor.execute(sql)
    # 找出整个部门的平均薪资，并将数据保存在变量avg中
    sql = "SELECT AVG(sal) AS avg FROM t_emp"
    cursor.execute(sql)
    temp = cursor.fetchone()
    avg = temp[0] # 公司平均底薪
    # 按部门分组，找出薪资大于公司平均薪资的部门
    sql = "SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG(sal) >= %s"
    cursor.execute(sql,[avg])
    temp = cursor.fetchall()
    sql = "INSERT INTO t_emp_new SELECT * FROM t_emp WHERE deptno IN ("

    for index in range(len(temp)):
        one = temp[index][0]
        if index < len(temp) - 1:
            sql += str(one) + ","
        else:
            sql += str(one)

    sql += ")"
    cursor.execute(sql)
    sql = "DELETE FROM t_emp WHERE deptno IN ("

    for index in range(len(temp)):
        one = temp[index][0]
        if index < len(temp) - 1:
            sql += str(one) + ","
        else:
            sql += str(one)

    sql += ")"
    cursor.execute(sql)

    sql = "SELECT deptno FROM t_dept WHERE dname = %s"
    cursor.execute(sql,["SALES"])
    deptno = cursor.fetchone()[0]

    sql = "UPDATE t_emp_new SET deptno = %s"
    cursor.execute(sql,[deptno])

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

09_mysql_connector综合使用2

"""
编写一个insert语句，向部门表插入两条记录，
每条记录都在部门原有最大主键值的基础上+10
"""
import mysql.connector.pooling
config = {
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"wq123",
    "database":"demo"
}
try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10  
    )
    con = pool.get_connection()
    con.start_transaction()
    sql = "INSERT INTO t_dept "\
          "(SELECT MAX(deptno)+10,%s,%s FROM t_dept UNION \
            SELECT MAX(deptno)+20,%s,%s FROM t_dept)"
    cursor = con.cursor()
    cursor.execute(sql,("A部门","北京","B部门","上海"))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)