MySQL 之 pymysql

MySQL 之 pymysql

一、概述

pymysql是python用来连接mysql的工具，安装方式：pip install pymysql -i https://pypi.douban.com/simple

这里使用豆瓣的镜像安装，可能会快点

二、使用

pymysql内置非常多的方式来操作mysqlServer

1. 连接

首先还是剖其源码查看

def __init__(self, host=None, user=None, password="",
			 database=None, port=0, unix_socket=None,
			 charset='', sql_mode=None,
			 read_default_file=None, conv=None, use_unicode=None,
			 client_flag=0, cursorclass=Cursor, init_command=None,
			 connect_timeout=10, ssl=None, read_default_group=None,
			 compress=None, named_pipe=None,
			 autocommit=False, db=None, passwd=None, local_infile=False,
			 max_allowed_packet=16*1024*1024, defer_connect=False,
			 auth_plugin_map=None, read_timeout=None, write_timeout=None,
			 bind_address=None, binary_prefix=False, program_name=None,
			 server_public_key=None):

通常在项目中，数据库的连接的信息一般写在config.py文件中，然后调用即可

pysql.connect(host="", user="", password="", database="", charset="utf8md4")

以上connect的参数不必赘述

2. 执行SQL

先建一张表，用来测试数据

create database run default character set utf8mb4 collate utf8mb4_general_ci;

create table info(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int not null
)charset=utf8mb4;

创建一个配置文件，用来存放数据库的连接信息

'''
数据库连接信息
'''
HOST = "xxx.xxx.xxx.xxx"
USER = "root"
PASSWORD = "Asd.1234"
DATABASE = "run"
PORT = 3306

使用OOP思想来写基本框架

import pymysql

from config import HOST,USER, PASSWORD, DATABASE, PORT

class ConnectToMysql(object):
    def __init__(self):
        self.db = pymysql.connect(host=HOST, user=USER, password=PASSWORD, database=DATABASE, port=PORT)
        self.cursor = self.db.cursor() # 执行完毕返回的结果集默认以元组显示

    def insert_to_db(self):
		'''
		数据的插入操作
		'''
        pass

    def query(self):
		'''
		数据的查询操作
		'''
        pass

    def upate(self):
		'''
		数据的更新操作
		'''
        pass

    def delete(self):
		'''
		数据的删除操作
		'''
        pass

    def main(self):
        pass


if __name__ == "__main__":
    mysql = ConnectToMysql()
    mysql.main()

一下的代码只展示需要填充的方法

以上代码中，cursor为游标，而在执行SQL语句使用cursor.execute(SQL)，当然，还有cusor.executemany(SQL)以执行多条SQL语句，其中的main函数需根据自身编写

2.1 插入数据

这里牵扯一个游标的问题，游标就是一个数据集合的指针，用来指向一条特定的数据行

通俗的讲，关系型数据库是面向集合的，在写sql 语句时，逻辑上也是面向集合的。而游标是面向行的，可以想象它是一个指针，逐行读取数据。
可以认为，通常的查询，就好像去提款机取款，例如，取1000，就是一次取1000；而游标是每次取100，取10次（假设100是最小单位）。
所以，通常游标是有“害” 的，会造成更多的I/O，消耗更多的资源，一般不建议使用游标。
存在即合理，有时候不得不使用游标，可能旧代码的写法，可能是业务复杂，而不得不使用游标；总之，它只是作为一种补充手段存在。

def insert_to_db(self):
	'''
	数据插入的操作:编写SQL、执行SQL、提交、关闭连接(当然，该实验中在最后关闭游标和连接)
	'''
	sql = "insert into info (name, age) values ('chancey', 18)"
	self.cursor.execute(sql)

	self.db.commit()
	self.cursor.close()
	self.db.close()

在插入数据之后必须提交才能将数据提交到数据库，现在查询一下数据库

mysql> select * from run.info;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | chancey |  18 |
+----+---------+-----+
1 row in set (0.00 sec)

执行多条SQL语句

def insert_to_db(self):
	sql = "insert into info (name, age) values (%s, %s)"
	data = [("waller", 21), ("mary", 25)]

	self.cursor.executemany(sql, data)

	self.db.commit()
	self.cursor.close()
	self.db.close()

查看数据库

mysql> select * from run.info;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | chancey |  18 |
|  2 | waller  |  21 |
|  3 | mary    |  25 |
+----+---------+-----+
3 rows in set (0.00 sec)

2.2 查询数据

同样的，执行SQL语句，但是在想要返回查询结果，则需要用fet函数来接收

• cursor.fetall() # 接收所有返回的查询结果
• cursor.fetone() # 接收第一条返回的查询结果
• cursor.fetmany() # 接收指定数量返回的查询结果，如果参数为空，则默认第一条

def query(self):
	sql = "select name,age from info"
	self.cursor.execute(sql)

	info1 = self.cursor.fetchone() # 返回第一条结果
	info2 = self.cursor.fetchmany(2) # 返回剩下结果的前两条
	info3 = self.cursor.fetchall() # 返回剩下的所有结果

	print(info1)
	print(info2)
	print(info3)
	
	self.cursor.close()
	self.db.close()

需要注意的是，第一次提取的数据在第二次提取的集合中是没有的，所以，提取结果的次数直接影响提取的结果
另外，该方法返回的是元组

2.3 更新数据

话不多说，直接代码

def upate(self):
	sql = "update info set age=%s where name=%s"

	self.cursor.execute(sql, [14, "waller"]) # 传多个参数需要用列表传参

	self.db.commit()
	self.cursor.close()
	self.db.close()

查询一下数据库

mysql> select * from run.info;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | chancey |  18 |
|  2 | waller  |  14 |
|  3 | mary    |  25 |
+----+---------+-----+
3 rows in set (0.00 sec)

2.4 删除数据

def delete(self):
	sql = "delete from info where name=%s"

	self.cursor.execute(sql, "waller")

	self.db.commit()
	self.cursor.close()
	self.db.close()

查询数据库

mysql> select * from run.info;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | chancey |  18 |
|  3 | mary    |  25 |
+----+---------+-----+
2 rows in set (0.00 sec)