Aqua Data Studio怎么连接mysql anaconda连接mysql

文章目录

• 1. 下载MySQL-Connector模块
• 2. 创建和关闭连接
• 3. 游标（Cursor）
• 4. 避免SQL注入攻击
• 5. 事务控制与异常处理
• 6. 数据库连接池技术
• 7. 循环执行SQL语句
• 8. MySQL Connect的综合使用

1. 下载MySQL-Connector模块

• 是MySQL官方的驱动模块，兼容性好
• 下载

• 官方地址：https://dev.mysql.com/downloads/connector/python/
• anaconda下载：https://anaconda.org/anaconda/mysql-connector-python
• anaconda 命令行下载：conda install -c anaconda mysql-connector-python

2. 创建和关闭连接

• 直接创建和关闭连接

import mysql.connector
#创建连接
con = mysql.connector.connect(
	host="localhost", port="3306",
	user="root", password="password",
	database="demo"
);
#关闭连接
con.close()

• 用config来创建连接

import mysql.connector

config={
	"host": "localhost",
    "port": 3306,
	"user": "root", 
    "password": "password",
	"database": "demo"
}

con = mysql.connector.connect(**config);

con.close()

3. 游标（Cursor）

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

cursor = con.cursor()
cursor.execute(sql语句)

• 举例

con = mysql.connector.connect(**config);

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()

4. 避免SQL注入攻击

• 由于SQL语句是解释型语言，所以在拼接SQL语句的时候容易被注入恶意的SQL语句，容易被注入恶意的SQL语句
• 比如下面这个代码直接把t_news的所有记录都被删除

name = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE name="+name"

• 解决办法：；利用SQL与预编译机制

• 预编译机制会提前把SQL语句编译成二进制，这样反复执行一条SQL语句的效率就大大提升了
• 在执行语句时，数据库就不会做词法分析，所有的传进去的数据只会当普通数据。比如上面的OR就是普通的字符串OR而不是逻辑运算符

• 举个预防SQL注入攻击的例子

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]) # 返回0，就代表没有读取到任何数据

5. 事务控制与异常处理

• 用法

con.start_transaction([事务隔离级别]) # 用con对象而不是cursor游标对象
con.commit() # 提交
con.rollback() # 回滚

• 什么时候提交什么时候回滚，需要根据异常来决定

try:
	con = mysql.connector.connect(...)
	[con = start_transaction()]
	...
except Exception as e:
	[con.rollback()]
	print(e)
finally:
	if "con" in dir():
		con.close()

• 下面一段代码是使用异常的例子

import mysql.connector

try:
    config={
        "host": "localhost",
        "port": 3306,
        "user": "root", 
        "password": "password",
        "database": "demo"
    }
    con = mysql.connector.connect(**config)
    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, "LuNa", "SALESMAN", None, "1985-12-1", 2500, None, 10))
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close()

6. 数据库连接池技术

• 数据库连接是一种关键的，有限的，昂贵的资源，在并发执行的应用程序中体现得尤为突出
• TCP连接需要三次握手，四次挥手，然后数据库还要验证用户信息处
• 数据库连接池（Connection Pool）预先创建出一些数据库连接，然后缓存起来，避免了程序语言反复创建和销毁连接昂贵代价

import mysql.connector.pooling
config={...}
pool = mysql.connector.pooling.MySQLConnectionPool(
	**config,
	pool_size=10) # 连接池大小
con = pool.get_connection() # 取出空闲连接

• 练习1：UPDATE

import mysql.connector.pooling

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

• 练习2：DELETE

from re import M
import mysql.connector.pooling

config={
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password", 
    "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"
    cursor.execute(sql)
    con.commit()
except Exception as e:
    if "con" in dir():
        con.rollback()
    print(e)

7. 循环执行SQL语句

• 游标对象中的executemany()函数可以反复执行一条SQL语句

sql = "INSERT INTO t_dept(deptno, dname, loc) VALUES(%s, %s, %s)"
data = [[100, "A部门", "北京"], [110, "B部门", "上海"]]
cursor.executemany(sql, data) #可以循环执行SQL语句提交data中的数据

8. MySQL Connect的综合使用

• 练习1：使用INSERT语句，把部门平均底薪超过公司平均底薪的这样部门里的员工信息导入到t_emp_new表里面，并让这些用工隶属于sales 部门

from re import M
import mysql.connector.pooling

config={
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password", 
    "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" # They have the same structure, but no data in the t_emp_new table
    cursor.execute(sql)

    sql = "SELECT AVG(sal) AS avg FROM t_emp"
    cursor.execute(sql)
    avg = cursor.fetchone()[0]
    print(avg) #公司的平均底薪
    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 (" #INSERT INTO 表名 结果表
    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 += ")"
    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)

• 练习2：编写一个INSERT语句向部门表插入两条数据，每条记录都在部门原有最大主键值的基础上+10

from re import M
import mysql.connector.pooling

config={
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "password", 
    "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 " \
          "(SELECT MAX(deptno)+10, %s, %s FROM t_dept UNION SELECT MAX(deptno)+20, %s, %s FROM t_dept);"
    cursor.execute(sql, ("Dept A", "Beijing", "Dept B", "Shanghai"))

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