Python操作MySQL数据库

1. MySQL Connector

1.1 创建连接

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

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

1.2 Cursor

import mysql.connector
con=mysql.connector.connect(
    host="localhost",port="3306",
    user="root",password="password",
    database="demo"
)
cursor=con.cursor()
sql="SELECT empno,job,sal FROM t_bonus;"
cursor.execute(sql)
print(type(cursor))
for i in cursor:
    print(i)
con.close()

Result:
    <class 'mysql.connector.cursor_cext.CMySQLCursor'>
    (7369, 'CLERK', Decimal('8000.00'))
    (7499, 'SALESMAN', Decimal('1600.00'))
    (7521, 'SALESMAN', Decimal('1250.00'))
    (7566, 'MANAGER', Decimal('2975.00'))
    (7654, 'SALESMAN', Decimal('1250.00'))
    (7698, 'MANAGER', Decimal('2850.00'))
    (7782, 'MANAGER', Decimal('2450.00'))
    (7788, 'ANALYST', Decimal('3000.00'))
    (7839, 'PRESIDENT', Decimal('5000.00'))
    (7844, 'SALESMAN', Decimal('1500.00'))
    (7900, 'CLERK', Decimal('950.00'))
    (7902, 'ANALYST', Decimal('3000.00'))
    (7934, 'CLERK', Decimal('1300.00'))

1.3 SQL注入攻击

1. username=1 OR 1=1 password=1 OR 1=1

2. 在使用字符串直接拼接时OR之前不管对错，与OR结合都为true

3. 解决方法——预编译（也可以提高速度）

   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.execute(sql,(username,password))
   print(cursor.fetchone()[0])
   

1.4 事务管理和异常处理

1. sql连接和使用要异常处理异常

   import mysql.connector
   try:
       con=mysql.connector.connect(
           host="localhost",port="3306",
           user="root",password="password",
           database="demo"
       )
       con.start_transaction()
       cursor=con.cursor()
       sql="INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
       cursor.execute(sql,(60,"SALES","HUBAI"))
       con.commit()
   except Exception as e:
       if "con" in dir():
           con.rollback()
       print(e)
   finally:
       if "con" in dir():
           con.close()
   

1.5 删除数据

import mysql.connector,mysql.connector.pooling
config={
    "host": "localhost", "port": "3306",
    "user": "root", "password": "password",
    "database": "demo"
}
try:
    pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
    con=pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "DELETE FROM t_dept WHERE deptno=%s"
    cursor.execute(sql, (70,))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
# do not need to close con

executemany() 反复执行一条SQL语句

import mysql.connector,mysql.connector.pooling
config={
    "host": "localhost", "port": "3306",
    "user": "root", "password": "password",
    "database": "demo"
}
try:
    pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
    con=pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
    date=[[70,"SALES","BEIJING"],[80,"ACTOR","SHANGHAI"]]
    cursor.executemany(sql, date)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
# do not need to close con

2. 数据库连接池

1. 数据库的连接是昂贵的，一个连接要经过TCP三次握手，四次挥手，而且一台计算机的最大线程数也是有限的

2. 数据库连接池技术就是先创建好连接，再直接拿出来使用

   import mysql.connector,mysql.connector.pooling
   config={
       "host": "localhost", "port": "3306",
       "user": "root", "password": "password",
       "database": "demo"
   }
   try:
       pool=mysql.connector.pooling.MySQLConnectionPool(**config,pool_size=5)
       con=pool.get_connection()
       con.start_transaction()
       cursor = con.cursor()
       sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s);"
       cursor.execute(sql, (70, "SALES", "HUBAI"))
       con.commit()
   except Exception as e:
       if "con" in dir():
           con.rollback()
       print(e)
   # do not need to close con