python3使用pymysql操作mysql

数据库知识

数据库种类概述之数据库分类

• SQL NoSQL（非关系型数据库，弥补关系型数据库的缺点，可以存储json对象）
• 单机（数据库运行在一台服务器上） 分布式（数据库运行在服务器集群上）
• 文件型（数据存放在硬盘） 内存型（数据存放在内存里）
• 批处理 交互式（分级查询之后汇总）----分布式数据库的子

数据库产品介绍

SQL：mysql（开源，免费），postgre sql（开源，免费），SQLserver，oricle
NoSQL：mongoDB（使用最广泛的一种数据库），neo4j（性能有问题），elasticsearch（全文检索），influxDB（性能有问题）
单机：mysql，postgre sql
分布式：hive，impala
文件型数据库：mysql，mongDB
内存型数据库：redis（支持复杂的数据类型），memcached

amazon web services==>AWS==>亚马逊云平台提供商===》注册可以免费使用一年的套餐，可以免费提供mysql，PostgreSQL的服务。

mysql

mysql数据库属于中小型数据库，对于中小型企业比较合适。

python 操作mysql

MySQL Python客户端包括3个，官方提供的mysql-connector（自带的这个游标不能被封装到函数中，会报错，比较不适用），第三方客户端MySQLdb（python2使用MySQLdb，python3使用pymysql），MySQldb的二次封装torndb

由于本机安装的是python3，所以这里采用第二种方式：pymysql客户端来进行学习

学习笔记

api操作流程流程

1. 引入API模块
2. 获取与数据库的连接
3. 执行SQL语句和存储过程
4. 关闭数据库连接

学习笔记

• pymysql安装：pip install pymysql
• pymysql中所有的有关更新数据（insert，update，delete）的操作都需要commit，否则无法将数据提交到数据库，既然有了commit()，就一定有对应的rollback（）,commit()表示提交，rollback()表示回滚
• 调用无参数存储过程：可以使用cursor.callproc('p2') #等价于cursor.execute("call p2()")
• 调用有参数的存储过程：cursor.callproc('p1', args=(1, 22, 3, 4))
• 关于pymysql防注入，字符串拼接查询，容易造成注入，为了避免注入，使用pymysql提供的参数化语句

#正常构造语句的情况
sql="select user,pass from tb7 where user='%s' and pass='%s'" % (user,passwd)
row_count=cursor.execute(sql)
#拼接语句被构造成下面这样，永真条件，此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。
#select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'

execute执行sql语句的时候，必须使用参数化的方式，否则必然产生SQL注入漏洞

