python操作文件的库_python操作文件入库

1 #!/usr/bin/env python

2 #-*- coding: utf-8 -*-

3

4 """

5 @author:niklaus6 @date:201507237 """

8

9 importsys,os10 11 importtime,linecache12 importdatetime13 sys.path.append( "这里是下面模块导入的路径")14 from msgOutput importmsgOutput15 from oracleDb importoracleDb16 nameReplace="文件入库程序"

17

18 #***************************************************************************************************

19 #逻辑层

20 #***************************************************************************************************

21 def fileLoad(fileName,flag,tableName,*column_names):22 username = '数据库用户名'

23 password = '数据库密码'

24 dbname = '数据库库名'

25 db =oracleDb(username,password,dbname)26 conn =db.connectDB()27 columnList = [item.upper() for item in column_names] #将可变参数存入列表

28 checkFileName(fileName)29 checkTableName(tableName,username,conn)30 checkColums(tableName,username,columnList,conn)31 loadInfos(fileName,flag,tableName,columnList)32 truncateTable(tableName,conn)33 loadDate(fileName,flag,tableName,columnList,conn)34

35 #***************************************************************************************************

36 #检查文件是否在

37 #***************************************************************************************************

38 defcheckFileName(fileName):39 try:40 if notos.path.exists(fileName):41 print msgOutput("数据文件[%s]不存在!请仔细检查..." %fileName,0 )42 sys.exit(0)43 exceptException,e:44 raisee45

46 defloadInfos(fileName,flag,tableName,columnList):47 lenFile = int(os.popen('wc -l %s' %(fileName)).read().split()[0])48 print msgOutput("数据文件[%s]共有 %d 条数据待入库..." %(fileName,lenFile),1)49 print msgOutput("数据库表[%s]..." % (tableName.upper()),1)50 print msgOutput("数据库表中字段%s..." % columnList,1)51

52 #***************************************************************************************************

53 #检查数据库表是否存在

54 #***************************************************************************************************

55 defcheckTableName(tableName,username,conn):56 try:57 sqlTxt = r'''select object_name from all_objects58 where owner = '%s'59 and object_type = 'TABLE'60 and object_name = '%s'61 ''' %(username.upper(),tableName.upper())62 cursor =conn.cursor()63 cursor.execute(sqlTxt)64 gename =cursor.fetchall()65 if len(gename) ==0:66 print msgOutput('数据库中表[%s]不存在!请检查确认...' %(tableName.upper()),0)67 sys.exit(0)68 else:69 returnTrue70 exceptException,e:71 print msgOutput('数据库查询失败,原因: %s...' %str( e ).replace("\n","") ,0)72 conn.close()73 sys.exit(0)74

75

76 #***************************************************************************************************

77 #检查数据库表字段与输入的是否一致

78 #***************************************************************************************************

79 defcheckColums(tableName,username,columnList,conn):80 try:81 sqlTxt = r'''

82 select column_name83 from dba_tab_columns x84 where x.table_name = '%s'85 and x.owner = '%s'86 ''' %(tableName.upper(),username.upper())87 cursor =conn.cursor()88 cursor.execute(sqlTxt)89 gename =cursor.fetchall()90 columnDb = [ item[0] for item ingename ]91 for item incolumnList:92 if item not incolumnDb:93 print msgOutput('输入字段与数据库表中字段不一致!请仔细检查!...',0)94 sys.exit(0)95 exceptException,e:96 print msgOutput('数据库查询失败,原因: %s...' %str( e ).replace("\n","") ,0)97 conn.close()98 sys.exit(0)99 #sqlTxt = r'''

100 #select column_name

101 #from dba_tab_columns x

102 #

103 #

104 #

105 #gename = db.executeDB(sqlTxt)

106 #columnDb = [ item[0] for item in gename ]

107 #for item in columnList:

108 #if item not in columnDb:

109 #print msgOutput('输入字段与数据库表中字段不一致!请仔细检查!...' ,0)

110 #sys.exit(0)

111 #***************************************************************************************************

112 #清空将入库的表

113 #***************************************************************************************************

114 deftruncateTable(tableName,conn):115 try:116 sqlTxt = r'''

117 truncate table %s118 ''' %tableName119 cursor =conn.cursor()120 cursor.execute(sqlTxt)121 print msgOutput('数据库表[%s]已经清空!...' % tableName.upper(),1)122 returnTrue123 exceptException,e:124 conn.close()125 print msgOutput('数据库执行失败,原因: %s...' %str( e ).replace("\n","") ,0)126

127 #***************************************************************************************************

128 #文件处理

129 #***************************************************************************************************

130 def loadDate(fileName,flag,tableName,columnList,conn,recordDefault=200000):131 #recordDefault = 200000 if recordDefault > 200000 else recordDefault

132 try:133 lines = [ item.split(flag)[:len(columnList)] for item inlinecache.getlines(fileName)]134 lenCount =len(lines)135 listInput =[]136 for x inxrange(0,lenCount,recordDefault):137 listInput = lines[x:x+recordDefault]138 if(dataInput(tableName,columnList,conn,listInput)):139 print msgOutput('数据文件[%s]已经入库 %d 条数据...' %(fileName,len(listInput)),1)140 listInput =[]141 exceptException,e:142 print msgOutput ('文件处理失败,原因: %s...' %str( e ).replace("\n","") ,0)143

144 #***************************************************************************************************

145 #数据入库

146 #***************************************************************************************************

147 defdataInput(tableName,columnList,conn,listInput):148 try:149 sqlValues = ",:".join(columnList)150 sqlTxt = r'''

151 insert into %s values(:%s)152 ''' %(tableName,sqlValues)153 cursor =conn.cursor()154 cursor.executemany(sqlTxt,listInput)155 conn.commit()156 returnTrue157 exceptException,e:158 print msgOutput('数据文件入库失败,原因: %s...' %str( e ).replace("\n","") ,0)159 conn.close()160 sys.exit(0)161

162 defmain():163 flag = '|(这里是文件中数据与数据之间的分割符)'

164 fileName = '文件的名称(全路径)'

165 tableName = 'temp_file_load_cs'

166 print msgOutput('%s开始运行...' %nameReplace,1)167 fileLoad(fileName,flag,tableName,"mr_copyright","mr_songid","mr_songname","mr_singerid","mr_singer")168 print msgOutput('\033[1;32m%s序运行结束...\033[0m' %nameReplace,1)169

170 if __name__=='__main__':171 main()