现有一个包含用户信息的文件userinfo,内容格式如下:
insert into USERINFO (ID, USERNAME, PWDHASH, USEALIAS, ALIAS, REALNAME, INUSE, F_BRANCH_ID, MOBILEPHONE, TELEPHONE, EMAIL, LICENSECODE,
ADDRESS, POSTCODE, GENDER, F_CUSTOMER_ID, ISCORE, ACTIVETIME, EXPIRETIME, IP)
values (248, 'police', '94216d6c554585824505a697b354b2b1', null, '', 'realname', 1, 223, 'mobile', 'phone', '1@163.com', '', 'address', '', 'M', 163, null, to_date('11-04-2013',
'dd-mm-yyyy'), to_date('19-04-2013', 'dd-mm-yyyy'), '192.168.0.0');
想要将类似to_date('19-04-2013', 'dd-mm-yyyy')的内容替换为null。
import re
f= open(r'D:\userinfo.txt') #用户信息文件
f2 =open(r'D:\userinfo_handled.txt','w+') #经过替换操作后的文件
p ='to_date\(.*?\)' #注意()是正则中的特殊字符,需要转义
for i in f:
if re.search(p,i):
i=re.sub(p,'null',i)
f2.write(i)
f2.close()
f.close()
替换后的结果如下:
insert into USERINFO (ID, USERNAME, PWDHASH, USEALIAS, ALIAS, REALNAME, INUSE, F_BRANCH_ID, MOBILEPHONE, TELEPHONE, EMAIL, LICENSECODE,
ADDRESS, POSTCODE, GENDER, F_CUSTOMER_ID, ISCORE, ACTIVETIME, EXPIRETIME, IP)
values (248, 'police', '94216d6c554585824505a697b354b2b1', null, '', 'realname', 1, 223, 'mobile', 'phone', '1@163.com', '', 'address', '', 'M', 163, null,
null, null,
'192.168.0.0');
然后可以使用userinfo_handled.txt中的语句在mysql数据库中执行。