How can I Insert multiple rows with one query

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': 'siren_int',
    'nic': 'nic int',
    'dateCreationEtablissement': '2000-09-26',
    'uniteLegale': {'denominationUniteLegale': None, 'nomUniteLegale': 'last name', 'prenomUsuelUniteLegale': 'firstname'},
    'adresseEtablissement': {
        'numeroVoieEtablissement': None,
        'libelleVoieEtablissement': 'street name',
        'codePostalEtablissement': 'postal code',
        'libelleCommuneEtablissement': 'commune name',
    }
}

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?

Answer

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()

Advertisement

Answer