更新表中的所有行

Question

我正在尝试使用csv文件上载表中的所有行。有“ic\u dph”、“psc”、“name”、“mesto”等字段。。。在csv文件中，我想将丢失的记录添加到数据库中。你知道吗

with open('phs.csv') as f:
    for line in f:
        i+=1
        line = line.strip('\n').decode('cp1250')
        splt = line.split(';')

        if len(splt)==6:
            cur.execute(u"""UPDATE companies SET ic_dph=?,mesto=?,ulica_cislo=?,psc=? WHERE nazov=?""",(splt[0],splt[2],splt[4],splt[3],splt[1]))
        if i%50==0: print i
    conn.commit()

这段代码工作正常，但非常慢（几秒钟内有50条记录）。你知道吗

你有什么办法提高更新速度吗？你知道吗

编辑：根据gordonlinoff的评论，我已经将数据加载到表中，并尝试以这种方式更新它。但是SqliteStudio说SQL命令中有一个错误。你知道吗

UPDATE companies SET companies.ic_dph = financna_sprava.ic_dph, companies.mesto = financna_sprava.mesto, companies.ulica_cislo = financna_sprava.ulica_cislo, companies.psc=financna_sprava.psc WHERE companies.nazov = financna_sprava.nazov

Answer 1

Sqlite不支持连接中的更新，所以可以使用子查询进行更新

UPDATE companies
SET companies.ic_dph =
  (SELECT financna_sprava.ic_dph
   FROM financna_sprava
   WHERE companies.nazov = financna_sprava.nazov),
    companies.mesto =
  (SELECT financna_sprava.mesto
   FROM financna_sprava
   WHERE companies.nazov = financna_sprava.nazov),
    companies.ulica_cislo =
  (SELECT financna_sprava.ulica_cislo
   FROM financna_sprava
   WHERE companies.nazov = financna_sprava.nazov),
    companies.psc=
  (SELECT financna_sprava.psc
   FROM financna_sprava
   WHERE companies.nazov = financna_sprava.nazov)