pymysql使用

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.

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() # 重置表(删除，创建)


    

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
      
郭襄
    
  

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
      
郭靖