知识库 : pymysql示例

1.简介

2.依赖环境

3.安装

4.示例

1.简介

pymysql是一个python模块，是一个纯python的mysql客户端库，pymysql的目标是替代安装繁琐的MySQLdb，并使之能在CPython、PyPy、IronPython上使用。

2.依赖环境

pymysql是纯python的mysql客户端库，只要求用户有python解释器和mysql服务，依赖环境如下：

Python -- one of the following:

• CPython >= 2.6 or >= 3.3
• PyPy >= 2.3
• IronPython 2.7

MySQL Server -- one of the following:

• MySQL >= 4.1
• MariaDB >= 5.1

3.安装

pymysql是纯python编写的，所以安装特别简单，只需要：

$ pip install PyMySQL

也可以从源码安装（推荐只在pip无法安装时采用这种方式）：

$ # X.X is the desired PyMySQL version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

4.示例

首先在mysql中建表：

CREATE TABLE `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email` varchar(255) COLLATE utf8_bin NOT NULL,
    `password` varchar(255) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

下面是插入和查询示例：

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             passwd='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

上面示例的输出如下：

{'password': 'very-secret', 'id': 1}