Programmers Guide

Adaptive Server Enterprise

Extension Module for Python
15.7
DOCUMENT ID: DC01692-01-1570-03
LAST REVISED: June 2012
Copyright 2012 by Sybase, Inc. All rights reserved.
This publication pertains to Sybase software and to any subsequent release until otherwise indicated in new editions or
technical notes. Information in this document is subject to change without notice. The software described herein is furnished
under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.
Upgrades are provided only at regularly scheduled software release dates. No part of this publication may be reproduced,
transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior
written permission of Sybase, Inc.
Sybase trademarks can be viewed at the Sybase trademarks page at http://www.sybase.com/detail?id=1011207. Sybase and
the marks listed are trademarks of Sybase, Inc. indicates registration in the United States of America.
SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered
trademarks of SAP AG in Germany and in several other countries all over the world.
Java and all Java-based marks are trademarks or registered trademarks of Oracle and/or its affiliates in the U.S. and other
countries.
Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.
All other company and product names mentioned may be trademarks of the respective companies with which they are
associated.
Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS
52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.
Sybase, Inc., One Sybase Drive, Dublin, CA 94568.
Contents
Adaptive Server Enterprise Extension Module for
Python ................................................................................1
Installing the Extension Module for Python ....................1
Extension Module for Python Configuration Overview
....................................................................................2
Developing Python Applications .....................................4
Loading the Extension Module for Python ............4
Establishing and Closing a Connection to
Adaptive Server Using Python ...........................4
Accessing and Updating Data Using Python .........4
Passing Input and Output Parameters to Stored
Procedures ........................................................5
Compute Rows Processing ...................................6
Parameter Support for Dynamic Statements and
Stored Procedures ............................................6
Extension Module for Python API Reference .........7
Glossary ...............................................................................13
Index ..................................................................................15

Programmers Guide iii

Contents

iv Adaptive Server Enterprise Extension Module for Python

 Adaptive Server Enterprise Extension Module for Python

Adaptive Server Enterprise Extension Module

for Python

The extension module for Python, sybpydb, provides a Sybase specific Python interface that

is used to execute queries against an Adaptive Server Enterprise database.
The extension module implements the Python Database API specification version 2.0 with
extensions. For more information about the API specification, see http://www.python.org/
dev/peps/pep-0249.

Extension Module for Python Data Flow

The following diagram describes how data is moved through the required components.

Required Components
Access to an Adaptive Server database using the Python programming language requires
these components:
sybpydb extension module for the Python scripting language.
Open Client SDK provides application development tools that allow access to data
source, information application or system service.
DBCAPI is a function library that acts as intermediate conversion layer between the
extension module and the CT-Library.
CT-Library (CT-Lib API) is part of the Open Client suite. CT-Library sends commands
to Adaptive Server and processes results.

Version Requirements
For information about platform support, see the Software Developers Kit and Open Server
Installation Guide for your platform.
Adaptive Server Enterprise version 15.7 or later
Python installation version 2.6 built-in threaded mode.
Open Client SDK version 15.7 or later.

Installing the Extension Module for Python

The extension module for Python is a component you can install through the Sybase Installer.
The extension module for Python is an optional installation component when you choose
Custom as the installation type. The extension module is installed by default if the installation

Programmers Guide 1
Adaptive Server Enterprise Extension Module for Python

type you choose is Typical or Full. For complete installation and configuration instructions,
see the Software Developers Kit and Open Server Installation Guide for your platform.

Extension Module for Python Configuration Overview

Complete basic configuration tasks for a Python application to make a connection and execute
commands.

Configuration Tasks
Configuration task include:
Python module search path
The name and address of the target server
Security and Directory Services
Runtime configuration through the ocs.cfg file

Python Module Search Path

Python searches for an imported module in the list of directories given by the Python variable
sys.path. This variable is initialized from the directory containing the application, and in
the list of directories specified by the environment variable PYTHONPATH, which uses the
same syntax as the shell variable PATH, that is, a list of directory names. If you have not set
PYTHONPATH, or if the file is not found, the search continues in an installation-dependent
default path.
To use the Adaptive Server Enterprise extension module for Python in an application, you
must set either PYTHONPATH, or the Python variable sys.path to one of the following
directory paths (these are the default directories where the different versions of the Adaptive
Server Python extension module are installed):
Platform Default Installation Path Python Version
Windows $SYBASE\$SYBASE_OCS 2.6
\python\py-
thon26_64\dll

$SYBASE\$SYBASE_OCS 2.7
\python\py-
thon27_64\dll

$SYBASE\$SYBASE_OCS 3.1
\python\py-
thon31_64\dll

