Take and return json data in flask

json data structure: take the city data of package ticket website as an example, don't panic to get the data,

1. Analyze the data structure first. There are several large fields ('returnCode' and 'returnValue' fields. Only one field is defined and the other field is reserved (no need to deal with)

2 key table - > split 'returnValue' to determine the database table structure, ('A' [] city initial alphabet and city specific information field {} table)

3. Insert the obtained data into the database

4. Return the data of the database to the user in the form of JSON

(a) Data obtained:

}
  "returnCode": "0",
  "returnValue": {
    "A": [
      {
        "id": 3643,
        "parentId": 0,
        "regionName": "ABA",
        "cityCode": 513200,
        "pinYin": "ABA"
      },
      {
        "id": 3090,
        "parentId": 0,
        "regionName": "Aksu",
        "cityCode": 652901,
        "pinYin": "AKESU"
      },
      {
        "id": 3632,
        "parentId": 0,
        "regionName": "Alashan",
        "cityCode": 152900,
        "pinYin": "ALASHAN"
      },
      {
        "id": 899,
        "parentId": 0,
        "regionName": "Ankang",
        "cityCode": 610900,
        "pinYin": "ANKANG"
      },
      {
        "id": 196,
        "parentId": 0,
        "regionName": "anqing",
        "cityCode": 340800,
        "pinYin": "ANQING"
      },
      {
        "id": 758,
        "parentId": 0,
        "regionName": "Anshan",
        "cityCode": 210300,
        "pinYin": "ANSHAN"
      },
      {
        "id": 388,
        "parentId": 0,
        "regionName": "Anshun",
        "cityCode": 520400,
        "pinYin": "ANSHUN"
      },
      {
        "id": 454,
        "parentId": 0,
        "regionName": "Anyang",
        "cityCode": 410500,
        "pinYin": "ANYANG"
      }
    ],
B.. C.. D.. Z omits other cities starting with capital letters, for example, city names starting with A

(b) Table structure, create foreign key models.py

from App.ext import db
#Define the capital letter class of city name, at the outermost layer of data
class Letter(db.Model):
    id = db.Column(db.Integer,primary_key =True,autoincrement=True)
    letter = db.Column(db.String(8),unique=True,nullable=False)
#Define city class, nesting layer
class City(db.Model):
    id = db.Column(db.Integer,primary_key = True,autoincrement = True)
    parentId = db.Column(db.Integer,nullable = False,defaut=0)
    regionName = db.Column(db.String(30),nullable = False)
    cityCode = db.Column(db.Integer)
    pinYin = db.Column(db.String(128))
   #Create foreign key 'initial'
    first_letter = db.Column(db.String(8),db.ForeignKey(Letter.letter))

 

(c)addcities.py insert data:

from flask_restful.representations import json
from sqlalchemy.dialects.mysql import pymysql

def add_cities():
#Linked database
    db = pymysql.Connect(host= '10.0.118.135',user = 'root',password ='xxxxxxx',database = 'tpp6666',port = 3306)
    cursor = db.cursor()
   #Read the obtained data and traverse the data
    with open('citylist.json')as cl:
        returnValue = json.load(cl).get('returnValue')
        for key in returnValue:
            for city in returnValue.get(key):
                 db.begin()
                 #Insert data, with each uppercase letter as a field, in the form of a dictionary
                 cursor.execute(
                     'insert into city(id,parentId,regionName,cityCode,pinYin,first_letter) values({},{},"{}",{},"{}","{}");'.format(
                         city['id'], city['parentId'], city['regionName'], city['cityCode'], city['pinYin'], key))
                 db.commit()


if __name__ == '__main__':
    add_cities()

(d) CityAPI.py reads the data and returns it as JSON:

from flask_restful import Resource, fields, marshal_with

from App.models import Letter, City

#Format field:
city_fields = {
    'id': fields.Integer,
    'Parent number': fields.Integer(attribute='parentId'),#Alias attribute
    'Name': fields.String(attribute='regionName'),
    'Pinyin': fields.String(attribute='pinYin'),
    'City coding': fields.Integer(attribute='cityCode'),
    'Initials': fields.String(attribute='first_letter')

}
value_fields = {
    'A': fields.List(fields.Nested(city_fields)),
    'B': fields.List(fields.Nested(city_fields)),
    'C': fields.List(fields.Nested(city_fields)),
    'D': fields.List(fields.Nested(city_fields)),
    'E': fields.List(fields.Nested(city_fields)),
    'F': fields.List(fields.Nested(city_fields)),
    'G': fields.List(fields.Nested(city_fields)),
    'H': fields.List(fields.Nested(city_fields)),
    'J': fields.List(fields.Nested(city_fields)),
    'K': fields.List(fields.Nested(city_fields)),
    'L': fields.List(fields.Nested(city_fields)),
    'M': fields.List(fields.Nested(city_fields)),
    'N': fields.List(fields.Nested(city_fields)),
    'P': fields.List(fields.Nested(city_fields)),
    'Q': fields.List(fields.Nested(city_fields)),
    'R': fields.List(fields.Nested(city_fields)),
    'S': fields.List(fields.Nested(city_fields)),
    'T': fields.List(fields.Nested(city_fields)),
    'W': fields.List(fields.Nested(city_fields)),
    'X': fields.List(fields.Nested(city_fields)),
    'Y': fields.List(fields.Nested(city_fields)),
    'Z': fields.List(fields.Nested(city_fields)),
}
result_fields = {
    'returnCode': fields.Integer,
    'returnValue': fields.Nested(value_fields)
}

#The whole logic is defined here:
@marshal_with yes flask Built in Json Serialized method,
//In Django, json serialization is json.dumps()
class CityResrouce(Resource):
    @marshal_with(result_fields)
    def get(self):
        #Define the outer field as an empty dictionary {} to store data
        returnValue = {}

        # Get all the initials
        letters = Letter.query.all()

        for letter in letters:
            # Get all cities corresponding to each initial according to the initial
            # The result of the filter is a BaseQuery object.
            # If you allow the BaseQuery object directly, it will output the SQL statement
            # If you want to print all the data in BaseQuery, call the all() method to get all the data in BaseQuery
            cities = City.query.filter(City.first_letter == letter.letter)

            # dict = {letter.letter: cities}
            # print(dict)
            returnValue[letter.letter] = cities.all()

           
       

        return {'returnCode': 0, 'returnValue': returnValue}

 

Tags: JSON Attribute Database MySQL

Posted on Sun, 09 Feb 2020 09:05:50 -0800 by netzverwalter