Chapter 15 - Developing: Applications - Migrating Python

Article
12/05/2007

Introduction and Goals

This chapter contains a detailed discussion of changes that must be made to existing Python applications to work with Microsoft® SQL Server. At the conclusion of this chapter, the Python application should be capable to successfully connect to the SQL Server database that was migrated from Oracle. The solution can then be tested.

As discussed in the "Define the Solution Concept" section of Chapter 2, "Envisioning Phase," there are four different strategies available for transitioning applications in an Oracle to SQL Server migration project. The strategies are:

Interoperate the application with UNIX
Port or rewrite the application to the Microsoft .NET platform
Port or rewrite the application to Microsoft Win32® Platform
Quick port using the Microsoft Windows® Services for UNIX 3.5 platform

Python is a portable, platform-independent, and general-purpose language with support for writing database client applications. Database capabilities are modularized, and they can be augmented through the use of additional APIs.

Python database modules that are based on the Database API (DB-API) specification can be used to access relational databases, including SQL Server and Oracle. As long as the database module used to access the Oracle database adheres to the DB-API specification, porting to SQL Server is straightforward and can be done with minimal changes. If the existing database drivers do not meet DB-API specifications, the driver will need to be replaced and configured.

Because of the cross-platform capabilities and the use of modular database drivers, some of the migration strategies are more feasible than others. For example, because Python can be ported to Windows, there is no need to rewrite the application in the .NET framework or for the Win32 environment. Also, because the application can run within the Windows environment, a quick port using Windows Services for UNIX is not necessary in most cases.

Based on the available migration strategies, two scenarios can be developed to migrate the Python application. These scenarios include:

Scenario 1: Interoperating Python on UNIX with SQL Server

If the business requirements do not include eliminating the UNIX environment, an interoperation strategy can be implemented quickly. Few changes need to be made to the source code, and installing a new driver allows the Python application to connect to a SQL Server database. Interoperation can also be used as an interim step, if the migration is performed in phases.
Scenario 2: Port the Python Application to Win32

Python applications can also be ported to run natively on the Windows platform. As with interoperation, few changes need to be made to the source code. These changes are usually related to connectivity issues.

Note: If your Python applications use UNIX system calls extensively (such as frequently calling

exec

), porting them to Windows Services for UNIX/Interix may be a suitable option because Interix has more support for the desired system calls. Chapter 2, "Envisioning Phase," provides a more detailed discussion of when choosing Windows Services for UNIX would be more appropriate when moving to a Windows-based platform.

You will need the following to implement this option (porting Python applications to SFU/SQL Server):

A port of Python for interix has been made available by Interop Systems and can be downloaded from https://www.interopsystems.com/tools/warehouse.htm.
A connectivity driver to the SQL Server database. This is provided by the port of FreeTDS on Interix and is downloadable from Interop Systems at https://www.interopsystems.com/tools/db.htm. FreeTDS provides two connectivity options for the Python applications to connect to the SQL Server database. One is a library called CTlib, The other is an ODBC driver.
If you chose the CTLib option for your database connectivity driver, you will need the Sybase Connectivity Module for your Python application to make appropriate calls to CTLib. The source code for this Sybase Connectivity Module can obtain from Object Craft at https://www.object-craft.com.au/projects/sybase/. This solution is not available pre-compiled from Interop Systems but is a simple port for a UNIX developer.
If you chose ODBC for your database connectivity, you will need an additional interface for the Python application to make ODBC calls. This is available as a commercial package called mxODBC and can be obtained from https://www.egenix.com.
If you use the ODBC driver, you will also need an ODBC driver manager. Two different ODBC driver managers iODBC and unixODBC are available for Windows Services for UNIX from https://www.interopsystems.com/tools/warehouse.htm.

This technology option (porting Python applications to SFU), however, has not been fully tested as a part of development of this solution and therefore has not been detailed further.

Scenario 1: Interoperating Python on UNIX with SQL Server

Two common modules used for connecting a Python application to an Oracle database in the UNIX environment are:

DCOracle2
mxODBC

