python笔记-操作mysql数据库
1、前言
1.1 安装pymysql
python3 与MySQL 进行交互编程需要安装 pymysql 库
①、使用命令安装pymysql
pip install pymysql
②、pycharm内部安装
③、导入模块
import pymysql
1.2 pymysql流程及模块说明
①pymysql操作流程
- 导入pymysql;
- 建立数据库连接,使用pymysql的connect()方法连接数据库,返回连接对象;
- 使用连接对象创建游标对象(用于操作sql);
- 准备写sql语句;
- 使用游标对象执行sql;
- 查询数据使用游标获取;
- 关闭游标(先)和数据库连接(后)。
②pymysql模块说明
¶ connection对象
表示:conn = connect(参数列表)
作用:用于创建与数据库的连接
创建对象:调用connect()方法
参数列表:
- host:连接的mysql主机,如本机是'localhost‘
- port:连接的mysql主机的端口,默认是3306
- database:数据库的名称
- user:连接的用户名
- password:连接的密码
- charset:通信采用的编码方式,推荐使用utf8
对象的方法:
- close():关闭连接
- commit():提交
- cursor():返回cursor对象,用于执行sql语句并获得结果
- execute(operation[,parameters]):执行语句,返回受影响的行数
- fetchone():执行查询语句,获取查询结果集的第一个行数据,返回一个元组
- fetchall():执行查询语句,获取结果集的所有行,一行构成一个元组,再将这些元素装入一个元组返回
¶ cursor对象
游标就是游动的标识,通俗的说,一条sql取出对应n条结果资源的接口/句柄,就是游标,沿着游标可以一次取出一行
- 用于执行sql语句,使用频度最高的语句为select、uodate、insert、delete
- 获取cursor对象:调用connection对象的cursor方法:cursor = conn.cursor()
对象的属性:
- rowcount:只读属性,表示最近一次execute()执行后受影响的行数
- connection获得当前连接对象
¶ 语法总结
a. 连接数据库,需要host、port、user、password、database、charset等信息
b. 操作数据库要先创建游标
c. 执行指定的sql语句,如果涉及到增、删、改数据库必须要conn.commit(),提交事务
d. 查询获取数据条数有三种方法fetchone、fetchall、fetchmany
- cursor.fetchone():默认获取查询结果的第一条数据
- cursor.fetchamany(2):获取查询结果的指定条数,比如获取2条数据
- cursr.fetchall():获取查询结果的所有数据
e.需要注意的是,fetch获取的数据默认是元组,如果想要字典类型:cursor = pymysql.cursors.DictCursor;
f. 先关闭游标,后关闭数据库连接
2、连接数据库
import pymysql # 打开数据库连接 conn = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8')
print(conn)
输出:
3、获取游标
要想操作数据库,光连接数据是不够的,必须拿到操作数据库的游标,才能进行后续的操作,比如读取数据、添加数据。通过获取到的数据库连接实例conn下的cursor()方法来创建游标,游标用来接收返回结果。
import pymysql # 打开数据库连接 conn = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8')
# 获取游标 cursor = conn.cursor()
说明:cursor返回的是一个游标实例对象,其中包含了很多操作数据库的方法,比如执行sql语句
python查询数据库返回类型默认是元组,使用cursorclass属性可以以字典形式返回操作结果
conn = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
4、执行sql语句
pymysql提供了两个执行语句的方法:一个是execute(),一个是executemany()
execute(sql):
可接受一条语句从而执行
executemany(templet,args):
能通知执行多条语句,执行同样多的语句可比execute()快很多,建议执行多条数据时使用executemany
templet:sql模板字符串。例如”insert into table(id,name) values(%s,%s)'
args:模板字符串的参数,是一个列表,列表中的每一个元素必须是元组,例如:[(1,'小明'),(2,'zeke'),(3,'琪琪'),(4,'香香')]
cursor.execute('insert into teacher(name,age,gender) values(%s,%s,%s)',('铁柱',25,'women')) #插入单条多个数据 cursor.executemany('insert into teacher(name,age,gender) values(%s,%s,%s)',[('铁柱',25,'women'),('钢蛋',21,'women')] #插入多条多个数据
5、创建表
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # 如果数据库已经存在使用execute()方法删除表 cursor.execute("DROP TABLE IF EXISTS EMPLOYEE") # 创建数据库表SQL语句 sql = """CREATE TABLE EMPLOYEE( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), SALARY FLOAT)""" cursor.execute(sql) cursor.close() # 关闭游标 db.close() # 关闭数据库连接
6、数据库插入操作
插入单条记录,示例如下:
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL插入语句 sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,SALARY) VALUES('Mac','Mohan', 20, 'M', 2500) """ try: cursor.execute(sql) # 执行sql语句 db.commit() # 提交到数据库执行 except: db.rollback() # 遇到错误时回滚 cursor.close() # 关闭游标 db.close() # 关闭数据库连接
插入多条记录,示例如下:
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL插入语句 sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,SALARY) VALUES(%s, %s, %s, %s, %s) """ try: cursor.executemany(sql, [('Mac','Mohan', 20, 'M', 2500),('hanhan','li', 24, 'F', 3500)]) # 执行sql语句 db.commit() # 提交到数据库执行 except: db.rollback() # 遇到错误时回滚 cursor.close() # 关闭游标 db.close() # 关闭数据库连接
7、查询操作
python查询mysql使用fetchone()方法获取单条数据,使用fetchall()方法获取多条数据
- fetchone():获取单条数据
- fetchall():获取多条数据
- fetchmany():获取指定条数,例如fetchmany(2)
- rowcount:只读属性,返回执行execute()方法后影响的行数
示例:查询EMPLOYEE表中salary(工资)字段大于1000的所有数据:
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL查询语句 sql = "SELECT * FROM EMPLOYEE WHERE SALARY > 1000" try: cursor.execute(sql) # 执行SQL语句 result = cursor.fetchall() # 获取所有记录 except: print("Error:unable to fetch data") cursor.close() # 关闭游标 db.close() # 关闭数据库连接
8、fetchone与fetchall的区别
fetchone:
不管查询结果是多条数据还是单条数据,使用fetchone得到的始终是一个元组。
如果查询结果是单条数据:fetchone得到的单条数据的元组;
如果查询结果是多条数据:fetchone默认是结果中的第一条数据构成的元组。
fetchall:
不管查询结果是多条数据还是单条数据,使用fetchall得到的始终是一个由元组组成的列表(不懂为什么往上教程都说是元组的列表,实际操作得到的是一条数据构成一个元组,然后这些元组再装入一个元组)。
如果查询结果是单条数据:fetchall得到的是由单个元组组成的列表,列表内是有单条数据组成的元组;
如果查询结果是多条数据:fetchall得到的是由多个元组组成的列表。
使用场景:
一般来说,查询结果集是单条数据的,使用fetchone获取数据
一般来说,查询结果集是多条数据的,使用fetchall获取数据
注意:
对于使用fetchone和fetchall获取到的结果,最好使用之前先判断非空,否则在存在空值的情况下获取元组内的数据时,会报“超出索引”的异常。
9、更新操作
示例:更新单条数据
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL更新语句 sql = "UPDATE EMPLOYEE SET SALARY = 2000 WHERE NAME = 'JACK'" try: cursor.execute(sql) # 执行SQL语句 db.commit() # 提交到数据库执行 except: db.rollback() # 发生错误时回滚 cursor.close() # 关闭游标 db.close() # 关闭数据库连接
示例:更新多条数据
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL更新语句 sql = "UPDATE EMPLOYEE SET SALARY = '%s' WHERE NAME = '%s'" try: cursor.executemany(sql, [(3000,'Amy'),(1500,'Jone')]) # 执行SQL语句 db.commit() # 提交到数据库执行 except: db.rollback() # 发生错误时回滚 cursor.close() # 关闭游标 db.close() # 关闭数据库连接
10、删除操作
示例:删除一条数据
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL删除语句 sql = "DELETE FROM EMPLOYEE WHERE NAME = 'Jack'" try: cursor.execute(sql) # 执行SQL语句 db.commit() # 提交到数据库执行 except: db.rollback() # 发生错误时回滚 cursor.close() # 关闭游标 db.close() # 关闭数据库连接
示例:删除多条数据
import pymysql # 打开数据库连接 db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # 使用cursor()方法获取操作游标 cursor = db.cursor() # SQL删除 sql = "DELETE FROM EMPLOYEE WHERE NAME = '%s'" try: cursor.executemany(sql, [('Jack'),('Tom')]) # 执行SQL语句 db.commit() # 提交到数据库执行 except: db.rollback() # 发生错误时回滚 cursor.close() # 关闭游标 db.close() # 关闭数据库连接
11、封装数据库类
在实际项目中,很多地方都有用到数据库的操作,所以需要将数据库相关操作进行封装,方便其他模块调用。
如下,在public目录下新建文件db.py,用于封装数据库操作
简单示例代码
class Database: def __init__(self, host, port, user, password, database, charset): # 连接数据库服务器 self.conn = pymysql.connect(host=host, port=port, user=user, password=password, database=database, cursorclass=pymysql.cursors.DictCursor, charset=charset) # 获取游标 self.cursor = self.conn.cursor() def query(self, sql, args=None, one=True): self.cursor.execute(sql, args) # 提交事务 self.conn.commit() if one: return self.cursor.fetchone() else: return self.cursor.fetchall() def close(self): self.cursor.close() self.conn.close() if __name__ == "__main__": db = Database(host='', port=, user='', password='', database='', charset='utf8') sql = '' result = db.query(sql)
12、使用pymysql的executemany方法时,需要注意的几个问题
1、在写sql语句时,不管字段为什么类型,占位符统一使用%s
,且不能加上引号。例如:
sql = "insert into table (id, name) values(%s, %s)"
2、添加的数据格式必须为list[tuple(), tuple(), tuple()]或者tuple(tuple(), tuple(), tuple()),例如:
values = [(1, "zhangsan"), (2, 'lisi")] 或者 values = ((1, "zhangsan"), (2, 'lisi"))