python, stored-procedure, mysql, flask, run api

$ vi api.py

------------------------------------------------

from flask import Flask

from flask_restful import Resource, Api

from flask_restful import reqparse

from flaskext.mysql import MySQL

app = Flask(__name__)

api = Api(app)

'''INPUT

{"email": "gyunseul9@gmail.com","username": "gyunseul9","password": "gyunseul10"}

END'''

#mysql connection

mysql = MySQL()

app.config['MYSQL_DATABASE_USER'] = 'USER_ID'

app.config['MYSQL_DATABASE_PASSWORD'] = 'PASSWORD'

app.config['MYSQL_DATABASE_DB'] = 'DATABASE'

app.config['MYSQL_DATABASE_HOST'] = 'DATABASE_ENDPOINT'

mysql.init_app(app)

class BlankPage(Resource):

def get(self):

return {'status':'200'}

class CreateUser(Resource):

def post(self):

#return {'status':'success'}

try:

parser = reqparse.RequestParser()

parser.add_argument('email', type=str)

parser.add_argument('username', type=str)

parser.add_argument('password', type=str)

args = parser.parse_args()

_userEmail = args['email']

_userName = args['username']

_userPassword = args['password']

conn = mysql.connect()

cursor = conn.cursor()

cursor.callproc('sp_create_user', (_userEmail, _userName, _userPassword))

data = cursor.fetchall()

if len(data) is 0:

conn.commit()

return {'StatusCode': '200', 'Message': 'User creation success!!!'}

else:

return {'StatusCode': '1000', 'Message': str(data[0])}

#return {'Email': args['email'], 'UserName': args['user_name'], 'Password': args['password']}

except Exception as e:

return {'error': str(e)}

api.add_resource(BlankPage,'/')

api.add_resource(CreateUser,'/user')

if __name__ == '__main__':

app.run('0.0.0.0',debug=True)

---------------------------------

$ vi user.sql

---------------------------------

CREATE TABLE user (

user_id INT NOT NULL AUTO_INCREMENT,

email VARCHAR(80) NULL,

username VARCHAR(45) NULL,

password VARCHAR(45) NULL,

PRIMARY KEY (user_id));

---------------------------------

$ vi sp_create_user.sql

---------------------------------

REATE PROCEDURE `sp_create_user`(

IN p_email varchar(80),

IN p_username varchar(45),

IN p_password varchar(45)

)

BEGIN

IF ( select exists (select 1 from user where username = p_username) ) THEN

select 'Username Exists !!';

ELSE

insert into user (

email,

username,

password

) values (

p_email,

p_username,

p_password

);

END IF;

END

--------------------------------

from http://lonbehold.tistory.com/134 by ccl(A) rewrite - 2020-03-06 18:20:29