!pip install PyMySQL  pandas

"""
Explanation: pymysql使用

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

安装

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


# %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()

    reset_table() # 重置表(删除，创建)

"""
Explanation: 准备
加载util.py模块
End of explanation
"""


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()


"""
Explanation: 基本操作
数据插入

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


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()

"""
Explanation: 修改
End of explanation
"""


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()

"""
Explanation: 删除
End of explanation
"""


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()

"""
Explanation: 高级使用

主要看一下commit和rollback的使用.
End of explanation
"""


