Insert a list of dictionaries into an SQL table using python

Question

I'm making my first steps using python and sql databases and still am not sure which package to use and how. I have a list with approx 300k dictionaries each with roughly about 20 keys. These dicts shall be inserted into an SQL table.

In my opinion the advantahe of the list of dict approach is, that I explicitly name the columns in which I want to enter specific values. (It might be, that this is not a good approach)

Let me present a more specific example catching the essentials of my problem. The table consists of three columns: ID (Integer), Price (Decimal), Type (string). Type supports null values.

The keys of my dict have the same name and the list of dicts might look like this:

lst = [{'ID':1, 'Price': '9.95', 'Type': None}, 
       {'ID':2, 'Price': '7.95', 'Type': 'Sports'}, 
       {'ID':3, 'Price': '4.95', 'Type': 'Tools'}, ...]

So the questions that arise are the following:

1. Is the approach using dicts the right? (Note that I have 20 columns)
2. If yes/or no: How should one perform such a query efficiently?
3. Is it necessary to convert the prices to Decimal and before the SQL statement, or can this be achieved 'on-the-fly'
4. Is the None value automatically converted to null, or is there extra work to be done?

Answer 1

Assuming you are using a Python Database API specification compliant database driver.

Type conversions (questions 3 and 4) should be handled by the database driver out-of-the-box.

As for the 2), there is executemany():

cursor.executemany("""
    INSERT INTO 
        mytable
        (id, price, type)
    VALUES
        (%(id)s, %(price)s, %(type)s)
""", lst)

Answer 2

mydb = MySQLdb.connect(host='',    # your host_name
                       user='',    # your username
                       passwd='',  # your password
                       db=''       # your database
                       )
cur= mydb.cursor()
insert_query = "INSERT INTO table_name(feild_1,feild2,feild3) VALUES ( %(id)s, %(price)s, %(type)s);"
cur.executemany(insert_query, lst)
mydb.commit

Answer 3

Answers to your questions:

• No problem with using a list of dictionaries
• down below is the full application the handle your case
• it is not necessary to convert the prices to Decimal, in this example in MySQL we declare price as decimal, but in the list, it was set as a string also as integer, but it's saved as decimal
• The None value automatically converted to null

from tkinter import *
import mysql.connector as myConnector
from tkinter import messagebox
from mysql.connector import Error
def insert(table,lst):

    myList = listNestedDictForTblInsert(lst)
    print(myList)
    
    mySqlStr = f'INSERT INTO {table}(ID, Price, Type) VALUES(%s,%s,%s)' 
    val = myList
    print(mySqlStr)
    print(val)
    myDb = myConnector.connect(host='localhost',
                               database = "libraryDb2",
                               user='root',
                               password='dhso')
    try:
       myCursor = myDb.cursor()
       myCursor.executemany(mySqlStr, val)
       myDb.commit()
       messagebox.showinfo("show info", "Data is saved successfully")
    except Error as e:
       messagebox.showinfo("show info", "Data is not saved")

    myDb.close()
    myCursor.close()


def listNestedDictForTblInsert(data):
#Convert the list of dictionaries into list of tuples
   myList = []
   for i in range(len(data)):
      myList1 = []
      for value in (data[i].values()):
         myList1.append(value)
      myList1 = tuple(myList1)   
      myList.append(myList1)
   return myList
#Output myList:
#[('Ralph', 45), ('Betty', 50), ('Joey', 45), ('Heather', 25)]
           
root = Tk()

lst = [{'ID':1, 'price': 9.95, 'type': None}, 
       {'ID':2, 'Price': '7', 'type': 'Sports'}, 
       {'ID':3, 'Price': 4, 'Type': 'Tools'}]
table = 'test1'
root.title("Testing Part")
btn = Button(root, text = "Insert Dictionary in MYSQL Table", width = 30, command = lambda : insert(table, lst))
btn.pack()
root.mainloop