Python-Ref > Databases and Python > Python and MySQL > How to retrieve data from a database
 
 

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

How to retrieve data from a database

Once we are connected to a database server, we can start asking it questions. The basic mechanism is to use normal SQL queries that are passed to the server as strings and retrieve the data back in form of tuples. For this purpose we use a so called cursor that enables us to execute queries and retrieve individual results of out them.
Expand/Shrink
Zdroj: (mysql2-1.py)
  1   import MySQLdb
  2   
  3   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost")
  4   cursor = connection.cursor()  # obtain the cursor
  5   cursor.execute( "SELECT * FROM City WHERE CountryCode = 'AUS';")  # normal SQL select statement
  6   print "Cities found:", cursor.rowcount
  7   
  8   for i in range( cursor.rowcount):
  9     data = cursor.fetchone()  # fetch one result as a tuple
 10     print data
 11   cursor.close()  # close the cursor
 12   connection.close()
stdout:
Cities found: 14
(130L, 'Sydney', 'AUS', 'New South Wales', 3276207L)
(131L, 'Melbourne', 'AUS', 'Victoria', 2865329L)
(132L, 'Brisbane', 'AUS', 'Queensland', 1291117L)
(133L, 'Perth', 'AUS', 'West Australia', 1096829L)
(134L, 'Adelaide', 'AUS', 'South Australia', 978100L)
(135L, 'Canberra', 'AUS', 'Capital Region', 322723L)
(136L, 'Gold Coast', 'AUS', 'Queensland', 311932L)
(137L, 'Newcastle', 'AUS', 'New South Wales', 270324L)
(138L, 'Central Coast', 'AUS', 'New South Wales', 227657L)
(139L, 'Wollongong', 'AUS', 'New South Wales', 219761L)
(140L, 'Hobart', 'AUS', 'Tasmania', 126118L)
(141L, 'Geelong', 'AUS', 'Victoria', 125382L)
(142L, 'Townsville', 'AUS', 'Queensland', 109914L)
(143L, 'Cairns', 'AUS', 'Queensland', 92273L)
Doba běhu: 153.0 ms
The default cursor used in the previous example fetches data in a form of a tuple. This is no problem when selected fields where given explicitly and their order is thus known. On the other hand, when you use "*" in select statement, you must either already know the order of the returned fields or you must query it. The following example shows how to do this.
Expand/Shrink
Zdroj: (mysql2-2.py)
  1   import MySQLdb
  2   
  3   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost")
  4   cursor = connection.cursor()  # obtain the cursor
  5   
  6   # find the structure of a table
  7   cursor.execute( "DESCRIBE City;")
  8   columns = [x[0] for x in cursor.fetchall()]  # a list of field names in order of the table
  9   
 10   cursor.execute( "SELECT * FROM City WHERE CountryCode = 'AUS';")  # normal SQL select statement
 11   print "Cities found:", cursor.rowcount
 12   
 13   print "Columns:", columns
 14   for i in range( cursor.rowcount):
 15     data = cursor.fetchone()  # fetch one result as a tuple
 16     print data
 17   cursor.close()  # close the cursor
 18   connection.close()
stdout:
Cities found: 14
Columns: ['ID', 'Name', 'CountryCode', 'District', 'Population']
(130L, 'Sydney', 'AUS', 'New South Wales', 3276207L)
(131L, 'Melbourne', 'AUS', 'Victoria', 2865329L)
(132L, 'Brisbane', 'AUS', 'Queensland', 1291117L)
(133L, 'Perth', 'AUS', 'West Australia', 1096829L)
(134L, 'Adelaide', 'AUS', 'South Australia', 978100L)
(135L, 'Canberra', 'AUS', 'Capital Region', 322723L)
(136L, 'Gold Coast', 'AUS', 'Queensland', 311932L)
(137L, 'Newcastle', 'AUS', 'New South Wales', 270324L)
(138L, 'Central Coast', 'AUS', 'New South Wales', 227657L)
(139L, 'Wollongong', 'AUS', 'New South Wales', 219761L)
(140L, 'Hobart', 'AUS', 'Tasmania', 126118L)
(141L, 'Geelong', 'AUS', 'Victoria', 125382L)
(142L, 'Townsville', 'AUS', 'Queensland', 109914L)
(143L, 'Cairns', 'AUS', 'Queensland', 92273L)
Doba běhu: 145.2 ms
Another solution to the above problem and also a more convenient approach for retrieving data is to change the Cursor class that is used to one more suitable. IMHO the most natural one is the DictCursor, that makes the data available as a dictionary "field_name"=>"value".
Expand/Shrink
Zdroj: (mysql2-3.py)
  1   import MySQLdb
  2   from MySQLdb.cursors import DictCursor
  3   
  4   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  5   cursor = connection.cursor()  # obtain the cursor
  6   cursor.execute( "SELECT * FROM City WHERE CountryCode = 'AUS';")  # normal SQL select statement
  7   print "Cities found:", cursor.rowcount
  8   
  9   for i in range( cursor.rowcount):
 10     data = cursor.fetchone()  # fetch one result as a tuple
 11     print data
 12   cursor.close()  # close the cursor
 13   connection.close()
