Convert Json data into SQL table using Python

Considering your data which you want to store in Mysql DB. You can use a One-to-One relationship for storing in DB. Instead of using pandas DataFrame it can be handled properly using SQLAlchemy ORM. The following code gives a better understanding to solve this issue. Right now I have considered only few keys from fields key.

from sqlalchemy import Column, Integer, String, Text, DateTime, Float, Boolean, ForeignKey
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import create_engine

total_data = [
    {
        "fields": {
            "bkdate": None,
            "business_credit_card_total_balances": None,
            "created_at": "2016-08-04T00:29:03.067Z",
        },
        "model": "common.prequalresult",
        "pk": 1
    },
    {
        "fields": {
            "bkdate": "2016-08-04T00:29:03.067Z",
            "business_credit_card_total_balances": 23,
            "created_at": "2016-08-04T00:29:03.067Z",
        },
        "model": "common.prequalresult",
        "pk": 2
    },
    {
        "fields": {
            "bkdate": "asdfas",
            "business_credit_card_total_balances": 1111,
            "created_at": "2016-08-04T00:29:03.067Z",
        },
        "model": "common.prequalresult",
        "pk": 3
    }
]

engine = create_engine('mysql://user:password@localhost:5432/my_data', echo=False)

Base = declarative_base()


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    pk = Column(Integer, primary_key=False)
    model = Column(String(100), nullable=True)
    child = relationship('Comment', backref='article', uselist=False)


class Comment(Base):
    __tablename__ = 'comment'
    id = Column(Integer, primary_key=True, autoincrement=True)
    bkdate = Column(String(100), nullable=True)
    business_credit_card_total_balances = Column(Integer, nullable=True)
    created_at = Column(String(100), nullable=True)
    article_id = Column(Integer, ForeignKey('article.id'))


x = Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

for temp_data in total_data:
    pk = temp_data['pk']
    model = temp_data['model']
    bkdate = temp_data['fields']['bkdate']
    business_credit_card_total_balances = temp_data['fields']['business_credit_card_total_balances']
    created_at = temp_data['fields']['created_at']
    parent1 = Article(pk=temp_data['pk'], model=model)
    child = Comment(bkdate=bkdate,
                    business_credit_card_total_balances=business_credit_card_total_balances,
                    created_at=created_at,
                    article=parent1)

    session.add(parent1)
    session.add(child)

session.commit()

As field pk given in each dict can be duplicated, so I have created id as a primary key.

Output:

mysql> select * from article;
+----+------+----------------------+
| id | pk   | model                |
+----+------+----------------------+
|  1 |    1 | common.prequalresult |
|  2 |    2 | common.prequalresult |
|  3 |    3 | common.prequalresult |
+----+------+----------------------+

mysql> select * from comment;
+----+--------------------------+-------------------------------------+--------------------------+------------+
| id | bkdate                   | business_credit_card_total_balances | created_at               | article_id |
+----+--------------------------+-------------------------------------+--------------------------+------------+
|  1 | NULL                     |                                NULL | 2016-08-04T00:29:03.067Z |          1 |
|  2 | 2016-08-04T00:29:03.067Z |                                  23 | 2016-08-04T00:29:03.067Z |          2 |
|  3 | asdfas                   |                                1111 | 2016-08-04T00:29:03.067Z |          3 |
+----+--------------------------+-------------------------------------+--------------------------+------------+