Python-MySQL Program
This post works through the Python configuration of Fedora instance, and continues the configuration of my LAMP VMware instance. It covers how you add the MySQL-python libraries to the Fedora instance, and provides the students with one more language opportunity for their capstone lab in the database class.
A standard Fedora Linux distribution installs Python 2.7 by default. Unfortunately, the MySQL-python library isn’t installed by default. You can verify the Python version by writing and running the following version.py program before installing the MySQL-python library:
1 2 3 4 5 | # Import sys library. import sys # Print the Python version. print sys.version |
You can run the version.py program dynamically like this from the current working directory:
python version.py |
It will print the following:
2.7.5 (default, Nov 3 2014, 14:26:24) [GCC 4.8.3 20140911 (Red Hat 4.8.3-7)] |
If you modify the program by adding the following first line
1 2 3 4 5 6 7 | #!/usr/bin/python # Import sys library. import sys # Print the Python version. print sys.version |
Provided you’ve set the file permissions to read and execute, you can run the program by simply calling version.py like this from the present working directory:
./version.py |
You can install the MySQL-python library with the yum utility like this:
yum install -y MySQL-python |
It shows you the following output:
Loaded plugins: langpacks, refresh-packagekit mysql-connectors-community | 2.5 kB 00:00 mysql-tools-community | 2.5 kB 00:00 mysql56-community | 2.5 kB 00:00 pgdg93 | 3.6 kB 00:00 updates/20/x86_64/metalink | 12 kB 00:00 updates | 4.9 kB 00:00 updates/20/x86_64/primary_db | 13 MB 00:04 (1/2): updates/20/x86_64/updateinfo | 1.9 MB 00:02 (2/2): updates/20/x86_64/pkgtags | 1.4 MB 00:02 Resolving Dependencies --> Running transaction check ---> Package MySQL-python.x86_64 0:1.2.3-8.fc20 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: MySQL-python x86_64 1.2.3-8.fc20 fedora 82 k Transaction Summary ================================================================================ Install 1 Package Total download size: 82 k Installed size: 231 k Downloading packages: MySQL-python-1.2.3-8.fc20.x86_64.rpm | 82 kB 00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction (shutdown inhibited) Installing : MySQL-python-1.2.3-8.fc20.x86_64 1/1 Verifying : MySQL-python-1.2.3-8.fc20.x86_64 1/1 Installed: MySQL-python.x86_64 0:1.2.3-8.fc20 Complete! |
After installing the MySQL-python library, you can call the following mysql_connect.py program from the local directory:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | #!/usr/bin/python # Import sys library. import MySQLdb import sys try: # Create new database connection. db = MySQLdb.connect('localhost','student','student','studentdb') # Query the version of the MySQL database. db.query("SELECT version()") # Assign the query results to a local variable. result = db.use_result() # Print the results. print "MySQL Version: %s " % result.fetch_row()[0] except MySQLdb.Error, e: # Print the error. print "ERROR %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
Like the version.py program, set the file permissions to read and execute and call , you can run the program by simply calling mysql_connect.py program like this from the present working directory:
./mysql_connect.py |
The mysql_connect.py program displays:
MySQL Version: 5.6.24 |
After verifying the MySQL connection, you can query actual data with the following mysql_queryset.py program:
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 | #!/usr/bin/python # Import sys library. import MySQLdb import sys try: # Create new database connection. db = MySQLdb.connect('localhost','student','student','studentdb') # Create a result set cursor. rs = db.cursor() rs.execute("SELECT item_title FROM item") # Assign the query results to a local variable. rows = rs.fetchall() # Print the results. for row in rows: print row except MySQLdb.Error, e: # Print the error. print "ERROR %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
You call the mysql_queryset.py file from the present working directory like this:
./mysql_queryset.py |
It prints the following:
('The Hunt for Red October',)
('Star Wars I',)
('Star Wars II',)
('Star Wars II',)
('Star Wars III',)
('The Chronicles of Narnia',)
('RoboCop',)
('Pirates of the Caribbean',)
('The Chronicles of Narnia',)
('MarioKart',)
('Splinter Cell',)
('Need for Speed',)
('The DaVinci Code',)
('Cars',)
('Beau Geste',)
('I Remember Mama',)
('Tora! Tora! Tora!',)
('A Man for All Seasons',)
('Hook',)
('Around the World in 80 Days',)
("Harry Potter and the Sorcerer's Stone",)
('Camelot',) |
You can substantially improve on the behavior of the prior example by handling each row one at a time. The following mysql_query.py program reads through the cursor result set one row at a time:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | #!/usr/bin/python # Import sys library. import MySQLdb import sys try: # Create new database connection. db = MySQLdb.connect('localhost','student','student','studentdb') # Create a result set cursor. rs = db.cursor() rs.execute("SELECT item_title FROM item") # Assign the query results to a local variable. for i in range(rs.rowcount): row = rs.fetchone() print row[0] except MySQLdb.Error, e: # Print the error. print "ERROR %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: # Close the connection when it is open. if db: db.close() |
You call the mysql_query.py with the following syntax:
./mysql_query.py |
It returns the following result set:
The Hunt for Red October Star Wars I Star Wars II Star Wars II Star Wars III The Chronicles of Narnia RoboCop Pirates of the Caribbean The Chronicles of Narnia MarioKart Splinter Cell Need for Speed The DaVinci Code Cars Beau Geste I Remember Mama Tora! Tora! Tora! A Man for All Seasons Hook Around the World in 80 Days Harry Potter and the Sorcerer's Stone Camelot |
As always, I hope this helps those looking for this type of solution. The Python tutorial web site teaches you more about the Python Programming Language. You may also find the TutorialsPoint.com site useful while you’re learning and using Python. The MySQLdb User’s Guide teaches more about working writing Python-MySQL library. The MySQLdb implements the Python Database API Specification v2.0.
Subscribe via RSS