How to insert this values into the database properly?

Question

I have a file name log.txt

It's structure it's kind of like this:

2022-04-28 22:33:02,290\\INFO\\Database connection established
2022-04-28 22:33:07,470\\INFO\\Files concatenation
2022-04-28 22:33:09,708\\INFO\\Table xxx_xxx created

I want to send this to a database I have previously made a connection to.

with open("test.log",'r') as data_file:
    values = [line.split("\\") for line in data_file]
    engine.execute('INSERT INTO control (log_date, debugType, messa) VALUES (%s, %s, %s)', values)

When I print the values that will be passed to the database it shows up like this:

[['2022-04-28 22:33:02,290', '', 'INFO', '', 'Database connection established\n'], ['2022-04-28 22:33:07,470', '', 'DEBUG', '', 'Files concatenation\n'], ['2022-04-28 22:33:09,708', '', 'DEBUG', '', 'Table xxx_xxx created\n'], ...]

First of all, I want to exclude the value that comes after the comma after the timestamp (e.g. on the first line: '2022-04-28 22:33:02,290', I want to take the ,290 out.

I also want to get rid of the empty strings where the \\ was.

Can you help me?

Answer 1

Your file seems to have additional characters inside of it that is why you are getting these empty strings. Naive and greedy solution:

final_list = []
with open("test.log",'r') as data_file:
    for line in data_file:
       line_list = line.rstrip('\n').split("\\")
       line_list[0] = line_list[0][:-4]
       final_list = list(filter(lambda l: l != '', line_list)) 
       final_list.append(final_line)

If you have list present (you completed reading and parsing into list) you could appply the same things:

1. To remove '' just do filter on each list

final_list = [ list(filter(lambda l: l != '', inside_l)) for inside_l in whole_list]

2. to remove last three elements from a string just do string[:-4] so iterate over whole list and get first element and apply slicing

for inside_list in final_list:
    inside_list[0] = inside_list[0][:-4]