2 Adaptive Server Enterprise Extension Module for Python

 Adaptive Server Enterprise Extension Module for Python

Platform Default Installation Path Python Version

All other platforms $SYBASE/$SYBASE_OCS/ 2.6, 2.7
python/python26_64r/
lib

$SYBASE/$SYBASE_OCS/ 3.1
python/python31_64r/
lib

Target Server Name and Address

The name of the target server is obtained from one of these sources, in this order:
The client application, which can provide the server name in the call to
sybpydb.connect().
The DSQUERY environment variable, if the application does not specify the target server.
The default name SYBASE, if DSQUERY is not set.
The target servers address is obtained from the directory service or from the platform-
dependent interfaces file. Create a server entry in the interfaces file or the LDAP directory
service. See the Open Client and Open Server Configuration Guide for your platform.

Security and Directory Services

To change the directory driver or security driver, configure libtcl.cfg
Directory driver in the [DIRECTORY] section.
Security driver in the [SECURITY] section.
See the Open Client and Open Server Configuration Guide for your platform.

Runtime Configuration
Use the runtime configuration file ocs.cfg to set:
Property values
Server option values
Server capabilities
Debugging options
See Using the Open Client and Open Server Runtime Configuration File in the Open Client
Client-Library/C Reference Manual for information about the file syntax and the properties
that can be set in the file.

Programmers Guide 3
Adaptive Server Enterprise Extension Module for Python

Developing Python Applications

Write Python scripts using the sybpydb interface.

Loading the Extension Module for Python

Use the import statement to load the extension module for Python.
To use the sybpydb module from a Python script, you must first load it by including this line at
the top of the python script.
import sybpydb

Establishing and Closing a Connection to Adaptive Server Using

Python
Open and close a connection to Adaptive Server.
The connect method opens a database connection and accepts the following keyword
arguments:
user the user login name that the connection uses to log on to a server.
password the password that a connection uses when logging on to a server.
servername defines the Adaptive Server name to which client programs connect. If you
do not specify this argument, the DSQUERY environment variable defines the Adaptive
Server name.
The connect method returns a connection object, which is then used to close the connection.
This example shows how an application opens and closes a connection:
import sybpydb

# Create a connection
conn = sybpydb.connect(user='john', password='sybase')

# Close the connection.

conn.close()

Accessing and Updating Data Using Python

After a connection is established, use a cursor object to manage the context of a fetch
operation.
A cursor object provides access to methods to prepare and execute queries, and fetch rows
from a result set. The cursor object is obtained from the connection object using the cursor
method. This example shows how an application accesses and updates data:
import sybpydb

#Create a connection.
conn = sybpydb.connect(user='sa')

4 Adaptive Server Enterprise Extension Module for Python

 Adaptive Server Enterprise Extension Module for Python

# Create a cursor object.

cur = conn.cursor()

cur.execute("drop table footab")

cur.execute("create table footab ( id integer, first char(20)
null, last char(50) null)")
cur.execute("insert into footab values( ?, ?, ? )", (1, "John",
"Doe"))
cur.execute("select * from footab")
rows = cur.fetchall()
for row in rows:
print "-" * 55
for col in range (len(row)):
print "%s" % (row[col]),

#Close the cursor object

cur.close()

#Close the connection

conn.close()

Passing Input and Output Parameters to Stored Procedures

Starting with 15.7 ESD#3, the Adaptive Server Enterprise extension module for Python
supports passing input and output parameters to stored procedures.
Use the callproc() method of the Cursor object to call a stored procedure. If there is an error in
executing the stored procedure, callproc() throws an exception and you can retrieve the status
value using the proc_status attribute. This support is an extension to the Python DBAPI
specification.
This is a sample Python application with multiple row results:
import sybpydb
#Create a connection.
conn = sybpydb.connect(user='sa')
# Create a cursor object.
cur = conn.cursor()
# Call the stored procedure
try:
cur.callproc('myproc')
continue = True
while(continue == True):
row = cur.fetchall()
continue = cur.nextset()
except sybpydb.Error:
print("Status=%d" % cur.proc_status)

To specify output parameters, the extension module provides the OutParam constructor. This
support is an extension to the Python DBAPI specification. The callproc() method returns a
list of all the parameters passed to the method. If there are output parameters, and no result sets
generated from the store procedure, the list contains the modified output values as soon as
callproc() completes. However, if there are result sets, the list does not contain modified

Programmers Guide 5
Adaptive Server Enterprise Extension Module for Python

output values until all result sets from the stored procedure have been retrieved using the
fetch*() methods and a call to nextset() is made to check if there are any more result sets. The
nextset() method must be called even if only one result set is expected.

This is a sample Python application with output parameters:

