Python中通过文件将数据导入sqlite3

将csv文件中数据导入sqlite3

1、简单demo代码

import csv, sqlite3

conn = sqlite3.connect("pcfc.sl3")
curs = conn.cursor()

curs.execute("CREATE TABLE PCFC (id INTEGER PRIMARY KEY, type INTEGER, term TEXT, definition TEXT);")
reader = csv.reader(open('PC.txt', 'r'), delimiter='|')

for row in reader:
    to_db = [unicode(row[0], "utf8"), unicode(row[1], "utf8"), unicode(row[2], "utf8")]
    curs.execute("INSERT INTO PCFC (type, term, definition) VALUES (?, ?, ?);", to_db)
conn.commit()
con.close()

2、工具(utiliy)方法代码

import csv, sqlite3

def _get_col_datatypes(fin):
    dr = csv.DictReader(fin) #逗号是默认分隔符
    fieldTypes = {}
    for entry in dr:
        feildslLeft = [f for f in dr.fieldnames if f not in fieldTypes.keys()]        
        if not feildslLeft: break 
        for field in feildslLeft:
            data = entry[field]
            if len(data) == 0:
                continue
            if data.isdigit():
                fieldTypes[field] = "INTEGER"
            else:
                fieldTypes[field] = "TEXT"
        #TODO: 目前在sqllite中没有对DATE的支持
    if len(feildslLeft) > 0:
        raise Exception("Failed to find all the columns data types - Maybe some are empty?")
    return fieldTypes

def escapingGenerator(f):
    for line in f:
        yield line.encode("ascii", "xmlcharrefreplace").decode("ascii")

def csvToDb(csvFile,dbFile,tablename, outputToFile = False):
    with open(csvFile,mode='r', encoding="ISO-8859-1") as fin:
        dt = _get_col_datatypes(fin)
        fin.seek(0)
        reader = csv.DictReader(fin)
        #保持列名称的顺序，就像在CSV中一样
        fields = reader.fieldnames
        cols = []
        # Set field and type
        for f in fields:
            cols.append("\"%s\" %s" % (f, dt[f]))
        #生成创建表语句:
        stmt = "create table if not exists \"" + tablename + "\" (%s)" % ",".join(cols)
        print(stmt)
        con = sqlite3.connect(dbFile)
        cur = con.cursor()
        cur.execute(stmt)
        fin.seek(0)

        reader = csv.reader(escapingGenerator(fin))
        #生成insert语句:
        stmt = "INSERT INTO \"" + tablename + "\" VALUES(%s);" % ','.join('?' * len(cols))
        cur.executemany(stmt, reader)
        con.commit()
        con.close()