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
--------------------------------

댓글