code

#!/usr/bin/python3
import os
import csv
import sqlite3

curDir = os.getcwd()
class Txt2Db:
def startLoad(self):
    # open the database
    self.connection = sqlite3.connect('xx.db')
    self.cursor = self.connection.cursor()

def endLoad(self):
    self.connection.commit()
    self.connection.close()

def load(self, file_dir):
    for root, dirs, files in os.walk(file_dir):
        for i in range(len(files)): # 当前路径下所有非目录子文件
            head = os.path.splitext(files[i])[0]
            tail = os.path.splitext(files[i])[1]
            if tail == '.txt':
                if head[0:4] == 'tra-':
                    self.loadJsonFile(files[i])

def loadJsonFile(self, fileName):
    oriTableName = os.path.splitext(fileName)[0]
    tableName = "[" + oriTableName + "]"

    # drop table
    sql = "DROP TABLE IF EXISTS" + tableName
    self.cursor.execute(sql)

    # create table
    sql = "CREATE TABLE" + tableName + \
        "(offset int PRIMARY KEY,json Text);"
    self.cursor.execute(sql)

    # drop index
    # sql = "DROP INDEX IF EXISTS offset_index;"
    # cursor.execute(sql)
    # connection.commit()

    # create index
    indexName = "index_" + oriTableName.split('-')[1]
    sql = "CREATE INDEX " + indexName + " ON " + tableName + "(offset)"
    self.cursor.execute(sql)
    self.connection.commit()

    # Load the CSV file into CSV reader
    csvfile = open(fileName, 'r')
    creader = csv.reader(csvfile, delimiter='\t', quotechar='"')
    # Iterate through the CSV reader, inserting values into the database
    for t in creader:
        sql = "INSERT INTO" + tableName + "VALUES (?,?)"
        self.cursor.execute(sql, t)

    # Close the csv file, commit changes, and close the connection
    csvfile.close()

txt2Db = Txt2Db()
txt2Db.startLoad()
txt2Db.load(curDir)
txt2Db.endLoad()