I made a code allowing me to GET data from an API and insert it into a database. But I encounter a problem my api contains about 20 million data and to insert everything in my database it will take me 43 days :)
I think the problem comes from the fact that I insert the data one by one.
That's why I'm looking for how to execute all my queries at once.
The problem is that I get my data as a list[dict] like that :
<class 'list'>
{
'siren': '000325175',
'nic': '00016',
'dateCreationEtablissement': '2000-09-26',
'uniteLegale': {'denominationUniteLegale': None, 'nomUniteLegale': 'JANOYER', 'prenomUsuelUniteLegale': 'THIERRY'},
'adresseEtablissement': {
'numeroVoieEtablissement': None,
'libelleVoieEtablissement': 'MANIHI COTE MONTAGNE TUAMOTU',
'codePostalEtablissement': '98770',
'libelleCommuneEtablissement': 'MANIHI',
}
}
Currently my code looks like this :
def insertGlobalBdd(self, rows : list[dict]) :
cursor = self.bdd.cursor()
for element in rows:
rows.append((element["siren"], self.TestFormat(element["uniteLegale"]["nomUniteLegale"]),
self.TestFormat(element["uniteLegale"]["prenomUsuelUniteLegale"]), self.TestFormat(element["uniteLegale"]["denominationUniteLegale"]),
element["dateCreationEtablissement"]))
cursor.execute("""INSERT INTO %s.dbo.Entreprise (siren, nomUniteLegale, prenomUsuelUniteLegale, denominationuniteLegale, dateCreationEtablissement) VALUES ('%s', '%s', '%s', '%s', '%s')""".format(",".join(str(i) for i in rows)))
self.bdd.commit()
would you have any idea how to do it?
CodePudding user response:
You can do with 'executemany'.
example
task_all[
(1, "Jahanzabe", 30),
(2, "Steve", 20),
(3, "Haider", 30),
(....All Others...)]
cur.executemany('Insert in to Table_name(id, name, no)
values(?,?,?)', task_all)
con.commit()