Python-Ref > Databases and Python > Python and MySQL > Subqueries
 
 

<-^^->
Klíčová slova
Moduly
Knihovní funkce

Subqueries

How to make additional queries while already processing one.
Sometimes it is necessary to query additional data from the database based on data that are retrived as a result of another query. In such cases we often need to use another cursor because we need the original one to retrieve more results from the first query. This is not a problem and the next example shows how to do it.
Expand/Shrink
Zdroj: (mysql3-1.py)
  1   # find 20 most populated cities and corresponding countries
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  6   cursor1 = connection.cursor()  # obtain the cursor
  7   cursor2 = connection.cursor()  # second cursor, used below
  8   
  9   cursor1.execute( 'SET NAMES "utf8"') # force communication in UTF-8 (might not be the default)
 10   cursor1.execute( "SELECT * FROM City ORDER BY Population DESC LIMIT 20;")  # normal SQL select statement
 11   
 12   print "%-20s %-20s %12s" % ('Name', 'Country','Population')
 13   
 14   for i in range( cursor1.rowcount):
 15     data = cursor1.fetchone()  # fetch one result as a tuple
 16     cursor2.execute( "SELECT Name FROM Country WHERE Code=%s;", data['CountryCode'])
 17     country = cursor2.fetchone()
 18     name = data['Name'].decode('utf-8')
 19     print ("%-20s %-20s %12d" % (name, country['Name'], data['Population'])).encode('utf-8')
 20   
 21   cursor1.close()
 22   cursor2.close()
 23   connection.close()
stdout:
Name                 Country                Population
Mumbai (Bombay)      India                    10500000
Seoul                South Korea               9981619
São Paulo            Brazil                    9968485
Shanghai             China                     9696300
Jakarta              Indonesia                 9604900
Karachi              Pakistan                  9269265
Istanbul             Turkey                    8787958
Ciudad de México     Mexico                    8591309
Moscow               Russian Federation        8389200
New York             United States             8008278
Tokyo                Japan                     7980230
Peking               China                     7472000
London               United Kingdom            7285000
Delhi                India                     7206704
Cairo                Egypt                     6789479
Teheran              Iran                      6758845
Lima                 Peru                      6464693
Chongqing            China                     6351600
Bangkok              Thailand                  6320174
Santafé de Bogotá    Colombia                  6260862
Doba běhu: 108.0 ms
Of course it is possible and probably much faster to reformulate the previous example using a natural join between the "Country" and "City" tables. On the other hand the first example is more general and also (which might be very important) more straightforward for someone not used to SQL.
The example code also shows how to write much more readable code for long SELECT statements using triple-quoted strings in Python.
Expand/Shrink
Zdroj: (mysql3-2.py)
  1   # find 20 most populated cities and corresponding countries
  2   import MySQLdb
  3   from MySQLdb.cursors import DictCursor
  4   
  5   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  6   cursor1 = connection.cursor()  # obtain the cursor
  7   
  8   cursor1.execute( """SELECT
  9                        City.Name,City.Population,Country.Name AS CName
 10                       FROM
 11                        Country, City
 12                       WHERE
 13                        City.CountryCode = Country.Code
 14                       ORDER BY
 15                        Population
 16                       DESC LIMIT 20;""")
 17   
 18   print "%-20s %-20s %12s" % ('Name', 'Country','Population')
 19   
 20   for i in range( cursor1.rowcount):
 21     data = cursor1.fetchone()  # fetch one result as a tuple
 22     name = data['Name'].decode('utf-8')
 23     print ("%-20s %-20s %12d" % (name, data['CName'], data['Population'])).encode('utf-8')
 24   
 25   cursor1.close()
 26   connection.close()
stdout:
Name                 Country                Population
Mumbai (Bombay)      India                    10500000
Seoul                South Korea               9981619
São Paulo            Brazil                    9968485
Shanghai             China                     9696300
Jakarta              Indonesia                 9604900
Karachi              Pakistan                  9269265
Istanbul             Turkey                    8787958
Ciudad de México     Mexico                    8591309
Moscow               Russian Federation        8389200
New York             United States             8008278
Tokyo                Japan                     7980230
Peking               China                     7472000
London               United Kingdom            7285000
Delhi                India                     7206704
Cairo                Egypt                     6789479
Teheran              Iran                      6758845
Lima                 Peru                      6464693
Chongqing            China                     6351600
Bangkok              Thailand                  6320174
Santafé de Bogotá    Colombia                  6260862
Doba běhu: 71.0 ms