Accessing a MySQL DB in Python

And another small intro into a MySQL API. This time we want to fetch the phpbb_config table within a small Python script.

---

Because Python doesn’t enforce try-except blocks like Java I will leave them away this time so that the example code stays better readable.

Requirements

* On Ubuntu/Debian you need the python-mysqldb package which should pull the right version of the library for your version of Python as well as all other dependencies you need for accessing a MySQL database with Python * On Gentoo Linux you need the mysql-python package which should also install all necessary packages through the dependencies

How?

First step: Import the MySQLdb package and open a connection to the server:

import MySQLdb
conn = MySQLdb.Connection(host='192.168.0.1',user='myuser',passwd='mypassword',db='phpbb20')

Now that we have the connection we want to query the database. The Connection objection already has a query() method but we will use a different way which will give us some additional options:

cur = conn.cursor()

Here we receive a Cursor object from the Connection class which we can now use to execute our already known query:

cur.execute("SELECT * FROM phpbb_config")

If the query was successful, we can fetch the result from it:

res = cur.fetchall()

We don’t need the connection to the database anymore so we can close the cursor and the database connection:

cur.close()
conn.close()

Final step: Let’s iterate through the result set:

for row in res:
	print row

If you are following these steps, you should now see a tuple of tuples on your screen. That’s because we used the “normal” Cursor class. But what if we want to have the rows stored in Dicts instead of Tuples? As the word “normal” should have indicated, there are some other subclasses of the BaseCursor class available and one of them stores the rows as Dicts. Simply replace the line where we got the cursor from the connection with this:

cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)

If you then start your script again, you should see a listing of Dicts :-)

References

* API guides on MySQL.com * The pydocs of the MySQLdb package