search and replace in a file :: newbie help

[techie2go]

hi...
i m now updating an sql file

old file contains lines
insert into mobilebill values ('Apr 01, 03', 'OUT', '91804103253', 34,
3.2);
insert into mobilebill values ('Apr 01, 03', 'OUT', '91806392475', 84,
5.2);
insert into mobilebill values ('Apr 01, 03', 'OUT', '918317048193', 76,
7.6);

i want to replace Apr 01,03 with 2003-01-03 etc...

and my script is
============
import re
lines=open('mb.sql').readlines() #mb.sql contains the above lines
months={}
months['Jan']='01';months['Feb']='02';months['Mar']='03';months['Apr']='04'
months['May']='05';months['Jun']='06';months['Jul']='07';months['Aug']='08'
months['Sep']='09';months['Oct']='10';months['Nov']='11';months['Dec']='12'
for line in lines:
#print line
inp=line
cre2=re.search('.*(\w{3}\s+\d+\,\s+\d+).*',line)
(month,day,year)=cre2.group(1).split();
cre3=re.compile(',')
day=cre3.sub("",day)
year='20'+year
repstr=year+'-'+months[month]+'-'+day
cre4=re.compile('.*(\w{3}\s+\d+\,\s+\d+).*')
line=cre4.sub(repstr,line)
print line
=================
but the output is just
2003-01-04
and not

insert into mobilebill values ('2003-01-04', 'OUT', '91804103253', 34,
3.2);

help me in this regard, what did i do wrong...
thanks and regards
gowthaman B

 

[Peter Otten]

i m now updating an sql file

=================
but the output is just
2003-01-04
and not

insert into mobilebill values ('2003-01-04', 'OUT', '91804103253', 34,
3.2);

help me in this regard, what did i do wrong...

You can find out yourself if you throw in some print statements

for line in lines:
#print line
inp=line
cre2=re.search('.*(\w{3}\s+\d+\,\s+\d+).*',line)
(month,day,year)=cre2.group(1).split();
cre3=re.compile(',')
day=cre3.sub("",day)
year='20'+year
repstr=year+'-'+months[month]+'-'+day

print "X", repstr # OK, so far

cre4=re.compile('.*(\w{3}\s+\d+\,\s+\d+).*')

print "X", cre4.search(line).group(0) # Oops, matches the whole line
# so it will be replaced by the
# reformatted date

line=cre4.sub(repstr,line)
print line

The minimal fix is to change cre4

cre4=re.compile('(\w{3}\s+\d+\,\s+\d+)')

And here is an alternative:

import re
import sys

lines = open('mb.sql')

months = {
"Jan": 1,
"Feb": 2,
"Mar": 3,
"Apr": 4,
# ...
}
r = re.compile(r"(\w{3})\s+(\d+)\,\s+(\d+)")

def fix_date(match):
month, day, year = match.groups()
return "20%02s-%02s-%02s" % (year, months[month], day)

for line in lines:
sys.stdout.write(r.sub(fix_date, line))

You might also consider performing the conversion in the database.

Peter

 

[Peter Otten]

i m now updating an sql file

=================
but the output is just
2003-01-04
and not

insert into mobilebill values ('2003-01-04', 'OUT', '91804103253', 34,
3.2);

help me in this regard, what did i do wrong...

You can find out yourself if you throw in some print statements:

for line in lines:
#print line
inp=line
cre2=re.search('.*(\w{3}\s+\d+\,\s+\d+).*',line)
(month,day,year)=cre2.group(1).split();
cre3=re.compile(',')
day=cre3.sub("",day)
year='20'+year
repstr=year+'-'+months[month]+'-'+day

print "X", repstr # OK, so far

cre4=re.compile('.*(\w{3}\s+\d+\,\s+\d+).*')

print "X", cre4.search(line).group(0) # Oops, matches the whole line
# so it will be replaced by the
# reformatted date

line=cre4.sub(repstr,line)
print line

The minimal fix is to change cre4

cre4=re.compile('(\w{3}\s+\d+\,\s+\d+)')

And here is an alternative:

import re
import sys

lines = open('mb.sql')

months = {
"Jan": 1,
"Feb": 2,
"Mar": 3,
"Apr": 4,
# ...
}
r = re.compile(r"(\w{3})\s+(\d+)\,\s+(\d+)")

def fix_date(match):
month, day, year = match.groups()
return "20%02s-%02d-%02s" % (year, months[month], day)

for line in lines:
sys.stdout.write(r.sub(fix_date, line))

You might also consider performing the conversion in the database.

Peter

 

[techie2go]

thanks , i got it