0

I'm hoping someone can help me? I'm trying to populate a MySQL database using Python 2.7 and the MySQLdb library. I've written the sample script below but no matter what I try, I can't get it to work.

The code runs without an error (it prints the 'Done' when I run the code but that's it) and when I try the query directly in phpMyAdmin it works fine, for some reason I can't get it to work from within Python.

Have I missed something? Is there something I need to activate in phpMyAdmin to get it to work? I've been searching around for most of the afternoon for an answer, when other people have similar problems though they tend to get error messages (which I don't). 

import MySQLdb

# Define the database access details
DB_HOST = "localhost"
DB_USER = "username"
DB_PASSWORD = "secretpassword"
DB_NAME = "TestDatabase"

# Establish the connection to the database
db = MySQLdb.connect(
                     host=DB_HOST,
                     user=DB_USER,
                     passwd=DB_PASSWORD,
                     db=DB_NAME
                     )
cur = db.cursor()

Query = "INSERT INTO SearchResults ( `TimeStamp`, `SearchTerm`, `SearchResult`) VALUES ('2013-06-22 17:28:09', 'TestSearchTerm', 'Test Search Result');"
cur.execute(Query)

print"Done"

This is my first experience with any of this so if I've overlooked something obvious, please forgive me! Any help would be appreciated!

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125
AdamDynamic
  • 761
  • 6
  • 15
  • 29

1 Answers1

2

You probably have to commit()your changes.

As this is your "first experience" some explanations:

Depending on the capabilities of your "database engine", when you modify your data, your changes are not immediately visible to the "outside world". Loosely speaking, your changes are "private" to your database connection.

This is a very important feature. Among others, this has for benefits to allow you to do several modifications -- and "publish" them all at once. An other benefit is, if at some point you decide the modifications are not valid, you could "cancel" them without having disturbed any other user of your database.

One minor drawback of that, is you have to explicitly commit your change once your are ready to publish them. Otherwise, they just vanish once the connection is closed. You also have to opportunity to set "autocommit" -- that is there will be an implicit commit after each request to the DB. Depending on your needs this could be an option. To summarize:

  • to "publish" your change to the DB: use db.commit(),
  • to "cancel" your changes to the DB: use db.rollback() or just close the connection without commit,
  • to commit implicitly after each request: db.autocommit(True)

DBA and other database experts will probably yell at me for the oversimplification of the above paragraphs. But I hope that will help you to understand what was going on !

Sylvain Leroux
  • 50,096
  • 7
  • 103
  • 125