How to Connect Python to MySQL: Step‑by‑Step Guide with Code Samples

In Python, connecting to a MySQL database requires three steps: install a driver, use it, and optionally use an ORM for advanced usage.

1. Install

You must install a MySQL driver because Python only includes SQLite by default. The most common package is MySQLdb, which can be difficult to install with easy_install. Windows users can obtain a MySQLdb executable; Linux users can install python-mysqldb via sudo apt-get install python-mysqldb; macOS users can use MacPorts.

2. Use

After installation, restart the interpreter, then import and connect just like any other 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 database

cur = db.cursor()
cur.execute("SELECT * FROM YOUR_TABLE_NAME")
for row in cur.fetchall():
    print row[0]

This is a basic example; many other options exist.

3. Advanced Usage

For higher‑level abstraction you can use an ORM such as SQLAlchemy or the lightweight Peewee library.

import peewee
from peewee import *

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

class Book(Model):
    author = CharField()
    title = TextField()
    class Meta:
        database = db

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

Install Peewee with pip install peewee; no further configuration is required.