CODEWITHSUNDEEP
mysqlflask
Navya Vaishnavi
Navya Vaishnavi

Reputation: 59

Python Flask Insert data into MySQL

I have a registration form like this:

<form action="{{url_for('app.register')}}" method="post">
            <input type="text" placeholder="First Name" name="fname" required/><br><br>
            <input type="text" placeholder="Last Name" name="lname" required/><br><br>
            <input type="text" placeholder="username" name="user" required/><br>
            <input type="password" placeholder="password" name="password" required/><br>
            <input type="password" placeholder="confirm password" name="cpassword" required/><br><br>
            <input type="text" placeholder="Email" name="email" required/><br><br>
            <select id="soflow" name="selection" required >
  <option value = "Default">Select a Security Question</option>
  <option value = "What is your first school">What is your first school?</option>
  <option value = "What is the name of your first pet">What is the name of your first pet?</option>
  <option value = "What is your first car">What is your first car?</option>
</select><br><br>
  <input type="text" placeholder="Security Answer" name="answer" required/><br>
            <input type="submit" value="Sign Up">

    </form>

And my python code to retrieve and insert data into database like this:

db = MySQLdb.connect(host="localhost", user="root", passwd="", db="osn")
cur = db.cursor()
@blueprint.route('/register/', methods=['GET', 'POST'])
def register():
    adduser()
    return render_template('reg.html')

@blueprint.route('/adduser/', methods=['GET', 'POST'])

def adduser():
    print("Entered")

    try:
        fname = request.form['fname']
        lname = request.form['lname']
        password = request.form['password']
        username = request.form['user']
        cpassword = request.form['cpassword']
        email = request.form['email']
        selection = request.form['selection']
        answer = request.form['answer']
        print fname,lname,password,username,cpassword,email,selection,answer
        cur.execute("INSERT INTO 'login'('fname','lname','username','password','email','question','answer') VALUES (%s,%s,%s,%s,%s,%s,%s,%s)",(fname,lname,username,password,cpassword,email,selection,answer))        
        print "Registered"
    except Exception as e:
       return(str(e))

The problem here is it is executing upto the print statement above insert statement it does not show any error but data is not being inserted. Where have I gone wrong. Please do consider that Im new to Flask. Thanks in advance :)

Upvotes: 2

Views: 29433

Answers (3)

hjpotter92
hjpotter92

Reputation: 80639

Looking at the query, I notice that:

INSERT INTO
    'login'('fname',
            'lname',
            'username',
            'password',
            'email',
            'question',
            'answer')
VALUES (%s,
        %s,
        %s,
        %s,
        %s,
        %s,
        %s,
        %s)

You are putting the table-name, as well as column name as strings (quoted with single-apostrophe). Instead, they should either appear with enclosing, or should be enclosed inside backticks: (`).

With the python mysql library, you have to commit your changes with the cursor:

cur.execute(
    """INSERT INTO 
        login (
            fname,
            lname,
            username,
            password,
            email,
            question,
            answer)
    VALUES (%s,%s,%s,%s,%s,%s,%s,%s)""", (fname, lname, username, password, cpassword, email, selection, answer))
db.commit()
    print "Registered"

Also, you have defined 7 columns to insert the data into, whereas you are passing 8 values.

Upvotes: 1

Navya Vaishnavi
Navya Vaishnavi

Reputation: 59

The following syntax worked: "INSERT INTO login(u_id, fname, lname, username, password, email, question, answer) VALUES ('',%s,%s,%s,%s,%s,%s,%s)"

Upvotes: 1

Menno H&#246;lscher
Menno H&#246;lscher

Reputation: 585

Your insert is done, but you do not commit your data to the database. So after your session ends, it is rolled back.

Depending on the transaction isolation strategy of MySQL it may never become visible to other programs or transactions.

Upvotes: 1

Related Questions