import sybpydb
#Create a connection.
conn = sybpydb.connect(user='sa')
# Create a cursor object.
cur = conn.cursor()
cur.execute("""
create procedure myproc
@int1 int,
@int2 int output
as
begin
select @int2 = @int1 * @int1
end
""")
int_in = 300
int_out = sybpydb.OutParam(int())
vals = cur.callproc('pyproc', (int_in, int_out))
print ("Status = %d" % cur.proc_status)
print ("int = %d" % vals[1])
cur.connection.commit()
# Remove the stored procedure
cur.execute("drop procedure myproc")
cur.close()
conn.close()

More examples of different output parameter types are available in the sample program
callproc.py.

Compute Rows Processing

Starting with 15.7 ESD#3, the Adaptive Server Enterprise extension module for Python
supports compute rows processing.
An example is available in the sample program compute.py.

Parameter Support for Dynamic Statements and Stored Procedures

Starting with 15.7 ESD#4, the Adaptive Server Enterprise extension module for Python
supports decimal, money, and LOB as parameters for dynamic statements and stored
procedures.

Decimal and money type parameters

The following is an example usage of decimal and money as parameters for a stored
procedure:
cur.execute("""
create procedure pyproc
@m1 money,

6 Adaptive Server Enterprise Extension Module for Python

 Adaptive Server Enterprise Extension Module for Python

@m2 money output,

@d1 decimal(5,3),
@d2 decimal(5,3) output,
as
begin
select @d2 = @d1
select @m2 = @m1
end
""")

dec_in = decimal.Decimal('1.23')
dec_out = sybpydb.OutParam(decimal.Decimal('0.00'))

dec_in = decimal.Decimal('1.23')
dec_out = sybpydb.OutParam(decimal.Decimal('0.00'))
m_in = decimal.Decimal('9.87')
m_out = sybpydb.OutParam(decimal.Decimal('0.00'))
vals = cur.callproc('pyproc', (int_out, dec_in, dec_out, m_in,
m_out))

print ("Status = %d" % cur.proc_status)

print ("decimal = %s" % vals[1])
print ("money= %s" % vals[3])

Support for date, time, datetime, and float parameters for stored procedures
The Adaptive Server Enterprise extension module for Python supports for date, time,
datetime, and float parameters for stored procedures.
See callproc.py sample that demonstrates calling stored procedures with parameters of
different datatypes including date, time, datetime, float, and integer. The sample also
demonstrates the handling of output parameters.

Extension Module for Python API Reference

The sybpydb extension interface API.

Module Interface Methods

The API used for module interface.
connect

Constructs a connection object representing a connection to a database. The method accepts

these keyword arguments:
user the user login name that the connection uses to log on to a server.
password the password that a connection uses when logging on to a server.
servername defines the Adaptive Server name to which client programs connects. If this
argument is not specified then the DSQUERY environment variable defines the Adaptive
Server name.
sybpydb.connect(user='name', password='password string',
servername='ase servername')

Programmers Guide 7
Adaptive Server Enterprise Extension Module for Python

Module Interface Constants

The constants used for the extension module interface.

apilevel

String constant stating the supported DB API level. The default value is 2.0.
class sybpydb.apilevel

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. The
value for this constant is qmark. The interface expects question mark style parameter
formatting, for example:
'...WHERE name=?'
class sybpydb.paramstyle

threadsafety

Integer constant stating the level of thread safety that the interface supports. The threadsafety
constant value for the module is 1, which indicates that the module can be shared but not the
connections.
class sybpydb.threadsafety

Connection Object Methods

The APIs used for connection objects.

close()

Closes the connection to the server. The connection is unusable after the call, and raises an
exception if any operation is attempted. The same applies to cursor objects attempting to
access the connection.
connection.close()

commit()

Executes the command commit.

connection.commit()

rollback()

Executes the command rollback.

connection.rollback()

cursor()

This method constructs a new cursor object using the connection.

8 Adaptive Server Enterprise Extension Module for Python

 Adaptive Server Enterprise Extension Module for Python

connection.cursor()

messages()

This is a Python list object to which the module appends tuples (exception class and exception
object) for all messages that the module receives for this connection. An error on any cursor
obtained from the same connection object, is appended to the messages attribute of the
connection object of the cursor.
connection.messages()

Usage example:
try:
cur.execute("select ...")
except sybpydb.Error:
for err in cur.connection.messages:
print("Exception %s, Value %s", % err[0], err[1])

Cursor Object Methods

The API used for cursor objects.
close
cursor.close()

callproc

Calls a stored database procedure with the given name. After fetching all the result sets and
rows, use the proc_status attribute to check the status result of the stored procedure.
cursor.callproc()

execute

