python学习 —— python3简单使用pymysql包操作数据库

　　python3只支持pymysql(cpython >= 2.6 or >= 3.3,mysql >= 4.1)，python2支持mysqldb。

　　两个例子：

import pymysql

db = pymysql.connect('localhost', 'root', '123456', 'crawlsql')

cursor = db.cursor()
try:
    create_table_sql = "CREATE TABLE IF NOT EXISTS `table` (" 
                       "`id` int(11) NOT NULL AUTO_INCREMENT," 
                       "`url` varchar(255) NOT NULL," 
                       "`path` varchar(255) NOT NULL," 
                       "`size` float NOT NULL," 
                       "PRIMARY KEY(`id`)" 
                       ") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;"
    cursor.execute(create_table_sql)
    db.commit()

    show_table_sql = "SELECT `table` FROM information_schema.tables WHERE table_schema = `crawlsql`;"
    cursor.execute(show_table_sql)
    result = cursor.fetchone()
    print(result)
finally:
    db.close()

　　注意在创建名加上`而不是'（我经历的一个小坑）。

　　这个例子实现了连接mysql中的crawlsql数据库以及创建张名为'table'的表。查看crawlsql数据库下的table表语句(第17行)报错误：

pymysql.err.InternalError: (1054, "Unknown column 'table' in 'field list'")

　　官方例子（稍微做了一点修改以满足官方结果）：

import pymysql.cursors

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

try:
    with connection.cursor() as cursor:
        sql = "CREATE TABLE IF NOT EXISTS `table` (" 
              "`id` int(11) NOT NULL AUTO_INCREMENT," 
              "`email` varchar(50) NOT NULL," 
              "`password` varchar(30) NOT NULL," 
              "PRIMARY KEY(`id`)" 
              ") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1"
        cursor.execute(sql)

    connection.commit()

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

    connection.commit()

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

　　运行结果：

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

Process finished with exit code 0

　　再修改一点点（没有意义）：

import pymysql.cursors

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

try:
    with connection.cursor() as cursor:
        sql = "CREATE TABLE IF NOT EXISTS `table` (" 
              "`id` int(11) NOT NULL AUTO_INCREMENT," 
              "`email` varchar(50) NOT NULL," 
              "`password` varchar(30) NOT NULL," 
              "PRIMARY KEY(`id`)" 
              ") ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1"
        cursor.execute(sql)

    connection.commit()

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

    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT * FROM `table`"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

　　运行结果：

{'id': 1, 'email': 'webmaster@python.org', 'password': 'very-secret'}

Process finished with exit code 0