DCOracle2 (https://www.zope.org/Products/DCOracle/) does not support SQL Server, but mxODBC (https://www.egenix.com/) supports the ODBC interface and can be used with SQL Server. If the existing application uses DCOracle2, this interface will need to be replaced to allow connectivity with SQL Server.

Because ODBC is not database-specific, only minor modifications are needed to connect to the migrated database.

Case 1: Interoperating Using the mxODBC Module

To interoperate a Python-based application using DCOracle2 module with SQL Server, follow these steps:

Note that these steps assume that a DCOracle2 module will be replaced with the mxODBC module.

Install the ODBC driver.

In order for mxODBC to connect to SQL Server, an ODBC driver manager must be installed. Two available driver managers are:
- iODBC, available at https://www.iodbc.org/.
- DataDirect ODBC for UNIX Platforms, available at https://www.datadirect.com/techzone/use_case/odbc-unix-sqlserver-main/index.ssp. For detailed installation instructions, refer to https://www.datadirect.com/developer/common_use_cases/odbc_unix_sqlserver_install/index.ssp.
Install the mxODBC module based on the Python version being used.

For installation instructions, refer to https://www.egenix.com/files/python/mxODBC-Zope-DA.html#Installation.
Configure the ODBC driver to work with mxODBC through the driver manager.
Create a SQL Server data source.

To function, ODBC needs a data source to connect to the database. The Data Source Name (DSN) is generally defined in an odbc.ini file that is used by the driver manager. The DSN is used by the driver manager to load the ODBC driver.

iODBC offers a graphical user interface to set up the DSN. Complete instructions are available at https://www.iodbc.org/index.php?page=docs/odbcstory.

The DSN can also be configured by manually modifying the odbc.ini file. The following example file uses a SQL Server DSN.
```
[ODBC Data Sources]
```

SS_HR_DB=Sample MS SQLServer [SS_HR_DB] Driver=/opt/odbc/lib/mxODBC.so Description=SQL Server 2000 Database=hrapp LogonID=daveb Password=cougar Address=win2kdbp1,1433

Test the connectivity.

iODBC contains a utility named odbctest which can be used to test DSN entries and interact with the database by connecting and issuing queries directly without any code.

The mxODBC package consists of a test script that can also be used to verify the database connectivity. To perform the test, execute the following command:

python mx/ODBC/Misc/test.py
Modify the application to use mxODBC instead of the existing DCOracle2 API.

Note The examples in this section use the iODBC driver. Syntax may vary slightly based on the ODBC driver that is used in your solution.

There are several, minor changes that may need to be made to the existing Python application to allow connectivity with the SQL Server database. These common issues that should be modified include:
- Import statements
  
  The import statement, generally found at the head of the application code, should be modified to include the new database module.
  
  If the application currently uses the DCOracle2 module, the import statement is as follows:
```
import DCOracle2
```
  This entry should be modified to allow for the mxODBC module. The import statement should be changed as follows:
```
import mx.ODBC.iODBC
```
- Connection objects
  
  The connection object will also need to be modified to point to the new data source. The current entry should be similar to the following example:
```
db = DCOracle2.Connect('scott/tiger@orahrdb')
```
  This entry should be modified to reflect the new data source. In the following example, the DriverConnect() API is used to pass additional information to SQL Server.
```
db = mx.ODBC.iODBC.DriverConnect('DSN=sqlserverDSN;UID=scott;PWD=tiger')
```
  This iODBC string will have to be modified based on the driver manager in use.
  
  There are other connection methods, such as ODBC() and connect(), which are also supported by mxODBC. The advantage with DriverConnect() is that it allows more configuration information, such as the log file name, to be passed as part of the connection string.
- Cursor Execution
  
  Change the cursor object execute() and executemany() method calls to use a question mark (?) for parameters instead of numeric parameters (:1) or named parameters (:column_name). The following example should be similar to your existing Python code being used with Oracle.
```
c = db.cursor()
```

custId = "ALFKI" c.execute("Select * from customers where customerID = :1", custId) ... id = "123" name = "Bill" desc = "CoffeeShop" c.execute("insert into categories " +
          " (categoryid, categoryName, description)"  +
          "values (:cid,:cname,:cdesc)"
          ,cid = id , cname = name ,cdesc= desc
         )

    The code should be modified for use with SQL Server. In the following code, note that numeric and named parameters have been replaced.
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">c= db.cursor()

custId = "ALFKI" c.execute("Select * from customers where customerID = ?",(custId,)) ... id = "123" name = "Bill" desc  = "CoffeeShop" c.execute("insert into categories" +
          " (categoryid, categoryName, description)"  +
          "values (?,?,?)"
          ,(id, name, desc)
          )

  - **Cursors — multiple executions**
    
    The executemany() function is used with DCOracle2 to perform multiple inserts with a single call by passing a list of parameters. For example,
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">cursor.executemany("insert into inventoryinfo values (:1,:2)",\

    [('Bill','Dallas'),('Bob','Atlanta'),('David','Chicago'),
     ('Ann','Miami'),('Fin','Detroit'),('Paul','Dallas'),
         ('Scott','Dallas'),('Mike','Dallas'),('Leigh','Dallas')])

    For using multiple cursors on a connection to SQL Server, use the **setconnectoption()** method to set the SQL.CURSOR\_TYPE to SQL.CURSOR\_DYNAMIC as shown as follows and replace the parameter specification style in the **executemany()** call:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml"># Connect to the database

