Python Tutorials: ADO and Python
Thursday, November 23, 2017
Chapter 2: Basic ADO Objects
There are quite a few types of ADO objects but our discussion will
be restricted to five main ones. In this section, we will quickly cover five objects and then
discuss each one in detail in succeeding chapters. Additional information may be obtained from
the Microsoft MSDN website.
The connection object is used to manage a connection to a data
source. You typically supply connection parameters such as the driver name, address of
database server, username, password and various connection options to this object and then
instruct it to connect to the database server. All other ADO objects are then hooked to this
connection object. The connection object also has a method to execute statements directly
without the need for any other ADO object. We will discuss this method, but not use it heavily
for reasons explained later.
The RecordSet object is typically used to iterate through the results
of a query or a stored procedure. The RecordSet object can access a row at a time and has a
collection of Fields, which represent individual columns in the row.
The RecordSet object also has methods to move to the next row, previous row, first row
and last row of a set of results. Additionally, if a stored procedure returns multiple sets of
results with different number of columns and column types (SQL Server can do this), the
RecordSet object can also jump between each set of results. The RecordSet can also execute
its own query/stored procedure (after being attached to a Connection of course) and obtain the
returned results. We will discuss this method, but not use it heavily in our examples.
Instead we will usually assign a RecordSet object to the result of a Command (or a Connection)
object and use the RecordSet to process the rows of results returned by the Command object.
The Field object represents a column in a row of results. As there
are usually multiple columns returned as part of a SQL query, a RecordSet object will contain
as many Field objects as there are columns. A Field object contains information about the
column that it represents, such as value of the column, type of column (integer, character,
float, money etc.), maximum length of the field, precision etc. We will mostly deal with Fields
as part of the RecordSet object in this tutorial.
The Command object is typically attached to a Connection object and
then used to execute queries and stored procedures or access tables through the Connection.
If there are results returned by the Command object (say when you run a SQL query), these are
assigned to a RecordSet object, which is then used to iterate through the rows. Most of our
examples will use this paradigm. A major feature of the Command object is the ability to use
parameters for queries and stored procedures. We will see why this is a good thing in this
chapter and why it is better to use this than to execute queries directly via the Connection
or RecordSet object.
A Parameter object is typically attached to a Command object. It is
used to pass parameter values to the Command object. A Command object can have multiple
Parameter objects attached to it. There are several benefits to using Parameter objects
though novice programmers almost never use them. In the next section, we will discuss the
reasons and advantages of using Parameter objects.
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.