Check if user exists before inserting into Mysql

Issue

How do i go about something like this, I want to check if a user exists against a table in python, and if the user exists , it should report that the particular user exists, else if the user does not, it should register (insert the user into the mysql database)

So far, this is what my code is looking like

@app.route('/api/user',methods=['POST'])
def create_user():
    _json = request.json
    _email = _json['email']
    _phone = _json['phone']
    _password = _json['password']

    fullname = 'NULL'
    custID = '123456'
    
    #conn = mysql.connect()
    #cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor = mysql.connection.cursor()
    checkuser = 'select email from accounts where email = %s' # check if user exists here.
    query = "insert into accounts (email,phone,fullname,password,custID) values (%s, %s,%s, %s,%s)"
            #query = "update empData set name = %s, email = %s, phone = %s, address = %s, salary = %s"
    bindData = (_email, _phone, _password , fullname , custID)
    cursor.execute(query,bindData)  
    mysql.connection.commit()
    cursor.close()
    output = {'email':_email, 'phone':_phone, 'fullname':fullname, 'custID':custID, 'message':'ok'}

    return jsonify({'result':output}),200

How do I go about something like this, I started out flask a week ago.

Edits

This is what i been working on, but it complains about indentation. Code is looking like so

@app.route('/api/user', methods=['POST'])
def create_user():
    _json = request.json
    _email = _json['email']
    _phone = _json['phone']
    _password = _json['password']

    fullname = 'NULL'
    custID = '123456'
    cursor = mysql.connection.cursor()
    checkuser = 'select email from accounts where email = %s'
    bindData = (_email)
    cursor.execute(query,bindData)
    acc = cursor.fetchone()
    if acc:
        return jsonify({'message':'User exists, Please Login'})
    elif:
        query = "insert into accounts (email,phone,fullname,password,custID) values (%s, %s,%s, %s,%s)"
        bindData = (_email, _phone, _password , fullname , custID)
        cursor.execute(query,bindData)  
        mysql.connection.commit()
        cursor.close()
    output = {'email':_email, 'phone':_phone, 'fullname':fullname, 'custID':custID, 'message':'ok'}
    
    return jsonify({'result':output}),200

Edits 2

So I made some Edits for the second time, it just fires back Error 500 when i am testing with Postman.

My code is looking Thus

@app.route("/api/user", methods=["POST"])
def create_user():
    _json = request.json
    _email = _json["email"]
    _phone = _json["phone"]
    _password = _json["password"]

    fullname = "NULL"
    custID = "123456"
    cursor = mysql.connection.cursor()
    cursor.execute('select * from accounts where email = %s', _email)
    acc = cursor.fetchone()
    if acc:
        return jsonify({"message": "User exists, Please Login"})
    else:
        query = "insert into accounts (email,phone,fullname,password,custID) values (%s, %s,%s, %s,%s)"
        bindData = (_email, _phone, _password, fullname, custID)
        cursor.execute(query, bindData)
        mysql.connection.commit()
        cursor.close()
        output = {
            "email": _email,
            "phone": _phone,
            "fullname": fullname,
            "custID": custID,
            "message": "ok",
        }

    return jsonify({"result": output}), 200

it says this is where the Error is according to the Log

which is here cursor.execute('select * from accounts where email = %s', _email) Is there something i missed?

Solution

I got it to work!
I had to do some reading and searching thru, this gave me an idea of what to do. Its like searching thru a List or something in the Database to get adequate results

So i saw this,https://stackoverflow.com/questions/21740359/python-mysqldb-typeerror-not-all-arguments-converted-during-string-formatting

Then changed my code from this

cursor.execute('select * from accounts where email = %s', _email

to this :

cursor.execute('select * from accounts where email = %s', [_email]

And it gave the actual response I wanted it to give. Just in case it should help someone.

Thanks everyone.

Edit

Below is what the code Looks like after the work arounds.

@app.route("/api/user", methods=["POST"])
def create_user():
    _json = request.json
    _email = _json["email"]
    _phone = _json["phone"]
    _password = _json["password"]

    fullname = "NULL"
    custID = "123456"
    cursor = mysql.connection.cursor()
    cursor.execute("select * from accounts where email = %s", [_email])
    acc = cursor.fetchone()
    if acc:
        return jsonify({"message": "User exists, Please Login"})
    else:
        query = "insert into accounts (email,phone,fullname,password,custID) values (%s, %s,%s, %s,%s)"
        bindData = (_email, _phone, fullname, _password, custID)
        cursor.execute(query, bindData)
        mysql.connection.commit()
        cursor.close()
        output = {
            "email": _email,
            "phone": _phone,
            "fullname": fullname,
            "custID": custID,
            "message": "ok",
        }

    return jsonify({"result": output}), 200

Answered By – Mike

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0