How to connect to MySQL database using Python

Last updated on January 28th, 2022 at 07:51 pm

For connecting to MySQL database and display table row content using Python 2, we need to first install MySQL-python.x86_64, This is for my 64-bit Linux machine. Without installing this package import MySQLdb statement will not work.

You can also see this in your apache error logs

[Fri Jan 5 16:52:10.108904 2015] [cgi:error] [pid 8865] [client 102.271.121.10:23612] AH01215: ImportError: No module named MySQLdb

In order to fix this first install MySQL-python.x86_64 package using yum

yum install MySQL-python.x86_64

Create a file called db.py and in order to display this Python file on a web browser use this code.

print "Content-type:text/html\r\n\r\n"

First you need to use import statement

import MySQLdb

Second step is open the database connection using MySQLdb.connect() statement.

db = MySQLdb.connect("localhost","USERNAME","PASSWORD","DATABASENAME" )

Now we need to prepare a cursor object using cursor() method

cursor = db.cursor()

Once the cursor object is created then we can run mysql query using the execute statement, Here I am selecting all rows from a table

cursor.execute("select * from TABLE_NAME")

Now fetch all rows using fetchall() statement

data = cursor.fetchall()

Using for statement get the row data from database, I am getting username and email column from the database.

for row in data:
 uname = row[1]
 email = row[2]
 print("Name ",uname,"<br>")
 print("Website ",email,"<hr>")

Last statement will be to disconnect from the database

db.close()

Complete code will look like this

#!/usr/bin/python

import MySQLdb
print ("Content-type:text/html\r\n\r\n")
# Open database connection
db = MySQLdb.connect("localhost","USERNAME","PASSWORD","DATABASENAME" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("select * from TABLE_NAME")

# Fetch a single row using fetchone() method.
data = cursor.fetchall()

for row in data:
 uname = row[1]
 email = row[2]
 print ("Name ",uname,"<br>")
 print ("Website ",email,"<hr>")
# disconnect from server
db.close()