MySQLdb returning old data when used with Flask Post

Question

I have a Flask website with a MySQL backend. I have a table called, users. It has two columns: username and name and one record:

name  username
Jim   testuser123

When a user clicks the button on the website, it updates the record to set the name to Bob then print all records where name = 'Bob'. Yet, it returns no results. If I refresh the connection before re-querying, then it does return one result as it should. Does the mdb.connect object cache data? How could it not be returning the correct results?

init.py:

import pandas as pd
import MySQLdb as mdb
from flask import Flask, render_template, request

def sql_con():
    return mdb.connect(host='myhost', port=3306, user='root', passwd='root', db='db', use_unicode=True, charset="utf8")

app = Flask(__name__)


def update_record():
    con = sql_con()
    cur = con.cursor()

    sql_string= "Update users set name = 'Bob' where username = 'testuser123'"

    cur.execute(sql_string)
    con.commit()


@app.route('/', methods=['GET', 'POST'])
def myroute():
    con = sql_con()

    if request.method == 'POST':
        update_record()
        print pd.read_sql("select * from users where name = 'Bob'", con=con)

    return render_template('1.html')

app.run( debug=True, port=5050)

1.html

<html>

<body>

    <form method="POST">
          <button id="mybutton" name='btn' value="mybutton">Submit Data</button>
    </form>
</body>

For this code to print one result, I must add con=sql_con() right after I call the update(), but before the print statement. Why is that?

Possibly related to https://stackoverflow.com/questions/29438471/how-to-prevent-pandas-psql-read-sql-query-from-fetching-cache — velblúd, Aug 02 '17 at 20:46
Possible duplicate of [Why are some mysql connections selecting old data the mysql database after a delete + insert?](https://stackoverflow.com/questions/9318347/why-are-some-mysql-connections-selecting-old-data-the-mysql-database-after-a-del) — Peter Brittain, Aug 07 '17 at 16:55

score 1 · Accepted Answer · answered Aug 06 '17 at 13:31

In general it is a good practice to use an ORM binding (i.e. Falsk-SQLAlchemy) with web frameworks (manages connection pools, automates commit/rollback, ...) even if an ORM seems overkill for a simple application.

Otherwise, avoid using multiple connections to the same database in the same request if you prefer manage this at low level (database connections).

Try this instead:

import pandas as pd
import MySQLdb as mdb
from flask import Flask, render_template, request

def sql_con():
    return mdb.connect(host='myhost', port=3306, user='root', passwd='root', db='db', use_unicode=True, charset="utf8")

app = Flask(__name__)


def update_record(con):
    cur = con.cursor()

    sql_string= "Update users set name = 'Bob' where username = 'testuser123'"

    cur.execute(sql_string)
    con.commit()


@app.route('/', methods=['GET', 'POST'])
def myroute():
    con = sql_con()

    if request.method == 'POST':
        update_record(con)
        print pd.read_sql("select * from users where name = 'Bob'", con=con)

    return render_template('1.html')

app.run( debug=True, port=5050)

If you want to scale a real app based on such solution, you should consider pulling an opened connection from a global connections pool. Creating a new db connection (at each HTTP request) may be time expensive.

I was talking about ORM binding (Object Relational Mapper) in place of low level bindings as in your code. Google "flask sqlalchemy" to get examples. Such ORMs takes care for you of synchronizing web transactions with DB transactions, connection pool management, sql injections filtering, sql quoting, object <-> db row automated conversion, ... — glenfant, Aug 07 '17 at 16:26

MySQLdb returning old data when used with Flask Post

1 Answers1