pymysql插入、删除数据

插入、删除数据的操作基本与创建表时的操作相同，只是在最后需要调用commit方法提交到数据库，否则更改不会保存。

import pprint
import pymysql


host = 'localhost'
user = 'test'
password = 'test'
db_name = 'test'


class my_sql_exec():
    def __init__(self, _host, _username, _password, _dbname):
        self.host = _host
        self.username = _username
        self.password = _password
        self.db_name = _dbname
        # 创建connect对象插入中文需要指定编码
        self.connect = pymysql.connect(host, user, password, self.db_name, charset='utf8')
        self.cursor = self.connect.cursor()

    def exec(self, sql):
        try:

            self.cursor.execute(sql)
            # 要执行commit方法，不然不会把更改提交到数据库
            self.connect.commit()
            try:
                result = self.cursor.fetchall()
            except:
                pass

            if result:
                pprint.pprint(result)
        except Exception as e:
            # 发生错误回滚
            self.connect.rollback()
            print('出现错误！\n', e.args)

    def close(self):
        self.cursor.close()
        self.connect.close()


insert_sql = '''
insert into users (name, age)
values ('python小白', 20), ('python老鸟', 40)
'''
connection = my_sql_exec(host, user, password, db_name)

connection.exec(insert_sql)
fetch_table_sql = '''
select * from users
'''
connection.exec(fetch_table_sql)
del_sql = '''
delete from users where id = 3
'''
connection.exec(del_sql)
fetch_table_sql = '''
select * from users
'''
connection.exec(fetch_table_sql)

connection.close()