How to connect MySQL databases in Python

1 - Settings

First, you must install the MySQL driver. Unlike PHP, Python has only SQLite drivers installed by default. The most commonly used package is MySQLdb, which is difficult to install even with easy_install.

For Windows users, you can download the exe file from MySQLdb.

For Linux, python-mysqldb is provided as a package. You can get the command by entering sudo apt-get install python-mysqldb for the Debian family, yum install mysql-python for the rpm family, and dnf install python-modes for the modern fedora.

Finally, the Mac can be installed using the Mac port.

2 - How to Use

After installation, restart the computer first. Although it is not an essential part, we recommend that you restart because there are occasional problems that may arise if you do not restart.

Then, you can just use it as below like other packages :

#!/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]

db.close()

Of course, this is just the most basic example, and there are various possibilities and settings. Please refer to this document.

3 - Deepened Use

Once you've learned how to work, you'll want to use ORM to prevent manual writing of SQL and to use tables as if they were Python objects. The most famous ORM in the Python community is SQLAlchemy.

If you want to work more comfortably, I strongly recommend using it.

Personally, I recently learned about a library called peewee, which is a light ORM that is very easy and fast to install. SQLAlchemy or Django can be very useful when creating small projects or independent forms of apps that seem a little too much.

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

The example above is an example that is available. There is nothing else to do except install peeewee (pip install peeewee).