数据库_sqlite3操作

目录

1. 创建表

2. 插入数据

3. 查询数据

4. 删除数据 

5. 判断数据库中表是否存在

---

1. 创建表

import json,sqlite3
from setting import SQLITE3_DB_DIR
# SQLITE3_DB_DIR 值为自己定义的路径，用于存放sqlite3的数据库
def create_t_example_db_example():
    db_database_name = 'db_example'
    t_table_name = 't_example'
    target_database = SQLITE3_DB_DIR + db_database_name + '.db'
    con = sqlite3.connect(target_database)
    cur = con.cursor()
    try:
        create_td_cmd = '''
            create table if not exists {t_table_name}(
            id integer primary key autoincrement,
            p_1 varchar,
            p_2 float,
            p_3 date,
            p_4 text    # 该字段用来记录数据很大的json
            )
            '''.format(t_table_name=t_table_name)
        cur.execute(create_td_cmd)
    except Exception as e:
        print(e)
        print('创建表失败')
        cur.close()
        con.close()
        return
    con.commit()
    cur.close()
    con.close()

2. 插入数据

def insert_t_example_db_example(pre_save_data:List[Any]):
    db_database_name = 'db_example'
    t_table_name = 't_example'
    target_database = SQLITE3_DB_DIR + db_database_name + '.db'
    con = sqlite3.connect(target_database)
    cur = con.cursor()
    try:
        create_td_cmd = '''
            create table if not exists {t_table_name}(
            id integer primary key autoincrement,
            p_1 varchar,
            p_2 float,
            p_3 date,
            p_4 text
            )
            '''.format(t_table_name=t_table_name)
        cur.execute(create_td_cmd)
    except Exception as e:
        print(e)
        print('创建表失败')
        cur.close()
        con.close()
        return
    cur.executemany(
        'insert into {t_table_name} (p_1,p_2,p_3,p_4)  values (?,?,?,?)'.format(
            t_table_name=t_table_name), pre_save_data)
    con.commit()
    cur.close()
    con.close()


if __name__ == '__main__':
    temp_json00 = {"a":"aaaaaaaaaaaaaaa","b":"bbbbbbbbbbbbbbbbb","c":"ccccccccccccccccc"}
    temp_json01 = {"a":"aaaaaaaaaaaaaaa111","b":"bbbbbbbbbbbbbbbbb111","c":"ccccccccccccccccc111"}

    pre_list = []
    pre_list.append((
    '哈哈哈',
    1.243,
    '2022-01-01',
    sqlite3.Binary(bytes(json.dumps(temp_json00), encoding='utf-8'))
    ))
    pre_list.append((
    '嘻嘻嘻',
    9.456,
    '2022-01-02',
    sqlite3.Binary(bytes(json.dumps(temp_json01), encoding='utf-8'))
    ))
    insert_t_example_db_example(pre_list)

3. 查询数据

def query_example_by_p1(p_str:str)->Dict[str,Any]:
    db_database_name = 'db_example'
    t_table_name = 't_example'
    target_database = SQLITE3_DB_DIR + db_database_name + '.db'
    con = sqlite3.connect(target_database)
    cur = con.cursor()
    sql_str = '''
                    select p_1,p_2,p_3,p_4 from {t_table_name} where p_1=\'{p_str}\'
                    '''.format(t_table_name=t_table_name,p_str=p_str)
    cur.execute(sql_str)
    res_one = cur.fetchone()
    cur.close()
    con.close()
    res_dict = {}
    res_dict['p_1'] = res_one[0]
    res_dict['p_2'] = res_one[1]
    res_dict['p_3'] = res_one[2]
    res_dict['p_4'] = json.loads(res_one[3])
    return res_dict

4. 删除数据 

def del_t_economic_by_code(code:str):
    db_database_name = 'db_example'
    t_table_name = 't_example'
    target_database = SQLITE3_DB_DIR + db_database_name + '.db'
    con = sqlite3.connect(target_database)
    cur = con.cursor()
    sql_str = '''
                        delete from {t_table_name} where catalog_code=\'{code_str}\'
                        '''.format(t_table_name=t_table_name, code_str=code)
    cur.execute(sql_str)
    con.commit()
    cur.close()
    con.close()

5. 判断数据库中表是否存在

def check_table_exist(database_name:str,table_name:str)->bool:
    db_database_name = database_name
    t_table_name = table_name
    target_database = SQLITE3_DB_DIR + db_database_name + '.db'
    con = sqlite3.connect(target_database)
    cur = con.cursor()
    sql_str = '''
                    select * from sqlite_master where name=\'{t_table_name}\'
                    '''.format(t_table_name=t_table_name)
    cur.execute(sql_str)
    res_one = cur.fetchone()
    cur.close()
    con.close()
    if res_one:
        return True
    else:
        return False
    pass

sqlite_master表中的字段如下：

type:类型，可以为table,index,trigger
name:名称，为对应表，索引，触发器名称
tbl_name:未索引，触发器对应表名
rootpage:为对应表，索引在数据库中存储的根页号
sql:创建的sql语句
 
至此，我们可以使用如下语句进行查询：
select * from sqlite_master where name='你的表名';
如果有，就会返回数据，否则不返回。