forked from barnettrob/flask_restful
/
restful_mysql.py
115 lines (99 loc) · 3.71 KB
/
restful_mysql.py
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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
# RESTful API creation using Flask.
# Based off of the below tutorial but with changes to use MySQL
# https://codeburst.io/this-is-how-easy-it-is-to-create-a-rest-api-8a25122ab1f3
# For MySQL changes, see https://codehandbook.org/python-web-application-flask-mysql/
from flask import Flask
from flask_restful import Api, Resource, reqparse
from flaskext.mysql import MySQL
app = Flask(__name__)
api = Api(app)
mysql = MySQL()
# MySQL configurations.
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'flask_test'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
# Make MYSQL init call with app.
mysql.init_app(app)
class User(Resource):
def get(self, name):
# MySQL connection.
conn = mysql.connect()
cursor = conn.cursor()
user_query = "SELECT * FROM user WHERE name = %s"
cursor.execute(user_query, (name,))
user = cursor.fetchone()
if user:
return user, 200
return "User not found", 404
def post(self, name):
parser = reqparse.RequestParser()
parser.add_argument("age");
parser.add_argument("occupation")
args = parser.parse_args()
# MySQL connection.
conn = mysql.connect()
cursor = conn.cursor()
user_query = "SELECT * FROM user WHERE name = %s"
cursor.execute(user_query, (name,))
exists = cursor.fetchone()
if exists:
return "User with name {} already exists".format(name), 400
else:
# Insert.
insert_query = ("INSERT INTO user (name, age, occupation) VALUES (%s, %s, %s)")
cursor.execute(insert_query, (name, int(args["age"]), args["occupation"],))
conn.commit()
user_query = "SELECT * FROM user WHERE name = %s"
cursor.execute(user_query, (name,))
user = cursor.fetchone()
if user:
return user, 201
def put(self, name):
parser = reqparse.RequestParser()
parser.add_argument("age")
parser.add_argument("occupation")
args = parser.parse_args()
# MySQL connection.
conn = mysql.connect()
cursor = conn.cursor()
user_query = "SELECT * FROM user WHERE name = %s"
cursor.execute(user_query, (name,))
exists = cursor.fetchone()
if not exists:
return "User with name {} does ot exist".format(name), 400
else:
update_query = ("UPDATE user SET age = %s, occupation = %s WHERE name = %s")
cursor.execute(update_query, (int(args["age"]), args["occupation"], name,))
conn.commit()
user_query = "SELECT * FROM user WHERE name = %s"
cursor.execute(user_query, (name,))
user = cursor.fetchone()
if user:
return user, 200
def delete(self, name):
# MySQL connection.
conn = mysql.connect()
cursor = conn.cursor()
user_query = "SELECT * FROM user WHERE name = %s"
cursor.execute(user_query, (name,))
exists = cursor.fetchone()
if not exists:
return "User with name {} does ot exist".format(name), 400
else:
delete_query = ("DELETE FROM user WHERE name = %s")
cursor.execute(delete_query, (name,))
conn.commit()
return "{} is deleted.".format(name), 200
class List(Resource):
def get(self):
# MySQL connection.
conn = mysql.connect()
cursor = conn.cursor()
users_query = "SELECT * FROM user"
cursor.execute(users_query)
users = cursor.fetchall()
return users, 200
api.add_resource(User, "/user/<string:name>")
api.add_resource(List, "/users/list")
app.run(debug=True)