txt2db

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()