PyMySQL简单调用

PyMySQL应用简单说明：

参考文档：https://pypi.org/project/PyMySQL/

• 安装：pip install PyMySQL
• 检测：pip list
  
• Demo -- MySQL数据库 "连接"、"插入"、"查询"：
  

# -*- coding:utf-8 -*-

import pymysql.cursors

# insert method
def insert_db_test(connection, sql, records):
    try:
        with connection.cursor() as cursor:
            # loop
            for record in records:
                # Create a new record
                cursor.execute(sql, record)
    except Exception as e:
        print('---- error:', e)
    finally:
        # connection is not autocommit by default. So you must commit to save your changes.
        connection.commit()
        print('---- Insert Record Over !!!')


# select method
def select_db_test(connection, sql, condition):
    result = None
    try:
        with connection.cursor() as cursor:
            cursor.execute(sql, condition)
            # result = cursor.fetchall()
            result = cursor.fetchone()
    except Exception as e:
        print('---- error:', e)
    finally:
        print('---- Select Record Over !!!')

    return result


if __name__ == '__main__':
    # Connect to the database
    db_connection = pymysql.connect(host='localhost',
                                 user='****',
                                 password='******',
                                 db='testdb',
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)

    # insert records
    print('---- Insert Record ...')
    # insert_sql = "INSERT INTO `student` (`id`, `name`, `sex`, `birth`, `department`, `address`) VALUES (%s, %s, %s, %s, %s, %s)"
    insert_sql = "INSERT INTO student (id, name, sex, birth, department, address) VALUES (%s, %s, %s, %s, %s, %s)"
    insert_record_list = [(1003, '刘五', '女', 1977, '数学系', '山东省青岛市'),
                (1004, '刘六', '男', 1976, '音乐系', '山东省济南市'),
                (1005, '刘七', '女', 1975, '美术系', '山东省烟台市'),
                (1006, '刘八', '男', 1974, '电子系', '山东省潍坊市')]
    insert_db_test(db_connection, insert_sql, insert_record_list)

    # select record
    print('---- Select Record ...')
    select_sql = "SELECT * FROM student WHERE id=%s"
    select_condition = (1004,)
    result = select_db_test(db_connection, select_sql, select_condition)
    print('---- Result:', result)

    # Close db connnection
    db_connection.close()