Sqlite（相关方法）

Sqlite操作

import sqlite3


class Sqlite:
    def __init__(self, name):
        self.name = name        # 数据库名
        self.conn = None
        self.cur = None

    def __enter__(self):
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.quit()

    def connect(self, autocommit=None):
        """
        建立数据库链接
        :param autocommit: 自动提交
        :return:
        """
        self.conn = sqlite3.connect(self.name)
        self.cur = self.conn.cursor()
        self.conn.isolation_level = autocommit

    def quit(self):
        """
        断开数据库链接
        :return:
        """
        self.cur.close()
        self.conn.close()

    def execute_sql(self, sql, commit=True):
        """
        执行 SQL 语句
        :param sql: SQL 语句
        :param commit: 是否提交
        """
        self.cur.execute(sql)
        if commit:
            self.conn.commit()

    def create_table(self, name, title):
        """
        新建表格
        :param name: 表名
        :param title: 表头 (..., ..., ...)
        :return:
        """
        try:
            sql = f"create table {name} {title}"
            self.execute_sql(sql)
            return "ok"
        except Exception as e:
            return "表格已存在"

    def insert_table(self, name, value):
        """
        插入数据
        :param name: 表名
        :param value: 内容 ('...', '...', '...')
        :return:
        """
        sql = f"insert into {name} values {value}"
        self.execute_sql(sql)

    def insert_table_s(self, name, value):
        """
        批量插入
        :param name: 表名
        :param value: 内容 "[('...', '...', '...'), ('...', '...', '...')]"
        :return:
        """
        sql = f"insert into {name} values (?, ?, ?)"
        self.cur.executemany(sql, value)

    def rollback(self):
        """
        回滚
        :return:
        """
        self.conn.rollback()

    def select_table(self, name):
        """
        查询
        :param name: 表名
        :return:
        """
        sql = f"select * from {name}"
        self.execute_sql(sql)
        return self.cur.fetchall()

    def delete_table(self, name):
        """
        删除表
        :param name: 表名
        :return:
        """
        sql = f"drop table {name}"
        self.execute_sql(sql)


if __name__ == '__main__':
    with Sqlite(name="test.db") as s:
        s.create_table("book", '(title, author, time)')
        s.insert_table("book", "('我的世界', 'aiden', '2020.02.02')")
        data = s.select_table("book")
        # data = s.execute_sql("drop table book")

    print(data)  # [('我的世界', 'aiden', '2020.02.02')]