Connecting MySQL from Python: Installation, Basic Usage, and Advanced ORM Options

To use MySQL in a Python program you need to install a MySQL driver because Python only includes SQLite by default. The most common driver is MySQLdb, which can be installed via easy_install (though this may be difficult), or by using platform‑specific packages such as python-mysqldb on Linux ( sudo apt-get install python-mysqldb ) or downloading an executable for Windows, and via MacPorts on macOS.

After installing the driver, restart the interpreter and import the module just like any other package. Below is a minimal example that connects to a local MySQL server, creates a cursor, runs a SELECT statement, and prints the first column of each returned row.

<code>#!/usr/bin/python
import MySQLdb

# establish connection
db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="john",      # your username
                     passwd="megajonhy", # your password
                     db="jonhydb")      # name of the database

# create a cursor object to execute queries
cur = db.cursor()

# execute a simple SELECT query
cur.execute("SELECT * FROM YOUR_TABLE_NAME")

# iterate over the result set and print the first column of each row
for row in cur.fetchall():
    print row[0]
</code>

Beyond the basic usage, you may want to avoid writing raw SQL by using an ORM. The most popular Python ORM is SQLAlchemy, but a lightweight alternative called Peewee is also recommended for small projects. The following example shows how to define a model, create a table, insert a record, and query it using Peewee.

<code>import peewee
from peewee import *

# define the database connection
db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')

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

# create the table
Book.create_table()

# insert a new record
book = Book.create(author="me", title="Peewee is cool")

# query and print titles of books by the same author
for book in Book.select().where(author="me"):
    print book.title
</code>

To install Peewee simply run pip install peewee ; no additional configuration is required. The article concludes that with these steps you can connect to MySQL from Python, execute queries directly, or use an ORM for more abstracted data handling.