How to Connect Python to MySQL using MySQLdb

Need to connect Python to MySQL?

If so, I’ll show you how to establish such a connection from scratch!

But before we begin, here is a template that you may use to connect Python to MySQL:

import MySQLdb

db = MySQLdb.connect(host='your host name',  # your host name is often 'localhost'
                     user='your username',            
                     passwd='your password',  
                     db='your database')        

cur = db.cursor()

# to apply SQL
cur.execute('SELECT * FROM your table')

for row in cur.fetchall():
    print row

db.close()

In the next section, I’ll show you how to apply the above syntax using a live example.

How to Connect Python to MySQL from Scratch

Let’s say that you want to create a database in MySQL, where:

  • the name of the database is: test_database
  • the database will contain a single table called: names_table
  • the names_table will have 3 columns with the following information:
First_Name Last_NameAge
JonSnow22
MariaSmith34
EmmaJones51
BillYu63
JackGreen27

First, you’ll need to create the test_database using the following query in MySQL:

CREATE DATABASE test_database

If you’re using MySQL Workbench, you’ll see the database name on the left hand side (under SCHEMAS):

Database in MySQL

Next, create the names_table using this query:

CREATE TABLE test_database.names_table (First_Name varchar(20), 
Last_Name varchar(20), Age varchar(3))

Your new table would also appear on the left hand side (under SCHEMAS):

Table in MySQL

Finally, add the records into the names_table using the following query:

INSERT INTO test_database.names_table (First_Name, Last_Name, Age)
VALUES
('Jon', 'Snow',22),
('Maria', 'Smith',34),
('Emma', 'Jones',51),
('Bill', 'Yu',63),
('Jack', 'Green',27)

To check that the data is indeed stored in the names_table, you may run this simple SELECT query:

SELECT * FROM test_database.names_table

Once you run the above query, you’ll see:

How to Connect Python to MySQL using MySQLdb

So now that you have the database ready, and all the records are stored in the names_table, you’ll need to install MySQLdb to be used in Python.

If you’re using Windows, you can download and install MySQL for Python. Make sure that the version you download match with the Python version. In our example, the Python version is 2.7:

MySQL for Python

Next, obtain your host name, user and password.

In some cases, your host name would be ‘localhost‘ for local instances.

You can see the user name in MySQL Workbench, by going to the Database tab, and then selecting Manage Connections… 

MySQL Workbench

Then, look for the Username for you instance. In my case, the Username is root, while my password is 1q2w3e4r

MySQL Server connection

Can you now connect Python to MySQL?

Absolutely! You should have all the information needed to establish this connection.

For our example, the following information can be entered in Python 2.7 (you’ll need to modify the code below to reflect your connection information):

import MySQLdb

db = MySQLdb.connect(host='localhost',    
                     user='root',         
                     passwd='1q2w3e4r',  
                     db='test_database')        

cur = db.cursor()

cur.execute('SELECT * FROM test_database.names_table')

for row in cur.fetchall():
    print row

db.close()

Run the code in Python, and you’ll get the exact same results as stored in the names_table in MySQL:

How to Connect Python to MySQL using MySQLdb

That’s it!

Once you established a connection between Python and MySQL, you may start applying SQL in Python.

You may also want to check the following source that explains how to insert values into MySQL table using Python.

Finally, the tutorials below explain how to establish a connection between Python and: