Python3连接MySQL
介绍
PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。
Django中也可以使用PyMySQL连接MySQL数据库。
安装模块
pip3 install pymysql
连接数据库
基本使用
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 定义要执行的SQL语句 sql = """ CREATE TABLE USER1 ( id INT auto_increment PRIMARY KEY , name CHAR(10) NOT NULL UNIQUE, age TINYINT NOT NULL )ENGINE=innodb DEFAULT CHARSET=utf8; """ # 执行SQL语句 cursor.execute(sql) # 关闭光标对象 cursor.close() # 关闭数据库连接 conn.close()
execute()之sql注入
注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符
根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'
最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了 #1、sql注入之：用户存在，绕过密码 egon' -- 任意字符 #2、sql注入之：用户不存在，绕过用户与密码 xxx' or 1=1 -- 任意字符
import pymysql # 获取用户输入 name = input("用户名>>:") pwd = input("密码>>:") # 校验用户输入的用户名和密码是否正确 # 去数据库里取数据做判断 # 1. 连上数据库 conn = pymysql.connect(host="localhost",database="day60", user="root", password="123456", charset="utf8") # 不是utf-8 # 光有链接还不行，需要获取光标，让我能够输入SQL语句并执行 cursor = conn.cursor() # 2. 执行SQL语句 --> select * from userinfo where name=name and pwd=pwd sql = "select * from userinfo WHERE name='%s' and pwd='%s';" % (name, pwd) print(sql) ret = cursor.execute(sql) # 获取影响的行数 # 关闭光标和连接 cursor.close() conn.close() if ret: print("登陆成功") else: print("登录失败")
解决mysql的注入问题
# 原来是我们对sql进行字符串拼接 # sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd) # print(sql) # res=cursor.execute(sql) #改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了） sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上 res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。
import pymysql # 获取用户输入 name = input("用户名>>:") pwd = input("密码>>:") # 校验用户输入的用户名和密码是否正确 # 去数据库里取数据做判断 # 1. 连上数据库 conn = pymysql.connect(host="localhost",database="day60", user="root", password="123456", charset="utf8") # 不是utf-8 # 光有链接还不行，需要获取光标，让我能够输入SQL语句并执行 cursor = conn.cursor() # 2. 执行SQL语句 --> select * from userinfo where name=name and pwd=pwd sql = "select * from userinfo WHERE name=%s and pwd=%s;" print(sql) # 解决：让pymysql拼接字符串 ret = cursor.execute(sql,[name,pwd]) # 获取影响的行数 # 关闭光标和连接 cursor.close() conn.close() if ret: print("登陆成功") else: print("登录失败")
增删改查操作
增
import pymysql # 连接 conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 获取光标 cursor = conn.cursor() # 写sql语句 sql = "insert into userinfo(name,pwd) VALUE (%s,%s);" username = "dandan" password = "1220" # 执行sql语句 cursor.execute(sql,(username,password)) conn.commit() # 把修改提交到数据库 （提交事务）
cursor.close() conn.close()
插入数据失败回滚
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" username = "lishi"
password = "1220"
try: # 执行SQL语句 cursor.execute(sql, [username, age]) # 提交事务 conn.commit() except Exception as e: # 有异常，回滚事务 conn.rollback() cursor.close() conn.close()
获取插入数据的ID(关联操作时会用到)
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" username = "Alex" age = 18 try: # 执行SQL语句 cursor.execute(sql, [username, age]) # 提交事务 conn.commit() # 提交之后，获取刚插入的数据的ID last_id = cursor.lastrowid except Exception as e: # 有异常，回滚事务 conn.rollback() cursor.close() conn.close()
批量执行：用executemany()插入多条信息
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "INSERT INTO USER1(name, age) VALUES (%s, %s);" data = [("lishi", 18), ("lili", 20), ("dandna", 21)] try: # 批量执行多条插入SQL语句 cursor.executemany(sql, data) # 提交事务 conn.commit() except Exception as e: # 有异常，回滚事务 conn.rollback() cursor.close() conn.close()
删
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() sql = "DELETE FROM USER1 WHERE id=%s;" try: cursor.execute(sql, [4]) # 提交事务 conn.commit() except Exception as e: # 有异常，回滚事务 conn.rollback() cursor.close() conn.close()
改
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 修改数据的SQL语句 sql = "UPDATE USER1 SET age=%s WHERE name=%s;" username = "Alex" age = 80 try: # 执行SQL语句 cursor.execute(sql, [age, username]) # 提交事务 conn.commit() except Exception as e: # 有异常，回滚事务 conn.rollback() cursor.close() conn.close()
查fetchone、fetchmany、fetchall
查询单条数据
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 查询数据的SQL语句 sql = "SELECT id,name,age from USER1 WHERE id=1;" # 执行SQL语句 cursor.execute(sql) # 获取单条查询数据 ret = cursor.fetchone() cursor.close() conn.close() # 打印下查询结果 print(ret)
查询多条数据
# 导入pymysql模块 import pymysql # 连接database conn = pymysql.connect(host=“你的数据库地址”, user=“用户名”,password=“密码”,database=“数据库名”,charset=“utf8”) # 得到一个可以执行SQL语句的光标对象 cursor = conn.cursor() # 查询数据的SQL语句 sql = "SELECT id,name,age from USER1;" # 执行SQL语句 cursor.execute(sql) # 获取多条查询数据 ret = cursor.fetchall() cursor.close() conn.close() # 打印下查询结果 print(ret)
进阶用法
# 可以获取指定数量的数据 cursor.fetctmany(3) # 光标按绝对位置移动1 cursor.scroll(1, mode="absolute") # 光标按照相对位置(当前位置)移动1 cursor.scroll(1, mode="relative")