Why is my mysql database not updating (using flask/python)

Question

I'm currently trying to add a user to the database when they enter in a username and password. However, the database is not updating at all and all of the info that was in the database just stays the same. I've tested out the code and it does run but the mysql.connect().commit() is not committing the code to the database.

my flask file:

from flask import Flask, jsonify, render_template, request, Response, json, redirect, url_for
from flaskext.mysql import MySQL
import re
from MyFunction import *
from get_tv_name import *

mysql = MySQL()
app = Flask(__name__, static_folder="", static_url_path='')
app.config['SECRET_KEY'] = 'F34TF$($e34D';
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ""
app.config['MYSQL_DATABASE_DB'] = 'accounts'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/register.html/', methods=['GET', 'POST'])
def register():
    error = None
    if request.method == 'POST':
        cursor = mysql.connect().cursor()
        cursor.execute("SELECT * from _accounts where Username='" + request.form['username'] + "' and Password='" + request.form['password'] + "'")
        data = cursor.fetchone()
        if data == None:
            cursor.execute("INSERT INTO _accounts VALUES (null,  '" + request.form['username'] + "', '" + request.form['password'] + "')")
            mysql.connect().commit
            redirect(url_for('index'))
        else:
            error = "it is complete"
return render_template('/register.html/', error=error)

if __name__ == '__main__':
    app.run(port=8080, debug=True)

my html:

<div class="container">
<div class="row">
    <div class="col-md-6 centered">
        <div class="panel panel-default">
            <div class="panel-heading"> <strong class="">Create an Account!</strong>

            </div>
            <div class="panel-body">
                <form class="form-horizontal" role="form" action="" method="post">
                    <div class="form-group">
                        <label for="inputFirstName3" class="col-sm-3 control-label">First Name</label>
                        <div class="col-sm-9">
                            <input type="email" class="form-control" id="inputEmail3" placeholder="First Name" required="">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="inputLastName3" class="col-sm-3 control-label">Last Name</label>
                        <div class="col-sm-9">
                            <input type="email" class="form-control" id="inputEmail3" placeholder="Last Name" required="">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="inputEmail3" class="col-sm-3 control-label">Username</label>
                        <div class="col-sm-9">
                            <input type="text" required="" placeholder="Username" class="form-control" name="username" value="{{
      request.form.username }}">
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="inputPassword3" class="col-sm-3 control-label">Password</label>
                        <div class="col-sm-9">
                           <input type="password" class="form-control" required="" placeholder="Password" name="password" value="{{
      request.form.password }}">
                        </div>
                    </div>
                    <div class="form-group last">
                        <div class="col-md-offset-3 col-md-3">
                            <button type="submit" class="btn btn-success btn-sm">Create account</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

I just re-tried it with both and .commit and .commit() are still working — user2146723
– user2146723, Commented Nov 6, 2014 at 19:37

dirn · Accepted Answer · 2014-11-06 19:55:40Z

9

The reason the database isn't updating is that you aren't committing the transaction. You create a connection with mysql.conn() and then get a cursor from it by adding .cursor(). When you go to commit your changes, you get a brand new connection by again calling mysql.conn().

What you want to do is capture a reference to the connection so that it can be used later. You'll also want to use a parameterized query to avoid things like SQL injection attacks.

conn = mysql.connect()
cursor = conn.cursor()

data = cursor.fetchone()
if data is None:
    cursor.execute("INSERT INTO _accounts VALUES (null, %s, %s)", (request.form['username'], request.form['password']))
    conn.commit()
    redirect(url_for('index'))  # I'm guessing you want to put a return here.
else:
    error = "it is complete"    # snip

Lastly, when comparing to None you'll want to use the is operator rather than equality. From PEP8

Comparisons to singletons like None should always be done with is or is not, never the equality operators.

answered Nov 6, 2014 at 19:55

dirn

21.1k7 gold badges75 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tmthyjames Over a year ago

Been trying to figure this out for hours! Thank you!

Collectives™ on Stack Overflow

Why is my mysql database not updating (using flask/python)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related