Pycharm访问MySQL数据库·上

1.MySQL驱动模块Connector

#导入数据库的驱动工具
import mysql.connector
#连接数据库必备的条件
config = {"host": "localhost","port": 3306,"user": "root","password": "888888","database": "demo"
}
#创建连接项(管道)
conn = mysql.connector.connect(**config)
#创建游标(结果集)
cursor = conn.cursor()
#准备sql
sql = "select * from t_emp where sal > %s and deptno = %s"
#执行sql
#参数1：执行的sql 参数2：传入的参数，位置对应
cursor.execute(sql,(2000,10))
#获得数据
temp = cursor.fetchall()
print(temp)
#关闭游标
cursor.close()

2.SQL注入攻击案例

2.1 演示sql注入

import mysql.connector
config = {"host": "localhost","port": 3306,"user": "root","password": "888888","database": "demo"
}
conn = mysql.connector.connect(**config)
#合成数据
username = "1 OR 1=1"
password = "1 OR 1=1"
#演示sql注入
sql = ("select count(*) from t_user where username="+username+" AND password="+password)
cursor = conn.cursor()
cursor.execute(sql)
#查询的结果为1行1列，使用fetchone只获得第一行的数据，[0]只获得第一个格子里的数据
temp = cursor.fetchone()[0]
print(temp)
cursor.close()

2.2 屏蔽sql注入

import mysql.connector
config = {"host": "localhost","port": 3306,"user": "root","password": "888888","database": "demo"
}
conn = mysql.connector.connect(**config)
#合成数据
username = "1 OR 1=1"
password = "1 OR 1=1"
#屏蔽sql注入
sql = "select count(*) from t_user where username = %s and password=%s"
cursor = conn.cursor()
#sql后拼接'%'为变量替换，易触发sql注入
#sql后拼接','为占位符，可屏蔽sql注入
cursor.execute(sql, (username, password))
temp = cursor.fetchone()[0]
print(temp)
cursor.close()

3.MySQLConnector的异常处理

import mysql.connector
try: # 程序正常执行的区域config = {"host": "localhost","port": 3306,"user": "root","password": "888888","database": "demo"}conn = mysql.connector.connect(**config)#开启事务控制conn.start_transaction()cursor = conn.cursor()sql = "update t_user set username=%s where id=%s"cursor.execute(sql, ("wagaga",1))sql = "insert into t_emp values (%s,%s,%s,%s,%s,%s,%s,%s)"cursor.execute(sql, (9528,'ZOYA','CLERK',7782,'1980-10-17',1300.00,100,10))conn.commit()
except Exception as e: #程序触发异常后执行的区域print("触发异常")if "conn" in dir():conn.rollback()# 如果触发异常则回滚
finally: # 无论程序是否正常执行，都会执行的区域，通常用于关闭连接if "conn" in dir():# 查询是否有conn地址conn.close()# 如果有，则关闭