Python操作mysql数据库

python 操作mysql

python与MySQL交互

Hello，大家好，我是景天，今天我们来一起探讨下python来对MySQL数据库进行操作。

要把python操作的数据保存到数据库需要用到pymysql

需要安装pymysql第三方包:

pip3 install pymysql

pymysql使用:

导入pymysql

import pymysql

1、创建连接对象

调用pymysql模块中的connect()函数来创建连接对象

conn = connect(参数列表)

参数host:连接mysql的主机ip，如果是本机就用’localhost’

参数port:连接mysql主机的端口号，默认是3306

参数user：连接mysql的用户名

参数password：连接密码

参数database：连接的数据库名称

参数charset：设置通信的编码格式，推荐使用utf8mb4

连接对象操作说明:

关闭连接:conn.close()

提交数据:conn.commit()

撤销数据:conn.rollback()

2、获取游标对象

获取游标对象的目的就是要执行sql语句，完成对数据库的增删改查

#调用连接对象的cursor()函数来获取游标对象

cur = conn.cursor()

游标操作说明:

使用游标执行sql语句:execute(operation, paramaters)执行sql语句，里面放sql语句。返回受影响的行数

主要执行insert update delete select等语句

paramaters可以是元祖，列表，字典

获取查询结果集中的一条数据:cur.fetchone() 返回一个元祖，如(1, ‘张三’)

获取查询结果集中的所有数据:cur.fetchall() 返回一个元祖，如((1, ‘张三’),(2, ‘李四’))

关闭游标: cur.close()表示和数据库操作完成

