数据库CRUD操作是什么
CRUD是指在做计算处理时的增加(Create)、读取(Retrieve)(重新得到数据)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中数据库或者持久层。
MySQL Connector(一)
◆ MySQL Connector是MySQL官方的驱动模块,兼容性特别好
创建链接(一)
import mysql.connector
con=mysql.connector.connect(
host="localhost",port="3307",
user="root",password="abc123456"
database="demo"
)
con.close()
创建链接(二)
import mysql.connector
config = {
"host": "localhost",
"port": "3306",
"user": "root",
"password": "abc123456",
"database": "demo"
}
con = mysql.connector.connect(**config)
游标(Cursor)
◆ MySQL Connector里面的游标用来执行SQL语句,而且查询的结果集也会保存在游标之中
cursor = con.cursor()
cursor.execute(sql语句)
例:
import mysql.connector
config = {
"host": "localhost",
"port": "3306",
"user": "root",
"password": "abc123456",
"database": "demo"
}
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()
MySQL Connector(二)
SQL注入攻击案例
import mysql.connector
config={
"host":"localhost",
"port":3306,
"user":"root",
"password": "abc123456",
"database": "vega"
}
con=mysql.connector.connect(**config)
username = "1 OR 1=1"
password = "1 OR 1=1"
sql="SELECT COUNT(*) FROM t_user WHERE username="+username+\
" AND AES_DECRYPT(UNHEX(password),'Helloworld')="+password;
cursor = con.cursor()
cursor.execute(sql)
print(cursor.fetchone()[0])
con.close()
SQL注入攻击的危害
◆ 由于SQL语句是解释型语言,所以在拼接SQL语句的时候,容易被注入恶意的SQL语句
id = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE id=" + id;
SQL预编译机制
◆ 预编译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预编译机制抵御注入攻击
◆ SQL语句编译的过程中,关键字已经被解析过了,所以向编译后的SQL语句传入参数,都被当做字符串处理,数据库不会解析其中注入的SQL语句
id = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE id=%s"
预防SQL注入攻击
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()
MySQL Connector(三)
事务控制
con.start_transaction([事务隔离级别])
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": "abc123456",
"database": "demo"
}
con=mysql.connector.connect(**config)
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-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()
MySQL Connector(四)
数据库连接的昂贵之处
◆ 数据库连接是一种关键的、有限的、昂贵的资源,在并发执行的应用程序中体现得尤为突出。
◆ TCP连接需要三次握手,四次回收,然后数据库还要验证用户信息
应用程序 <-TCP协议-> 数据库
数据库连接池的意义
◆ 数据库连接池(Connection Pool)预先创建出一些数据库连接,然后缓存起来,避免了程序语言反复创建和销毁连接昂贵代价
import mysql.connector.pooling
config={
"host": "localhost",
"port": 3306,
"user": "root",
"password":"abc123456",
"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)
MySQL Connector(五)
DELETE语句
import mysql.connector.pooling
config={
"host": "localhost",
"port": 3306,
"user": "root",
"password":"abc123456",
"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)
TRUNCATE语句
import mysql.connector.pooling
config={
"host": "localhost",
"port": 3306,
"user": "root",
"password":"abc123456",
"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)
循环执行SQL语句
◆ 游标对象中的executemany()函数可以反复执行一条SQL语句
sql = "INSERT INTO t_dept(deptno,dname,loc) VALUES(%s,%s,%s)"
data = [[100,"A部门","北京"],[110,"B部门","上海"]]
cursor.executemany(sql, data)
例:
import mysql.connector.pooling
config={
"host": "localhost",
"port": 3306,
"user": "root",
"password":"abc123456",
"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部门", "北京"],[110, "B部门", "上海"]
]
cursor.executemany(sql,data)
con.commit()
except Exception as e:
if "con" in dir():
con.rollback()
print(e)