How do I connect to a MySQL Database in Python?

Connecting to MYSQL with Python 2 in three steps

1 - Setting

You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install. Please note MySQLdb only supports Python 2.

For Windows user, you can get an exe of MySQLdb.

For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb (for debian based distros), yum install MySQL-python (for rpm-based), or dnf install python-mysql (for modern fedora distro) in command line to download.)

For Mac, you can install MySQLdb using Macport.

2 - Usage

After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.

Then it is just like using any other package :

#!/usr/bin/pythonimport MySQLdbdb = MySQLdb.connect(host="localhost",    # your host, usually localhost                     user="john",         # your username                     passwd="megajonhy",  # your password                     db="jonhydb")        # name of the data base# you must create a Cursor object. It will let#  you execute all the queries you needcur = db.cursor()# Use all the SQL you likecur.execute("SELECT * FROM YOUR_TABLE_NAME")# print all the first cell of all the rowsfor row in cur.fetchall():    print row[0]db.close()

Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.

3 - More advanced usage

Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

I strongly advise you to use it: your life is going to be much easier.

I recently discovered another jewel in the Python world: peewee. It's a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :

import peeweefrom peewee import *db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')class Book(peewee.Model):    author = peewee.CharField()    title = peewee.TextField()    class Meta:        database = dbBook.create_table()book = Book(author="me", title='Peewee is cool')book.save()for book in Book.filter(author="me"):    print book.title

This example works out of the box. Nothing other than having peewee (pip install peewee) is required.

Here's one way to do it, using MySQLdb, which only supports Python 2:

#!/usr/bin/pythonimport MySQLdb# Connectdb = MySQLdb.connect(host="localhost",                     user="appuser",                     passwd="",                     db="onco")cursor = db.cursor()# Execute SQL select statementcursor.execute("SELECT * FROM location")# Commit your changes if writing# In this case, we are only reading data# db.commit()# Get the number of rows in the resultsetnumrows = cursor.rowcount# Get and display one row at a timefor x in range(0, numrows):    row = cursor.fetchone()    print row[0], "-->", row[1]# Close the connectiondb.close()

Reference here

If you do not need MySQLdb, but would accept any library, I would very, very much recommend MySQL Connector/Python from MySQL: http://dev.mysql.com/downloads/connector/python/.

It is one package (around 110k), pure Python, so it is system independent, and dead simple to install. You just download, double-click, confirm license agreement and go. There is no need for Xcode, MacPorts, compiling, restarting …

Then you connect like:

import mysql.connector    cnx = mysql.connector.connect(user='scott', password='tiger',                              host='127.0.0.1',                              database='employees')try:   cursor = cnx.cursor()   cursor.execute("""      select 3 from your_table   """)   result = cursor.fetchall()   print resultfinally:    cnx.close()