python3操作sqite3删除数据表字段

本文介绍python3对sqite3数据库的常用操作，sqite官网。

一、创建数据库操作类

class CDBManage:

    def __init__(self, dbname: str = None):
        self.__dbname = 'local.db'
        if dbname:
            self.__dbname = '{0}.db'.format(dbname)

        self.__conn = sqlite3.connect(self.__dbname)

    def __del__(self):
        self.__conn.close()

    def get_dbname(self):
        return '{0}/{1}'.format(os.path.abspath('./'), self.__dbname)

    def sqlite3_query(self, sql: str):
        results = []

        try:
            cursor = self.__conn.execute(sql)
            results = cursor.fetchall()
        except Exception as e:
            self.__conn.rollback()  # 事务回滚
            logging.error('[sqlite3_query]Failed to run sqlite3_query, {0}'.format(e))

        return results

    # 执行自定义sql语句增、删、改
    def sqlite3_execute(self, sql: str):
        try:
            cur = self.__conn.cursor()
            cur.execute(sql)
            self.__conn.commit()
            cur.close()
        except Exception as e:
            self.__conn.rollback()  # 事务回滚
            logging.error('[sqlite3_execute]Failed to run sqlite3_execute, {0},{1}'.format(e, sql))

    # 批量执行自定义sql语句增、删、改
    def sqlite3_batch_execute(self, sqlList: list):
        if not sqlList:
            return

        try:
            cur = self.__conn.cursor()
            for sql in sqlList:
                cur.execute(sql)
            self.__conn.commit()
            cur.close()
        except Exception as e:
            self.__conn.rollback()  # 事务回滚
            logging.error('[sqlite3_batch_execute]Failed to run sqlite3_batch_execute, {0}'.format(e))

说明：

• 类创建对象时，创建默认数据库名称local.db，并创建数据库连接
• 对象销毁时，关闭数据库连接
• sqlite3_query运行查询sql语句，结果以列表形式返回
• sqlite3_execute运行sql语句增、删、改
• sqlite3_batch_execute批量运行sql语句增、删、改
• 增、删、改操作出错后支持事务回滚

二、sqite3常用操作

1. 创建数据表

def create_database(tb_name: str, tb_content: str):
    dbman = CDBManage()
	sql = "select * from sqlite_master where type = 'table' and name = '{0}';".format(tb_name)
    results = dbman.sqlite3_query(sql)
    if not results:
        dbman.sqlite3_execute(tb_content)
    else:
        logging.info('数据表{0}已经存在'.format(tb_name))

# 调用例子
create_database('site_info', '''CREATE TABLE site_info
       (ST_Id INTEGER PRIMARY KEY AUTOINCREMENT,
       ST_Site           CHAR(255)    NOT NULL,
       ST_Storage        CHAR(32)    NOT NULL,
       ST_Desc           CHAR(255)    NOT NULL,
       ST_Type           CHAR(64));''')

2. 删除数据表

def delete_table(tb_name: str):
    dbman = CDBManage()
	sql = "drop table {0};".format(tb_name)
    dbman.sqlite3_execute(sql)

# 调用例子
delete_table('site_info')

3. 清空数据表

def empty_table(tb_name: str):
    dbman = CDBManage()
	sql = "delete from {0};".format(tb_name)
    dbman.sqlite3_execute(sql)

# 调用例子
empty_table('site_info')

4. 增加数据表字段

def add_table_field(tb_name: str, field: str):
    dbman = CDBManage()
	sql = "ALTER TABLE {0} ADD COLUMN {1};".format(tb_name, field)
    dbman.sqlite3_execute(sql)

# 调用例子
add_table_field('site_info', 'field1 CHAR(32)')
add_table_field('site_info', 'field2 INT')

5. 删除数据表字段

SQLite目前还不支持drop column，删除需要写代码实现

def del_table_field(tb_name: str, field: str):
    dbman = CDBManage()

	# 查询数据表所有字段
	sql = "pragma table_info({0});".format(tb_name)
	field_list = []
    results = dbman.sqlite3_query(sql)
    # [(0, 'ST_Id', 'INTEGER', 0, None, 1), (1, 'ST_Site', 'CHAR(255)', 1, None, 0), (2, 'ST_Storage', 'CHAR(32)', 1, None, 0), (3, 'ST_Desc', 'CHAR(255)', 1, None, 0), (4, 'ST_Type', 'CHAR(64)', 0, None, 0)]
    if results:
    	for res in results:
    		field_list.append(res[1])  # 第二个值是字段名称
	if field not in field_list:
		return
	sql = "create table temp as select {0} from {1} where 1 = 1;".format(','.join(field_list.remove(field, tb_name)))
	dbman.sqlite3_execute(sql)  # 删除字段后用剩余字段创建临时表
	sql = "drop table {0};".format(tb_name);  
	dbman.sqlite3_execute(sql)  # 删除旧表
	sql = "alter table temp rename to {0};".format(tb_name);  
	dbman.sqlite3_execute(sql)  # 把临时表改名为旧表名称

# 调用例子
del_table_field('site_info', 'ST_Type')