How do I connect to a MySQL Database in Python?

Connecting to MYSQL with Python in 3 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.

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

For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb in command line to download.)

For Mac, you can install MySQLdb using Macport.

2 - Usage

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

Then it is just like using another package :

#!/usr/bin/python
import MySQLdb

db = 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 need
cur = db.cursor() 

# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# print all the first cell of all the rows
for row in cur.fetchall() :
    print row[0]

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 writting SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

I strongly advice 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 peewee
from peewee import *

db = MySQLDatabase('jonhydb', user='john',passwd='megajonhy')

class Book(peewee.Model):
    author = peewee.CharField()
    title = peewee.TextField()

    class Meta:
        database = db

Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
    print book.title

Peewee is cool

This example works out of the box. Nothing other than having peewee (pip install peewee :-)) is required. No complicated setup. It's really cool.

Here's one way to do it.

#!/usr/bin/python
import MySQLdb

# connect
db = MySQLdb.connect(host="localhost", user="appuser", passwd="",
db="onco")

cursor = db.cursor()

# execute SQL select statement
cursor.execute("SELECT * FROM LOCATION")

# commit your changes
db.commit()

# get the number of rows in the resultset
numrows = int(cursor.rowcount)

# get and display one row at a time.
for x in range(0,numrows):
    row = cursor.fetchone()
    print row[0], "-->", row[1]

From here.

Oracle (MySQL) now supports a pure Python connector. That means no binaries to install: it's just a Python library. It's called "Connector/Python".

http://dev.mysql.com/downloads/connector/python/

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 result
finally:
    cnx.close()

As a db driver, there is also oursql. Some of the reasons listed on that link, which say why oursql is better:

• oursql has real parameterization, sending the SQL and data to MySQL completely separately.
• oursql allows text or binary data to be streamed into the database and streamed out of the database, instead of requiring everything to be buffered in the client.
• oursql can both insert rows lazily and fetch rows lazily.
• oursql has unicode support on by default.
• oursql supports python 2.4 through 2.7 without any deprecation warnings on 2.6+ (see PEP 218) and without completely failing on 2.7 (see PEP 328).
• oursql runs natively on python 3.x.

So how to connect to mysql with oursql?

Very similar to mysqldb:

import oursql

db_connection = oursql.connect(host='127.0.0.1',user='foo',passwd='foobar',db='db_name')
cur=db_connection.cursor()
cur.execute("SELECT * FROM `tbl_name`")
for row in cur.fetchall():
    print row[0]

The tutorial in the documentation is pretty decent.

And of course for ORM SQLAlchemy is a good choice, as already mentioned in the other answers.

Try using MySQLdb

There is a how to page here: http://www.kitebird.com/articles/pydbapi.html

From the page:

# server_version.py - retrieve and display database server version

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                        user = "testuser",
                        passwd = "testpass",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()
conn.close ()

MySQLdb is the straightforward way. You get to execute SQL queries over a connection. Period.

My preferred way, which is also pythonic, is to use the mighty SQLAlchemy instead. Here is a query related tutorial, and here is a tutorial on ORM capabilities of SQLALchemy.

For python 3.3

CyMySQL https://github.com/nakagami/CyMySQL

I have pip installed on my windows 7, just pip install cymysql

(you don't need cython) quick and painless