Writing Generic cursor.executemany() in python

Question

Im new to python, trying to write a generic function that has arguments as the list of dictionaries, tablename, dbusername, password and Insert different data in to different tables like below

dict_list=[{'name': 'sandeep', 'age': '10'}, {'name': 'sa', 'age': '0'}] another_dict_list=[{'area': 'AP', 'pin': '00'}, {'area': 'UP', 'pin': '01'}]

def func(listofdict, tablename, dbusername, dbpassword):
  #all connection and cursor code  here#
  query = """insert into tablename (dict keys) values(dict values)"""
  cursor.executemany(query, listofdict)

now ill call the above funtion

func(dict_list, nametable, dbuser, dbpassword)
func(another_dict_list ,areatable, dbuser, dbpassword)

Wanted to know if we can write a generic code for insertion of data using execute many

Answer 1

Assuming that the list of dictionaries contain at least one entry and each entry has the same keys, you can do the following:

def func(table_name, args):
    names = list(args[0].keys())
    column_names = ",".join(names)
    bv_names = ",".join(":" + n for n in names)
    sql = f"insert into {table_name} ({column_names}) values ({bv_names})"
    cursor.executemany(sql, args)

The first line assumes that there is at least one entry in args and that it is a dictionary and that all subsequent entries are also dictionaries with the same keys. If any of those assumptions are incorrect the function will fail.

The next few lines build up the SQL that you want to execute with the bind variables.

Answer 2

To access the data of the list of dictionaries you're to use a loop for doing that, cause its double list.

I created a database using sqlite3 so, you can get in touch with what I'm doing;

First, I created the database:

__author__ = "{D/R}"

import sqlite3
conn = sqlite3.connect("demo.sqlite", detect_types=sqlite3.PARSE_DECLTYPES)

conn.execute("CREATE TABLE IF NOT EXISTS dicListTable ("
             "name TEXT NOT NULL,"
             "age INTEGER NOT NULL)")

List Of dictionaries:

list_dic = [{'name': 'sandeep', 'age': 10}, {'name': 'sa', 'age': 14}]

Function:

def func(list_of_dict):

    cursor = conn.cursor()
    query = "insert into dicListTable (name, age) VALUES (?, ?)"
    for i in range(0, len(list_of_dict)):
        cursor.executemany(query, ((list_of_dict[i]["name"], list_of_dict[i]["age"]),))
    cursor.connection.commit()

Calling the function

func(list_dic)

close database

conn.close()

OUTPUT

+---------+-----+
|   name  | age |
+---------+-----+
| sandeep |  10 |
+---------+-----+
|    sa   |  14 |
+---------+-----+