logo

MySQL with Python

In this tutorial you will learn how to use a widely used database management system called MySQL in Python. You do not need any previous knowledge of MySQL to use this tutorial, but there is a lot more to MySQL than covered in this short introductory tutorial.

Related course
Master SQL Databases with Python

MySQL tutorial
Data is stored in a collection of tables with each table consisting of a set of rows and columns. This is similar to how data is stored in SQLite. To interact with the data stored in tables we use a special-purpose programming language called SQL.

Step 1: Install MySQL
First you must install a MySQL driver, use the specific installation method below.

On Windows:
Install MySQLdb using the installer.

On Linux:
Install MySQLdb using:

sudo apt-get install python-mysqldb
yum install mysql-python

depending on your version.

On Mac:
Follow the installation instructions from stackoverflow

MySQL server has to be running before going to the next step.

Step 2: Setup the database

Make sure you have database access, from the command line type:

mysql -u USERNAME -p

MySQL will then ask your password. Type these commands:

mysql> CREATE DATABASE pythonspot;
mysql> USE pythonspot;

We go on the create the table:

CREATE TABLE IF NOT EXISTS examples (
id int(11) NOT NULL AUTO_INCREMENT,
description varchar(45),
PRIMARY KEY (id)
);

Then we can insert data into the table (these are SQL queries):

1
2
3
INSERT INTO examples(description) VALUES ("Hello World");
INSERT INTO examples(description) VALUES ("MySQL Example");
INSERT INTO examples(description) VALUES ("Flask Example");

You can now grab all records from the table using a SQL query:

mysql> SELECT * FROM examples;
+----+---------------+
| id | description |
+----+---------------+
| 1 | Hello World |
| 2 | MySQL Example |
| 3 | Flask Example |
+----+---------------+
3 rows in set (0.01 sec)

Step 3: Getting the data from Python
You can access the database directly from Python using the MySQLdb module.

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost", # your host
user="root", # username
passwd="root", # password
db="pythonspot") # name of the database

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

# Select data from table using SQL query.
cur.execute("SELECT * FROM examples")

# print the first and second columns
for row in cur.fetchall() :
print row[0], " ", row[1]

Output:

1   Hello World
2 MySQL Example
3 Flask Example

Related course
Master SQL Databases with Python