| My Free Software | |||||||
|
| My Python Code | |||
|
| Python Tutorials and Software | e-mail me |
|
|
|
Chapter 5: The ADO Connection Object
|
The Connection object is our starting point to obtaining data from a source. All the
other ADO objects use the Connection object to point to the data source. Hence, this is
the first object that needs to be created. We will first study some of the properties and
methods that a Connection object provides and then see how they can be called from Python.
The following table outlines some of the common properties of the Connection object. Most of these properties should be set before a connection is made. The properties control how the connection is made. The bold green text indicates the most useful ones. |
| Property | Description |
| Attributes | Sets/returns the attributes. |
| CommandTimeout | Sets/returns the maximum number of seconds to wait for a command to complete. This value will be inherited by other ADO components that use this connection object, unless they override their value explicitly. |
| ConnectionString | Sets/returns the parameters used for the connection. This is probably the most important property of them all, as it can be used to set all other properties instead of setting them one at a time. We will cover some examples below. |
| CursorLocation | Sets/returns the location of the cursor service. This property only applies to certain database engines. |
| DefaultDatabase | Sets/returns the default database name that the object switches to when it connects to the datasource. |
| IsolationLevel | Sets/returns the transaction isolation level. Again, this may apply to only certain database engines. |
| Mode | Sets/returns the access permissions |
| Provider | Sets/returns the provider name (i.e.) the driver to be used to connect to the DB. |
| State | Returns the state of the connection object, whether connected or not connected to the datasource. |
| Version | Returns the version number of the ADO objects |
|
Next, we will study the methods that the Connection object has. As
before, the the bold green text indicates the most useful
ones. |
| Property | Description |
| BeginTrans | Begins a new transaction. Only useful if the datasource actually supports transactions, which not all engines do. |
| Cancel | Cancels the currently executing statement. |
| Close | Closes the connection. |
| CommitTrans | Commits all the changes made in the current transaction and then ends the transaction. You should have called BeginTrans first to start the transaction. Not all engines support transactions. |
| Execute | Executes a query, stored procedure or a statement specific to the datasource. |
| Open | Opens a connection to the datasource. |
| OpenSchema | Returns schema information about the datasource. This is not supported by all database engines, so you may end up with no information if your database doesn't support it. |
| RollbackTrans | Cancels any changes made in the current transaction and ends the transaction. Only applies if your database engine supports transactions. You should have called BeginTrans first also. |
The way to use a Connection object is to first create the object, then set the various
properties (depending on your database) and then call Open() to connect to the database. The
ConnectionString property is the most important property of them all. Instead of
setting individual properties like this:
oconn.Provider = "SQLOLEDB" oconn.CommandTimeout = 60you can instead set all the properties in one shot with the ConnectionString property. All you need to do is separate each argument with a semicolon(;) character. oconn.ConnectionString = "provider=SQLOLEDB; CommandTimeout=60; ... more properties ..."The ADO Connection piggybacks on top of other connection methods such as ODBC, OLE DB, RDS etc. Aside from the ConnectionString though, the other ADO components have no idea of what specific connection method is being used. This is one of the advantages of abstraction that ADO provides to the user. Now, on to some real Python code. We will assume we're connecting to SQL server for this example using OLEDB and then show some connection strings for other database engines. The code is very simple and the comments actually outweigh the code, so you should have no trouble understanding what is going on here.
# First import two useful modules
from win32com.client import Dispatch
from adoconstants import *
# Create the ADO Connection object via COM.
oConn = Dispatch('ADODB.Connection')
# Now set the connection properties via the ConnectionString
# We're connecting to a SQL Server on 192.168.1.100 using OLEDB.
oConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=192.168.1.100;" + \
"uid=my_user_name;pwd=my_password;database=my_database_name"
# Now open the connection
oConn.Open()
# Instead of setting the ConnectionString and then calling Open, it is also
# possible to call the Open method directly and pass the connection string
# as an argument to the method. {i.e.)
# oConn.Open("Provider=SQLOLEDB.1; Data Source=.....")
if oConn.State == adStateOpen:
# Do something here
print "We've connected to the database."
# Execute a stored procedure.
oConn.Execute("myStoredProcedure")
# Execute an INSERT statement
oConn.Execute("INSERT INTO table(col1, col2) VALUES (2, 'Test String')")
else:
print "We failed to connect to the database."
# Close up the connection and unload the COM object
if oConn.State == adStateOpen: oConn.Close()
oConn = None
That was extremely easy to work with, wasn't it? Now you may not have access to a box
running SQL Server, but not to worry. You can make it connect to just about any database
engine by just altering the ConnectionString property. The following table illustrates
connection strings for some common engines. If your engine is not in the list below, it
is fairly easy to find it by using Google and
searching for "ADO ConnectionString <your database engine>".
|
| Database Engine | ConnectionString |
| DBASE (using ODBC) | Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=C:\path\to\database |
| Excel (using ODBC) | Driver={Microsoft Excel Driver (*.xls)};DriverID=790;Dbq=C:\path\to\spreadsheet;DefaultDir=C:\path\to\defaultdir |
| Excel (using OLE DB) | Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\To\sheet.xls; |
| Access (using ODBC) | Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\path\to\database.mdb;Uid=username;Pwd=password
You can also pass additional options -- for example Exclusive=1; sets it to be opened in exclusive mode. |
| Access (using OLE DB) | Using standard security Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\to\database.mdb;User Id=username;Password=somepassword; Using Workgroup security str="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=database.mdb;JET OLEDB:System Database=mysystem.mdw;" oConn.Open(str, "my_user_name", "my_password") |
| Firebird | Remote Database Provider='LCPI.IBProvider';Data Source='remotehost:C:\path\to\database.fdb';User ID='username';Password='pwd';Auto Commit=true; The above is reported to work for a Firebird database by Edward Diamond (ediamond at water dot ca dot gov). I would presume that it could work on a local Firebird server, simply by removing "remotehost" from the string above. Edward reports that even simple queries don't work without the "Auto Commit" part in the connection string. |
| MySQL (using ODBC) | Local Database Driver={MySQL ODBC 3.51 Driver};Server=localhost;User=username;Password=mypassword;Database=mydatabase; Remote Database Driver={MySQL ODBC 3.51 Driver};Server=192.168.1.100;Port=3306;User=username;Password=mypassword;Database=mydatabase; There are more parameters that can be set (for example, Option, which controls several connection properties such as logging, packet size limits etc.) See section 3.3 (Connection Parameters) of the MyODBC manual for more information. |
| MySQL (using OLE DB) | Provider=MySQLProv;Server=192.168.1.100;Port=3306;User=username;Password=mypassword;Database=mydatabase; If you have the datasource already set up: Provider=MySQLProv;Data Source=name_of_datasource; You will need to download and install MyOleDB first. Last time I checked MyOLEDB was no longer maintained. |
| Oracle (using ODBC) |
Driver={Microsoft ODBC for Oracle};Server=MyOracleServer;Uid=username;Pwd=password See the MSDN library for additional options. |
| Oracle (using OLE DB) |
Using OLE DB provider from Microsoft Provider=MSDAORA;Data Source=MyOracleDB;User Id=username;Password=password See the MSDN library for additional options. Using OLE DB provider from Oracle Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=username;Password=password |
| SQL Server (using ODBC) |
Standard Security Driver={SQL Server};Server=192.168.1.100;Uid=username;Pwd=password;Database=dbname; Trusted Connection Simply add Trusted_Connection=yes to the above string. See MSDN Library for more options. |
| SQL Server (using OLE DB) |
Provider=SQLOLEDB.1;Data Source=192.168.1.100;Uid=username;Pwd=password;Database=dbname; See MSDN Library for more options. |