将csv文件中数据导入sqlite3
1、简单demo代码
import csv, sqlite3conn = 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, sqlite3def _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 fieldTypesdef 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()