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 7: The ADO Command Object

    The Command object is used to perform a query or execute a stored procedure. Thus, it can be used to create, delete, update or select records. If it is used to retrieve data, then the data will be returned via a RecordSet object. One of the major advantages of a Command object is the ability to use parameters. A complete glossary of all the properties and methods of a Command object is available from the MSDN Library and so we won't repeat all that information here. As before, the engine is assumed to be MySQL for this example, but it could easily apply to any other database engine by merely changing the ConnectionString property. The rest of the code can remain the same for any database engine.

    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 Command object and open a table
      oCmd = Dispatch('ADODB.Command')
      oCmd.ActiveConnection = oConn    # Set the Command to connect thru oConn
      oCmd.CommandType = adCmdTable    # Set the CommandType to open a table
      oCmd.CommandText = "zipcode"     # called zipcode
      oCmd.Prepared = True             # Set the Command object to prepare the query
                                       # ahead of time
    
      # Now execute the command object and collect the results in a recordset
      (oRS, result) = oCmd.Execute()
    
      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
      oCmd = None
      oConn.Close()
      oConn = None    
        
    Nothing very different from the previous page where we opened a table. However, we are only just beginning to explore the Command object capabilities. Instead of opening a table, let's now execute a query. This can be done by just changing two lines in the above code, where we set the CommandType and CommandText properties.
      oCmd.CommandType = adCmdText     # Set the CommandType to open a query
      oCmd.CommandText = "SELECT city, state, zip FROM zipcode"
        
    As you can see, the code is still very similar to how we used a RecordSet object. In fact, we could have done this by using the Open() method of a RecordSet object that we used in the previous chapter. Now comes the REALLY BIG advantage of using a Command object to execute queries. Let's say you need to work with a query like this:
    SELECT city, state, zip FROM zipcode WHERE city = 'some_city_entered_by_user'
    If you use a RecordSet object, you will need to build a new SQL string each time the user enters a new city name. Also, you will need to perform some checking to see if the string entered by the user contains quotes, percent signs or any other special characters and escape them out accordingly.

    However, with a Command object using parameters, you can get around both these problems. Recall in chapter 3, we discussed how parameters can make our queries much more efficient. Now let's put that theory into practice here. We're going to assume a case where the user is prompted to enter two zip codes and the program will return all the places between the two zipcodes.
      from win32com.client import Dispatch
      from adoconstants import *
    
      # Create DB 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 command object and prepare the query
      oCmd = Dispatch('ADODB.Command')
      oCmd.ActiveConnection = oConn
    
      # Create a query that accepts two parameters
      oCmd.CommandType = adCmdText
      oCmd.CommandText = "SELECT * FROM zipcode WHERE zipcode >= ? AND zipcode <= ?"
    
      # Now create the Parameter objects
      oParamZip1 = oCmd.CreateParameter('minzip', adInteger, adParamInput)
      oParamZip2 = oCmd.CreateParameter('maxzip', adInteger, adParamInput)
      oCmd.Parameters.Append(oParamZip1)
      oCmd.Parameters.Append(oParamZip2)
    
      # Request the query to be prepared ahead of time
      oCmd.Prepared = True
    
      while 1:
          # Ask the user for input
          min = raw_input("Enter min zip code: ")
          if (min == ""):
              break
          max = raw_input("Enter max zip code: ")
    
          # Set the parameter values and execute our query
          oParamZip1.Value = min
          oParamZip2.Value = max
          (oRS, result) = oCmd.Execute()
    
          # Print out the query results
          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
              oRS.MoveNext()
    
      # Close and clean up    
      oRS.Close()
      oRS = None
      oCmd = None
      oConn.Close()
      oConn = None
        
    As you can see in the above code, we are asking the user for input and executing the query using the input values as parameters. It is not necessary to rebuild the SQL statement again for different input values. All we have to do is change the values of the two Parameter objects and call the Execute method again. Hence, if there's a need to execute the same query over and over again with different values, using this method provides some significant performance advantages.

    Also note that we created two parameters of type adInteger above. Different parameter types require different number of arguments to CreateParameter(). For example, creating a parameter of type adVarChar requires CreateParameter() to be called with four arguments. Details about creating various parameter types are presented in Chapter 10 and you will also encounter them in the next few chapters.

    In the next chapter, we will explore how to use the Command object to execute Insert and Update statements.


    <<Previous: The ADO RecordSet Object ^Up to Mayukh's World^ Next: More on the 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.