Integrating MySQL with Python

Python has good MySQL support, via multiple packages. In this course we will be using the MySQLdb package to connect from Python to a MySQL database server and run queries.
For more information on MySQLdb see the official documentation.

Connecting to the database

The code to connect to the database is rather straight forward:

import MySQLdb
db = MySQLdb.connect('hostname', 'username', 'password', 'databasename')
db.autocommit(True)

To disconnect, when all queries are completed, usually at the end of your Python script:

db.close()

In practice, this method is not what is normally used directly. Please review the next section!

Connecting (in practice)

Since your username and password (for the database) is needed for the connection, we'll create a small mydb.py script which stores the connection and can be used by all of our scripts that need the connection. As an exercise for this lecture we'll create a file and a connection class to work with the database. The example below is in a file called mydb.py:

import MySQLdb

db = MySQLdb.connect('db.cs.usna.edu', # Hostname
                     'm123456',        # Username
                     'm123456',        # Password
                     'm123456')        # Schema/DB Name

db.autocommit(True)

and then connect to the database via:

from mydb import db                    # connect to MySQL when needed

Performing a basic query

There are a few basic steps needed when performing SQL queries via Python and MySQL. In the examples below simple queries will be presented on the left, while dynamic queries are shown on the right.

1. Open a cursor which is a special structure used to actually execute queries in a database:
   
   

   cursor = db.cursor()      

2. Create a variable that will store the SQL query. To support dynamic queries, place a %s in spaces that will be filled in by variables later.
Simple Query

query = """SELECT Alpha, Course
             FROM Grades
            WHERE Course='SD321'"""

query = """INSERT INTO Grades(Alpha, AcYear,
                              Sem, Course, Title)
           VALUES(250234, 2024, 'FALL', 'SD321', 'Data Storage')"""

Dynamic Query

query = """SELECT Alpha, Course
            FROM Grades
            WHERE Course=%s"""      

3. Provide any variables needed for the dynamic query. We need to provide the variables as a list with all the required values in the order that the %s where written in the SQL query string.
   
   
    

   values = ['SD321']    

4. Execute the query. Send the query and, if needed, the data, to the database by using the cursor. We could do this in a try..except statement to catch the errors.



cursor.execute(query)

cursor.execute(query, values)    

5. Check for any warnings, and provide them to the user if desired.



warning = cursor.messages

6. Optionally retrieve any basic / interesting metadata (# rows returned, AUTO_INCREMENTED values, etc.) Use these options when needed, such as when you insert a row into a table and the primary key is auto incremented and you need that key for future queries.



insert_id = cursor.lastrowid       # auto_increment value last inserted
row_count = cursor.rowcount        # number of rows affected by last insert, delete, or update
column_info = cursor.description   # description of columns selected  

7. For SELECT queries, retrieve the results. There are multiple ways to retrieve the results of the query, depending on the expected size of the result:
   • you can retrieve one row at a time using cursor.fetchone(),
   • specify how many rows you want using cursor.fetchmany(size = 10),
   • or retrieve all of them at once using cursor.fetchall() (this can be a problem if the result set is very large and does not fit in memory).

   Below are a few examples of retreiving the relevant rows using the methods above.
   

   # Example 1: Using a loop and fetchone()
   row = cursor.fetchone()
   while row is not None:
     print(row)
     row = cursor.fetchone()

   # Example 2: Using the cursor as iterator
   for (alpha, course) in cursor:
     print(f'Student {alpha} in course {course}')
   

   # Example 3: Using fetchall
   allresults = cursor.fetchall()
   
   for row in allresults:
     print(row)

8. Close the cursor after executing all desired queries.

cursor.close()

Problems

If you don't have the Alpha and Grades tables from previous classes or labs, please download the sample data - sample_data.sql.

1. Create your mydb.py file based on the "Connecting (in practice)" and "Performing a basic query" sections above. In particular this class will:
   • create a class connection with a constructor that accepts username, password, host, and database as parameters and connects the specified database on the server, which will be stored in self.db property of the class.
   • The next method in the class should be called query (see the "Performing a basic query" section) and accept a sql statement and a list of values as parameters. The query method should execute the provided query and return the list of names of the columns in the result, the actual data returned by the query, the value of the last row id inserted, any errors produced by the query, and any warnings.
   • Include a method close that closes the database connection.
   • In the same mydb.py script, create a variable db that is an object of type connection, initialized with corect values to connect to your own database.


2. Build a Python script that uses your db object from mydb. Submit a select query, and print the Alpha, First, Last, Course, and CourseGrade for students in Midn and Grades tables.


3. Modify the previous script so that it will take an alpha from the command line and only return the results for the specific alpha.


4. Build a Python script that uses your db object from mydb. The script should retrieve an alpha, first, last, connect to the database, insert a row into the Midn table with the information received from the user, and print whether the insert was successful or not.