python操作sqlite数据库

　　python操作轻量级数据库sqlite较为简单，直接上代码啦~

 

一. 函数版

　　举例了建表、查询、入库等操作，其它操作大同小异。

import sqlite3 as db


def execute(sql: str, data=''):
    conn = db.connect('useful.db')  # 没有的话会自动创建
    cursor = conn.cursor()
    res = cursor.execute(sql, data)
    yield res.fetchall()
    conn.commit()
    cursor.close()
    conn.close()


if __name__ == '__main__':
    create_table = "create table if not exists zhihu(" \
                   "hot text primary key unique, title text, excerpt text, detail_url text)"
    select_all = 'select * from zhihu'
    insert_any = '''insert or ignore into zhihu values ("faffadfadd","Fda","fda","fa")'''

    for i in execute(insert_any):
        print(i)

　　函数版的虽然也可以，但是通过yield抛出这里多少有点不够完美。。。

　　这里可以用上下文解决。

 

二. 上下文版

　　数据库的连接和关闭通过上下文解决，完美！

import sqlite3 as db


class DB:
    def __init__(self, sql: str, data='', db_path='useful.db'):
        self._sql = sql
        self._data = data
        self._conn = db.connect(db_path)  # 没有的话会自动创建
        self._cursor = self._conn.cursor()

    def __enter__(self):
        res = self._cursor.execute(self._sql, self._data)
        self._conn.commit()
        return res.fetchall()

    def __exit__(self, exc_type, exc_value, exc_traceback):
        self._cursor.close()
        self._conn.close()

    def get(self):
        return self._cursor.fetchone()


if __name__ == '__main__':
    create_table = "create table if not exists zhihu(" \
                   "hot text primary key unique, title text, excerpt text, detail_url text)"
    select_all = 'select * from zhihu'
    insert_any = '''insert or ignore into zhihu values ("faffadfadd","Fda","fda","fa")'''

    with DB(select_all) as f:
        for i in f:
            print(i)