4.sqlite3封装

import sqlite3


def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


class Demo(object):

    def __init__(self):
        super(Demo, self).__init__()
        self.conn = sqlite3.connect('test.db')

    def create(self):
        with self.conn:
            self.conn.execute('''CREATE TABLE COMPANY
               (ID INT PRIMARY KEY     NOT NULL,
               NAME           TEXT    NOT NULL,
               AGE            INT     NOT NULL,
               ADDRESS        CHAR(50),
               SALARY         REAL);'''
            )

    def insert(self):
        with self.conn:
            self.conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
               VALUES (5, 'Paul', 32, 'California', 20000.00 )")

    def select(self):
        self.conn.row_factory = dict_factory  # 查询数据结果返回为字典：[{},{}]
        c = self.conn.cursor()
        c.execute("SELECT id, name, address, salary  from COMPANY")
        print(c.fetchall())  # [{key: value, key: value}, {...}, {...}]
        print(c.fetchone())  # [{key: value, key: value}]
        return c.fetchall()  # List[Dict]

    def update(self):
        with self.conn:
            self.conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID=1")

    def delete(self):
        with self.conn:
            self.conn.execute("DELETE from COMPANY where ID=2;")

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


if __name__ == '__main__':
    obj = Demo()
    # obj.create()
    # obj.insert()
    obj.select()
    # obj.update()
    # obj.delete()