Python export Database query result as JSON for Calendar JS-CodePudding

I am querying my Database to then return the values and JSONIFY them into JSON format so that the "fullcalendar js" can parse them and show them on my front end.

My model of the DB is as follows :

class database(db.Model):
id = db.Column(db.Integer, primary_key=True)
userid = db.Column(db.Integer, nullable=False)
user = db.Column(db.String(200), nullable=False)
startdate = db.Column(db.String(10), nullable=False)
enddate = db.Column(db.String(10), nullable=False)
duration = db.Column(db.Integer, nullable=False)
approved = db.Column(db.Boolean, nullable=False)

The JSON format i need them to be is :

[
  {
    "title": "Long Event",
    "start": "2014-09-07",
    "end": "2014-09-10"
  }
]

What i have tried ("And failed!")

alldata = database.query.filter(database.approved == True).all()

events = {"title": [], "start": [], "end": []};

for row in alldata:
    events["title"].append(row.user.strip("[]"))
    events["start"].append(datetime.datetime.strptime(row.startdate,'%d/%m/%Y').strftime('%Y-%m-%d').strip("[]"))
    events["end"].append(datetime.datetime.strptime(row.enddate, '%d/%m/%Y').strftime('%Y-%m-%d').strip("[]"))

# Serializing json
json_object = json.dumps(events, indent=4)
jsontoreturn="[" json_object "]"
return jsontoreturn

The reason this fails is the resulting JSON is in the format of :

[{
"title": [
    "Ben"
],
"start": [
    "2021-12-29"
],
"end": [
    "2021-12-31"
]
}]

As you can see the Dictionary is adding Sqaure brackets and as a result the FullCalendar JS (https://fullcalendar.io/docs/events-json-feed) doesnt parse it correctly and fails.

Anyone got any ideas?

CodePudding user response：

I don't have the ability to actually test this against your value for alldata but I suspect the following will work:

alldata = database.query.filter(database.approved == True).all()

def process_row(row):
    # func which returns a dictionary like:
    # {"title": "Long Event", "start": "2014-09-07", "end": "2014-09-10" }
    return dict (title = row.user.strip("[]"),
                 start = datetime.datetime.strptime(row.startdate,'%d/%m/%Y').strftime('%Y-%m-%d').strip("[]"),
                 end = datetime.datetime.strptime(row.enddate, '%d/%m/%Y').strftime('%Y-%m-%d').strip("[]"),
                 )

# Make a list of dictionaries based on the input, `alldata`
events = [process_row(r) for r in alldata]

Then simply:

json_object = json.dumps(events, indent=4)

return json_object

OR, if you want to use Flask's jsonify function which also sets the Content-Type header:

return jsonify(events)