CODEWITHSUNDEEP
pythonjsonsqlalchemy
Hi There
Hi There

Reputation: 187

SQLAlchemy insert from a JSON list to database

I have a list with a JSON like so:

print(type(listed)) # <class 'list'>
print (listed)

[
   {
       "email": "[email protected]",
       "fullname": "xg gf",
       "points": 5,
       "image_url", "https://imgur.com/random.pmg"
   },
   {
        ... similar json for the next user and so on
   }
]

I'm trying to insert them into my postgres database that has a model like this:

class Users(db.Model):
    __tablename__ = 'users'

    email = db.Column(db.String(), primary_key=True)
    displayName = db.Column(db.String())
    image = db.Column(db.String())
    points = db.Column(db.Integer())

But I'm quite stuck, I've tried several approaches but none worked, anyone can guide me with an example on how to do it properly?

Upvotes: 1

Views: 6332

Answers (3)

Andrew Allen
Andrew Allen

Reputation: 8062

Using marshmallow-sqlalchemy

  1. validate the incoming JSON
  2. create general utilities for loading and dumping data

Define schemas

schema.py

from marshmallow import EXCLUDE
from marshmallow_sqlalchemy import ModelSchema
from app import db

class UserSchema(ModelSchema):
    class Meta(ModelSchema.Meta):
        model = Users
        sqla_session = db.session

user_schema_full = UserSchema(only=(
    'email',
    'displayName',
    'image',
    'points'
))

utils.py

Exact details below don't matter but create general utility for going from JSON to ORM objects and ORM objects to JSON. schema_partial used for auto generated primary keys.

def loadData(data, schema_partial, many=False,
             schema_full=None, instance=None):
    try:
        if instance is not None:
            answer = schema_full.load(data, instance=instance, many=many)
        else:
            answer = schema_partial.load(data, many=many)
    except ValidationError as errors:
        raise InvalidData(errors, status_code=400)
    return answer


def loadUser(data, instance=None, many=False):
    return loadData(data=data,
                    schema_partial=user_schema_full,
                    many=many,
                    schema_full=user_schema_full,
                    instance=instance)

def dumpData(load_object, schema, many=False):
    try:
        answer = schema.dump(load_object, many=many)
    except ValidationError as errors:
        raise InvalidDump(errors, status_code=400)
    return answer


def dumpUser(load_object, many=False):
    return dumpData(load_object, schema=user_schema_full, many=many)

Use loadUser and dumpUser within api to produce clean flat code. api.py

@app.route('/users/', methods=['POST'])
def post_users():
    """Post many users"""
    users_data = request.get_json()
    users =  loadUser(users_data, many=True)
    for user in users:
        db.session.add(user)

    object_dump = dumpUser(users, many=True)

    db.session.commit()

    return jsonify(object_dump), 201

Upvotes: 1

c8999c 3f964f64
c8999c 3f964f64

Reputation: 1627

Here's a solution without pandas, using SQLAlchemy Core

create engine

engine = sqlalchemy.create_engine('...')

load the metadata using the engine as the bind parameter

metadata = sqalchemy.Metadata(bind=engine)

make a reference to the table

users_table = sqlalchemy.Table('users', metadata, autoload = True)

you can then start your inserts

for user in json:
    query = users_table.insert()
    query.values(**user)

    my_session = Session(engine)
    my_session.execute(query)
    my_session.close()

This creates a session for every user in json, but I thought you might like it anyway. Its very flexible and works for any table, you don't even need a model. Just make sure the json doesnt contain any columns that dont exist in the db (this means you will need to use "img_url" (column name) in both the json key and in the db column name)

Upvotes: 2

Onur Guven
Onur Guven

Reputation: 640

Here is an example json list, like you provided.

json = [
   {
       "email": "[email protected]",
       "fullname": "xg gf",
       "points": 5,
       "image_url": "https://imgur.com/random.pmg"
   },
    {
       "email": "[email protected]",
       "fullname": "o g",
       "points": 7,
       "image_url": "https://imgur.com/random_x.pmg"
   }
]

Now create an empty dataframe all_df and run iterations inside your json list. Each iteration creates a dataframe with the data from dictionary inside the list, transpose it and append to all_df.

import pandas as pd

all_df = pd.DataFrame()
for i in json:
    df = pd.DataFrame.from_dict(data=i, orient='index').T
    all_df = all_df.append(df)

Output: expected output

Now you can go ahead create a session to your database and push all_df

all_df.to_sql(con=your_session.bind, name='your_table_name', if_exists='your_preferred_method', index=False)

Upvotes: 1

Related Questions