Insert values in multiple tables SQLite in a single for loop

General Usage Performance
1

Is there a way to insert all those values in a SQLite data base in a single loop? I’ve wrote as much loops as tables I have with the same structure. How do I call variables in a loop?.

Thank you

blocks=readlines(“blocks.txt”);
fields=readlines(“fields.txt”);
reservoirs=readlines(“reservoirs.txt”);
basins=readlines(“basins.txt”);
als=readlines(“als.txt”);
wells=readlines(“wells.txt”);
production=readlines(“production.txt”);

for i=1:size(blocks,1)
SQLite.execute!(db,“INSERT INTO blocks VALUES $(blocks[i])”)
end

for i=1:size(fields,1)
SQLite.execute!(db,“INSERT INTO fields VALUES $(fields[i])”)
end

for i=1:size(reservoirs,1)
SQLite.execute!(db,“INSERT INTO reservoirs VALUES $(reservoirs[i])”)
end

for i=1:size(basins,1)
SQLite.execute!(db,“INSERT INTO basins VALUES $(reservoirs[i])”)
end

for i=1:size(als,1)
SQLite.execute!(db,“INSERT INTO als VALUES $(als[i])”)
end

for i=1:size(wells,1)
SQLite.execute!(db,“INSERT INTO wells VALUES $(wells[i])”)
end

for i=1:size(production,1)
SQLite.execute!(db,“INSERT INTO production VALUES $(production[i])”)
end

2

Since the operations on each dataset appear to be the same, you might be able to assign your data to an array and loop over it like so:

blocks=readlines(“blocks.txt”);
fields=readlines(“fields.txt”);
reservoirs=readlines(“reservoirs.txt”);
basins=readlines(“basins.txt”);
als=readlines(“als.txt”);
wells=readlines(“wells.txt”);
production=readlines(“production.txt”);

allData = [blocks,fields,...]

for data in allData
    for i in 1:size(data,1)
        SQLite.execute!(db,“INSERT INTO data VALUES $(data[i])”)
    end 
end

I have not tested the code, but something along those lines might work.

1 Like
3

Or perhaps something like this. Again, approximate, not tested.

fileNames = ["blocks","fields",...etc]

allData = [readlines(f*".txt") for f in fileNames]


for (data,name) in zip(allData,fileNames)
    for i in 1:size(data,1)
        SQLite.execute!(db,“INSERT INTO $name VALUES $(data[i])”)
    end 
end