 |
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.
|
|