21 Aug 2017
Solving Q2
Notes:
About venv
- After
1virtualenv venv
actually a folder named venv is created in the current directory, enter -
1source venv/bin/activate
to enter the virtual environment stored in that folder. Anythings done in Step 2 after that venv will only effective inside that venv
About sqlite
- After
1sqlite3 charger
actually a db named charger is created in the current directory, You can rename it to charger.db without problems. - .databases can show the databases names in the .db
- .tables can show the tables in the db
- to import .csv, you need to change to .mode csv inside sqlite before import csv file
About cursor.fetchall(),
- It return a list of tuple. Although the each tuple has only one instance, you still need to claim the instance by using e.g. tuple[0], otherwise, a sequence is passed into the query. That maybe invalid .
code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
from flask import Flask, request from flask_restful import Resource, Api import sqlite3 from json import dumps from flask_jsonpify import jsonify ''' CREATE TABLE charging_stations( IP TEXT NOT NULL, CP_NO TEXT NOT NULL, CP_Status TEXT NOT NULL, Last_Update TEXT NOT NULL, Location TEXT NOT NULL ); charger type:{ type:"xx", charging info:[ { location:[ {info item},.. ], } ] } ''' app = Flask(__name__) api = Api(app) class Charger(Resource): def get(self): charging_info_list=[] conn = sqlite3.connect('charger.db') # connect to database cur = conn.cursor() cur.execute("select distinct Location from charging_stations") # This line performs query and returns json result location_q = cur.fetchall() for location in location_q: location_list=[] print (str(location)) l = str(location) #cur.execute("select CP_NO, CP_Status, Last_Update from charging_stations where Location = '%s'"%location) cur.execute("select CP_NO, CP_Status, Last_Update from charging_stations where Location =?",(str(location[0]),)) info_q = cur.fetchall() #print( len(info_q)) #print( info_q) #info_list=[] for info in info_q: info_itm_dict={'CP_NO':info[0],'CP_Status':info[1],'Last_Update':info[2]} location_list.append(info_itm_dict) location_dict={'%s' %(str(location[0])):location_list} charging_info_list.append(location_dict) charger_type_dict={'type':'Semi-quick charging station','charging info':charging_info_list} result = {'charger type':charger_type_dict} #print (result) return jsonify(result) api.add_resource(Charger, '/charger') # Route_1 if __name__ == '__main__': app.run(port='5002') |
