Python实现MySql数据库交互的示例

一、使用MySql进行持久化存储

在任何应用中，都需要持久化存储。一般有 3 种基础的存储机制：文件、数据库系统以及一些混合类型。这种混合类型包括现有系统上的 API、 ORM、文件管理器、电子表格、配置文件等。

对于非常简单的应用而言，使用文件作为持久化存储通常就足够了，但是当文件或创建的数据存储系统不适用于大项目时，我们需要转而使用数据库，大多数复杂的数据驱动的应用是需要使用全功能的关系数据库的。

一个关系数据库管理系统（ RDBMS）通常可以管理多个数据库，比如销售、市场、用户支持等，都可以在同一个服务端（如果 RDBMS 基于服务器，可以这样。不过一些简单的系统通常不是基于服务器的）。在本章将要看到的例子中， MySQL 是一种基于服务的RDBMS，因为它有一个服务器进程始终运行以等待命令行输入；而 SQLite 则不会运行服务器。

二、安装MySql数据库和Python库PyMySQL

在开始之前，请确保：

• 已经安装了MySql数据库并已经开启了相关服务。
• 正确安装名为PyMySQL的Python库。

MySql数据库的安装请参考另一片文章。此处不再赘述。

在python3中，如果想要将数据存储到mysql中，就需要借助PyMySQL来操作，安装的话，还是推荐使用pip来进行，简单快捷。

相关链接：

GitHub : https://github.com/PyMySQL/

PyMySQL官方文梢 ： http://pymysql.readthedocs.io/

PyPl: https://pypi.python.org/pypi/PyMySQL

pip install pymysql

验证一下安装是否成功：

如果能够像上图输出版本信息，则表示PyMySQL已经成功安装。

三、使用pymysql链接mysql数据库

假设当前MySql服务器运行在本地，用户名为root，密码为123456，运行端口为3306。

尝试连接到数据库并创建一个新的数据库，名字叫testdb

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='123456', port=3306)
cur = conn.cursor()
cur.execute('select version()')
data = cur.fetchone()
print(f'database version: {data}')

cur.execute('CREATE DATABASE testdb DEFAULT CHARACTER SET utf8')
conn.close()

能够正常输出版本，说明python已经能够成功连接到数据库。

在数据库中，也可以看到我们刚刚建立的新的数据库。

四、创建表

一般来说，创建数据库的操作只需要执行一次就好了。当然，我们也可以手动创建数据库 。

创建数据库后，在连接时需要额外指定一个参数 db 。

加下来，我们创建一个新的数据表students, 指定3个字段，如下所示：

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='testdb')
cur = conn.cursor()
sql = '''
CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))
'''
cur.execute(sql)
conn.close()

可以看到新的表已经创建成功了。

五、插入数据

这里仅演示一下数据的插入，其他的操作，比如删除数据、更新数据等等，请参考官方文档示例。

import pymysql

conn = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='testdb')
cur = conn.cursor()

sdudent = [
    ('001', '张三', 12),
    ('002', '李四', 13),
    ('003', '王五', 14)]

try:
    cur.executemany("INSERT INTO students(id, name, age) VALUES (%s, %s, %s)", sdudent)
    conn.commit()
except:
    conn.rollback()

conn.close()

查表，可以看到数据已经插入成功。

六、后记

注意，pymysql模块中不能使用占位符 ？ ， 这点与SQLite不一样。必须使用paramstyle = "pyformat"的风格。

比如

# 这么写会报错
cur.executemany("INSERT INTO students(id, name, age) VALUES (?, ?, ?)", sdudent)

发生异常: TypeError
not all arguments converted during string formatting
File “G:\Code\practice\testpython\mysql.py”, line 10, in
cur.executemany(“INSERT INTO students(id, name, age) VALUES (?, ?, ?)”, sdudent)

# 必须使用paramstyle = "pyformat"的风格
cur.executemany("INSERT INTO students(id, name, age) VALUES (%s, %s, %s)", sdudent)

详情在另一篇文章(《Python数据库编程之关系数据库API规范 》)里有描述，大致原因如下：

在整个数据库模块实现中没有关于占位符的标准规则。但是，每一个模块都定义了一个变量paramstyle, 它描述了将在查询中使用的值替换格式。这一变量可能的值如下：