python和mysql交互学习笔记

一、Mysql驱动模块Connector的语法

1.创建连接

方法1.

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

2.方法2

config={
    "host":"localhost",
    "port":3306,
    "user":"root",
    "password":"a123456",
    "database":"demo",
    "auth_plugin":"mysql_native_password"
}
co = mysql.connector.connect(**config)

游标（Cursor）

MySQL Connector里面的游标用来执行SQL语句，而且查询的结果集会保存在游标中

cursor = con.cursor()  #创建游标

cursor.execute(sql语句) # 执行sql语句

import mysql.connector
con = mysql.connector.connect(
    host="localhost",port="3306",
    user='root',password='a123456',
    database='demo', auth_plugin='mysql_native_password'
)
cursor = con.cursor()
sql = "SELECT ename,empno FROM t_emp"
cursor.execute(sql)
for one in cursor:
    print(one[0],one[1])

con.close()

2.MySQL Connector的异常处理

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

con.commit()  #提交

con.rollback()  #回滚

import mysql.connector
try:
    con = mysql.connector.connect(
        host="localhost", port="3306",
        user='root', password='a123456',
        database='demo', auth_plugin='mysql_native_password'
    )
    # 开启事务
    con.start_transaction()
    # 创建游标
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES (%s,%s,%s)"
    # 执行sql语句
    cursor.execute(sql,(70,'技术部','北京'))
    # 提交事务
    con.commit()
except Exception as e:
    # 如果有异常，回滚事务
    con.rollback()
    print(e)
finally:
    # 关闭进程
    if 'con' in dir():
        con.close()

三、数据库连接池技术

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

3.1、数据库连接池的意义

数据库连接池（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":3306,
    "user":"root",
    "password":"a123456",
    "database":"demo",
    "auth_plugin":"mysql_native_password"
}
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,10))
    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)