Python遇上MySQL

Python3.x通过PyMySQL模块进行MySQL数据库操作，首先安装模块：

pip install PyMySQL

PyMySQL模块有如下要求：

• Python解释器（满足下列条件之一）：
  
  • Cpython解释器 >= 2.6 或 >= 3.3
  • PyPy >= 4.0
  • IronPython = 2.7
• MySQL服务（满足下列条件之一）：
  
  • MySQL >= 4.1
  • MariaDB >= 5.1

首先通过如下SQL语句建表：

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 ;

通过PyMySQL做CRUD操作：

import pymysql

# Connect to the database 连接数据库
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='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. 连接不会自动更改数据，需要手动commit
    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}

Connection对象

Connection对象表述一个对mysql服务的socket连接。通过调用connect()方法来得到一个实例，通过该实例对MySQL数据库进行连接。

class 
pymysql.connections.Connection(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=<class 'pymysql.cursors.Cursor'>, init_command=None, connect_timeout=10, ssl=None, read_default_group=None, compress=None, named_pipe=None, no_delay=None, autocommit=False, db=None, passwd=None, local_infile=False, max_allowed_packet=16777216, defer_connect=False, auth_plugin_map={}, read_timeout=None, write_timeout=None bind_address=None, binary_prefix=False)

重要参数解读：

• host - mysql服务器主机地址
• user - 登录mysql账号的用户名
• password - 登录mysql账号的密码
• database - 所连接的数据库
• port - 连接的端口，默认为3306，一般使用默认即可
• unix_socket - 可以选择使用unix socket来进行连接，而非TCP/IP
• charset - 编码设置
• cursorclass - 选择使用的游标类
• connect_timeout - 连接的超时时间
• autocommit - 设置是否为自动commit

属性与方法：

• autocommit_mode=None

  autocommit模式，None表示使用MySQL服务器默认方式

• begin()

  开始执行事务

• close()

  发送退出信息并关闭socket连接

  • 异常

  当连接以及关闭，再次关闭会报错

• commit()

  提交更新到数据库里

• cursor(cursor=None)

  创建一个新游标，去执行数据库操作

  • 参数：

  cursor - 创建的游标类型，类型有SSCursor,Cursor,DictCursor,SSDictCursor。None表示使用Cursor

• open

  当连接已经打开时返回True

• ping(reconnect=True)

  检查连接是否活跃

  • 参数：

  reconnect - 如果连接已关闭，则重连

  • 异常：

  当连接已断开，而reconnect=False时，抛出异常

• rollback()

  回滚当前事务

• select_db(db)

  设置当前数据库

  • 参数

  db - 数据库名称

• show_warnings()

  发送SHOW WARNINGS SQL命令

Cursor对象

class pymysql.cursors.Cursor(connection)

用来与数据库交互的对象。不要实例化Cursor对象来使用，通过connections.Connection.cursor()的方式使用。

• callproc(procname, args=())

  执行指定的存储过程。

  参数：

  • procname

  存储过程的名字，以字符形式传入

  • args

  存储过程中需要的参数

  返回初始参数。

• close()

  当取完数据或使用完成后，通过该方法关闭游标。

• execute(query, args=None)

  执行sql操作。

  参数：

  • query

  所要执行的sql语句

  • args（元组、列表或字典）

  sql操作所需加入的参数

  返回值为影响行数，int类型。

• executemany(query, args)

  一次执行多个操作。参数以及返回值与execute方法一样，功能相当于循环执行了execute方法。

• fetchall()

  取出执行结果的所有数据

• fetchmany(size=None)

  取多条数据

• fetchone()

  取下一条数据

• max_stmt_length=1024000

  executmanay方法的执行最大条数

• mogrify(query, args=None)

  返回通过调用execute方法发送给数据库的字符串。即最后发给MySQL服务器的命令语句

• setinputsizes(*args)以及setouputsizes(*args)

  这两种方法不做任何事情，为DB API必需

class pymysql.cursors.SSCursor(connection)

非缓冲的游标，主要用来处理大量数据的请求，或是网速较慢的请求。具体表现为，取数据时不会只会把需要的数据取过来，而非每行都取过来，避免缓存太大占用太大空间，并且加快数据返回的速度。

然而这种方式依然有限制。MySQL协议不支持返回数据总数，因此想知道数据共有多少条，只能通过所有数据都取完了才能统计。不仅如此，由于只有当前数据存在内存中，所以这种方法目前不支持回滚。

• close

  当取完所有数据后关闭游标

• fetchall()

  取查询结果的所有数据，直接缓存到内存中，因此当取很多数据时尽量不用该方法

• fetchall_unbuffered()

  查询所有数据并返回一个生成器，而非缓存到内存中，因此适用于查询很多数据的场景

• fetchmany(size=None)

  取多条数据

• fetchone()

  取下一条数据

• read_next()

  读取下一条数据

class pymysql.cursors.DictCursor(connection)

以字典的形式返回结果，其余与Cursor一样

class pymysql.cursors.SSDictCursor(connection)

一种非缓冲的游标，以字典的形式返回结果，其余与Cursor一样