How to write a Python list of dictionaries to a Database?

Step #1: Create list of Python dictionaries

Let’s start by defining a simple list made of several dictionaries which we would like to save as records in a Database table:

course_lst = [
    {'Course_ID': 1, 'Course_Name': 'Python Data Visualization'},
    {'Course_ID': 2, 'Course_Name': 'Python Fundamentals'},
    {'Course_ID': 3, 'Course_Name': 'Excel Data Analysis'},
    {'Course_ID': 4, 'Course_Name': 'Power BI Advanced'}
]

Note: We could as well define a list of tuples to be imported into the db.

Step #2: Define your Database connection and create a table

We will use SQLite as our Database.

Let’s create the database table – we’ll first import the sqlite3 library.

import sqlite3 #1

db_conn = sqlite3.connect('hr_db.db') #2

cur = db_conn.cursor() #3

cur.execute('''CREATE TABLE IF NOT EXISTS courses
               (Course_ID INTEGER PRIMARY KEY, Course_Name TEXT)''') # 4

Explanation:

#1 – Import he SQLite Python package into your development project / script( on Jupyter, PyCharm, VSCode etc’).
#2 – Define a connection to a Database. This will create the SQLite database if it doesn’t exitsts.
#3 – Define a cursor – which is a database object that acts like a pointer to the Database records returned from a SQL statement.
#4 – Create a new table, named Courses in our database.

Step#3: Save your dictionary to a table

We will now go ahead and insert our Dictionary as multiple database table rows.


for course in course_lst:
    cur.execute("INSERT INTO courses VALUES (:Course_ID, :Course_Name )", course) #1

db_conn.commit() 
db_conn.close() #3

Explanation:

#1 – This SQl query will loop through the Python list of dictionaries defined in Step number 1 and insert each dictionary line by line as records in our Courses table.
#2 – Commit the transaction in the database and dispose the database connection.

Step #4: Verify that records were written to the Database

Using a couple of commands when can easily fetch some or all records that we just inserted from the Database table.

import sqlite3

db_conn = sqlite3.connect('hr_db.db') 

cur = db_conn.cursor()

cur.execute ("SELECT * FROM courses")

results = cur.fetchall()

for record in results:
 print(record)

db_conn.close()

Here are our results:

(1, 'Python Data Visualization')
(2, 'Python Fundamentals')
(3, 'Excel Data Analysis')
(4, 'Power BI Advanced')

Step #5: Write your database table to a pandas DataFrame

If you are using the pandas library, you could easily select your table records and write them into a Pandas DataFrame.

import sqlite3
import pandas as pd

db_conn = sqlite3.connect('hr_db.db')
courses_df = pd.read_sql_query("SELECT * FROM Courses", db_conn)

db_conn.close()

We can now look into the DataFrame records:

courses_df.set_index('Course_ID', inplace=True)
courses_df.head()

	Course_Name
Course_ID
1	Python Data Visualization
2	Python Fundamentals
3	Excel Data Analysis
4	Power BI Advanced