How to match values from one file to another in Python-CodePudding

I have two files:

values_list.txt

val1, "Sean"
val11, "Smith"
val111, "25"
val2, "Bob"
val22, "Rogers"
val222, "30"
val3, "Michael"
val33, "Jones"
val333, "40"
val4, "Aaron"
val44, "Michaels"
val444, "41"
val5, "John"
val55, "Brown"
val555, "26"

insert_values.sql

insert into users(fname,lname,age) values(val1,val11,val111)
insert into users(fname,lname,age) values(val2,val22,val222)
insert into users(fname,lname,age) values(val3,val33,val333)
insert into users(fname,lname,age) values(val4,val44,val444)
insert into users(fname,lname,age) values(val5,val55,val555)

I need to generate a third file like so ....

insert into users(fname,lname,age) values("Sean","Smith","25")
insert into users(fname,lname,age) values("Bob", "Rogers", "30")
insert into users(fname,lname,age) values("Michael","Jones","40")
insert into users(fname,lname,age) values("Aaron","Michaels","41")
insert into users(fname,lname,age) values("John","Brown","26")

This is what I have so far ...

#Read each line of the text file 
valsfile = open('values_list.txt')
valscontents = valsfile.read()
valsline = valscontents.splitlines()

#Read each line of the sql file 
sqlfile = open('insert_values.sql')
sqlcontents = sqlfile.read()
sqlline = sqlcontents.splitlines()

Now I need to get each individual word from sqlline - and if it contains "val" - then grab the word and get the matching value from valsline - and write/append a new insert statement to a new file

Any help would be appreciated.

Thanks

CodePudding user response：

First, we create a dictionary to lookup values using values_list.txt then we iterate over all the lines in the sqlfile and replace the dictionary keys with their values. The code is as follows:

valsfile = open('values_list.txt')
valsline = valsfile.read().splitlines() 

d = {}
for i in valsline:
    i = i.split(',')
    d[i[0]] = i[1]

sqlfile = open('insert_values.sql')
sqlcontents = sqlfile.read()
sqlline = sqlcontents.splitlines() 

text = []
for i in sqlline:
    for word, initial in d.items():
        i = i.replace(word, initial)
    text.append(i '\n')

f = open("final_sqlfile.sql", "a")
f.writelines(text)
f.close()

The contents of final_sqlfile.sql are given below:

insert into users(fname,lname,age) values( "Sean", "Sean"1, "Sean"11)
insert into users(fname,lname,age) values( "Bob", "Bob"2, "Bob"22)
insert into users(fname,lname,age) values( "Michael", "Michael"3, "Michael"33)
insert into users(fname,lname,age) values( "Aaron", "Aaron"4, "Aaron"44)
insert into users(fname,lname,age) values( "John", "John"5, "John"55)

CodePudding user response：

This presupposes that the values are grouped in threes. Try this:-

with open('values_list.txt') as vlist:
    with open('insert_values.sql', 'w') as sql:
        vals = []
        for line in vlist.readlines():
            v = line.strip().split(',')
            if len(v) == 2:
                vals.append(v[1])
                if len(vals) == 3:
                    sql.write(f'insert into users(fname,lname,age) values({vals[0]},{vals[1]},{vals[2]})\n')
                    vals=[]