Problem with inserting into MySQL database from Python

Question

I am having trouble inserting a record into a MySQL database from python. This is what I am doing.

def testMain2():
        conn = MySQLdb.connect(charset='utf8', host="localhost", user="root", passwd="root", db="epf")
        cursor = conn.cursor()

        tableName = "test_table"

        columnsDef = "(export_date BIGINT, storefront_id INT, genre_id INT, album_id INT, album_rank INT)"
        exStr = """CREATE TABLE %s %s""" % (tableName, columnsDef)
        cursor.execute(exStr)

        #Escape the record
        values = ["1305104402172", "12", "34", "56", "78"]                    
        values = [conn.literal(aField) for aField in values]

        stringList = "(%s)" % (", ".join(values))
        columns = "(export_date, storefront_id, genre_id, album_id, album_rank)"
        insertStmt = """INSERT INTO %s %s VALUES %s""" % (tableName, columns, stringList)
        cursor.execute(insertStmt)

        cursor.close()
        conn.close()

The table is created however nothing is in the table. I can run the INSERT statement successfully in Terminal with the same credentials.

Any suggestions on what I may be doing wrong?

Excuse me? Are you saying that printing insertStmt (not output of query exec) results in no output? — virtualeyes
– virtualeyes, Commented May 22, 2011 at 18:32
if that's the case then stringlist may be the culprit; can you print stringlist? Not a pythonista, you may want to post under python tag, this is likely a no brainer for experienced python user (apologies if that is you!) — virtualeyes
– virtualeyes, Commented May 22, 2011 at 18:35
I don't get any outputs or an error when I run the above code. — David
– David, Commented May 22, 2011 at 20:53

Daniel Roseman · Accepted Answer · 2011-05-22 20:56:10Z

9

You haven't committed the transaction.

conn.commit()

(The MySQLdb library sets autocommit to False when connecting to MySQL. This means that you need to manually call commit or your changes will never make it into the database.)

edited May 22, 2011 at 20:56

answered May 22, 2011 at 18:29

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

Collectives™ on Stack Overflow

Problem with inserting into MySQL database from Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related