Wednesday, January 7, 2015

Tutorial: How to Connect to MySQL With Python

The more I jump into Python the more I like it. This tutorial is about one of the more basic parts of Python - connecting it to a MySQL database.
The reason I chose MySQL is purely because of ubiquity, I figure this will be the one people will be connecting to the most if they’re using Python especially in a web environment.

Get The Database Setup

If you’re following this excersize exactly, you’ll want to create a table on your MySQL database that holds names. This is just a simple dumb table for this excersize.

SQL to create the table for this tutorial.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE  TABLE `test`.`name` (
  `nameid` INT NOT NULL AUTO_INCREMENT ,
  `firstname` VARCHAR(45) NULL ,
  `lastname` VARCHAR(45) NULL ,
  PRIMARY KEY (`nameid`)
);

INSERT INTO `test`.`name`
(`firstname`,`lastname`)
VALUES
("Cookie","Monster"),
("Guy","Smiley"),
("Big","Bird"),
("Oscar","Grouch"),
("Alastair","Cookie");

Now that you have your highly sophisticated database set up, let’s connect to it and start playing around.

Create Your Python Script

The first step of course is to create your Python Script. Create a file called “datademo.py” (or whatever you want to call it).

Your Initial Python Script.
1
2
3

#!/usr/bin/python
# filename: datademo.py 
# a simple script to pull some data from a MySQL table

Connect to the Database

The first thing you’ll need do is import the MySQL modules with the following line:

1

import MySQLdb

This assumes you have MySQLdb installed. If not, don’t worry it’s a quick install.
Now that you have that set up, let’s get connected:

1

db = MySQLdb.connect(host="localhost", user="root", passwd="", db="test")

With this string you can connect using your MySQL credentials. If you want you can store these credentials in variables elsewhere.

The Cursor

Python uses a “cursor” when dealing with data. A cursor is a simple data structure that transverses the records in the database. Cursors perform CRUD ( Create Read Update Delete ) operations on the database.

1
2

#create a cursor for the select
cur = db.cursor()

This intializes the cursor so you can use the “cur “object wherever needed. So the next thing we need to do is come up with an SQL command.

1

SELECT firstname,lastname FROM test.name;

This of course selects a first and last name from our database. We want to stuff that SQL command into a parameter for the execute method of the cursor:

1
2

#execute an sql query
cur.execute("SELECT firstname,lastname FROM test.name")

Iteration and Display

The next part of this is iterating through the database result and displaying it.

1
2
3
4
5
6
7
8
9

# loop to iterate
for row in cur.fetchall() :
      #data from rows
        firstname = str(row[0])
        lastname = str(row[1])

      #print it
        print "The first name is " + firstname
        print "The last name is " + lastname

Pretty simple huh? The for loop iterates through the data and produces an array, in this case it’s “row”. You then select the index of that row to get the data from it.
When you run it you should see this output:
The first name is Cookie
The last name is Monster
The first name is Guy
The last name is Smiley
The first name is Big
The last name is Bird
The first name is Oscar
The last name is Grouch
The first name is Alastair
The last name is Cookie
This is just a straight dump of the database. Let’s clean it up little.

1
2
3
4
5
6
7
8

# loop to iterate
for row in cur.fetchall() :
      #data from rows
        firstname = str(row[0])
        lastname = str(row[1])

      #print i
        print "This Person's name is " + firstname + " " + lastname

This obviously is a cleaned up version of the same thing. Just remember, for iterates but the cursor is the important part.
Your output will look like this:
This Person's name is Cookie Monster
This Person's name is Guy Smiley
This Person's name is Big Bird
This Person's name is Oscar Grouch
This Person's name is Alastair Cookie
You can also simply print out the row and look at the raw data:

1
2
3

# loop to iterate
for row in cur.fetchall() :
  print row

Your output will look something like this:
('Cookie', 'Monster')
('Guy', 'Smiley')
('Big', 'Bird')
('Oscar', 'Grouch')
('Alastair', 'Cookie')
This allows you to look at the data structure to determine what you want to grab.

Closing it all up

One quick way to bring down a server is leaving your connections open. Since there are persistent connections, when you end your script that doesn’t mean the database session ends with it, generally it does not. So here is how you close it up:

1
2
3
4
5

# close the cursor
cur.close()

# close the connection
db.close ()

Notice how we call the close() method for both objects. closing them. You are actually closing two things: the cursor and the connection. It’s actually a good thing you have to do them separate, as opposed to one function. There may be a need to close a cursor yet leave the connection open. This is why we do it in two steps.

The full script

Here is the full code for this article, in case you are one of those people who skip down to the code, then download it and play around.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#!/usr/bin/python
# datademo.py 
# a simple script to pull some data from MySQL

import MySQLdb

db = MySQLdb.connect(host="localhost", user="root", passwd="", db="test")

#create a cursor for the select
cur = db.cursor()

#execute an sql query
cur.execute("SELECT firstname,lastname FROM test.name")

##Iterate 
for row in cur.fetchall() :
      #data from rows
        firstname = str(row[0])
        lastname = str(row[1])

      #print 
        print "This Person's name is " + firstname + " " + lastname

# close the cursor
cur.close()

# close the connection
db.close ()

There it is, easy as that! In the next article I’ll be diving in a little deeper and we’ll build some tests to demonstrate the MySQL usage.
Good luck!
Labels: , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)