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