MySQL for Python : Passing a query to MySQL

10/12/2012 2:03:47 AM
We have just seen how to form a query for a generic MySQL session. While that was not particularly difficult, using MySQL for Python is even easier. For this next section, we will be working against a database fish with a table menu that has the following contents:
| id | name | price |
| 1 | tuna | 7.50 |
| 2 | bass | 6.75 |
| 3 | salmon | 9.50 |
| 4 | catfish | 5.00 |
| 5 | trout | 6.00 |
| 6 | haddock | 6.50 |
| 7 | yellowfin tuna | 12.00 |

Python's interface with MySQL requires a cursor. It is through the cursor object that we pass commands to MySQL. So, we import MySQL for Python, log into our database fish and create the cursor as follows:

import MySQLdb
mydb = MySQLdb.connect(host = 'localhost',
user = 'skipper',
passwd = 'mysecret',
db = 'fish')
cur = mydb.cursor()

A simple SELECT statement

To pass a command to MySQL, we use the execute() method´╗┐ . The execute() method, as the name implies, expects an argument of what is to be executed by Python. In other words, it takes the MySQL sentence or statement as its argument. Its basic syntax is as follows:

results_variable = cursor_handle.execute('MySQL statement')

In practice, it looks like this:

command = cur.execute('SELECT * FROM menu')

As you might surmise from the previous discussion on SELECT, this returns all rows of the table menu.

You will notice that we did not have to specify the database in the execute() call. This is because it was already specified in the MySQLdb.connect() call. Each connection represents one database being accessed in the name of one user on one host. If any of those dynamics need to change, a new connection object becomes necessary. It is possible to create a connection without declaring a database at the outset, but a database must be specified before a cursor can be created or a query made.

Unlike in the MySQL shell, the execute() call here does not immediately return the results. They are held in system memory (RAM) until you tell MySQL for Python what you want to do with them. This is another reason why it is important to mind your use of system resources in the use of HAVING and LIMIT, as mentioned previously.

For the purposes of illustration, we can pull down all of the results in one go. To do this, we use the fetchall() method´╗┐ of the cursor object.

results = command.fetchall()

At this point, the results have now passed from MySQL for Python into the calling program's resource matrix. The fetchall() method returns its results as a series of tuples. Printing the value of results shows the following:

>>> print results
((1L, 'tuna', Decimal('7.50')), (2L, 'bass', Decimal('6.75')), (3L, 'salmon', Decimal('9.50')), 
(4L, 'catfish', Decimal('5.00')), (5L, 'trout', Decimal('6.00')), (6L, 'haddock', Decimal('6.50')), (7L, 'yellowfin tuna', Decimal('12.00')))

This is obviously far from human-friendly. However, we can now use Python's own data-handling resources to parse it. In programming terms, we now have greater control over our data and can present it as we want. So let's create a loop to iterate through the results and print the results in a formatted way.

>>> for record in results:
... print record[0] , "-->", record[1] , " @", record[2], "each"
1 --> tuna @ 7.50 each
2 --> bass @ 6.75 each
3 --> salmon @ 9.50 each
4 --> catfish @ 5.00 each
5 --> trout @ 6.00 each
6 --> haddock @ 6.50 each
7 --> yellowfin tuna @ 12.00 each

Modifying the results

The last query could easily have returned more results than we could use. As mentioned previously, this is why the SELECT command comes with a comprehensive suite of modifiers to nuance one's query and, hopefully, use a minimal amount of system resources.

To use GROUP BY, ORDER BY, or any of the other clauses that one can add to a SELECT statement, one simply adds them to the MySQL statement that is passed to the execute() method.

If you wanted to retrieve information only on fish whose price is greater than $7, you would need to sort through the data again and find the record with the matching name. Better to let MySQL do the dirty work. Using the preceding simple query (see Where under Other helpful quantifiers), we can do the following:

command = cur.execute("""SELECT * FROM menu WHERE price > 7""")
results = command.fetchall()
for record in results:
print record[0], ". ", record[1], "(%s)" %record[2]

The results would be:

1 . tuna (7.50)
3 . salmon (9.50)
7 . yellowfin tuna (12.00)

Similar statements can be passed for each of the SELECT clauses discussed above.

  •  MySQL for Python : Forming a query in MySQL
  •  SQL Injection : Platform-Level Defenses - Securing the Database
  •  SQL Injection : Platform-Level Defenses - Using Runtime Protection (part 2) - Intercepting Filters
  •  SQL Injection : Platform-Level Defenses - Using Runtime Protection (part 1) - Web Application Firewalls
  •  SQL Injection : Code-Level Defenses - Designing to Avoid the Dangers of SQL Injection
  •  SQL Injection : Code-Level Defenses - Canonicalization
  •  SQL Injection : Code-Level Defenses - Encoding Output
  •  Upgrading and Converting to Access 2010 : TO CONVERT OR TO ENABLE
  •  Oracle Database 11g : Database Fundamentals - Learn the Basic Oracle Database 11g Data Types, Work with Tables, Work with Stored Programmed Objects
    PS4 game trailer XBox One game trailer
    WiiU game trailer 3ds game trailer
    Top 10 Video Game
    -   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
    -   Total War: Warhammer [PC] Demigryph Trailer
    -   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
    -   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
    -   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
    -   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
    -   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
    -   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
    -   Satellite Reign [PC] Release Date Trailer
    Game of War | Kate Upton Commercial