Python Create a Table in MySQL from Dictionary   

In this guide, I'll show you how to create a MySQL table from a Python dictionary. Two cases are covered:

• connection with PyMySQL and building SQL inserts
• SQLAlchemy creation of SQL table from a DataFrame

Notebook: 41. Create a table in SQL(MySQL Database) from python dictionary

Below are the steps that you may follow.

Step 1: Read/Create a Python dict for SQL

The first step is to read data from a JSON file, python dictionary or another data source. You can find the example data which will be used in this example:

{"id":1,"label":"A","size":"S"}
{"id":2,"label":"B","size":"XL"}
{"id":3,"label":"C","size":"XXl"}

below you can find the code which is going to read the JSON file and convert to a Python dict:

import pandas as pd

# read normal JSON with pandas
df = pd.read_json('/home/vanx/Downloads/old/normal_json.json')

data_dict = df.to_dict()

This is the starting dict which we are going to use:

{'id': {0: 1, 1: 2, 2: 3},
 'label': {0: 'A', 1: 'B', 2: 'C'},
 'size': {0: 'S', 1: 'XL', 2: 'XXl'}}

If you want to convert back a dict to a Pandas DataFrame then you can use:

df2 = pd.DataFrame.from_dict(data_dict)

Step 2: Pandas DataFrame to MySQL table with SQLAlchemy

This step shows how to create a new MySQL table (replace the old one if it exists) by converting Pandas DataFrame to SQL statement. First step is to connect to our MySQL DataBase:

# connect
from sqlalchemy import create_engine
cnx = create_engine('mysql+pymysql://test:pass@localhost/test')    

Explanation: connection to localhost, DataBase - test, user - test, password - pass

Now we are going to use DataFrame created in the previous step. This will create new table from our DataFrame data and drop the existing table:

# create table from DataFrame
df.to_sql('test', cnx, if_exists='replace', index = False)

Next we can check the results by directly as a Pandas DataFrame by using the previous connection:

# query table
df = pd.read_sql('SELECT * FROM test', cnx)
df.head()

Step 3: Python Create Table and Insert Records Into a MySQL Database

In this step we are going to:

• connect to MySQL DataBase by using PyMySQL
• create a table from our data
• insert data into the new table

The Python code below shows the connection string for MySQL to a database - test:

# connect
import pymysql

connection = pymysql.connect(host='localhost',
                             user='test',
                             password='pass',
                             db='test')
cursor = connection.cursor()

In case of successful connection we can jump to creation of new table from the Python dictionary by:

# Create table
cols = df.columns
table_name = 'test'
ddl = ""
for col in cols:
    ddl += "`{}` text,".format(col)

sql_create = "CREATE TABLE IF NOT EXISTS `{}` ({}) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;".format(table_name, ddl[:-1])
cursor.execute(sql_create)

This Python snippet creates a basic DDL for creation a new MySQL table:

CREATE TABLE IF NOT EXISTS `test` (`id` text,`label` text,`size` text) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Now we can insert data into newly created table by(the connection is not auto committed by default, so we must commit to save our changes):

# insert data
cols = "`,`".join([str(i) for i in df.columns.tolist()])

# insert dict records .
for i,row in data_dict:
    sql = "INSERT INTO `test` (`" +cols + "`) VALUES (" + "%s,"*(len(row)-1) + "%s)"
    cursor.execute(sql, tuple(row))
    connection.commit()

Finally we can read data by:

# read
sql = "SELECT * FROM test"
cursor.execute(sql)
result = cursor.fetchall()
for i in result:
    print(i)

The result is:

(1, 'A', 'S')
(2, 'B', 'XL')
(3, 'C', 'XXl')