文本处理学习笔记5
环境如下：
python-2.5.2
工作中经常遇到类似csv格式的文件文件，
为了处理起来方便，一般要导入到数据库中
MYSQL导入工具代码：
# coding:UTF-8 import osimport sysimport timeimport MySQLdbperread = 1048576 # 1Moriginal = 32size_list = []for i in range(125): size_list.append(original) original = original + 32def getSize(aName, aSize): for i in size_list: if aSize<i: return i raise ValueError("字段太长 '%s' %s"%(aName, aSize))def loadCSV(fileName, separtor=""): print "loadCSV", fileName, "..." tf = file(fileName, "r") connMys = MySQLdb.connect(host="127.0.0.1", user="root", passwd="pass", db="test", charset="UTF8") begin = time.time() mark = 0 count = 0 currLines = tf.readlines(perread) count = count + len(currLines) - 1 currTable = [i.rstrip().split(separtor) for i in currLines if len(i.rstrip())>0] columnName = currTable[0] for i in columnName: if not i: raise ValueError("字段名不能为空!") columnCount = len(columnName) currTable = fillSpace(currTable, columnCount) currTable = distinctColumn(currTable) aName = os.path.split(fileName)[1].split(".")[0] dropSQL, createSQL, insertSQL = buildCreate(aName, currTable) cursorMys = connMys.cursor() print dropSQL, ";" cursorMys.execute(dropSQL) print createSQL, ";" cursorMys.execute(createSQL) connMys.commit() print insertSQL, ";" for i in currTable[1:]: cursorMys.execute(insertSQL, i) connMys.commit() print "insert", count, "lines." while tf.tell() > mark: mark = tf.tell() currLines = tf.readlines(perread) if not tf.tell() > mark: break count = count + len(currLines) currTable = [i.rstrip().split(separtor) for i in currLines if len(i.rstrip())>0] currTable = fillSpace(currTable, columnCount) for i in currTable: cursorMys.execute(insertSQL, i) connMys.commit() print "insert", count, "lines." end = time.time() print "Count:%s Time:%s"%(count, end - begin) cursorMys.close() connMys.close() tf.close()def fillSpace(aTable, colCount): for ii, iv in enumerate(aTable): while len(iv) < colCount: iv.append("") if len(iv) != colCount: raise ValueError("字段不够多!" + str(iv)) return aTabledef distinctColumn(aTable): columnNames = aTable[0] ucolumnNames = set(columnNames) if len(columnNames)==len(ucolumnNames): return aTable tcinclude = [] tcexclude = [] for i, v in enumerate(columnNames): if v in tcinclude: print "字段竟然重复", v tcexclude.append(i) else: tcinclude.append(v) result = [] for i in aTable: result.append([v for j, v in enumerate(i) if j not in tcexclude]) return resultdef buildCreate(aName, aTable): columnNames = aTable[0] columnSizes = [getSize(k, len(v)) for k, v in zip(aTable[0], aTable[1])] for i in aTable[2:]: for j, v in enumerate(i): if not len(v)<columnSizes[j]: columnSizes[j] = getSize(columnNames[j], len(v)) dropSQL = "DROP TABLE IF EXISTS " + aName createSQL = ["CREATE TABLE IF NOT EXISTS " + aName] createSQL.append("(") for k, v in zip(columnNames, columnSizes): currLines = " %s VARCHAR(%s),"%(k, v) createSQL.append(currLines) createSQL[-1] = " %s VARCHAR(%s)"%(k, v) createSQL.append(")") k = ", ".join(columnNames) v = ", ".join(["%s" for i in range(len(columnNames))]) insertSQL = "INSERT INTO %s (%s) VALUES (%s)"%(aName, k, v) return (dropSQL, "\n".join(createSQL), insertSQL)def usage(): print "CSVloader -h" print "CSVloader -f bsc.csv bts.csv" print "CSVloader -d dir1 dir2" print "CSVloader -f bsc.csv bts.csv -s ," print "CSVloader -d dir1 dir2 -s ," print "CSVloader -s , -f bsc.csv bts.csv" print "CSVloader -s , -d dir1 dir2" print " Options include:" print " -h [help] - 打印帮助" print " -s [separtor] - 使用的分割符，默认 tab" print " -f [fileName] - 文件名，可以指定多个" print " -d [dirName] - 文件夹，可以指定多个" print print " 注意：文件必须是 UTF-8 编码"if __name__ == "__main__": if "-h" in sys.argv: usage() elif "-d" in sys.argv: separtor = "" idxf = sys.argv.index("-d") if "-s" in sys.argv: idxs = sys.argv.index("-s") separtor = sys.argv[idxs+1] if idxs>idxf: for i in sys.argv[idxf+1:idxs]: for j in os.listdir(i): loadCSV(os.path.join(i, j), separtor) else: for i in sys.argv[idxf+1:]: for j in os.listdir(i): loadCSV(os.path.join(i, j), separtor) else: for i in sys.argv[idxf+1:]: for j in os.listdir(i): loadCSV(os.path.join(i, j), separtor) elif "-f" in sys.argv: separtor = "" idxf = sys.argv.index("-f") if "-s" in sys.argv: idxs = sys.argv.index("-s") separtor = sys.argv[idxs+1] if idxs>idxf: for i in sys.argv[idxf+1:idxs]: loadCSV(i, separtor) else: for i in sys.argv[idxf+1:]: loadCSV(i, separtor) else: for i in sys.argv[idxf+1:]: loadCSV(i, separtor) else: usage()使用方法如下：
CSVloader -h
CSVloader -f bsc.csv bts.csv
CSVloader -d dir1 dir2
CSVloader -f bsc.csv bts.csv -s ,
CSVloader -d dir1 dir2 -s ,
CSVloader -s , -f bsc.csv bts.csv
CSVloader -s , -d dir1 dir2
Options include:
-h [help] - 打印帮助
-s [separtor] - 使用的分割符，默认 tab
-f [fileName] - 文件名，可以指定多个
-d [dirName] - 文件夹，可以指定多个
注意：文件必须是 UTF-8 编码
顺便写了个bat文件：
CSVloader.bat
@echo offpython CSVloader.py %*
（WinXP SP2，MySQL 5.0下使用通过，MySQL数据库使用UTF8编码，csv文件使用UTF8编码）
测试文本：A_OBJECT.txt
oid,city,type,pid7,邢台市,103,78,廊坊市,103,81,沧州市,103,13,高邑县,104,44,河间市,104,12,赵县,104,4
Oracle导入工具：
# coding:GBK import osimport sysimport timeimport cx_Oracleperread = 1048576 # 1M# original = 64# size_list = []# while original<4000:# size_list.append(original)# original = original*2original = 32size_list = []for i in range(125): size_list.append(original) original = original + 32def getSize(aName, aSize): for i in size_list: if aSize<i: return i raise ValueError("字段太长 '%s' %s"%(aName, aSize))def loadCSV(fileName, separtor=""): print "loadCSV", fileName, "..." tf = file(fileName, "r") connOra = cx_Oracle.connect("user", "pass", "192.168.1.110:1521/testdb") begin = time.time() mark = 0 count = 0 currLines = tf.readlines(perread) count = count + len(currLines) - 1 currTable = [i.rstrip().split(separtor) for i in currLines if len(i.rstrip())>0] columnName = currTable[0] for i in columnName: if not i: raise ValueError("字段名不能为空!") columnCount = len(columnName) currTable = fillSpace(currTable, columnCount) currTable = distinctColumn(currTable) aName = os.path.split(fileName)[1].split(".")[0] createSQL, insertSQL = buildCreate(aName, currTable) cursorOra = connOra.cursor() print createSQL, ";" cursorOra.execute(createSQL) connOra.commit() print insertSQL, ";" cursorOra.prepare(insertSQL) cursorOra.executemany(None, currTable[1:]) connOra.commit() print "insert", count, "lines." while tf.tell() > mark: mark = tf.tell() currLines = tf.readlines(perread) if not tf.tell() > mark: break count = count + len(currLines) currTable = [i.rstrip().split(separtor) for i in currLines if len(i.rstrip())>0] currTable = fillSpace(currTable, columnCount) cursorOra.executemany(None, currTable) connOra.commit() print "insert", count, "lines." end = time.time() print "Count:%s Time:%s"%(count, end - begin) cursorOra.close() connOra.close() tf.close()def fillSpace(aTable, colCount): for ii, iv in enumerate(aTable): while len(iv) < colCount: iv.append("") if len(iv) != colCount: raise ValueError("字段不够多!" + str(iv)) return aTabledef distinctColumn(aTable): columnNames = aTable[0] ucolumnNames = set(columnNames) if len(columnNames)==len(ucolumnNames): return aTable tcinclude = [] tcexclude = [] for i, v in enumerate(columnNames): if v in tcinclude: print "字段竟然重复", v tcexclude.append(i) else: tcinclude.append(v) result = [] for i in aTable: result.append([v for j, v in enumerate(i) if j not in tcexclude]) return resultdef buildCreate(aName, aTable): columnNames = aTable[0] columnSizes = [getSize(k, len(v)) for k, v in zip(aTable[0], aTable[1])] for i in aTable[2:]: for j, v in enumerate(i): if not len(v)<columnSizes[j]: columnSizes[j] = getSize(columnNames[j], len(v)) createSQL = ["CREATE TABLE " + aName] createSQL.append("(") for k, v in zip(columnNames, columnSizes): currLines = " %s VARCHAR2(%s),"%(k, v) createSQL.append(currLines) createSQL[-1] = " %s VARCHAR2(%s)"%(k, v) createSQL.append(")") k = ", ".join(columnNames) v = ", ".join([":%s"%(i+1) for i in range(len(columnNames))]) insertSQL = "INSERT INTO %s (%s) VALUES (%s)"%(aName, k, v) return ("\n".join(createSQL), insertSQL)def usage(): print "CSVloader -h" print "CSVloader -f bsc.csv bts.csv" print "CSVloader -d dir1 dir2" print "CSVloader -f bsc.csv bts.csv -s ," print "CSVloader -d dir1 dir2 -s ," print "CSVloader -s , -f bsc.csv bts.csv" print "CSVloader -s , -d dir1 dir2" print " Options include:" print " -h [help] - 打印帮助" print " -s [separtor] - 使用的分割符，默认 tab" print " -f [fileName] - 文件名，可以指定多个" print " -d [dirName] - 文件夹，可以指定多个" print print " 注意：文件必须是 GBK 编码"if __name__ == "__main__": if "-h" in sys.argv: usage() elif "-d" in sys.argv: separtor = "" idxf = sys.argv.index("-d") if "-s" in sys.argv: idxs = sys.argv.index("-s") separtor = sys.argv[idxs+1] if idxs>idxf: for i in sys.argv[idxf+1:idxs]: for j in os.listdir(i): loadCSV(os.path.join(i, j), separtor) else: for i in sys.argv[idxf+1:]: for j in os.listdir(i): loadCSV(os.path.join(i, j), separtor) else: for i in sys.argv[idxf+1:]: for j in os.listdir(i): loadCSV(os.path.join(i, j), separtor) elif "-f" in sys.argv: separtor = "" idxf = sys.argv.index("-f") if "-s" in sys.argv: idxs = sys.argv.index("-s") separtor = sys.argv[idxs+1] if idxs>idxf: for i in sys.argv[idxf+1:idxs]: loadCSV(i, separtor) else: for i in sys.argv[idxf+1:]: loadCSV(i, separtor) else: for i in sys.argv[idxf+1:]: loadCSV(i, separtor) else: usage()（WinXP SP2，Oracle 11下使用通过，Oracle数据库使用GBK编码，csv文件使用GBK编码）
注意一下，小工具拿到文件后，
会以文件名作为表名，开始造表(create)，
字段全部为字符串，各个字段长度会根据
第一批读入的数据进行计算(第一批读1M，可以调，防大文件用的)，
还要注意一下
MySQL版本与Oracle版本有点区别，
MySQL版本有自动删除表功能，先试着删除一遍，才会建表，导入数据；
Oracle版本没有加删除表功能，只有建表导入数据过程。
(MySQL一般只在自己本本上使用，所以导入，改改文件再导入方便，无需去手动删除表，
Oracle一般会在服务器上导数据用，怕删掉不该删除的东西，所以未加这个功能)
再有MySQL版用UTF8编码，Oracle版用的GBK编码，这个跟数据库的设置有关
UTF8编码与GBK编码转换，可参考使用文本学习笔记3的批量转换工具
当然，文件不多也可以用文本编辑器手动另存转换。
贾
卢
惨
御
雏
皿
陨
褶
苷
嫌
盯
铡