import json
import mysql.connector as mysql
import requests

class SQLConnector:
    def __init__(self):
        re=requests.get("http://viamigliara.ddns.net:8081/scatalog/sql")
        print (re.content)
        r=json.loads(re.content)
        self.db = mysql.connect(user=r["username"], password=r["password"], host=r["host"],
                                database=r["database"])
        self.cursor = self.db.cursor()

    def query(self, number):
        query = 'SELECT PASSENGERS FROM busdata WHERE NUMBER = ' + str(number) + ";"
        try:
            self.cursor.execute(query)
            stop = self.cursor.fetchone()
            obj = {
                "passengers": stop[0] #modificare
            }
        except:
            return None
        return json.dumps(obj)

    def close(self):
        self.cursor.close()
        self.db.close()

if __name__ == '__main__':
    t=SQLConnector()
    print(t.query(10))
    t.close()