db = mx.ODBC.iODBC.DriverConnect('DSN=sqlserverDSN;UID=scott;PWD=tiger'

Set SQL Server connection option

DB.setconnectoption(SQL.CURSOR_TYPE, SQL.CURSOR_DYNAMIC) cursor = db.cursor () cursor.executemany("insert into inventoryinfo values (?,?)",
         [('Bill','Dallas'),('Bob','Atlanta'),('David','Chicago'),
     ('Ann','Miami'),('Fin','Detroit'),('Paul','Dallas'),
         ('Scott','Dallas'),('Mike','Dallas'),('Leigh','Dallas')])

  - **Stored Procedures**
    
    The DB API 2.0 **callProc()** method for executing stored procedures is not implemented in mxODBC. To overcome this deficiency, a stored procedure can be executed similar to a SQL statement using one of the execute methods. However, a requirement in this case is that the stored procedure returns some result. All calls to **callProc()** need to change to use **cursor.execute**, as shown in the following example:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">c.execute("{call proc_name(?,?)}", (1,2)})</pre>

Change all embedded SQL statements to T-SQL.

This is a step common to all migrations. Refer to Chapter 11, "Developing: Applications — Migrating Oracle SQL and PL/SQL," for a detailed discussion on modifying Oracle SQL to be SQL Server-compliant.

Scenario 2: Port the Python Application to Win32

Because Python is a cross-platform language, porting applications from a UNIX environment to Windows is not much more complex than the steps required for interoperation. The API used in the discussion on porting to the Windows environment is the mxODBC. The strategy, steps, and actual changes required are very similar to those discussed for interoperation. Zope offers an ADO adapter for SQL Server on the Windows platform which can give better performance than the use of ODBC. Details on this can be obtained from https://zope.org/Members/freiser/ZADODA.

Case 1: Porting a Python Application using mxODBC

Most of the following steps are covered in more detail in the "Case 1: Interoperating Using the mxODBC Module" section discussed in scenario 1.

To port the application to Windows, follow these steps:

Download Python for Windows from https://www.python.org.
Install Python on Windows.
Add the Python folder to the Windows CLASSPATH environment variable.
Install the appropriate mxODBC for the version of Python installed.
Create an ODBC DSN for the target database.

Create an ODBC data source to connect to SQL Server. After the DSN is created, a Python application using mxODBC can connect to SQL Server and run queries.
Transport source code to the target server.
Update the application code.

If your application uses DCOracle2, then the application changes required will be similar to those discussed in the "Scenario 1: Interoperating Python on UNIX with SQL Server" section earlier in this chapter. Some variations based on the Windows environment include the import statement and the connection object as shown in the following examples.
- The import statement should be changed from:
```
import DCOracle2
```
  to
```
import mx.ODBC.Windows
```
- The connection object must be initialized as in the following example:
```
db = mx.ODBC.Windows.DriverConnect('DSN=sqlserverDS')
```
  Note This DSN uses Windows Authentication Mode to connect to SQL Server.
Change all embedded SQL statements to T-SQL.

This is a step common to all migrations. Refer to Chapter 11, "Developing: Applications — Migrating Oracle SQL and PL/SQL" for a detailed discussion on modifying Oracle SQL to be SQL Server-compliant.