sqlite-insertMultipleRecords.py

import sqlite3
"""
读取文件到sqlite
"""
def insertMultipleRecords(db, sqlite_insert_query, recordList):
    try:
        sqliteConnection = sqlite3.connect(db)
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")



        cursor.executemany(sqlite_insert_query, recordList)
        sqliteConnection.commit()
        print("Total", cursor.rowcount, "Records inserted successfully into SqliteDb_developers table")
        sqliteConnection.commit()
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to insert multiple records into sqlite table", error)
    finally:
        if (sqliteConnection):
            sqliteConnection.close()
            print("The SQLite connection is closed")



if __name__ == '__main__':

    db = './data/stock.db'
    filename = '/Users/tsukasamanabubon/Desktop/20200724092303.csv'
    sqlite_insert_query = """INSERT INTO stock_meta
                              (id, stock_code, stock_name) 
                              VALUES (?, ?, ?);"""
    recordList = []
    with open(filename, 'r', encoding='utf-16') as f:
        for i, line in enumerate(f.readlines()):
            if i > 0:
                stock.id, stock.code, stock.name, stock.other = line.strip().split('\t',3)
                entry = (
                    i,
                    stock.code,
                    stock.name
                )
                recordList.append(entry)
                # 每次1000行提交一次，最好不满1000行时再提交一次
                if i % 1000 == 0:
                    insertMultipleRecords(db, sqlite_insert_query, recordList)
                    recordList = []

        insertMultipleRecords(db, sqlite_insert_query, recordList)