Code Monkey Monday- Querying a MySQL Database with Python

1. Set up a MySQL Database on your computer.
2. Install the Python library MySQL-python from http://www.lfd.uci.edu/~gohlke/pythonlibs/
3. In your code, add the import statement “import MySQLdb” at the top.
4. Find the database host, username, password, and database of interest.
5. Set up a database connection in Python with a line similar to
“db_connection = MySQLdb.connect(host=’localhost’, user=’root’, passwd=’FluffyBunnies1234′, db=’MyAwesomeDatabase’)”
Here, my host is “localhost” because the database is stored locally on my desktop at work. I access the database from the “root” user account. You can also connect to external databases as long as you frame the connection string properly.
6. Set up a database cursor that you will use to execute queries. “cursor = db_connection.cursor()”
7. Structure up your SQL query. The query should be a string. All white space is treated equivalently, so you can have a space or a newline between parts of the SQL. Example “SQL = SELECT alpha, beta FROM parameter_table WHERE alpha>.5 ORDER BY beta ASC”.
8. Execute the query with the cursor. “cursor.execute(SQL)”
9. Fetch the output. “output = cursor.fetchall()”. If your query will return too many results to grab all at once, you can instead use the function fetchone() instead.
10. Your “output” variable will be a list of lists, where each inner list represents one line returned by query and each element of the inner list represents a column referenced in the SELECT statement. If my query above returned 500 parameter combinations, then I would have a list of 500 lists, where each of the 500 inner lists has two elements: alpha and beta.

Hope this gets you started. For a full documentation of the MySQLdb class, you could start here. Pay attention to make sure you’re reading the parts about the MySQLdb wrapper.

0 thoughts on “Code Monkey Monday- Querying a MySQL Database with Python

  1. Pingback: Code Monkey Monday- Transferring an Access Database to MySQL | Eric Webb

Leave a Reply

Your email address will not be published. Required fields are marked *