Prepares and executes a query.

cursor.execute()

executemany

Prepares a database operation and executes it against all parameter sequences found in the
sequence seq_of_parameters.
cursor.executemany()

fetch

Fetches the next row of a query result set, returning a single sequence, or None when no more
data is available.
cursor.fetch()

fetchmany

Fetches the next set of rows of a query result, returning a sequence of sequences, for example,
a list of tuples. An empty sequence is returned when no rows are available.

Programmers Guide 9
Adaptive Server Enterprise Extension Module for Python

cursor.fetchmany()

fetchall

Fetches all (remaining) rows of a query result, returning them as a sequence of sequences.
cursor.fetchall()

description

A read-only attribute describing the column information.

cursor.description()

nextset

Forces the cursor to skip to the next available set, discarding any remaining rows from the
current set.
cursor.nextset()

arraysize

This read/write attribute specifies the number of rows to fetch at a time with fetchmany(). It
defaults to 1 which indicates to fetch a single row at a time.
cursor.arraysize()

proc_status

Read/write attribute specifies the number of rows to fetch at a time with fetchmany(). Defaults
to 1 which indicates to fetch a single row at a time.
cursor.proc_status

Warning and Error Messages

All error and warning information is available through exceptions and subclasses.
Warning

Exception raised for warnings. A subclass of the Python StandardError exception.

sybpydb.Warning

Error

Exception that is the base class of all other exceptions defined by sybpydb. Error is a subclass
of the Python StandardError exception.
sybpydb.Error

InterfaceError

Exception raised for errors that are related to the database interface rather than the database
itself. It is a subclass of Error.
sybpydb.InterfaceError

10 Adaptive Server Enterprise Extension Module for Python

 Adaptive Server Enterprise Extension Module for Python

DatabaseError

Exception raised for errors that are related to the database. It is a subclass of Error.
sybpydb.DatabaseError

DataError

Exception raised for errors that are related to problems with the processed data. It is a subclass
of DatabaseError.
sybpydb.DataError

OperationalError

Exception raised for errors that are related to problems to the operation of the database but are
not necessarily under the control of the programmer. It is a subclass of DatabaseError.
sybpydb.OperationalError

IntegrityError

Exception raised when the relational integrity of the database is affected. It is a subclass of
DatabaseError.
sybpydb.IntegrityError

InternalError

Exception raised when the database encounters an internal error. It is a subclass of

DatabaseError.
sybpydb.InternalError

ProgrammingError

Exception raised for programming errors. It is a subclass of DatabaseError

sybpydb.ProgrammingError

NotSupportedError

Exception raised when an unsupported method or database API is used. It is a subclass of

DatabaseError.
sybpydb.NotSupportedError

Programmers Guide 11
Adaptive Server Enterprise Extension Module for Python

12 Adaptive Server Enterprise Extension Module for Python

 Glossary

Glossary
Glossary of term specific to scripting languages.
Client-Library part of Open Client, a collection of routines for use in writing client
applications. Client-Library is designed to accommodate cursors and other advanced
features in the Sybase product line.
CS-Library included with both the Open Client and Open Server products, a collection
of utility routines that are useful to both Client-Library and Server-Library applications.
CT-Library (CT-Lib API) is part of the Open Client suite and is required to let an
scripting application connect to Adaptive Server.
DBD database vendor-specific-driver that translates DBI database API calls into a form
that is understood by the target database SDK.
Extension or module the Python language can be extended by modules that are written
in Python.
Python is an interpreted, general-purpose high-level programming language. For more
information, go to http://www.python.org.
thread a path of execution through Open Server application and library code and the
paths associated stack space, state information, and event handlers.
Transact-SQL an enhanced version of the database language SQL. Applications can use
Transact-SQL to communicate with Adaptive Server Enterprise.

Programmers Guide 13
Glossary

14 Adaptive Server Enterprise Extension Module for Python

 Index

Index
C
components I
description 1
input parameters 5
required 1
installation options 1
compute rows processing 6
configuration
runtime 2 O
search path 2
security and directory services 2 output parameters 5
target server 2
task 2 P
connection
closing 4 parameter support
establishing 4 dynamic statements 6
cursor object 4 stored procedures 6
parameters
D date 6
datetime 6
data decimal 6
accessing 4 float 6
updating 4 money 6
data flow diagram 1 time 6

E S
extension module stored procedures 5
connection object methods 8 sybpydb interface 4
cursor object methods 9
interface constants 8
interface methods 7 V
loading 4
Python API Reference 7 version requirements 1
warning and error messages 10
W
G
warning and error messages 10
Glossary 13

Programmers Guide 15
Index

16 Adaptive Server Enterprise Extension Module for Python