（技术）Python 3 与 pymysql 操作数据库

1.创建表

import pymysql
def create_table():
    #建立数据库链接
    #  写法2： db = pymysql.connect('localhost','root','**','**')
    db = pymysql.connect(host='localhost',
                         user='**',
                         password='**',
                         db='**')
    # 创建 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))'
    # 创建游标对象
    cursor = db.cursor()
    try:
        cursor.execute(sql)
        db.commit()
        print('表创建成功')
    except BaseException as e:
        print('表创建失败',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'
    # 创建游标对象
    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);