pymysql


pymysql使用

  • 安装
  • 准备
  • 基本使用(表创建、删除、增删改查)
  • 高级使用(commit\rollback)
  • 扩展

安装

为了将数据库以表格显示,这里会使用pandas


In [2]:
!pip install PyMySQL  pandas


Requirement already satisfied (use --upgrade to upgrade): PyMySQL in /usr/local/lib/python3.6/site-packages
Requirement already satisfied (use --upgrade to upgrade): pandas in /usr/local/lib/python3.6/site-packages
Requirement already satisfied (use --upgrade to upgrade): python-dateutil>=2 in /usr/local/lib/python3.6/site-packages (from pandas)
Requirement already satisfied (use --upgrade to upgrade): numpy>=1.7.0 in /usr/local/lib/python3.6/site-packages (from pandas)
Requirement already satisfied (use --upgrade to upgrade): pytz>=2011k in /usr/local/lib/python3.6/site-packages (from pandas)
Requirement already satisfied (use --upgrade to upgrade): six>=1.5 in /usr/local/lib/python3.6/site-packages (from python-dateutil>=2->pandas)
You are using pip version 8.1.2, however version 9.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.

准备

加载util.py模块


In [1]:
# %load util.py
from IPython.display import display
import pandas as pd
import pymysql.cursors
from collections import defaultdict


def print_table():
    db_result = []
    try:
        connection = get_connection()
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT * FROM `auth_user` "
            cursor.execute(sql)
            db_result = cursor.fetchall()
    finally:
        connection.close()
    
    if not db_result:
        return
    if isinstance(db_result, dict):
        db_result = [db_result]
    data = defaultdict(list)
    for item in db_result:
        for key in item:
            data[key].append(item[key])
    df = pd.DataFrame(data)
    display(df)

def get_connection():
    connection = pymysql.connect(host='192.168.0.88',
                           user='root',
                           password='letmegoletmego',
                           db='db',
                           port=32768,
                           charset='utf8mb4',
                           cursorclass=pymysql.cursors.DictCursor)
    return connection


def reset_table():
    connection = get_connection()
    table_sql = '''
DROP TABLE IF EXISTS `auth_user`;
CREATE TABLE `auth_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `password` varchar(128) NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
'''

    try:
        with connection.cursor() as cursor:
            cursor.execute(table_sql)
    finally:
        connection.close()

In [8]:
reset_table() # 重置表(删除,创建)

基本操作

数据插入

批量插入的话可以使用改善性能. This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute(). 下面使用executemany代替三个单独插入语句.


In [9]:
try:
    connection = get_connection()
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `auth_user` (`username`, `password`,`is_superuser`,`last_login`) VALUES (%s, %s,%s,%s)"
#         cursor.execute(sql, ('郭靖', '66666',1,'2017-6-21'))
#         cursor.execute(sql, ('黄蓉', '77777',0,'2017-6-22'))
#         cursor.execute(sql, ('郭襄', '88888',1,'2017-6-25'))
        cursor.executemany(sql, [('郭靖', '66666',1,'2017-6-21'),('黄蓉', '77777',0,'2017-6-22'),('郭襄', '88888',1,'2017-6-25')])
    connection.commit()
finally:
    connection.close()
    print_table()


id is_superuser last_login password username
0 1 1 2017-06-21 66666 郭靖
1 2 0 2017-06-22 77777 黄蓉
2 3 1 2017-06-25 88888 郭襄

修改


In [32]:
try:
    connection = get_connection()
    with connection.cursor() as cursor:
        # Create a new record
        sql = "UPDATE `auth_user` SET password = %s WHERE username = %s"
        cursor.execute(sql, ( '66666_XXX','郭靖'))
        cursor.execute(sql, ('77777_XXX','黄蓉'))
        cursor.execute(sql, ( '88888_XXX','郭襄'))
    connection.commit()
finally:
    connection.close()
    print_table()


id is_superuser last_login password username
0 1 1 2017-06-21 66666_XXX 郭靖
1 2 0 2017-06-22 77777_XXX 黄蓉
2 3 1 2017-06-25 88888_XXX 郭襄

删除


In [33]:
try:
    connection = get_connection()
    with connection.cursor() as cursor:
        # Create a new record
        sql = "DELETE from  `auth_user`  WHERE username = %s"
        cursor.execute(sql, ( '郭靖'))
    connection.commit()
finally:
    connection.close()
    print_table()


id is_superuser last_login password username
0 2 0 2017-06-22 77777_XXX 黄蓉
1 3 1 2017-06-25 88888_XXX 郭襄

高级使用

主要看一下commitrollback的使用.


In [44]:
try:
    reset_table()
    connection = get_connection()
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `auth_user` (`username`, `password`,`is_superuser`,`last_login`) VALUES (%s, %s,%s,%s)"
        cursor.execute(sql, ('郭靖', '66666',1,'2017-6-21'))
        cursor.execute(sql, ('黄蓉', '77777','xxx','2017-6-22'))
        cursor.execute(sql, ('郭襄', '88888',1,'2017-6-25'))
    connection.commit()
except Exception as e:
    print(e)
    #connection.rollback()
finally:
    connection.commit()
    connection.close()
    print_table()


(1366, "Incorrect integer value: 'xxx' for column 'is_superuser' at row 1")
id is_superuser last_login password username
0 1 1 2017-06-21 66666 郭靖

首先说明一下, 当执行 commitROLLBACK后,放在回滚段中的数据就会被删除.

  • 上面第一条插入语句正确,进入回滚段里,第二条插入语句错误,抛出异常,最后调用commit将数据提交.
  • 如果在异常时执行connection.rollback(),则最后结果为空.

扩展

封装pymysql,使操作更简单.代办..