Tutorial: How to connect with MySQL with Python

Final Replace: Mar 8, 2023

The extra I bounce into Python the extra I prefer it. This tutorial is about one of many extra fundamental elements of Python – connecting it to a MySQL database.

The explanation I selected MySQL is solely due to ubiquity, I determine this would be the one individuals can be connecting to probably the most in the event that they’re utilizing Python particularly in an internet envronment.

Get The Database Setup

Should you’re following this excersize precisely, you’ll wish to create a desk in your MySQL database that holds names. That is only a easy dumb desk for this excersize.

CREATE  TABLE `take a look at`.`title` (
	`nameid` INT NOT NULL AUTO_INCREMENT ,
	`firstname` VARCHAR(45) NULL ,
	`lastname` VARCHAR(45) NULL ,
	PRIMARY KEY (`nameid`)
);

INSERT INTO `take a look at`.`title`
(`firstname`,`lastname`)
VALUES
("Cookie","Monster"),
("Man","Smiley"),
("Massive","Hen"),
("Oscar","Grouch"),
("Alastair","Cookie");

Now that you’ve got your extremely subtle database arrange, let’s connect with it and begin taking part in round.

Create Your Python Script

Step one in fact is to create your Python Script. Create a file known as “datademo.py” (or no matter you wish to name it).

#!/usr/bin/python
# filename: datademo.py
# a easy script to tug some knowledge from a MySQL desk

Connect with the Database

The very first thing you’ll want do is import the MySQL modules with the next line:

This assumes you will have MySQLdb put in. If not, don’t fear it’s a fast set up.

Now that you’ve got that arrange, let’s get related:

db = MySQLdb.join(host="localhost", person="root", passwd="", db="take a look at")

With this string you possibly can join utilizing your MySQL credentials. If you would like you possibly can retailer these credentials in variables elsewhere.

The Cursor

Python makes use of a “cursor” when coping with knowledge. A cursor is an easy knowledge construction that transverses the information within the database. Cursors carry out CRUD ( Create Learn Replace Delete ) operations on the database.

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

This intializes the cursor so you need to use the “cur “object wherever wanted. So the subsequent factor we have to do is give you an SQL command.

SELECT firstname,lastname FROM take a look at.title;

This in fact selects a primary and final title from our database. We wish to stuff that SQL command right into a parameter for the execute technique of the cursor:

#execute an sql question
cur.execute("SELECT firstname,lastname FROM take a look at.title")

Iteration and Show

The subsequent a part of that is iterating via the database consequence and displaying it.

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

		#print it
		print "The primary title is " + firstname
		print "The final title is " + lastname

Fairly easy huh? The for loop iterates via the information and produces an array, on this case it’s “row”. You then choose the index of that row to get the information from it.

If you run it it is best to see this output:

The primary title is Cookie
The final title is Monster
The primary title is Man
The final title is Smiley
The primary title is Massive
The final title is Hen
The final title is Grouch
The primary title is Oscar
The final title is Cookie
The primary title is Alastair

That is only a straight dump of the database. Let’s clear it up little.

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

		#print i
		print "This Particular person's title is " + firstname + " " + lastname

This clearly is a cleaned up model of the identical factor. Simply bear in mind, for iterates however the cursor is the essential half.

Your output will appear to be this:

This Particular person’s title is Cookie Monster
This Particular person’s title is Man Smiley
This Particular person’s title is Massive Hen
This Particular person’s title is Oscar Grouch
This Particular person’s title is Alastair Cookie

You can too merely print out the row and have a look at the uncooked knowledge:

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

Your output will look one thing like this:

(‘Cookie’, ‘Monster’)
(‘Man’, ‘Smiley’)
(‘Massive’, ‘Hen’)
(‘Oscar’, ‘Grouch’)
(‘Alastair’, ‘Cookie’)

This lets you have a look at the information construction to find out what you wish to seize.

Closing all of it up

One fast solution to carry down a server is leaving your connections open. Since there are persistent connections, whenever you finish your script that doesn’t imply the database session ends with it, usually it doesn’t. So right here is the way you shut it up:

# shut the cursor
cur.shut()

# shut the connection
db.shut ()

Discover how we name the shut() technique for each objects. closing them. You might be truly closing two issues: the cursor and the connection. It’s truly a superb factor you must do them separate, versus one perform. There could also be a necessity to shut
a cursor but go away the connection open. For this reason we do it in two steps.

The total script

Right here is the complete code for this text, in case you might be a type of individuals who skip right down to the code, then obtain it and mess around.

#!/usr/bin/python
# datademo.py
# a easy script to tug some knowledge from MySQL

import MySQLdb

db = MySQLdb.join(host="localhost", person="root", passwd="", db="take a look at")

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

#execute an sql question
cur.execute("SELECT firstname,lastname FROM take a look at.title")

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

		#print
		print "This Particular person's title is " + firstname + " " + lastname

# shut the cursor
cur.shut()

# shut the connection
db.shut ()

There it’s, straightforward as that! Within the subsequent article I’ll be diving in just a little deeper and we’ll construct some checks to display the MySQL utilization.

Good luck!