如何使用Python玩转MySQL数据库

一、背景

我是在Anaconda notebook中进行连接实验的，环境Python3.6，当然也可以在Python Shell里面进行操作。

最常用也最稳定的用于连接MySQL数据库的python库是PyMySQL。

二、基本操作 1、安装PyMySQL库

最简单的方式：

如何使用Python轻松玩转MySQL数据库

在命令行输入 pip install pymysql

或者：

下载whl文件[1]进行安装，安装过程自行百度。

2、安装MySQL数据库

MySQL数据库有两种：

MySQL和MariaDB

我用的是MariaDB，它是MySQL的一个分支。

两者在绝大部分性能上是兼容的，使用起来感觉不到啥区别。

给出下载地址：MySQL[2]，MariaDB[3]，安装过程很简单，一路Next Step，不过要记好密码。

有个小插曲，MySQL和MariaDB相当于姐姐妹妹的关系，两者由同一个人（Widenius）创建的。

MySQL被Oracle收购后，Widenius先生觉得不爽，于是搞了个MariaDB，可以完全替代MySQL。

大牛就是任性。

3、SQL基本语法

下面要用SQL的表创建、查询、数据插入等功能，这里简要介绍一下SQL语言的基本语句。

  • 查看数据库：SHOW DATABASES；

  • 创建数据库：CREATE DATEBASE 数据库名称；

  • 使用数据库：USE 数据库名称；

  • 查看数据表：SHOW TABLES；

  • 创建数据表：CREATE TABLE 表名称（列名1 （数据类型1），列名2 （数据类型2））；

  • 插入数据：INSERT INTO 表名称（列名1，列名2） VALUES（数据1，数据2）；

  • 查看数据：SELECT * FROM 表名称；

  • 更新数据：UPDATE 表名称 SET 列名1=新数据1，列名2=新数据2 WHERE 某列=某数据；

4、连接数据库

安装好必要得文件和库后，接下来正式开始连接数据库吧，虽然神秘却不难哦！

#首先导入PyMySQL库
import pymysql
#连接数据库，创建连接对象connection
#连接对象作用是：连接数据库、发送数据库信息、处理回滚操作（查询中断时，数据库回到最初状态）、创建新的光标对象
connection = pymysql.connect(host = '
localhost'
#host属性
user = '
root'
#用户名
password = '
******'
#此处填登录数据库的密码
db = '
mysql'
#数据库名
)

执行这段代码就连接好了！

5、增删改查操作

首先来查看一下有哪些数据库：

#创建光标对象，一个连接可以有很多光标，一个光标跟踪一种数据状态。
#光标对象作用是：、创建、删除、写入、查询等等
cur = connection.cursor()
#查看有哪些数据库，通过cur.fetchall()获取查询所有结果
print(cur.fetchall())

打印出所有数据库：

(('
information_schema'
,),
('
law'
,),
('
mysql'
,),
('
performance_schema'
,),
('
test'
,))

在test数据库里创建表:

#使用数据库test
cur.execute('
USE test'
)
#在test数据库里创建表student，有name列和age列
cur.execute('
CREATE TABLE student(name VARCHAR(20),age TINYINT(3))'
)

向数据表student中插入一条数据：

sql = '
INSERT INTO student (name,age) VALUES (%s,%s)'

cur.execute(sql,('
XiaoMing'
,23))

查看数据表student内容：

cur.execute('
SELECT * FROM student'
)
print(cur.fetchone())

打印输出为：('
XiaoMing'
, 23)

Bingo！是我们刚刚插入的一条数据

最后，要记得关闭光标和连接：

#关闭连接对象，否则会导致连接泄漏，消耗数据库资源
connection.close()
#关闭光标
cur.close()

OK了，整个流程大致如此。

当然这里都是很基础的操作，更多的使用方法需要在PyMySQL官方文档[4]里去寻找。

三、导入大数据文件

以csv文件为例，csv文件导入数据库一般有两种方法：

1、通过SQL的insert方法一条一条导入，适合数据量小的CSV文件，这里不做赘述。

２、通过load data方法导入，速度快，适合大数据文件，也是本文的重点。

样本CSV文件如下：

总体工作分为3步：

1、用python连接mysql数据库；

2、基于CSV文件表格字段创建表；

3、使用load data方法导入CSV文件内容。

sql的load data语法简介：

LOAD DATA LOCAL INFILE '
csv_file_path'
INTO TABLE table_name FIELDS TERMINATED BY '
,'
LINES TERMINATED BY '
\\r\\n'
IGNORE 1 LINES

