5 Best Ways to Insert Python Dictionaries into MySQL

by

💡 Problem Formulation: A common task in web and software development is transferring data from in-memory structures to a database. Specifically, developers often need to insert the contents of a Python dict into a MySQL table. The input in this scenario is a Python dict, and the desired output is a row in a MySQL database table where each key-value pair from the dictionary corresponds to a column-value pair in the table.

Method 1: Using MySQL Connector/Python

The MySQL Connector/Python provides a direct API to connect and interact with MySQL from Python. It allows the execution of SQL queries and is particularly useful for inserting dictionaries as it supports parameterized queries, which prevent SQL injection attacks.

Here’s an example:

import mysql.connector

config = {
  'user': 'username',
  'password': 'password',
  'host': '127.0.0.1',
  'database': 'mydatabase',
  'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

data_dict = {'name': 'John', 'age': 28}
placeholders = ', '.join(['%s'] * len(data_dict))
columns = ', '.join(data_dict.keys())
sql = "INSERT INTO users (%s) VALUES (%s)" % (columns, placeholders)

cursor.execute(sql, list(data_dict.values()))
cnx.commit()

cursor.close()
cnx.close()

The output would be the record inserted into the ‘users’ table in the MySQL database.

This code snippet establishes a connection to a MySQL database using the provided credentials and database name. A Python dictionary representing the data to insert is created, then a SQL query is constructed using the dictionary’s keys and values. The query is executed, safely parameterized with the dictionary values, and the transaction is committed. Finally, the cursor and connection are closed to free resources.

Method 2: Using SQLAlchemy Core

SQLAlchemy Core provides a Pythonic way to build SQL statements and interact with the database. It has the advantage of being database-agnostic and providing a higher level of abstraction to execute common SQL tasks like inserting a dictionary.

Here’s an example:

from sqlalchemy import create_engine, Table, Column, MetaData, String
from sqlalchemy.sql import insert

engine = create_engine('mysql+mysqlconnector://username:password@localhost/mydatabase')
metadata = MetaData()
users = Table('users', metadata, autoload_with=engine)

data_dict = {'name': 'Jane', 'age': 25}
stmt = insert(users).values(data_dict)

with engine.connect() as conn:
    conn.execute(stmt)
    conn.commit()

The output is the ‘users’ table in the MySQL database having a new row with the data from the Python dictionary.

In this method, we use SQLAlchemy Core to define a connection and metadata object. The ‘users’ table is loaded using reflection, and an `insert` statement is created by using the `values` method with the provided Python dictionary. The transaction is executed inside a context manager to handle the connection, and then committed. SQLAlchemy handles the insertion abstraction, which simplifies the code.

Method 3: Using pandas with SQLAlchemy

pandas, along with SQLAlchemy, provides a means to efficiently insert large amounts of data from a DataFrame structure, which can be easily constructed from Python dictionaries.

Here’s an example:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://username:password@localhost/mydatabase')

data_dict = {'name': 'Alice', 'age': 30}
df = pd.DataFrame([data_dict])

df.to_sql('users', con=engine, if_exists='append', index=False)

The output is a new row for ‘Alice’ inserted into the ‘users’ table in the MySQL database.

This snippet first uses pandas to create a DataFrame from a list of dictionaries where each dictionary represents a row of data. The DataFrame is then written to a MySQL table using the `to_sql` method, which appends the data to the existing ‘users’ table. The SQLAlchemy engine handles the connection, and pandas abstract the SQL insertion logic.

Method 4: Using PyMySQL

PyMySQL is a pure-Python MySQL client library, which is useful for those who prefer a minimalistic and straightforward approach to connect to MySQL and execute queries, including inserting a Python dictionary into a MySQL table.

Here’s an example:

import pymysql

conn = pymysql.connect(host='localhost', user='username', password='password', db='mydatabase')

data_dict = {'name': 'Eric', 'age': 22}
keys = ', '.join(data_dict.keys())
values = ', '.join(['%s'] * len(data_dict))

sql = 'INSERT INTO users (%s) VALUES (%s)' % (keys, values)

with conn.cursor() as cursor:
    cursor.execute(sql, tuple(data_dict.values()))
    conn.commit()

conn.close()

The output is a new record added to the ‘users’ table in MySQL with Eric’s data.

This example uses PyMySQL to create a connection to the MySQL database and prepares a SQL insert statement, just as with the MySQL Connector/Python. It uses parameterized queries to safely execute the insertion. A cursor is used within a context manager, and the transaction is committed before closing the connection.

Bonus One-Liner Method 5: Inserting with ORMs (like Django, SQLAlchemy ORM)

Object-Relational Mapping (ORM) tools like Django and SQLAlchemy ORM allow you to work with databases using high-level entities (models), mapping Python classes to database tables. This makes inserting Python dictionaries a One-Liner once the ORM setup is complete.

Here’s an example:

# Assuming Django ORM
from myapp.models import User

data_dict = {'name': 'Diana', 'age': 27}
User.objects.create(**data_dict)

The high-level output is the creation of a new ‘User’ object with Diana’s data in the database.

In this method, assuming the Django ORM setup, a model class `User` is defined representing the database table. We then use the ORM’s `create` method to insert a new record represented by a Python dictionary directly. The ORM abstracts away SQL statements, offering a very high-level API for database interactions.

Summary/Discussion

  • Method 1: MySQL Connector/Python. Offers a straightforward Pythonic API and is secure against SQL injection. However, it can be verbose and requires manual handling of connections and cursors.
  • Method 2: SQLAlchemy Core. Provides a good balance of abstraction and control, also database-agnostic. The downside is the additional layer of complexity and overhead.
  • Method 3: pandas with SQLAlchemy. Excellent for handling large datasets and simplifies the process of inserting dictionaries. It might be overkill for simple tasks and has more dependencies.
  • Method 4: PyMySQL. A lightweight library good for those who prefer a more manual approach without additional abstraction layers. It can be less beginner-friendly and lacks the advanced features of other librarians.
  • Bonus Method 5: ORMs. High-level and extremely efficient for developers familiar with ORM patterns. However, it abstracts away SQL completely, which might not be desired in all cases, and it requires understanding of the ORM’s design.