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 start, here is the generic syntax 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 can run this simple SELECT query:

SELECT * FROM test_database.names_table

 

Once you run the above query, you would 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 most 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 we 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 below code to correspond to 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()

 

Press F5 to 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: