How to make this Flask-mysql insert commit?

Question

I'm still using Flask-mysql.

I'm getting the database context (the mysql variable) just fine, and can query on the database / get results. It's only the insert that is not working: it's not complaining (throwing Exceptions). It returns True from the insert method.

This should be done inserting the record when it commits, but for some reason, as I watch the MySQL database with MySQL Workbench, nothing is getting inserted into the table (and it's not throwing exceptions from the insert method):

I'm passing in this to insertCmd:

"INSERT into user(username, password) VALUES ('test1','somepassword');"

I've checked the length of the column in the database, and copied the command into MySQL Workbench (where it successfully inserts the row into the table).

I'm at a loss. The examples I've seen all seem to follow this format, and I have a good database context. You can see other things I've tried in the comments.

def insert(mysql, insertCmd):
     try:
        #connection = mysql.get_db()
        cursor = mysql.connect().cursor()
        cursor.execute(insertCmd)
        mysql.connect().commit()
        #mysql.connect().commit
        #connection.commit()
        return True
     except Exception as e:
        print("Problem inserting into db: " + str(e))
        return False

score 7 · Accepted Answer · answered Oct 29 '17 at 06:02

7

You need to keep a handle to the connection; you keep overriding it in your loop.

Here is a simplified example:

con = mysql.connect()
cursor = con.cursor()

def insert(mysql, insertCmd):
     try:
        cursor.execute(insertCmd)
        con.commit()
        return True
     except Exception as e:
        print("Problem inserting into db: " + str(e))
        return False

If mysql is your connection, then you can just commit on that, directly:

def insert(mysql, insertCmd):
  try:
    cursor = mysql.cursor()
    cursor.execute(insertCmd)
    mysql.commit()
    return True
  except Exception as e:
    print("Problem inserting into db: " + str(e))
    return False
  return False

answered Oct 29 '17 at 06:02

Burhan Khalid

169,990
18
245
284

Thanks! Trying that now. Also, Laurent, thanks for pointing that out. Deleting the last return. – JakeJ Oct 29 '17 at 06:11
Works! Thanks a lot! No idea why my attempts in the comments didn't stumble into that, could've sworn I was committing on the connection. I'll have to examine it carefully now. – JakeJ Oct 29 '17 at 06:13
A classic usage is also to rollback the last sql execution on exception… – Laurent LAPORTE Oct 29 '17 at 06:17

score 1 · Answer 2 · answered Oct 29 '17 at 06:19

1

Apparently, you MUST separate the connect and cursor, or it won't work.

To get the cursor, this will work: cursor = mysql.connect().cursor()

However, as Burchan Khalid so adeptly pointed out, any attempt after that to make a connection object in order to commit will wipe out the work you did using the cursor.

So, you have to do the following (no shortcuts):

connection = mysql.connect()
cursor = connection.cursor()
cursor.execute(insertCmd)
connection.commit()

answered Oct 29 '17 at 06:19

JakeJ

2,361
5
23
35

I'm leaving Khalid's up as the answer and putting this up to help solidify it. – JakeJ Oct 29 '17 at 06:19
Here's the full repo with all the flask code involved: https://github.com/DiginessForever/flicklistBlog.git I've been doing all kinds of regex matches, etc. These latest changes will be committed soon (with a bunch of other minor fixes from the last few hours). – JakeJ Oct 29 '17 at 06:31

How to make this Flask-mysql insert commit?

2 Answers2