Python:访问数据库MySQL

2019年1月20日寒假大数据师资培训班

【版权声明】博客内容由厦门大学数据库实验室拥有版权,未经允许,请勿转载!版权所有,侵权必究!
[返回Python教程首页]

访问数据库MySQL

MySQL数据库是最常用的轻量级数据库,本节介绍Python编程访问数据库MySQL。
此前,请确保已经安装了MySQL。可参考博文Ubuntu安装MySQL及常用操作安装MySQL。
Python访问MySQL有多种方式,这里主要介绍两种最常用的访问方式:
1. 利用模块PyMySQL
2. 利用支持Python的MySQL驱动包


前期准备

无论是哪种方式,首先都需要安装python模块管理工具——pip,因为我们使用的Python3,所以pip需要使用与之配套的pip3,直接使用pip的话,会默认将模块安装到python2的目录下。
1. 安装pip3

sudo apt-get install python3-pip

2. 使用pip3
利用pip3可以安装实现了各种功能的python模块,命令为:

pip3 install modulename

3. MySQL准备
接下来我们通过一个编程实例来讲解如何使用通过Python操作MySQL数据库。
如果未设置MySQL自动启动,先利用下列命令启动MySQL服务。

service mysql start

再输入命令mysql -u root -p,并输入MySQL的登陆密码后,这里的root是笔者MySQL的用户名,进入MySQL命令行界面。为了防止和其他数据混合在一起,我们可以创建数据库pythondb。

mysql> create database pythondb;  //创建数据库pythondb
Query OK, 1 row affected (0.01 sec)
mysql> show databases;   //显示所有数据库
+--------------------+
| Database           |
+--------------------+
| pythondb           |
| sparktest          |
| sys                |
+--------------------+
3 rows in set (0.01 sec)
mysql> quit  //退出MySQL
Bye

利用模块PyMySQL访问MySQL

这里我们需要利用pip3安装PyMySQL模块来连接MySQL,命令为:

pip3 install PyMySQL

安装成功截图:

接下来进入Python命令行界面,开始利用Python编程访问MySQL数据库。连接MySQL数据库之前请确保以下事项:
1. MySQL服务已开启
2. 已创建好pythondb数据库
3. 电脑上已经安装PyMySQL模块
将下列代码写入python_mysql.py文件,保存后,利用命令python3 python_mysql.py来执行代码。请注意,不要将python文件命名与已有模块名重复,否则在import引入时会优先引入用户创建的模块。因此这里不要将python文件命名为pymysql.py,否则会和我们下载的pymysql模块冲突。

#引入pymysql模块。注意小写
import pymysql
#打开数据库连接。localhost为主机地址,root是mysql登陆名,123是登陆密码,pythondb是数据库名
conn = pymysql.connect("localhost","root","123","pythondb")
#使用cursor()方法获取操作游标,利用游标来进行相关的数据操作
cursor = conn.cursor()

#预定义sql语句。创建student数据库表
create_sql = "create table student(id varchar(8),name varchar(10),age tinyint,sex varchar(2))"
#执行sql语句
cursor.execute(create_sql)

try:
    #插入语句。%s为占位符
    cursor.execute('insert into student values (%s, %s, %s, %s)', ['201701','Tom',20,'M'])
    cursor.execute('insert into student values (%s, %s, %s, %s)', ['201702','Mary',22,'W'])
    #插入语句。有参sql的另一个传递方式。
    #三引号之间输入的内容将被原样保留,可避免繁杂的转义
    insert_sql = """insert into student values ('201703','Jack',19,'M')"""
    cursor.execute(insert_sql)
    #提交到数据库执行,不加commit,则无法提交到数据库
    #只有对数据库进行了增删改时需要提交数据库,查询不需要
    conn.commit()
except:
    #try语句块出错时则回滚
    conn.rollback()
#关闭数据连接
conn.close()

执行完成后,接下来检验student表是否创建成功,数据是否插入成功。另外启动一个终端,输入mysql -u root -p进入MySQL命令行界面(root是笔者的MySQL登陆名)。

mysql> use pythondb;   #切换数据库pythondb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;  #显示当前数据库所有表。创建student表成功
+--------------------+
| Tables_in_pythondb |
+--------------------+
| student            |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from student;   #查询数据。插入数据成功
+--------+------+------+------+
| id     | name | age  | sex  |
+--------+------+------+------+
| 201701 | Tom  |   20 | M    |
| 201702 | Mary |   22 | W    |
| 201703 | Jack |   19 | M    |
+--------+------+------+------+
3 rows in set (0.01 sec)

输出的结果显示利用PyMySQL模块访问MySQL实验成功!

利用支持Python的MySQL驱动包

实际上,上述PyMySQL模块也是对mysql连接做了一层封装,本质上也是使用的MySQL驱动包。接下来介绍更基础的方法。
首先,安装支持Python的MySQL驱动包。

pip3 install mysql-connector-python --allow-external mysql-connector-python

接下来,编程实现读取sutdent表中的数据,以及删除指定数据。

#导入MySQL驱动
import mysql.connector
#建立连接。提供mysql登陆名,登陆密码,连接的数据库名
conn = mysql.connector.connect(user='root',password='123',database='pythondb')
#获取游标
cursor = conn.cursor()
#查询语句
select_sql = """select * from student where id = '201702'"""
#执行语句
cursor.execute(select_sql)
#读取游标所指的所有数据
#常用的还有cursor.fetchone():可返回一条数据
data = cursor.fetchall()
#打印数据
print(data)
try:
    #删除语句
    delete_sql = """delete from student where id = '201702'"""
    cursor.execute(delete_sql)
    #操作提交
    conn.commit()
except:
    #出错时回滚
    conn.rollback()
#关闭连接
conn.close()

执行完成后,屏幕上会输出id = '201702'的学生信息,如下:

[('201702', 'Mary', 22, 'W')]

查看另一个终端的MySQL命令行界面,可看出id = '201702'的学生信息已被成功删除。如下:

mysql> select * from student;
+--------+------+------+------+
| id     | name | age  | sex  |
+--------+------+------+------+
| 201701 | Tom  |   20 | M    |
| 201703 | Jack |   19 | M    |
+--------+------+------+------+
2 rows in set (0.00 sec)

输出的结果显示利用支持Python的MySQL驱动访问MySQL实验成功!

总结

本文通过两种方式访问MySQL数据库,一是通过PyMySQL模块,二是通过支持Python的MySQL驱动。两种方式都是很常用的,大家可二者择一。