CODEWITHSUNDEEP
pythonmysqlflaskflask-mysql
JakeJ
JakeJ

Reputation: 2501

How to make this Flask-mysql insert commit?

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

Upvotes: 3

Views: 11989

Answers (2)

JakeJ
JakeJ

Reputation: 2501

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()

Upvotes: 1

Burhan Khalid
Burhan Khalid

Reputation: 174624

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

Upvotes: 8

Related Questions