from flask import Flask, request, jsonify, json
from flask_restful import Resource, Api
from sqlalchemy import create_engine
from json import dumps
import mysql.connector
from mysql.connector import Error


app = Flask(__name__)
api = Api(app)


#Online Database
try:
    dbcon = mysql.connector.connect(host='localhost',
                                       database='login',
                                       user='root',
                                       password='')
    if dbcon.is_connected():
        print('Connected to MySQL database')

    else:
        print('NOT Connected to MySQL database')

except Error as e:
    print(e)

class users(Resource):
    def get(self):
        cursor = dbcon.cursor()
        cursor.execute("SELECT * from tbl_kunde")
        return {'users': [i[0] for i in cursor.fetchall()]}

class user(Resource):
    def get(self,user_id):
        cursor = dbcon.cursor()
        query = cursor.execute("SELECT * from tbl_kunde WHERE id_kunde =%d "  %int(user_id))
        r = [dict((cursor.description[i][0], value)
              for i, value in enumerate(row)) for row in cursor.fetchall()]
        return jsonify({'user' : r})

class Tracks(Resource):
    def get(self):
        conn = db_connect.connect()
        query = conn.execute("select trackid, name, composer, unitprice from tracks;")
        result = {'data': [dict(zip(tuple (query.keys()) ,i)) for i in query.cursor]}
        return jsonify(result)

class Employees_Name(Resource):
    def get(self, employee_id):
        conn = db_connect.connect()
        query = conn.execute("select * from employees where EmployeeId =%d "  %int(employee_id))
        result = {'data': [dict(zip(tuple (query.keys()) ,i)) for i in cursor]}
        return jsonify(result)

api.add_resource(users, '/users') #
api.add_resource(user, '/user/<user_id>') # Route_1
api.add_resource(Tracks, '/tracks') # Route_2
api.add_resource(Employees_Name, '/employees/<employee_id>') # Route_3


if __name__ == '__main__':
     app.run(port='5002')