#避免注入，使用pymysql提供的参数化语句
user="u1' or '1'-- "
passwd="u1pass"
#执行参数化查询
row_count=cursor.execute("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
#内部执行参数化生成的SQL语句，对特殊字符进行了加\转义，避免注入语句生成。
# sql=cursor.mogrify("select user,pass from tb7 where user=%s and pass=%s",(user,passwd))
# print sql
#select user,pass from tb7 where user='u1\' or \'1\'-- ' and pass='u1pass'被转义的语句。

• 使用mysql存储过程动态执行SQL防注入，使用cursor.callproc('p2',(1,2,3,4))
• sql语句中存在中文字符的时候，需要在pymysql.connect()的时候，指定添加参数charset='utf8'，否则中文显示为乱码。
• 获取查询数据：cursor.fetchone()获取剩余结果的第一行数据，cursor.fetchmany(3)获取剩余结果的前3行数据，cursor.fetchall()获取剩余结果的所有数据。
• cursor提供一个参数可以获取最新insert自增的id，也就是最后插入的一条数据ID，如果没有insert过，执行cursor.lastrowid会报错
• 游标是可以移动的，通过cursor.scroll()来移动
• 关于pymysql默认select获取的数据是元祖类型，如果想要字典类型的数据，有两种方式

#方式1：pymysql.connection()中指定参数
conn = pymysql.connect(
    host = "127.0.0.1",
    user = "root",
    password = "",
    database = "hetingdemo",
    charset = 'utf8',
    cursorclass = pymysql.cursors.DictCursor)

#方式2：conn.cursor()中指定参数
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

实例

实例1：创建数据库（创建表）

#!/usr/bin/env python
#coding=utf-8

import pymysql

#创建数据库连接，注意这里我加入了charset和cursorclass参数
conn = pymysql.connect(
    host = "127.0.0.1",
    user = "root",
    password = "",
    database = "hetingdemo",
    charset = 'utf8',
    cursorclass = pymysql.cursors.DictCursor)

#获取游标
cursor = conn.cursor()
#构建数据表的sql语句
sql = '''
CREATE TABLE para5(
    id int UNSIGNED AUTO_INCREMENT  ,
    name varchar(30) NOT NULL,
    age int NOT NULL,
    PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT charset=utf8;
'''

try:
    # 执行sql语句
    cursor.execute(sql)
    # 提交执行
    conn.commit()
except Exception as  e:
    # 如果执行sql语句出现问题，则执行回滚操作
    conn.rollback()
    print(e)
finally:
    # 不论try中的代码是否抛出异常，这里都会执行
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

实例2：insert数据

#!/usr/bin/env python
#coding=utf-8

import pymysql

#创建数据库连接，注意这里我加入了charset和cursorclass参数
conn = pymysql.connect(
    host = "127.0.0.1",
    user = "root",
    password = "",
    database = "hetingdemo",
    charset = 'utf8',
    cursorclass = pymysql.cursors.DictCursor)
#获取游标
cursor = conn.cursor()

try:
    # 执行一条insert语句，返回受影响的行数
    # cursor.execute("INSERT INTO para5(name,age) VALUES(%s,%s);",('次牛','12'))
    # 执行多次insert并返回受影响的行数
    cursor.executemany("INSERT INTO para5(name,age) VALUES(%s,%s);", [('次牛444', '12'), ("次牛2", '11'), ('次牛3', '10')])
    # 提交执行
    conn.commit()
except Exception as e:
    # 如果执行sql语句出现问题，则执行回滚操作
    conn.rollback()
    print(e)
finally:
    # 不论try中的代码是否抛出异常，这里都会执行
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

实例：数据库查询操作

#!/usr/bin/env python
#coding=utf-8

import pymysql

#创建数据库连接，注意这里我加入了charset和cursorclass参数
conn = pymysql.connect(
    host = "127.0.0.1",
    user = "root",
    password = "",
    database = "hetingdemo",
    charset = 'utf8',
    cursorclass = pymysql.cursors.DictCursor)
#获取游标
cursor = conn.cursor()
cursor.execute("SELECT * FROM para5 ;")
#fetchall：获取所有的数据，默认以元祖的方式返回，如果你指定了cursorclass = pymysql.cursors.DictCursor，则以dict的方式返回
#result = cursor.fetchall()
#for row in result:
#    print("id是%d,name是%s，age是%d"%(row["id"],row["name"],row["age"]))
#fetchone：获取剩余数据的第一条数据
result = cursor.fetchone()
print(result)
#fetchmany:获取剩余数据的前2条数据
#result = cursor.fetchmany(2)
#print(result)
cursor.close()
conn.close()

补充

《《补充1》》：pymysql中有几个方法的参数构造上很容易和字符串格式化相互混淆

self.cur.execute(query,params)
self.cur.executemany(query,params)

构造：
cursor.execute("INSERT INTO para5(name,age) VALUES(%s,%s);",('次牛','12'))
cursor.executemany("INSERT INTO para5(name,age) VALUES(%s,%s);", [('次牛444', '12'), ("次牛2", '11'), ('次牛3', '10')])

注意：这里没有字符串格式化里面的%d，%f，只有%s