3.4 Python操作Mysql

在Python中可以通过PyMySQL库来操作MySQL，需要注意的是该库一般直接调用标准SQL来执行数据库的操作，并非使用封装的新的函数来执行。

先来看看PyMySQL官网是如何操作MYSQL的。

The following examples make use of a simple table

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',
                             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, ('[e-mail]', '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, ('[e-mail]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

This example will print:

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

可以看到使用PyMySql操作SQL就是先使用connect() 方法声明一个 MySQL 连接对象 ，后续定义一个字符串变量sql存储标准sql语句，要操作SQL时调用execute()方法来执行标准sql语句即可。

如下面的例子，首先连接在本地数据库test,然后在该数据库下创建一个新表article_test，并添加一条记录，后查询该记录。

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             port = 3306,
                             db='test',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    # 新建表
    with connection.cursor() as cursor:
        # Create a new table: article_test
        sql = "CREATE TABLE IF NOT EXISTS article_test(article_id INT UNSIGNED AUTO_INCREMENT,article_title VARCHAR(100) NOT NULL,article_author VARCHAR(40) NOT NULL,article_date DATE,PRIMARY KEY (article_id))ENGINE=InnoDB DEFAULT CHARSET=utf8;"
        cursor.execute(sql)
    # connection is not autocommit by default. So you must commit to save your changes.
    connection.commit()

    # 向表中插入数据
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO article_test (article_id, article_title, article_author) VALUES (1, 'first article', 'marco');"
        cursor.execute(sql)
    # 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 `*` FROM `article_test`"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()