stdout:
Cities found: 14
{'District': 'New South Wales', 'Population': 3276207L, 'ID': 130L, 'CountryCode': 'AUS', 'Name': 'Sydney'}
{'District': 'Victoria', 'Population': 2865329L, 'ID': 131L, 'CountryCode': 'AUS', 'Name': 'Melbourne'}
{'District': 'Queensland', 'Population': 1291117L, 'ID': 132L, 'CountryCode': 'AUS', 'Name': 'Brisbane'}
{'District': 'West Australia', 'Population': 1096829L, 'ID': 133L, 'CountryCode': 'AUS', 'Name': 'Perth'}
{'District': 'South Australia', 'Population': 978100L, 'ID': 134L, 'CountryCode': 'AUS', 'Name': 'Adelaide'}
{'District': 'Capital Region', 'Population': 322723L, 'ID': 135L, 'CountryCode': 'AUS', 'Name': 'Canberra'}
{'District': 'Queensland', 'Population': 311932L, 'ID': 136L, 'CountryCode': 'AUS', 'Name': 'Gold Coast'}
{'District': 'New South Wales', 'Population': 270324L, 'ID': 137L, 'CountryCode': 'AUS', 'Name': 'Newcastle'}
{'District': 'New South Wales', 'Population': 227657L, 'ID': 138L, 'CountryCode': 'AUS', 'Name': 'Central Coast'}
{'District': 'New South Wales', 'Population': 219761L, 'ID': 139L, 'CountryCode': 'AUS', 'Name': 'Wollongong'}
{'District': 'Tasmania', 'Population': 126118L, 'ID': 140L, 'CountryCode': 'AUS', 'Name': 'Hobart'}
{'District': 'Victoria', 'Population': 125382L, 'ID': 141L, 'CountryCode': 'AUS', 'Name': 'Geelong'}
{'District': 'Queensland', 'Population': 109914L, 'ID': 142L, 'CountryCode': 'AUS', 'Name': 'Townsville'}
{'District': 'Queensland', 'Population': 92273L, 'ID': 143L, 'CountryCode': 'AUS', 'Name': 'Cairns'}
Doba běhu: 80.3 ms
Just as an afterthought, here is an example how to order the results. The ordering is most conveniently done on the SQL side using the "ORDER BY" command.
Expand/Shrink
Zdroj: (mysql2-4.py)
  1   import MySQLdb
  2   from MySQLdb.cursors import DictCursor
  3   
  4   connection = MySQLdb.connect( user="test", passwd="pass123", db="world", host="localhost", cursorclass=DictCursor)
  5   cursor = connection.cursor()  # obtain the cursor
  6   cursor.execute( "SELECT * FROM City WHERE CountryCode = 'AUS' ORDER BY Name;")
  7   print "Cities found:", cursor.rowcount
  8   
  9   for i in range( cursor.rowcount):
 10     data = cursor.fetchone()  # fetch one result as a tuple
 11     print data['Name'], data['Population']
 12   cursor.close()  # close the cursor
 13   connection.close()
stdout:
Cities found: 14
Adelaide 978100
Brisbane 1291117
Cairns 92273
Canberra 322723
Central Coast 227657
Geelong 125382
Gold Coast 311932
Hobart 126118
Melbourne 2865329
Newcastle 270324
Perth 1096829
Sydney 3276207
Townsville 109914
Wollongong 219761
Doba běhu: 59.5 ms