Doing it in Python

As we have seen in previous chapters, processing a SELECT query in Python is as simple as execute() and fetchall(). However, Python also allows us to build statements dynamically, and this applies to joins, unions, and subqueries as well.

Subqueries

If we want column from table1, but the column reference for the subquery is colref, from both table1 and table2, we can write the following:

#!/usr/bin/env python

import MySQLdb

mydb = MySQLdb.connect('localhost', 'skipper', 'secret', 'sakila')
cursor = mydb.cursor()

table1 = 'film'
table2 = 'film_actor'
column = 'film_id, title'
colref = 'film_id'

statement = "SELECT %s FROM %s WHERE %s IN (SELECT %s FROM %s)" %(column, table1, colref, colref, table2)

cursor.execute(statement)
results = cursor.fetchall()

for i in results: print i[0], '\t', i[1]

The results obviously will be the title and identifier for each title in film. We can further nuance this for reader input to allow searches by the name of the actor.

#!/usr/bin/env...