How to convert SQL entry into specific form in python given an input file of multiple SQL entries?

Question

Let's say we have a text file containing a SQL entry on each line formatted as follows:

INSERT INTO TABLE1(NUM, TIMESTAMP) VALUES ('0', '2016-04-26 15:38:07')

Is there a way to format this into the format below using a Python script?

[(26 Apr 2016 15:38:07)|0]

The number NUM can be multiple digits so this can't be done just using hard coded indices.

So, given a text file input.txt, how would you convert each line into the new format and save it as output.txt?

Answer 1

What about regular expressions?

import re
from datetime import datetime
line = "INSERT INTO TABLE1(NUM, TIMESTAMP) VALUES ('0', '2016-04-26 15:38:07')"
m = re.search(r"VALUES \('(\d+)', '(.*)'\)", line)
num = m.group(1)  # => '0'
ts = m.group(2)  # => '2016-04-26 15:38:07'
date_str = datetime.strptime(ts, "%Y-%m-%d %H:%M:%S").strftime('%m %b %Y %H:%M:%S')
newline = '[({})|{}]'.format(date_str, num)  # => "[(2016-04-26 15:38:07')|0]"

More information or some code would help, the answer depends a lot in your data, the source, the format, etc...

Answer 2

Just an alternative to not using re.

import datetime
line = "INSERT INTO TABLE1(NUM, TIMESTAMP) VALUES ('0', '2016-04-26 15:38:07')"
num, date = line.split('(')[2].split(', ')
num = num.strip("'")
date = datetime.datetime.strptime(date, "'%Y-%m-%d %H:%M:%S')").strftime('%m %b %Y %H:%M:%S')
print('[({}), {}]'.format(date, num))