cursor用来执行命令的方法:

 callproc(self, procname, args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数

 execute(self, query, args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数

 executemany(self, query, args):执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数

 nextset(self):移动到下一个结果集

 cursor用来接收返回值的方法:

 fetchall(self):接收全部的返回结果行. 返回一个元祖，如((1, ‘张三’),(2, ‘李四’))

 fetchmany(self, size=None):接收size条返回结果行.如果size的值大于返回的结果行的数量,

则会返回cursor.arraysize条数据. 返回一个元祖，如((1, ‘张三’),(2, ‘李四’))

 fetchone(self):返回一条结果行. 返回一个元祖，如(1, ‘张三’)

 scroll(self, value, mode=‘relative’):移动指针到某一行.如果mode=‘relative’,

则表示从当前所在行移动value条,如果mode=‘absolute’,则表示从结果集的第一 行移动value条.

pymysql.connect() 参数含义

host=None, # 要连接的主机地址

user=None, # 用于登录的数据库用户

password=‘’, # 密码

database=None, # 要连接的数据库

port=0, # 端口，一般为 3306

unix_socket=None, # 选择是否要用unix_socket而不是TCP/IP

charset=‘’, # 字符编码

sql_mode=None, # Default SQL_MODE to use.

read_default_file=None, # 从默认配置文件(my.ini或my.cnf)中读取参数

conv=None, # 转换字典

use_unicode=None, # 是否使用 unicode 编码

client_flag=0, # Custom flags to send to MySQL. Find potential values in constants.CLIENT.

cursorclass=, # 选择 Cursor 类型

init_command=None, # 连接建立时运行的初始语句

connect_timeout=10, # 连接超时时间，(default: 10, min: 1, max: 31536000)

ssl=None, # A dict of arguments similar to mysql_ssl_set()'s parameters.For now the capath and cipher arguments are not supported.

read_default_group=None, # Group to read from in the configuration file.

compress=None, # 不支持

named_pipe=None, # 不支持

no_delay=None, #

autocommit=False, # 是否自动提交事务，默认不自动提交

db=None, # 同 database，为了兼容 MySQLdb

passwd=None, # 同 password，为了兼容 MySQLdb

local_infile=False, # 是否允许载入本地文件

max_allowed_packet=16777216, # 限制 LOCAL DATA INFILE 大小

defer_connect=False, # Don’t explicitly connect on contruction - wait for connect call.

auth_plugin_map={}, #

read_timeout=None, #

write_timeout=None,

bind_address=None # 当客户有多个网络接口，指定一个连接到主机

3、基本语法

“”"

(1) 创建连接对象 host user password database 这四个参数必写

conn = pymysql.connect( host=“127.0.0.1” , user=“root” , password=“123456” , database=“db003” , charset=“utf8mb4” , port=3306 )

(2) 创建游标对象 (用来操作数据库的增删改查)

cursor = conn.cursor()

print(cursor)

游标对象

(3) 执行sql语句

sql = “select * from employee”

#执行查询语句返回的总条数

res = cursor.execute(sql)

print(res)

(4) 获取数据 fetchone 获取一条

(4) 获取数据 fetchone 获取一条数据

#返回的是元组,里面包含的是第一条的完整数据

res = cursor.fetchone()

print(res)

res = cursor.fetchone()

print(res)

res = cursor.fetchone()

print(res)

(5) 释放游标对象

cursor.close()

(6) 释放连接对象

conn.close()

“”"

3、创建/删除 表操作

#conn = pymysql.connect(host=“127.0.0.1”,user=“root”,password=“123456”,database=“db003”)

#cursor = conn.cursor()

1.创建一张表

sql = """
create table t1(
id int unsigned primary key auto_increment,
first_name varchar(255) not null,
last_name varchar(255) not null,
sex tinyint not null,
age tinyint unsigned not null,
money float
);
"""
#res = cursor.execute(sql)
#print(res) # 无意义返回值

2.查询表结构

sql = “desc t1”

res = cursor.execute(sql)

print(res) # 返回的是字段的个数

res = cursor.fetchone()

print(res)

res = cursor.fetchone()

print(res)

res = cursor.fetchone()

print(res)

3.删除表

try:

sql = “drop table t1”

res = cursor.execute(sql)

print(res) # 无意义返回值

except:

pass

4、事务处理

pymysql 默认开启事务的,所有增删改的数据必须提交,否则默认回滚;rollback

所以，事务处理必须commit

conn = pymysql.connect(host="127.0.0.1",user="root",password="123456",database="db003")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "update employee set emp_name='程咬钻石' where id = 18 "
sql3 = "commit"

res1 = cursor.execute(sql1)
res1 = cursor.execute(sql2)
res1 = cursor.execute(sql3)
# 一般在查询的时候,通过fetchone来获取结果
res1 = cursor.fetchone()
print(res1)


cursor.close()
conn.close()

执行更新语句，返回1，表示执行成功

5、sql 注入攻击

sql注入与防止sql注入:

什么是sql注入:

用户提交带有恶意的数据与sql语句进行字符串形式的拼接，从而影响了SQL语句的语义，最终产生了数据泄露的现象

如何防止sql注入:

sql语句参数化:

sql语言中的参数使用%s来占位，此处不是python格式化字符串操作

将sql语句中%s占位所需要的的参数存在一个列表中，把参数列表传递给ececute()方法中的第二个参数

(1) sql注入的现象

import pymysql

''' 现象:绕开账号密码登录成功 '''
'''
user = input("请输入您的用户名>>>")
pwd  = input("请输入您的密码>>>")

conn = pymysql.connect(host="127.0.0.1" , user="root" , password="123456",database="db005")
cursor = conn.cursor()
sql1 = """
create table usr_pwd(
id int unsigned primary key auto_increment,
username varchar(255) not null,
password varchar(255) not null
)
"""
sql2 = "select * from usr_pwd where username='%s' and password='%s' " % (user,pwd)
print(sql2)
res = cursor.execute(sql2)
print(res) # 1查到成功 0没查到失败
# res=cursor.fetchone()    
"""
select * from usr_pwd where username='2222' or 4=4 -- aaa' and password='' 
相当于 : select * from usr_pwd where 10=10; 绕开了账户和密码的判断 -- 代表的是注释;
"""
if res:
    print("登录成功")
else:
    print("登录失败")

cursor.close()
conn.close()
'''

sql中是字符串的，变量处要加引号

sql注入，-- 是sql 的注释

输入用户名时加上一个引号，单引号双引号要根据代码中sql来定，可以一种不行，可以切换另一种，然后加上 or 1 =1 – 后面全是注释

(2) 预处理机制

在执行sql语句之前,提前对sql语句中出现的字符进行过滤优化,避免sql注入攻击

execute( sql , (参数1,参数2,参数3 … ) ) execute2个参数默认开启预处理机制

填写 234234’ or 100=100 – sdfsdfsdfsdf 尝试攻击sql语句参数化:

sql语言中的参数使用%s来占位，此处不是python格式化字符串操作

将sql语句中%s占位所需要的的参数存在一个列表中，把参数列表传递给ececute()方法中的第二个参数

第二个参数可以是字符串，元祖，列表或字典

原码

user = input(“请输入您的用户名>>>”)

pwd = input(“请输入您的密码>>>”)

conn = pymysql.connect(host=“127.0.0.1” , user=“root” , password=“123456”,database=“db005”)

cursor = conn.cursor()

sql = “select * from usr_pwd where username=%s and password=%s”

res = cursor.execute(sql , (user,pwd) )

print(res)

print( “登录成功” if res else “登录失败” )

cursor.close()

conn.close()

%s占位符依次取execute()方法中的第二个参数，参数输入对才能查询成功 %s不能要任何引号

6、python 操作mysql 数据库 (增删改查)

import pymysql

python 操作mysql增删改时,默认是开启事务的,
必须最后commit提交数据,才能产生变化

提交数据: commit 
默认回滚: rollback

pymysql.connect()函数默认是不提交的，所以每次增删改之后，要想执行结果持久化到数据库需要提交事务

conn = pymysql.connect(host=“127.0.0.1”,user=“root”,password=“123456”,database=“db005”)

#默认获取查询结果时是元组,可以设置 返回字典; cursor=pymysql.cursors.DictCursor

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

#执行对mysql 的操作

1.增

(1) 一次插入一条

sql = "insert into t1(first_name,last_name,sex,age,money) values(%s,%s,%s,%s,%s)"

res = cursor.execute( sql , ("孙","健",0,15,20000)  )
print(res) # 1
# 获取最后插入这条数据的id号
print(cursor.lastrowid)

(2) 一次插入多条

res = cursor.executemany(  sql , [  ("安","晓东",0,18,30000) , ("刘","玉波",1,20,50000) ,("张","光旭",0,80,60000) , ("李","是元",0,10,10) , ("高","大奥",1,20,80000)   ]   )
print(res) # 返回插入的条数
# 获取插入5条数据中的第一条数据的id
print(cursor.lastrowid)
# 获取最后一个数据的id
sql = "select id from t1 order by id desc limit 1"
res = cursor.execute(sql)
print(res)
# 获取结果,返回元组
res = cursor.fetchone()
print(res["id"])
# 默认元组 : (57, '高', '大奥', 1, 20, 80000.0)
# 返回字典 : {'id': 51, 'first_name': '高', 'last_name': '大奥', 'sex': 1, 'age': 20, 'money': 80000.0}

创建游标对象时，cur = conn.cursor() 默认通过fetchone,fetchall,fetchmany获取查询结果时是元组

可以设置返回字典; 将参数 cursor=pymysql.cursors.DictCursor 放入cur = conn.cursor() 中

插入一条数据和插入多条数据，插入多条数据时，每条数据放入元祖中，多条数据多个小元祖放入到列表中

executemany() 参数可跟 字符串，列表，元祖

2.删

sql = “delete from t1 where id in (%s,%s,%s)”

res = cursor.execute(sql , (3,4,5) )

print(res) # 返回的是3,代表删除了3条

if res:

print(“删除成功”)

else:

print(“删除失败”)

防止sql注入，使用%s占位

mysql删除不存在的数据不报错

3.改

sql = “update t1 set first_name = ‘王’ where id = %s”

sql = “update t1 set first_name = ‘王’ where id in (%s,%s,%s,%s)”

res = cursor.execute(sql , (6,7,8,9))

print(res) # 返回的是4,代表修改了4条

if res:

print(“修改成功”)

else:

print(“修改失败”)

4.查

fetchone 获取一条

fetchmany 获取多条

fetchall 获取所有

sql = “select * from t1”

res = cursor.execute(sql)

print(res) # 针对于查询语句来说,返回的res是总条数;

(1) fetchone 获取一条

res = cursor.fetchone()

print(res)

res = cursor.fetchone()

print(res)

fetchone()获取的是查询的第一条

(2) fetchmany 获取多条

res = cursor.fetchmany() # 默认获取的是一条数据,返回列表,里面里面是一组一组的字典;
data = cursor.fetchmany(3)
print(data)
"""
[
    {'id': 9, 'first_name': '王', 'last_name': '是元', 'sex': 0, 'age': 10, 'money': 10.0}, 
    {'id': 10, 'first_name': '孙', 'last_name': '健', 'sex': 0, 'age': 15, 'money': 20000.0}, 
    {'id': 11, 'first_name': '安', 'last_name': '晓东', 'sex': 0, 'age': 18, 'money': 30000.0}
]
"""
for row in data:
    first_name = row["first_name"]
    last_name = row["last_name"]
    sex = row["sex"]
    if sex == 0:
        sex = "男性"
    else:
        sex = "女性"
    age = row["age"]
    money = row["money"]
    strvar = "姓:{},名:{},性别:{},年龄:{},收入:{}".format(first_name,last_name,sex,age,money)
print(strvar)

fetchmany() 参数是要获取的数量,返回的是列表，里面是一条条记录，元祖或者字典

将数据组织好发给前端

(3) fetchall 获取所有

data = cursor.fetchall()

print(data)

如果是字典方式获取，得到的是列表包含字典

(4) 自定义搜索查询的位置，从哪里开始搜索

print(“<==================>”)

默认是相对滚动

如fetchall(),fetchmany(),fetchone()同时作用于同一个查询时，每个方法执行开头是上一个方法执行的结尾

----可以通过scroll()来重置游标位置，从我们需要的位置获取我们想要的数据

1.相对滚动 relative

“”“相对于上一次查询的位置往前移动(负数),或者往后移动(正数)”“”

“”"

cursor.scroll(-1,mode=“relative”)

#cursor.scroll(5,mode=“relative”)

res = cursor.fetchone()

print(res)

“”"

上面fetchall已经查询完毕，通过滚动搜索位置，向前移一位，又可以查询到数据

2.绝对滚动 absolute

“”“永远从数据的开头起始位置进行移动,不能向前滚”“”

cursor.scroll(0,mode=“absolute”)

res = cursor.fetchone()

print(res)

conn.commit()

cursor.close()

conn.close()

从开头开始偏移，cur.scroll(0,mode=“absolute”) 表示从开头开始查询。cur.scroll(2,mode=“absolute”)表示从开头向右滚动2个后，开始查询