1.創(chuàng)建表

import pymysql
def create_table():
    #建立數據庫鏈接
    #  寫法2： db = pymysql.connect('localhost','root','**','**')
    db = pymysql.connect(host='localhost',
                         user='**',
                         password='**',
                         db='**')
    # 創(chuàng)建 t_user 表
    sql = 'create table if not exists t_user(id int not null auto_increment,name text,age text,address text,primary key (id))'
    # 創(chuàng)建游標對象
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        print('表創(chuàng)建成功')
    except BaseException as e:
        print('表創(chuàng)建失敗',e)
        db.rollback()
    finally:
        cursor.close()
        db.close()


if __name__ == '__main__':
    create_table()

2.插入數據

import pymysql
def insert_table(value):
    # 建立數據庫鏈接
    #  寫法2： db = pymysql.connect('localhost','root','**','**')
    db = pymysql.connect(host='localhost',
                         user='***',
                         password='***',
                         db='***')
    # t_user 插入數據sql
    sql = 'insert into t_user(name,age,address) values(%s,%s,%s)'
    try:
        cursor = db.cursor()
        # 設置字符集為utf8 否則  報錯 'latin-1' codec can't encode characters in position...
        db.set_charset('utf8')
        cursor.execute(sql,value)
        db.commit()
        print('數據插入成功')
    except BaseException as e:
        print('數據插入失敗',e)
        db.rollback()
        cursor.close()
    finally:
        db.close()
        cursor.close()

if __name__ == '__main__':
    data = ('張三瘋', '18', '武當山')  # Tuple 元組
    insert_table(data)

3、查詢數據

import pymysql
def fetch_table_data():
    #建立數據庫鏈接
    #  寫法2： db = pymysql.connect('localhost','root','**','**')
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='mgah',
                         db='xk')
    # 查詢 t_user 表全部數據
    sql = 'select * from t_user'
    # 創(chuàng)建游標對象
    cursor = db.cursor()
    try:
        #設置字符集，否則中文會顯示亂碼
        db.set_charset('utf8')
        cursor.execute(sql)
        #獲取所有的數據，返回的結果為Tuple元組
        result = cursor.fetchall();
        # mode默認是relative,relative:表示從當前所在的?開始移動; absolute:表示從第??開始移動
        cursor.scroll(0, mode='absolute')# 重置游標位置，偏移量:?于0向后移動;?于0向前移動
        single_result = cursor.fetchone() # 獲取單條數據
        cursor.scroll(0, mode='absolute')# 重置游標位置，偏移量:?于0向后移動;?于0向前移動
        many_result = cursor.fetchmany(2) #獲取2條數據
        db.commit()
        print('查詢所有數據',result)
        print('獲取單條數據', single_result)
        print('獲取兩條', many_result)
    except BaseException as e:
        print('查詢失敗',e)
        db.rollback()
    finally:
        cursor.close()
        db.close()


if __name__ == '__main__':
    fetch_table_data();

4.更新數據

import pymysql
def update_table(value):
    # 建立數據庫鏈接
    #  寫法2： db = pymysql.connect('localhost','root','**','**')
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='mgah',
                         db='xk')
    # t_user 插入數據sql
    sql = 'update t_user set name=%s where id=%s'
    try:
        cursor = db.cursor()
        # 設置字符集為utf8 否則  報錯 'latin-1' codec can't encode characters in position...
        db.set_charset('utf8')
        cursor.execute(sql,value)
        db.commit()
        print('數據更新成功')
    except BaseException as e:
        print('數據更新失敗',e)
        db.rollback()
        cursor.close()
    finally:
        db.close()
        cursor.close()

if __name__ == '__main__':

    data = ('張一瘋', '1') # Tuple 元組
    update_table(data);

5、刪除數據

import pymysql
def delete_table(value):
    # 建立數據庫鏈接
    #  寫法2： db = pymysql.connect('localhost','root','**','**')
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='mgah',
                         db='xk')
    # t_user 插入數據sql
    sql = 'delete from t_user where id=%s'
    try:
        cursor = db.cursor()
        # 設置字符集為utf8 否則  報錯 'latin-1' codec can't encode characters in position...
        db.set_charset('utf8')
        cursor.execute(sql,value)
        db.commit()
        print('數據刪除成功')
    except BaseException as e:
        print('數據刪除失敗',e)
        db.rollback()
        cursor.close()
    finally:
        db.close()
        cursor.close()

if __name__ == '__main__':

    id = '1';
    delete_table(id);