Python SqLite3接口函数

import sqlite3


class MySql:
    def __init__(self, database_name):
        self.conn = None
        self.c = None
        self.connect_database(database_name)

    #   连接数据库
    def connect_database(self, database_name):
        try:
            #   排除错误的数据库名
            if database_name[-3:] != '.db':
                database_name += '.db'
            self.conn = sqlite3.connect(database_name)
            self.c = self.conn.cursor()
        except sqlite3.Error as e:
            print("unable to open database file:", e)

    #   关闭数据库
    def close_database(self):
        try:
            self.conn.close()
        except sqlite3.Error as e:
            print("unable to close database file:", e)

    #   创建表
    def create_table(self, table_name, args):
        try:
            table_items = ''
            if type(args) == list or type(args) == tuple:
                for item in args:
                    table_items += item + ','
                table_items = table_items[:-1]  # 去掉最后一个逗号
            elif type(args) == dict:
                for item in args.items():
                    table_items += item[0] + ' ' + item[1] + ','
                table_items = table_items[:-1]  # 去掉最后一个逗号

            self.c.execute('CREATE TABLE ' + table_name + ' (' + table_items + ')')
        except sqlite3.Error as e:
            print("unable to create table:", table_name, e)

    #   删除表
    def drop_table(self, table_name):
        try:
            self.c.execute('DROP TABLE ' + table_name)
        except sqlite3.Error as e:
            print("unable to drop table:", table_name, e)

    #   添加数据（增）
    def insert_values(self, table_name, args):
        try:
            table_items = ''
            table_values = ''
            if type(args) == list or type(args) == tuple:
                for item in args:
                    if type(item) == str:
                        value = '"' + item + '"'
                    else:
                        value = item
                    table_values += str(value) + ','
                table_values = table_values[:-1]  # 去掉最后一个逗号
                self.c.execute('INSERT INTO ' + table_name + ' VALUES (' + table_values + ')')
            elif type(args) == dict:
                for item in args.items():
                    table_items += item[0] + ','
                    if type(item[1]) == str:
                        value = '"' + item[1] + '"'
                    else:
                        value = item[1]
                    table_values += str(value) + ','
                table_items = table_items[:-1]
                table_values = table_values[:-1]  # 去掉最后一个逗号
                self.c.execute('INSERT INTO ' + table_name + ' (' + table_items + ') VALUES (' + table_values + ')')
            self.conn.commit()
        except sqlite3.Error as e:
            print("unable to insert table:", table_name, e)

    #   获取数据列（查）
    def select_items(self, table_name, args, constrains=''):
        try:
            table_items = ''
            for item in args:
                table_items += item + ','
            table_items = table_items[:-1]  # 去掉最后一个逗号
            self.c.execute('SELECT ' + table_items + ' FROM ' + table_name + ' ' + constrains)
            return self.c.fetchall()
        except sqlite3.Error as e:
            print("unable to select items from:", table_name, e)

    #   更新数据（改）
    def update_values(self, table_name, args, constrains=''):
        try:
            table_items = ''
            for item in args.items():
                if type(item[1]) == str:
                    value = '"' + item[1] + '"'
                else:
                    value = item[1]
                table_items += item[0] + '=' + str(value) + ','
            table_items = table_items[:-1]  # 去掉最后一个逗号
            self.c.execute('UPDATE ' + table_name + ' SET ' + table_items + ' ' + constrains)
            self.conn.commit()
        except sqlite3.Error as e:
            print("unable to update values in", table_name, e)

    #   删除数据（删）
    def delete_values(self, table_name, constrains=''):
        try:
            self.c.execute('DELETE FROM ' + table_name + ' ' + constrains)
        except sqlite3.Error as e:
            print("unable to delete values from:", table_name, e)

    #   获取表的项目名称、数据类型、非空限制、主键等信息
    def get_table_info(self, table_name):
        try:
            self.c.execute('PRAGMA table_info(' + table_name + ')')
            return self.c.fetchall()
        except sqlite3.Error as e:
            print("unable to get table info:", table_name, e)


if __name__ == '__main__':
    sql = MySql('example')

    sql.delete_values('COMPANY')

    table_COMPANY = [
        'ID INT PRIMARY KEY NOT NULL',
        'NAME TEXT NOT NULL',
        'AGE INT NOT NULL UNIQUE',
        'ADDRESS CHAR(50)',
        'SALARY REAL DEFAULT 50000.00 CHECK(SALARY > 0)',
    ]
    table_DEPARTMENT = {
        'ID': 'INT PRIMARY KEY NOT NULL',
        'DEPT': 'CHAR(50) NOT NULL',
        'EMP_ID': 'INT NOT NULL',
    }
    sql.create_table('COMPANY', table_COMPANY)
    sql.create_table('DEPARTMENT', table_DEPARTMENT)
    # sql.drop_table('DEPARTMENT')

    print(sql.get_table_info('COMPANY'))

    value_Paul = [
        1,
        'Paul',
        32,
        'California',
        20000.00,
    ]
    value_Allen = {
        'ID': 2,
        'NAME': 'Allen',
        'AGE': 25,
        'ADDRESS': 'Texas',
        'SALARY': 15000.00,
    }
    sql.insert_values('COMPANY', value_Paul)
    sql.insert_values('COMPANY', value_Allen)
    sql.insert_values('COMPANY', [3, 'Teddy', 23, 'Norway', 20000.00])
    sql.insert_values('COMPANY', [4, 'Mark', 25, 'Rich-Mond ', 65000.00])
    sql.insert_values('COMPANY', [5, 'David', 27, 'Texas', 85000.00])
    sql.insert_values('COMPANY', [6, 'Kim', 22, 'South-Hall', 45000.00])
    sql.insert_values('COMPANY', [7, 'James', 37, 'Norway', 5000.00])
    sql.insert_values('COMPANY', [8, 'James', 43, 'Norway', 5000.00])
    sql.insert_values('COMPANY', [9, 'James', 44, 'Norway', 5000.00])

    sql.insert_values('DEPARTMENT', [1, 'IT Billing', 1 ])
    sql.insert_values('DEPARTMENT', [2, 'Engineering', 2 ])
    sql.insert_values('DEPARTMENT', [3, 'Finance', 7])

    print(sql.select_items('COMPANY', '*', 'LIMIT 3 OFFSET 2'))  # 最多采3条数据，从第2条（不包含）开始采
    print(sql.select_items('COMPANY', '*', 'ORDER BY SALARY ASC'))  # ASC升序，DESC降序
    print(sql.select_items('COMPANY', ['NAME', 'AGE', 'SUM(SALARY)'], 'GROUP BY NAME HAVING AGE > 26'))  # 合并相同项目
    print(sql.select_items('COMPANY', '*', 'WHERE AGE >= 25 AND SALARY >= 65000'))
    print(sql.select_items('COMPANY', '*', 'WHERE ADDRESS LIKE "%a_"'))  # %表示任意，_表示单个
    print(sql.select_items('COMPANY', '*', 'WHERE ADDRESS GLOB "*a?"'))  # *表示任意，?表示单个

    update_Allen = {
        'AGE': 30,
        'ADDRESS': 'South-Hall',
        'SALARY': 70000.00,
    }
    sql.update_values('COMPANY', update_Allen, 'WHERE NAME == "Allen"')

    # 直接调用cursor
    sql.c.execute('PRAGMA page_size')
    print(sql.c.fetchall())