sqlist3数据库读写

将二维数组信息，邪恶如数据库，如果没有数据库就自动创建数据库；sqlist3数据库

import os
import sqlite3

def write_to_sqlite(data, db_filename, table_name, headers):
    '''
    将二维数组信息，邪恶如数据库，如果没有数据库就自动创建数据库；
    :param data: #要写入的二维数组
    :param db_filename: 数据库名字
    :param table_name: 数据库表明名
    :param headers: 一维数组，数据库表头
    :return:
    '''
    db_filename=db_filename+'.db'
    if not os.path.exists(db_filename):
        open(db_filename, 'w').close()
    conn = sqlite3.connect(db_filename)
    c = conn.cursor()
    # 如果表不存在，则创建表
    c.execute(f"SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{table_name}'")
    if c.fetchone()[0] == 0:
        create_sql = f"CREATE TABLE {table_name} ({','.join([f'{header} TEXT' for header in headers])})"
        c.execute(create_sql)
    # 插入数据
    placeholders = ",".join(["?" for _ in range(len(headers))])
    insert_sql = f"INSERT INTO {table_name} ({','.join(headers)}) VALUES ({placeholders})"
    for row in data:
        c.execute(insert_sql, row)
    conn.commit()
    conn.close()

python 读取sqlist3返回二维数组

def read_sqlist(table_name,db_filename):
    '''
    从指定表明中 读取sqlist，返回二维数组
    :param table_name: 
    :param db_filename: 
    :return: 
    '''
    db_filename = db_filename + '.db'
    conn = sqlite3.connect(db_filename)#链接数据库 filePath_db数据库路径
    dataFrame = pd.read_sql('select * from %s'%table_name, conn)#读取数据库信息,转换dataFrame
    print(dataFrame)
    arr_data=dataFrame.values
    arr_data=arr_data.tolist()
    print(arr_data)
    return arr_data

删除sqlist3中的所有数据

def delsqlist(db_file = 'main.db',table_name="table_name"):
    '''

    删除表 中的所有数据
    :param db_file:
    :param table_name:
    :return:
    '''
    # manyData可以是二维列表、元组或者迭代器
    conn = sqlite3.connect(db_file)
    # 删除表中所有行数据
    conn.execute('DELETE FROM %s'%table_name)
    conn.commit()
    conn.close()