The World of Mayukh Bose

<< Back to Main Page Mayukh's world: e-mail me | about
Mayukh's World: Python Tutorials: ADO and Python Thursday, November 23, 2017
Python and ADO
  • 0. Introduction
  • 1. What is ADO
  • 2. Basic Objects
  • 3. Using Parameters
  • 4. ADO/Python Basics
  • 5. Connection Object
  • 6. RecordSet Object
  • 7. Command Object
  • 8. More Command Fun
  • 9. Command and Procs
  • 10. Creating Params
  • 11. Links and Credits
  • My Free Software
  • Delphi/C++ Builder
  • Pocket PC
  • FreeSpeech Chat
  • C/C++ Freebies
  • Perl
  • Python
  • Ruby
  • My Python Code
  • ADO and Python
  • Addlice
  • IE Controller
  • Python Tutorials and Software e-mail me

    Chapter 6: The ADO RecordSet Object

    The RecordSet object is used to iterate through a set of results from a query or a stored procedure. A RecordSet Object must always be assigned a Connection object, where it can retrieve its data from. This is usually done by setting its ActiveConnection property. A complete glossary of all the properties and methods of a RecordSet object is available from the MSDN Library and so we won't repeat all that information here. We will see how to perform some basic selections using the RecordSet object. The following examples assume a MySQL database, but the same code could very well apply to any other database engine (Access, SQL Server, Oracle etc.), merely by changing the ConnectionString property. All the other code can remain the same irrespective of the actual database engine being used.

    Opening a Table and Accessing Fields

      from win32com.client import Dispatch
      from adoconstants import *
    
      # Create Connection object and connect to database.
      oConn = Dispatch('ADODB.Connection')
      oConn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};" + \
                               "Server=192.168.0.50;Port=3306;" + \
                               "User=foobar;Password=bigsecret;Database=mytestdb"
      oConn.Open()
    
      # Now create a RecordSet object and open a table
      oRS = Dispatch('ADODB.RecordSet')
      oRS.ActiveConnection = oConn    # Set the recordset to connect thru oConn
      oRS.Open("zipcode")             # Open a table called zipcode
      # Can also use oRS.Open("zipcode", oConn) instead of setting ActiveConnection
    
      while not oRS.EOF:
          # Access individual fields by name -- all 4 methods are equivalent
          print oRS.Fields.Item("city").Value, oRS.Fields("state"), \
                oRS.Fields("zip").Value, oRS.Fields.Item("city")
          # Access individual fields by position -- all 4 methods are equivalent
          print oRS.Fields.Item(0), oRS.Fields(1), \
                oRS.Fields(2).Value, oRS.Fields.Item(0).Value
    
          # Move to the next record in the RecordSet
          oRS.MoveNext()
    
      # Close and clean up    
      oRS.Close()
      oRS = None
    
      oConn.Close()
      oConn = None    
        
    As you can see, the above example is fairly self-explanatory. The next few examples will demonstrate how to execute queries and stored procedures.

    Using a RecordSet for Query / Stored Procedure

    The code to open a query or a stored procedure is very similar to the example above. All you have to do is replace the line where we open the table above (i.e. oRS.Open("zipcode")) with one of the lines below. The rest of the code can remain the same.
      oRS.Open("SELECT zip, city, state FROM zipcode ORDER BY id")
      oRS.Open("SELECT zip, city, state FROM zipcode ORDER BY id", oConn)
    
    
      # Assuming we have an engine that supports stored procedures which return result sets,
      # such as SQL Server, we can call a stored proc as well. Here, the name of the stored
      # proc is assumed to be selZipCode
      oRS.Open("selZipCode")
      oRS.Open("selZipCode", oConn)
        
    Another way to create a RecordSet object is to use the result from an Execute method of a Connection object. The Execute method of the Connection object returns a RecordSet object, if you pass it a query or stored procedure that returns a recordset. If you use this method, you do not need to create a new RecordSet object or assign its ActiveConnection property.
      # No Need to create a RecordSet object, so the next two lines are commented out
      # oRS = Dispatch('ADODB.RecordSet')
      # oRS.ActiveConnection = oConn
    
      # Execute a query that returns a RecordSet
      (oRS, result) = oConn.Execute("SELECT zipcode, city, state FROM zipcode ORDER BY id")
      # Execute a stored proc that returns a RecordSet
      (oRS, result) = oConn.Execute("selZipCode")
    
       while not oRS.EOF:
            # rest of code
        
    The above techniques can be used if the query is a simple one. However, if you have a query where you need to build the string like this:
      sql = "SELECT zip, city, state FROM zipcode WHERE city = '" + cityvar + "' ORDER BY id"
    
    you may run into problems if cityvar has a quote character in it. As we discussed in chapter 3 of this tutorial, the best way to get around this is to use parameters. We will demonstrate how to use parameters using a Command object in the next chapter.


    <<Previous: The ADO Connection Object ^Up to Mayukh's World^ Next: The ADO Command Object >>

    Copyright © 2004 Mayukh Bose. All rights reserved.
    This work may be freely reproduced provided this copyright notice is preserved.
    OPTIONAL: Please consider linking to this website (http://www.mayukhbose.com/) as well.