csv_file_path 指文件绝对路径 table_name指表名称 FIELDS TERMINATED BY '
,'
指以逗号分隔 LINES TERMINATED BY '
\\r\\n'
指换行 IGNORE 1 LINES指跳过第一行，因为第一行是表的字段名

下面给出全部代码：

#导入pymysql方法
import pymysql


#连接数据库
config = {:'
'
,
:3306,
:'
username'
,
:'
password'
,
:'
utf8mb4'
,
:1
}
conn = pymysql.connect(**config)
cur = conn.cursor()


#load_csv函数，参数分别为csv文件路径，表名称，数据库名称
def load_csv(csv_file_path,table_name,database='
evdata'
):
#打开csv文件
file = open(csv_file_path, '
r'
,encoding='
utf-8'
)
#读取csv文件第一行字段名，创建表
reader = file.readline()
b = reader.split('
,'
)
colum = '
'

for a in b:
colum = colum + a + '
varchar(255),'

colum = colum[:-1]
#编写sql，create_sql负责创建表，data_sql负责导入数据
create_sql = '
create table if not exists '
+ table_name + '
'
+ '
('
+ colum + '
)'
+ '
DEFAULT CHARSET=utf8'

data_sql = "
LOAD DATA LOCAL INFILE '
%s'
INTO TABLE %s FIELDS TERMINATED BY '
,'
LINES TERMINATED BY '
\\r\\n'
IGNORE 1 LINES"
% (csv_filename,table_name)

#使用数据库
cur.execute('
use %s'
% database)
#设置编码格式
cur.execute('
SET NAMES utf8;
'
)
cur.execute('
SET character_set_connection=utf8;
'
)
#执行create_sql，创建表
cur.execute(create_sql)
#执行data_sql，导入数据
cur.execute(data_sql)
conn.commit()
#关闭连接
conn.close()
cur.close()

Python是一种高效的编程语言，而MySQL是一种流行的关系型数据库系统。若想在使用Python的过程中利用MySQL作为后端数据存储，我们有哪些方法呢？在本文中，我们将详细介绍如何使用Python玩转MySQL数据库。
一、Python连接MySQL数据库的方法
Python连接MySQL数据库共有三种常用方法：MySQLdb、PyMySQL和mysqlclient。这三者的使用方法基本相同，其中MySQLdb作为标准化Python数据库接口，是大多数Python开发人员使用的选择；而PyMySQL则是一种纯Python实现的接口，可提供较好的兼容性和稳定性。mysqlclient是Python的MySQL驱动器之一，是原生C的Python连接MySQL库。
为了成功连接MySQL数据库，并进而实现数据存储与管理，每个方法的使用方式和注意事项都各有不同。因此，开发人员需要根据自身需求和技能水平，决定在Python程序中采用哪种连接MySQL的库进行操作。
二、Python操作MySQL数据库的基本步骤
在Python连接MySQL数据库之后，还需为MySQL数据库创建表格，插入、删除、更新数据等基本操作。Python开发人员可以采用SQLAlchemy等ORM框架，大大便利了以上过程。
同时，在Python程序中操作MySQL数据库需要注意以下几点：
1. 建立连接时要关注MySQL的字符集，避免因编码问题导致数据读写异常；
2. 及时关闭MySQL连接以释放资源，防止因长时间占用而出现连接异常；
3. 在进行数据更新操作时，建议使用事务进行操作，以保证数据的完整性。
三、Python如何优化MySQL数据库的查询性能
随着应用程序中数据量的增加，MySQL数据库查询性能变得越来越关键。在Python中，一旦查询量过大或复杂度过高，就会出现应用程序响应缓慢等问题。在此，推荐以下几种优化MySQL数据库查询性能的方法：
1. 合理创建优化MySQL索引；
2. 合理进行SQL语句重构，防止出现冗杂查询；
3. 尽量使用Python内置API，以减少代码执行量；
4. 增加MySQL的缓存，以提高查询效率。
诚然，以上方法并非万能的，对于深度优化MySQL数据库的需求，还需要Python开发人员在具体情况下，进行深入了解和研究。
总结
在通过Python开发应用程序时，使用MySQL作为后端数据库是一个较为流行且高效的选择。本文介绍了Python连接MySQL数据库的方法、Python操作MySQL数据库的基本步骤以及Python如何优化MySQL数据库的查询性能。希望对Python开发人员有所帮助，使得Python操作MySQL数据库看起来更加从容和可控。