Am new to python and am using marshmallow serialization. Unable to use the nested schema. My code:
from sqlalchemy import Column, Float, Integer, String, Text, text,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
metadata = Base.metadata
class CompanyDemo(Base):
__tablename__ = 'company_demo'
company_id = Column(Integer, primary_key=True,
server_default=text("nextval('company_demo_company_id_seq'::regclass)"))
name = Column(Text, nullable=False)
address = Column(String(50))
location = Column(String(50))
class UsersDemo(Base):
__tablename__ = 'users_demo'
id = Column(Integer, primary_key=True,
server_default=text("nextval('users_demo_id_seq'::regclass)"))
company_id = Column(Integer,ForeignKey('company_demo.company_id'), nullable=False)
email = Column(String)
company = relationship('CompanyDemo')
Schema:
from marshmallow import Schema, fields, pprint
class CompanySchema(Schema):
company_id = fields.Int(dump_only=True)
name = fields.Str()
address = fields.Str()
location = fields.Str()
class UserSchema(Schema):
email = fields.Str()
company = fields.Nested(CompanySchema)
user = UserSchema()
user = UserSchema(many=True)
company = CompanySchema()
company = CompanySchema(many=True)
and my flask app:
from flask import Flask, jsonify, url_for, render_template
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from flask_sqlalchemy import SQLAlchemy
from model import CompanyDemo, UsersDemo
from schemas.userschema import user, company
app = Flask(__name__)
app.secret_key = "shiva"
def db_connect():
engine = create_engine('postgresql://ss@127.0.0.1:5432/test')
Session = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# create a Session
session = Session()
session._model_changes = {}
return session
@app.route('/company', methods=["GET", "POST"])
def get_all_company():
db = db_connect()
allcompany = db.query(CompanyDemo).join(UsersDemo).all()
return jsonify(company.dump(allcompany, many=True).data) # company is marshmallow schema
if __name__ == '__main__':
app.run(host='0.0.0.0', port=15418, debug=True)
Is there anything wrong in my code? I am facing problems with nested schema and unable to get the nested data in output.
The output is below:
[ { "address": "qqq ", "company_id": 1, "location": "www ", "name": "eee" }, { "address": "www ", "company_id": 2, "location": "qqq ", "name": "aaa" } ]
Self contained example using in-memory SQLite:
from flask import Flask, jsonify
from flask_sqlalchemy import SQLAlchemy
from marshmallow import Schema, fields, pprint
app = Flask(__name__)
app.config['DEBUG'] = True
app.config['SECRET_KEY'] = 'super-secret'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)
class CompanyDemo(db.Model):
__tablename__ = 'company_demo'
company_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.Text, nullable=False)
address = db.Column(db.String(50))
location = db.Column(db.String(50))
def __unicode__(self):
return u"{name} ({address})".format(name=self.name, address=self.address)
class UsersDemo(db.Model):
__tablename__ = 'users_demo'
id = db.Column(db.Integer, primary_key=True,)
company_id = db.Column(db.Integer, db.ForeignKey('company_demo.company_id'), nullable=False)
company = db.relationship('CompanyDemo')
email = db.Column(db.String)
def __unicode__(self):
return u"{email}".format(email=self.email)
class CompanySchema(Schema):
company_id = fields.Int(dump_only=True)
name = fields.Str()
address = fields.Str()
location = fields.Str()
class UserSchema(Schema):
email = fields.Str()
company = fields.Nested(CompanySchema)
user_schema = UserSchema()
company_schema = CompanySchema()
@app.route('/')
def index():
return "<a href='/dump_company'>Dump Company</a><br><a href='/dump_user'>Dump User</a>"
@app.route('/dump_user')
def dump_user():
user = UsersDemo.query.first()
return jsonify(user_schema.dump(user).data)
@app.route('/dump_company')
def dump_company():
company = CompanyDemo.query.first()
return jsonify(company_schema.dump(company).data)
def build_db():
db.drop_all()
db.create_all()
company = CompanyDemo(name='Test 1', address='10 Downing Street', location='wherever')
db.session.add(company)
user = UsersDemo(email='fred@example.com', company=company)
db.session.add(user)
db.session.commit()
@app.before_first_request
def first_request():
build_db()
if __name__ == '__main__':
app.run(debug=True, port=7777)