I got a huge load of data in CSV that I need to combine into one table in an SQLite database. The main table has columns with values pointing to rows in different tables from which I need to fetch the values. I made a simple spaghetti code in Python that works, and although very slow it was fine. Problem is, with the amount of data I need to transfer from this table (Names) it would take roughly 2 years using this script. The table structure is as such:

Table "Names":

For the above row the first table, I want it to be |12|John|Smith|James|

My need is to move the values from Names.name to the People.fname, People.lname, People.mname columns in the appropriate places. Is there any efficient way to do this? My SQL is a bit rusty and help would be much appreciated. If needed, I will import the CSV into the database as a table and work it from there, but I have no clue where to begin with. Here is the Python code I made that worked but was too slow:

import sqlite3

con = sqlite3.connect("database.db")
cursor = con.cursor()

with open("Names.csv", encoding='utf8') as f:
  l = f.readlines()
  f.close()
  
lines = [i.split(';') for i in l]
l = []
columns = ["fname", "lname", "mname"]

for i in lines:
  f = i[1].strip()
  f = f.replace("'", "''")
  for column in columns:
    query = f"UPDATE People SET {column}='{f}' WHERE {column}={i[0]}"
    print(query)
    cursor.execute(query)
    if int(i[0])  % 1000 == 0:
      con.commit()
  
con.commit()

And here is the updated version after importing the CSV into the database as a table:

import sqlite3

con = sqlite3.connect("database.db")
cursor = con.cursor()

query = 'SELECT id, name FROM Names'
cursor.execute(query)
results = cursor.fetchall()
columns = ["fname", "lname", "mname"]

for i in results:
    f = i[1]
    f = f.replace("'", "''")
    for column in columns:
        query = f"UPDATE People SET `{column}`='{f}' WHERE `{column}`={i[0]}"
        print(query)
        cursor.execute(query)
        if int(i[0])  % 1000 == 0:
            con.commit()
    
con.commit()

CodePudding user response：

1. Import CSV into the database via SQLite shell:
   Read intro, practice, then go to 8.5 here.
2. Update the table

UPDATE People AS p
SET (fname, lname, mname) = (f.name, l.name, m.name)
FROM Names AS f, Names AS l, Names AS m
WHERE (p.fname, p.lname, p.mname) = (f.id, l.id, m.id);