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

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

    PropertyDescription
    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 = 60
    
    you 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 EngineConnectionString
    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.


    <<Previous: ADO and Python Basics ^Up to Mayukh's World^ Next: The ADO RecordSet 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.