Skip to main content

Teradata SQL Driver for Python

Project description

Teradata SQL Driver for Python

This package enables Python applications to connect to the Teradata Database.

This package implements the PEP-249 Python Database API Specification 2.0.

This package requires 64-bit Python 3.4 or later, and runs on Windows, macOS, and Linux. 32-bit Python is not supported.

For community support, please visit the Teradata Community forums.

For Teradata customer support, please visit Teradata Access.

Please note, this driver may contain beta/preview features ("Beta Features"). As such, by downloading and/or using the driver, in addition to agreeing to the licensing terms below, you acknowledge that the Beta Features are experimental in nature and that the Beta Features are provided "AS IS" and may not be functional on any machine or in any environment.

Copyright 2020 Teradata. All Rights Reserved.

Table of Contents

Table of Contents links do not work on PyPI due to a PyPI limitation.

Features

The Teradata SQL Driver for Python is a DBAPI Driver that enables Python applications to connect to the Teradata Database. The Teradata SQL Driver for Python implements the PEP-249 Python Database API Specification 2.0.

The Teradata SQL Driver for Python is a young product that offers a basic feature set. We are working diligently to add features to the Teradata SQL Driver for Python, and our goal is feature parity with the Teradata JDBC Driver.

At the present time, the Teradata SQL Driver for Python offers the following features.

  • Supported for use with Teradata Database 14.10 and later releases. Informally tested to work with Teradata Database 12.0 and later releases.
  • COP Discovery.
  • Encrypted logon using the TD2, JWT, LDAP, KRB5 (Kerberos), or TDNEGO logon mechanisms.
  • Data encryption enabled via the encryptdata connection parameter.
  • Unicode character data transferred via the UTF8 session character set.
  • Auto-commit for ANSI and TERA transaction modes.
  • 1 MB rows supported with Teradata Database 16.0 and later.
  • Multi-statement requests that return multiple result sets.
  • Most JDBC escape syntax.
  • Parameterized SQL requests with question-mark parameter markers.
  • Parameterized batch SQL requests with multiple rows of data bound to question-mark parameter markers.
  • ElicitFile protocol support for DDL commands that create external UDFs or stored procedures and upload a file from client to database.
  • CREATE PROCEDURE and REPLACE PROCEDURE commands.
  • Stored Procedure Dynamic Result Sets.

Limitations

  • The UTF8 session character set is always used. The charset connection parameter is not supported.
  • The following complex data types are not supported yet: JSON, DATASET STORAGE FORMAT AVRO, and DATASET STORAGE FORMAT CSV.
  • No support yet for data encryption that is governed by central administration. To enable data encryption, you must specify a true value for the encryptdata connection parameter.
  • Laddered Concurrent Connect is not supported yet.
  • No support yet for Recoverable Network Protocol and Redrive.
  • FastExport is not available yet.
  • Monitor partition support is not available yet.

Installation

The Teradata SQL Driver for Python depends on the pycryptodome package which is available from PyPI.

Use pip install to download and install the Teradata SQL Driver for Python and its dependencies automatically.

Platform Command
macOS or Linux pip install teradatasql
Windows py -3 -m pip install teradatasql

When upgrading to a new version of the Teradata SQL Driver for Python, you may need to use pip install's --no-cache-dir option to force the download of the new version.

Platform Command
macOS or Linux pip install --no-cache-dir -U teradatasql
Windows py -3 -m pip install --no-cache-dir -U teradatasql

License

Use of the Teradata SQL Driver for Python is governed by the License Agreement for the Teradata SQL Driver for Python.

When the Teradata SQL Driver for Python is installed, the LICENSE and THIRDPARTYLICENSE files are placed in the teradatasql directory under your Python installation directory.

In addition to the license terms, the driver may contain beta/preview features ("Beta Features"). As such, by downloading and/or using the driver, in addition to the licensing terms, you acknowledge that the Beta Features are experimental in nature and that the Beta Features are provided "AS IS" and may not be functional on any machine or in any environment.

Documentation

When the Teradata SQL Driver for Python is installed, the README.md file is placed in the teradatasql directory under your Python installation directory. This permits you to view the documentation offline, when you are not connected to the Internet.

The README.md file is a plain text file containing the documentation for the Teradata SQL Driver for Python. While the file can be viewed with any text file viewer or editor, your viewing experience will be best with an editor that understands Markdown format.

Sample Programs

Sample programs are provided to demonstrate how to use the Teradata SQL Driver for Python. When the Teradata SQL Driver for Python is installed, the sample programs are placed in the teradatasql/samples directory under your Python installation directory.

The sample programs are coded with a fake Teradata Database hostname whomooz, username guest, and password please. Substitute your actual Teradata Database hostname and credentials before running a sample program.

Program Purpose
BatchInsert.py Demonstrates how to insert a batch of rows
BatchInsPerf.py Measures time to insert one million rows
CharPadding.py Demonstrates the Teradata Database's Character Export Width behavior
CommitRollback.py Demonstrates commit and rollback methods with auto-commit off.
DriverDatabaseVersion.py Displays the Teradata SQL Driver version and Teradata Database version
ElicitFile.py Demonstrates C source file upload to create a User-Defined Function (UDF)
FakeResultSetCon.py Demonstrates connection parameter for fake result sets
FakeResultSetEsc.py Demonstrates escape function for fake result sets
FastLoadBatch.py Demonstrates how to FastLoad batches of rows
HelpSession.py Displays session information
IgnoreErrors.py Demonstrates how to ignore errors
InsertXML.py Demonstrates how to insert and retrieve XML values
LoadCSVFile.py Demonstrates how to load data from a CSV file into a table
MetadataFromPrepare.py Demonstrates how to prepare a SQL request and obtain SQL statement metadata
ParamDataTypes.py Demonstrates how to specify data types for parameter marker bind values
StoredProc.py Demonstrates how to create and call a SQL stored procedure
TJEncryptPassword.py Creates encrypted password files

Using the Teradata SQL Driver for Python

Your Python script must import the teradatasql package in order to use the Teradata SQL Driver for Python.

import teradatasql

After importing the teradatasql package, your Python script calls the teradatasql.connect function to open a connection to the Teradata Database.

You may specify connection parameters as a JSON string, as kwargs, or using a combination of the two approaches. The teradatasql.connect function's first argument is an optional JSON string. The teradatasql.connect function's second and subsequent arguments are optional kwargs.

Connection parameters specified only as kwargs:

con = teradatasql.connect(host="whomooz", user="guest", password="please")

Connection parameters specified only as a JSON string:

con = teradatasql.connect('{"host":"whomooz","user":"guest","password":"please"}')

Connection parameters specified using a combination:

con = teradatasql.connect('{"host":"whomooz"}', user="guest", password="please")

When a combination of parameters are specified, connection parameters specified as kwargs take precedence over same-named connection parameters specified in the JSON string.

Connection Parameters

The following table lists the connection parameters currently offered by the Teradata SQL Driver for Python.

Our goal is consistency for the connection parameters offered by the Teradata SQL Driver for Python and the Teradata JDBC Driver, with respect to connection parameter names and functionality. For comparison, Teradata JDBC Driver connection parameters are documented here.

Parameter Default Type Description
account string Specifies the Teradata Database account. Equivalent to the Teradata JDBC Driver ACCOUNT connection parameter.
column_name "false" quoted boolean Controls the behavior of cursor .description sequence name items. Equivalent to the Teradata JDBC Driver COLUMN_NAME connection parameter. False specifies that a cursor .description sequence name item provides the AS-clause name if available, or the column name if available, or the column title. True specifies that a cursor .description sequence name item provides the column name if available, but has no effect when StatementInfo parcel support is unavailable.
cop "true" quoted boolean Specifies whether COP Discovery is performed. Equivalent to the Teradata JDBC Driver COP connection parameter.
coplast "false" quoted boolean Specifies how COP Discovery determines the last COP hostname. Equivalent to the Teradata JDBC Driver COPLAST connection parameter. When coplast is false or omitted, or COP Discovery is turned off, then no DNS lookup occurs for the coplast hostname. When coplast is true, and COP Discovery is turned on, then a DNS lookup occurs for a coplast hostname.
database string Specifies the initial database to use after logon, instead of the user's default database. Equivalent to the Teradata JDBC Driver DATABASE connection parameter.
dbs_port "1025" quoted integer Specifies the Teradata Database port number. Equivalent to the Teradata JDBC Driver DBS_PORT connection parameter.
encryptdata "false" quoted boolean Controls encryption of data exchanged between the Teradata Database and the Teradata SQL Driver for Python. Equivalent to the Teradata JDBC Driver ENCRYPTDATA connection parameter.
fake_result_sets "false" quoted boolean Controls whether a fake result set containing statement metadata precedes each real result set.
host string Specifies the Teradata Database hostname.
lob_support "true" quoted boolean Controls LOB support. Equivalent to the Teradata JDBC Driver LOB_SUPPORT connection parameter.
log "0" quoted integer Controls debug logging. Somewhat equivalent to the Teradata JDBC Driver LOG connection parameter. This parameter's behavior is subject to change in the future. This parameter's value is currently defined as an integer in which the 1-bit governs function and method tracing, the 2-bit governs debug logging, the 4-bit governs transmit and receive message hex dumps, and the 8-bit governs timing. Compose the value by adding together 1, 2, 4, and/or 8.
logdata string Specifies extra data for the chosen logon authentication method. Equivalent to the Teradata JDBC Driver LOGDATA connection parameter.
logmech "TD2" string Specifies the logon authentication method. Equivalent to the Teradata JDBC Driver LOGMECH connection parameter. Possible values are TD2 (the default), JWT, LDAP, KRB5 for Kerberos, or TDNEGO.
max_message_body "2097000" quoted integer Not fully implemented yet and intended for future usage. Equivalent to the Teradata JDBC Driver MAX_MESSAGE_BODY connection parameter.
partition "DBC/SQL" string Specifies the Teradata Database Partition. Equivalent to the Teradata JDBC Driver PARTITION connection parameter.
password string Specifies the Teradata Database password. Equivalent to the Teradata JDBC Driver PASSWORD connection parameter.
sip_support "true" quoted boolean Controls whether StatementInfo parcel is used. Equivalent to the Teradata JDBC Driver SIP_SUPPORT connection parameter.
teradata_values "true" quoted boolean Controls whether str or a more specific Python data type is used for certain result set column value types. Refer to the Data Types table below for details.
tmode "DEFAULT" string Specifies the transaction mode. Equivalent to the Teradata JDBC Driver TMODE connection parameter. Possible values are DEFAULT (the default), ANSI, or TERA.
user string Specifies the Teradata Database username. Equivalent to the Teradata JDBC Driver USER connection parameter.

COP Discovery

The Teradata SQL Driver for Python provides Communications Processor (COP) discovery behavior when the cop connection parameter is true or omitted. COP Discovery is turned off when the cop connection parameter is false.

A Teradata Database system can be composed of multiple Teradata Database nodes. One or more of the Teradata Database nodes can be configured to run the Teradata Database Gateway process. Each Teradata Database node that runs the Teradata Database Gateway process is termed a Communications Processor, or COP. COP Discovery refers to the procedure of identifying all the available COP hostnames and their IP addresses. COP hostnames can be defined in DNS, or can be defined in the client system's hosts file. Teradata strongly recommends that COP hostnames be defined in DNS, rather than the client system's hosts file. Defining COP hostnames in DNS provides centralized administration, and enables centralized changes to COP hostnames if and when the Teradata Database is reconfigured.

The coplast connection parameter specifies how COP Discovery determines the last COP hostname.

  • When coplast is false or omitted, or COP Discovery is turned off, then the Teradata SQL Driver for Python will not perform a DNS lookup for the coplast hostname.
  • When coplast is true, and COP Discovery is turned on, then the Teradata SQL Driver for Python will first perform a DNS lookup for a coplast hostname to obtain the IP address of the last COP hostname before performing COP Discovery. Subsequently, during COP Discovery, the Teradata SQL Driver for Python will stop searching for COP hostnames when either an unknown COP hostname is encountered, or a COP hostname is encountered whose IP address matches the IP address of the coplast hostname.

Specifying coplast as true can improve performance with DNS that is slow to respond for DNS lookup failures, and is necessary for DNS that never returns a DNS lookup failure.

When performing COP Discovery, the Teradata SQL Driver for Python starts with cop1, which is appended to the database hostname, and then proceeds with cop2, cop3, ..., copN. The Teradata SQL Driver for Python supports domain-name qualification for COP Discovery and the coplast hostname. Domain-name qualification is recommended, because it can improve performance by avoiding unnecessary DNS lookups for DNS search suffixes.

The following table illustrates the DNS lookups performed for a hypothetical three-node Teradata Database system named "whomooz".

  No domain name qualification With domain name qualification
(Recommended)
Application-specified
Teradata Database hostname
whomooz whomooz.domain.com
Default: COP Discovery turned on, and coplast is false or omitted,
perform DNS lookups until unknown COP hostname is encountered
whomoozcop110.0.0.1
whomoozcop210.0.0.2
whomoozcop310.0.0.3
whomoozcop4→undefined
whomoozcop1.domain.com10.0.0.1
whomoozcop2.domain.com10.0.0.2
whomoozcop3.domain.com10.0.0.3
whomoozcop4.domain.com→undefined
COP Discovery turned on, and coplast is true,
perform DNS lookups until COP hostname is found whose IP address matches the coplast hostname, or unknown COP hostname is encountered
whomoozcoplast10.0.0.3
whomoozcop110.0.0.1
whomoozcop210.0.0.2
whomoozcop310.0.0.3
whomoozcoplast.domain.com10.0.0.3
whomoozcop1.domain.com10.0.0.1
whomoozcop2.domain.com10.0.0.2
whomoozcop3.domain.com10.0.0.3
COP Discovery turned off and round-robin DNS,
perform one DNS lookup that returns multiple IP addresses
whomooz10.0.0.1, 10.0.0.2, 10.0.0.3 whomooz.domain.com10.0.0.1, 10.0.0.2, 10.0.0.3

Round-robin DNS rotates the list of IP addresses automatically to provide load distribution. Round-robin is only possible with DNS, not with the client system hosts file.

The Teradata SQL Driver for Python supports the definition of multiple IP addresses for COP hostnames and non-COP hostnames.

For the first connection to a particular Teradata Database system, the Teradata SQL Driver for Python generates a random number to index into the list of COPs. For each subsequent connection, the Teradata SQL Driver for Python increments the saved index until it wraps around to the first position. This behavior provides load distribution across all discovered COPs.

The Teradata SQL Driver for Python masks connection failures to down COPs, thereby hiding most connection failures from the client application. An exception is thrown to the application only when all the COPs are down for that database. If a COP is down, the next COP in the sequence (including a wrap-around to the first COP) receives extra connections that were originally destined for the down COP. When multiple IP addresses are defined in DNS for a COP, the Teradata SQL Driver for Python will attempt to connect to each of the COP's IP addresses, and the COP is considered down only when connection attempts fail to all of the COP's IP addresses.

If COP Discovery is turned off, or no COP hostnames are defined in DNS, the Teradata SQL Driver for Python connects directly to the hostname specified in the host connection parameter. This permits load distribution schemes other than the COP Discovery approach. For example, round-robin DNS or a TCP/IP load distribution product can be used. COP Discovery takes precedence over simple database hostname lookup. To use an alternative load distribution scheme, either ensure that no COP hostnames are defined in DNS, or turn off COP Discovery with cop as false.

Stored Password Protection

Overview

Stored Password Protection enables an application to provide a connection password in encrypted form to the Teradata SQL Driver for Python.

An encrypted password may be specified in the following contexts:

  • A login password specified as the password connection parameter.
  • A login password specified within the logdata connection parameter.

If the password, however specified, begins with the prefix ENCRYPTED_PASSWORD( then the specified password must follow this format:

ENCRYPTED_PASSWORD(file:PasswordEncryptionKeyFileName,file:EncryptedPasswordFileName)

Each filename must be preceded by the file: prefix. The PasswordEncryptionKeyFileName must be separated from the EncryptedPasswordFileName by a single comma.

The PasswordEncryptionKeyFileName specifies the name of a file that contains the password encryption key and associated information. The EncryptedPasswordFileName specifies the name of a file that contains the encrypted password and associated information. The two files are described below.

Stored Password Protection is offered by the Teradata JDBC Driver, the Teradata SQL Driver for Python, and the Teradata SQL Driver for R. These drivers use the same file format.

Program TJEncryptPassword

TJEncryptPassword.py is a sample program to create encrypted password files for use with Stored Password Protection. When the Teradata SQL Driver for Python is installed, the sample programs are placed in the teradatasql/samples directory under your Python installation directory.

This program works in conjunction with Stored Password Protection offered by the Teradata JDBC Driver and the Teradata SQL Driver for Python. This program creates the files containing the password encryption key and encrypted password, which can be subsequently specified via the ENCRYPTED_PASSWORD( syntax.

You are not required to use this program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files. You may also use the TJEncryptPassword.java sample program that is available with the Teradata JDBC Driver Reference. The only requirement is that the files must match the format expected by the Teradata SQL Driver for Python, which is documented below.

This program encrypts the password and then immediately decrypts the password, in order to verify that the password can be successfully decrypted. This program mimics the password decryption of the Teradata SQL Driver for Python, and is intended to openly illustrate its operation and enable scrutiny by the community.

The encrypted password is only as safe as the two files. You are responsible for restricting access to the files containing the password encryption key and encrypted password. If an attacker obtains both files, the password can be decrypted. The operating system file permissions for the two files should be as limited and restrictive as possible, to ensure that only the intended operating system userid has access to the files.

The two files can be kept on separate physical volumes, to reduce the risk that both files might be lost at the same time. If either or both of the files are located on a network volume, then an encrypted wire protocol can be used to access the network volume, such as sshfs, encrypted NFSv4, or encrypted SMB 3.0.

This program accepts eight command-line arguments:

Argument Example Description
Transformation AES/CBC/NoPadding Specifies the transformation in the form Algorithm/Mode/Padding. Supported transformations are listed in a table below.
KeySizeInBits 256 Specifies the algorithm key size, which governs the encryption strength.
MAC HmacSHA256 Specifies the message authentication code (MAC) algorithm HmacSHA1 or HmacSHA256.
PasswordEncryptionKeyFileName PassKey.properties Specifies a filename in the current directory, a relative pathname, or an absolute pathname. The file is created by this program. If the file already exists, it will be overwritten by the new file.
EncryptedPasswordFileName EncPass.properties Specifies a filename in the current directory, a relative pathname, or an absolute pathname. The filename or pathname that must differ from the PasswordEncryptionKeyFileName. The file is created by this program. If the file already exists, it will be overwritten by the new file.
Hostname whomooz Specifies the Teradata Database hostname.
Username guest Specifies the Teradata Database username.
Password please Specifies the Teradata Database password to be encrypted. Unicode characters in the password can be specified with the \uXXXX escape sequence.

Example Commands

The TJEncryptPassword program uses the Teradata SQL Driver for Python to log on to the specified Teradata Database using the encrypted password, so the Teradata SQL Driver for Python must have been installed with the pip install teradatasql command.

The following commands assume that the TJEncryptPassword.py program file is located in the current directory. When the Teradata SQL Driver for Python is installed, the sample programs are placed in the teradatasql/samples directory under your Python installation directory. Change your current directory to the teradatasql/samples directory under your Python installation directory.

The following example commands illustrate using a 256-bit AES key, and using the HmacSHA256 algorithm.

Platform Command
macOS or Linux python TJEncryptPassword.py AES/CBC/NoPadding 256 HmacSHA256 PassKey.properties EncPass.properties whomooz guest please
Windows py -3 TJEncryptPassword.py AES/CBC/NoPadding 256 HmacSHA256 PassKey.properties EncPass.properties whomooz guest please

Password Encryption Key File Format

You are not required to use the TJEncryptPassword program to create the files containing the password encryption key and encrypted password. You can develop your own software to create the necessary files, but the files must match the format expected by the Teradata SQL Driver for Python.

The password encryption key file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

Property Description
version=1 The version number must be 1. This property is required.
transformation=Algorithm/Mode/Padding Specifies the transformation in the form Algorithm/Mode/Padding. Supported transformations are listed in a table below. This property is required.
algorithm=Algorithm This value must correspond to the Algorithm portion of the transformation. This property is required.
match=MatchValue The password encryption key and encrypted password files must contain the same match value. The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors. This property is required.
key=HexDigits This value is the password encryption key, encoded as hex digits. This property is required.
mac=MACAlgorithm Specifies the message authentication code (MAC) algorithm HmacSHA1 or HmacSHA256. Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack. This property is required.
mackey=HexDigits This value is the MAC key, encoded as hex digits. This property is required.

The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

Encrypted Password File Format

The encrypted password file is a text file in Java Properties file format, using the ISO 8859-1 character encoding.

The file must contain the following string properties:

Property Description
version=1 The version number must be 1. This property is required.
match=MatchValue The password encryption key and encrypted password files must contain the same match value. The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors. This property is required.
password=HexDigits This value is the encrypted password, encoded as hex digits. This property is required.
params=HexDigits This value contains the cipher algorithm parameters, if any, encoded as hex digits. Some ciphers need algorithm parameters that cannot be derived from the key, such as an initialization vector. This property is optional, depending on whether the cipher algorithm has associated parameters.
hash=HexDigits This value is the expected message authentication code (MAC), encoded as hex digits. After encryption, the expected MAC is calculated using the ciphertext, transformation name, and algorithm parameters if any. Before decryption, the Teradata SQL Driver for Python calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. If the calculated MAC differs from the expected MAC, then either or both of the files may have been tampered with. This property is required.

While params is technically optional, an initialization vector is required by all three block cipher modes CBC, CFB, and OFB that are supported by the Teradata SQL Driver for Python. ECB (Electronic Codebook) does not require params, but ECB is not supported by the Teradata SQL Driver for Python.

Transformation, Key Size, and MAC

A transformation is a string that describes the set of operations to be performed on the given input, to produce transformed output. A transformation specifies the name of a cryptographic algorithm such as DES or AES, followed by a feedback mode and padding scheme.

The Teradata SQL Driver for Python supports the following transformations and key sizes.

Transformation Key Size
DES/CBC/NoPadding 64
DES/CBC/PKCS5Padding 64
DES/CFB/NoPadding 64
DES/CFB/PKCS5Padding 64
DES/OFB/NoPadding 64
DES/OFB/PKCS5Padding 64
DESede/CBC/NoPadding 192
DESede/CBC/PKCS5Padding 192
DESede/CFB/NoPadding 192
DESede/CFB/PKCS5Padding 192
DESede/OFB/NoPadding 192
DESede/OFB/PKCS5Padding 192
AES/CBC/NoPadding 128
AES/CBC/NoPadding 192
AES/CBC/NoPadding 256
AES/CBC/PKCS5Padding 128
AES/CBC/PKCS5Padding 192
AES/CBC/PKCS5Padding 256
AES/CFB/NoPadding 128
AES/CFB/NoPadding 192
AES/CFB/NoPadding 256
AES/CFB/PKCS5Padding 128
AES/CFB/PKCS5Padding 192
AES/CFB/PKCS5Padding 256
AES/OFB/NoPadding 128
AES/OFB/NoPadding 192
AES/OFB/NoPadding 256
AES/OFB/PKCS5Padding 128
AES/OFB/PKCS5Padding 192
AES/OFB/PKCS5Padding 256

Stored Password Protection uses a symmetric encryption algorithm such as DES or AES, in which the same secret key is used for encryption and decryption of the password. Stored Password Protection does not use an asymmetric encryption algorithm such as RSA, with separate public and private keys.

CBC (Cipher Block Chaining) is a block cipher encryption mode. With CBC, each ciphertext block is dependent on all plaintext blocks processed up to that point. CBC is suitable for encrypting data whose total byte count exceeds the algorithm's block size, and is therefore suitable for use with Stored Password Protection.

Stored Password Protection hides the password length in the encrypted password file by extending the length of the UTF8-encoded password with trailing null bytes. The length is extended to the next 512-byte boundary.

  • A block cipher with no padding, such as AES/CBC/NoPadding, may only be used to encrypt data whose byte count after extension is a multiple of the algorithm's block size. The 512-byte boundary is compatible with many block ciphers. AES, for example, has a block size of 128 bits (16 bytes), and is therefore compatible with the 512-byte boundary.
  • A block cipher with padding, such as AES/CBC/PKCS5Padding, can be used to encrypt data of any length. However, CBC with padding is vulnerable to a "padding oracle attack", so Stored Password Protection performs Encrypt-then-MAC for protection from a padding oracle attack. MAC algorithms HmacSHA1 and HmacSHA256 are supported.
  • The Teradata SQL Driver for Python does not support block ciphers used as byte-oriented ciphers via modes such as CFB8 or OFB8.

The strength of the encryption depends on your choice of cipher algorithm and key size.

  • AES uses a 128-bit (16 byte), 192-bit (24 byte), or 256-bit (32 byte) key.
  • DESede uses a 192-bit (24 byte) key. The The Teradata SQL Driver for Python does not support a 128-bit (16 byte) key for DESede.
  • DES uses a 64-bit (8 byte) key.

Sharing Files with the Teradata JDBC Driver

The Teradata SQL Driver for Python and the Teradata JDBC Driver can share the files containing the password encryption key and encrypted password, if you use a transformation, key size, and MAC algorithm that is supported by both drivers.

  • Recommended choices for compatibility are AES/CBC/NoPadding and HmacSHA256.
  • Use a 256-bit key if your Java environment has the Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files from Oracle.
  • Use a 128-bit key if your Java environment does not have the Unlimited Strength Jurisdiction Policy Files.
  • Use HmacSHA1 for compatibility with JDK 1.4.2.

File Locations

For the ENCRYPTED_PASSWORD( syntax of the Teradata SQL Driver for Python, each filename must be preceded by the file: prefix. The PasswordEncryptionKeyFileName must be separated from the EncryptedPasswordFileName by a single comma. The files can be located in the current directory, specified with a relative path, or specified with an absolute path.

Example for files in the current directory:

ENCRYPTED_PASSWORD(file:JohnDoeKey.properties,file:JohnDoePass.properties)

Example with relative paths:

ENCRYPTED_PASSWORD(file:../dir1/JohnDoeKey.properties,file:../dir2/JohnDoePass.properties)

Example with absolute paths on Windows:

ENCRYPTED_PASSWORD(file:c:/dir1/JohnDoeKey.properties,file:c:/dir2/JohnDoePass.properties)

Example with absolute paths on Linux:

ENCRYPTED_PASSWORD(file:/dir1/JohnDoeKey.properties,file:/dir2/JohnDoePass.properties)

Processing Sequence

The two filenames specified for an encrypted password must be accessible to the Teradata SQL Driver for Python and must conform to the properties file formats described above. The Teradata SQL Driver for Python raises an exception if the file is not accessible, or the file does not conform to the required file format.

The Teradata SQL Driver for Python verifies that the match values in the two files are present, and match each other. The Teradata SQL Driver for Python raises an exception if the match values differ from each other. The match values are compared to ensure that the two specified files are related to each other, serving as a "sanity check" to help avoid configuration errors. The TJEncryptPassword program uses a timestamp as a shared match value, but a timestamp is not required. Any shared string can serve as a match value. The timestamp is not related in any way to the encryption of the password, and the timestamp cannot be used to decrypt the password.

Before decryption, the Teradata SQL Driver for Python calculates the MAC using the ciphertext, transformation name, and algorithm parameters if any, and verifies that the calculated MAC matches the expected MAC. The Teradata SQL Driver for Python raises an exception if the calculated MAC differs from the expected MAC, to indicate that either or both of the files may have been tampered with.

Finally, the Teradata SQL Driver for Python uses the decrypted password to log on to the Teradata Database.

Transaction Mode

The tmode connection parameter enables an application to specify the transaction mode for the connection.

  • "tmode":"ANSI" provides American National Standards Institute (ANSI) transaction semantics. This mode is recommended.
  • "tmode":"TERA" provides legacy Teradata transaction semantics. This mode is only recommended for legacy applications that require Teradata transaction semantics.
  • "tmode":"DEFAULT" provides the default transaction mode configured for the Teradata Database, which may be either ANSI or TERA mode. "tmode":"DEFAULT" is the default when the tmode connection parameter is omitted.

While ANSI mode is generally recommended, please note that every application is different, and some applications may need to use TERA mode. The following differences between ANSI and TERA mode might affect a typical user or application:

  1. Silent truncation of inserted data occurs in TERA mode, but not ANSI mode. In ANSI mode, the Teradata Database returns an error instead of truncating data.
  2. Tables created in ANSI mode are MULTISET by default. Tables created in TERA mode are SET tables by default.
  3. For tables created in ANSI mode, character columns are CASESPECIFIC by default. For tables created in TERA mode, character columns are NOT CASESPECIFIC by default.
  4. In ANSI mode, character literals are CASESPECIFIC. In TERA mode, character literals are NOT CASESPECIFIC.

The last two behavior differences, taken together, may cause character data comparisons (such as in WHERE clause conditions) to be case-insensitive in TERA mode, but case-sensitive in ANSI mode. This, in turn, can produce different query results in ANSI mode versus TERA mode. Comparing two NOT CASESPECIFIC expressions is case-insensitive regardless of mode, and comparing a CASESPECIFIC expression to another expression of any kind is case-sensitive regardless of mode. You may explicitly CAST an expression to be CASESPECIFIC or NOT CASESPECIFIC to obtain the character data comparison required by your application.

The Teradata Database Reference / SQL Request and Transaction Processing recommends that ANSI mode be used for all new applications. The primary benefit of using ANSI mode is that inadvertent data truncation is avoided. In contrast, when using TERA mode, silent data truncation can occur when data is inserted, because silent data truncation is a feature of TERA mode.

A drawback of using ANSI mode is that you can only call stored procedures that were created using ANSI mode, and you cannot call stored procedures that were created using TERA mode. It may not be possible to switch over to ANSI mode exclusively, because you may have some legacy applications that require TERA mode to work properly. You can work around this drawback by creating your stored procedures twice, in two different users/databases, once using ANSI mode, and once using TERA mode.

Refer to the Teradata Database Reference / SQL Request and Transaction Processing for complete information regarding the differences between ANSI and TERA transaction modes.

Auto-Commit

The Teradata SQL Driver for Python provides auto-commit on and off functionality for both ANSI and TERA mode.

When a connection is first established, it begins with the default auto-commit setting, which is on. When auto-commit is on, the driver is solely responsible for managing transactions, and the driver commits each SQL request that is successfully executed. An application should not execute any transaction management SQL commands when auto-commit is on. An application should not call the commit method or the rollback method when auto-commit is on.

An application can manage transactions itself by calling the execute method with the teradata_nativesql and teradata_autocommit_off escape functions to turn off auto-commit.

cur.execute("{fn teradata_nativesql}{fn teradata_autocommit_off}")

When auto-commit is off, the driver leaves the current transaction open after each SQL request is executed, and the application is responsible for committing or rolling back the transaction by calling the commit or the rollback method, respectively.

Auto-commit remains turned off until the application turns it back on.

cur.execute("{fn teradata_nativesql}{fn teradata_autocommit_on}")

Best practices recommend that an application avoid executing database-vendor-specific transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK, because such commands differ from one vendor to another. (They even differ between Teradata's two modes ANSI and TERA.) Instead, best practices recommend that an application only call the standard methods commit and rollback for transaction management.

  1. When auto-commit is on in ANSI mode, the driver automatically executes COMMIT after every successful SQL request.
  2. When auto-commit is off in ANSI mode, the driver does not automatically execute COMMIT. When the application calls the commit method, then the driver executes COMMIT.
  3. When auto-commit is on in TERA mode, the driver does not execute BT or ET, unless the application explicitly executes BT or ET commands itself, which is not recommended.
  4. When auto-commit is off in TERA mode, the driver executes BT before submitting the application's first SQL request of a new transaction. When the application calls the commit method, then the driver executes ET until the transaction is complete.

As part of the wire protocol between the Teradata Database and Teradata client interface software (such as the Teradata SQL Driver for Python), each message transmitted from the Teradata Database to the client has a bit designated to indicate whether the session has a transaction in progress or not. Thus, the client interface software is kept informed as to whether the session has a transaction in progress or not.

In TERA mode with auto-commit off, when the application uses the driver to execute a SQL request, if the session does not have a transaction in progress, then the driver automatically executes BT before executing the application's SQL request. Subsequently, in TERA mode with auto-commit off, when the application uses the driver to execute another SQL request, and the session already has a transaction in progress, then the driver has no need to execute BT before executing the application's SQL request.

In TERA mode, BT and ET pairs can be nested, and the Teradata Database keeps track of the nesting level. The outermost BT/ET pair defines the transaction scope; inner BT/ET pairs have no effect on the transaction because the Teradata Database does not provide actual transaction nesting. To commit the transaction, ET commands must be repeatedly executed until the nesting is unwound. The Teradata wire protocol bit (mentioned earlier) indicates when the nesting is unwound and the transaction is complete. When the application calls the commit method in TERA mode, the driver repeatedly executes ET commands until the nesting is unwound and the transaction is complete.

In rare cases, an application may not follow best practices and may explicitly execute transaction management commands. Such an application must turn off auto-commit before executing transaction management commands such as BT, ET, ABORT, COMMIT, or ROLLBACK. The application is responsible for executing the appropriate commands for the transaction mode in effect. TERA mode commands are BT, ET, and ABORT. ANSI mode commands are COMMIT and ROLLBACK. An application must take special care when opening a transaction in TERA mode with auto-commit off. In TERA mode with auto-commit off, when the application executes a SQL request, if the session does not have a transaction in progress, then the driver automatically executes BT before executing the application's SQL request. Therefore, the application should not begin a transaction by executing BT.

# TERA mode example showing undesirable BT/ET nesting
cur.execute("{fn teradata_nativesql}{fn teradata_autocommit_off}")
cur.execute("BT") # BT automatically executed by the driver before this, and produces a nested BT
cur.execute("insert into mytable1 values(1, 2)")
cur.execute("insert into mytable2 values(3, 4)")
cur.execute("ET") # unwind nesting
cur.execute("ET") # complete transaction

# TERA mode example showing how to avoid BT/ET nesting
cur.execute("{fn teradata_nativesql}{fn teradata_autocommit_off}")
cur.execute("insert into mytable1 values(1, 2)") # BT automatically executed by the driver before this
cur.execute("insert into mytable2 values(3, 4)")
cur.execute("ET") # complete transaction

Please note that neither previous example shows best practices. Best practices recommend that an application only call the standard methods commit and rollback for transaction management.

# Example showing best practice
cur.execute("{fn teradata_nativesql}{fn teradata_autocommit_off}")
cur.execute("insert into mytable1 values(1, 2)")
cur.execute("insert into mytable2 values(3, 4)")
con.commit()

Data Types

The table below lists the Teradata Database data types supported by the Teradata SQL Driver for Python, and indicates the corresponding Python data type returned in result set rows.

Teradata Database data type Result set Python data type With teradata_values as false
BIGINT int
BLOB bytes
BYTE bytes
BYTEINT int
CHAR str
CLOB str
DATE datetime.date str
DECIMAL decimal.Decimal str
FLOAT float
INTEGER int
INTERVAL YEAR str
INTERVAL YEAR TO MONTH str
INTERVAL MONTH str
INTERVAL DAY str
INTERVAL DAY TO HOUR str
INTERVAL DAY TO MINUTE str
INTERVAL DAY TO SECOND str
INTERVAL HOUR str
INTERVAL HOUR TO MINUTE str
INTERVAL HOUR TO SECOND str
INTERVAL MINUTE str
INTERVAL MINUTE TO SECOND str
INTERVAL SECOND str
NUMBER decimal.Decimal str
PERIOD(DATE) str
PERIOD(TIME) str
PERIOD(TIME WITH TIME ZONE) str
PERIOD(TIMESTAMP) str
PERIOD(TIMESTAMP WITH TIME ZONE) str
SMALLINT int
TIME datetime.time str
TIME WITH TIME ZONE datetime.time with tzinfo str
TIMESTAMP datetime.datetime str
TIMESTAMP WITH TIME ZONE datetime.datetime with tzinfo str
VARBYTE bytes
VARCHAR str
XML str

The table below lists the parameterized SQL bind-value Python data types supported by the Teradata SQL Driver for Python, and indicates the corresponding Teradata Database data type transmitted to the server.

Bind-value Python data type Teradata Database data type
bytes VARBYTE
datetime.date DATE
datetime.datetime TIMESTAMP
datetime.datetime with tzinfo TIMESTAMP WITH TIME ZONE
datetime.time TIME
datetime.time with tzinfo TIME WITH TIME ZONE
datetime.timedelta VARCHAR format compatible with INTERVAL DAY TO SECOND
decimal.Decimal NUMBER
float FLOAT
int BIGINT
str VARCHAR

Transforms are used for SQL ARRAY data values, and they can be transferred to and from the database as VARCHAR values.

Transforms are used for structured UDT data values, and they can be transferred to and from the database as VARCHAR values.

Null Values

SQL NULL values received from the Teradata Database are returned in result set rows as Python None values.

A Python None value bound to a question-mark parameter marker is transmitted to the Teradata Database as a NULL VARCHAR value.

Character Export Width

The Teradata SQL Driver for Python always uses the UTF8 session character set, and the charset connection parameter is not supported. Be aware of the Teradata Database's Character Export Width behavior that adds trailing space padding to fixed-width CHAR data type result set column values when using the UTF8 session character set.

The Teradata Database CHAR(n) data type is a fixed-width data type (holding n characters), and the Teradata Database reserves a fixed number of bytes for the CHAR(n) data type in response spools and in network message traffic.

UTF8 is a variable-width character encoding scheme that requires a varying number of bytes for each character. When the UTF8 session character set is used, the Teradata Database reserves the maximum number of bytes that the CHAR(n) data type could occupy in response spools and in network message traffic. When the UTF8 session character set is used, the Teradata Database appends padding characters to the tail end of CHAR(n) values smaller than the reserved maximum size, so that the CHAR(n) values all occupy the same fixed number of bytes in response spools and in network message traffic.

Work around this drawback by using CAST or TRIM in SQL SELECT statements, or in views, to convert fixed-width CHAR data types to VARCHAR.

Given a table with fixed-width CHAR columns:

CREATE TABLE MyTable (c1 CHAR(10), c2 CHAR(10))

Original query that produces trailing space padding:

SELECT c1, c2 FROM MyTable

Modified query with either CAST or TRIM to avoid trailing space padding:

SELECT CAST(c1 AS VARCHAR(10)), TRIM(TRAILING FROM c1) FROM MyTable

Or wrap query in a view with CAST or TRIM to avoid trailing space padding:

CREATE VIEW MyView (c1, c2) AS SELECT CAST(c1 AS VARCHAR(10)), TRIM(TRAILING FROM c2) FROM MyTable

SELECT c1, c2 FROM MyView

This technique is also demonstrated in sample program CharPadding.py.

Module Constructors

teradatasql.connect( JSONConnectionString , Parameters... )

Creates a connection to the database and returns a Connection object.

The first parameter is an optional JSON string that defaults to None. The second and subsequent arguments are optional kwargs. Specify connection parameters as a JSON string, as kwargs, or a combination of the two.

When a combination of parameters are specified, connection parameters specified as kwargs take precedence over same-named connection parameters specified in the JSON string.


teradatasql.Date( Year , Month , Day )

Creates and returns a datetime.date value.


teradatasql.DateFromTicks( Seconds )

Creates and returns a datetime.date value corresponding to the specified number of seconds after 1970-01-01 00:00:00.


teradatasql.Time( Hour , Minute , Second )

Creates and returns a datetime.time value.


teradatasql.TimeFromTicks( Seconds )

Creates and returns a datetime.time value corresponding to the specified number of seconds after 1970-01-01 00:00:00.


teradatasql.Timestamp( Year , Month , Day , Hour , Minute , Second )

Creates and returns a datetime.datetime value.


teradatasql.TimestampFromTicks( Seconds )

Creates and returns a datetime.datetime value corresponding to the specified number of seconds after 1970-01-01 00:00:00.

Module Globals

teradatasql.apilevel

String constant "2.0" indicating that the Teradata SQL Driver for Python implements the PEP-249 Python Database API Specification 2.0.


teradatasql.threadsafety

Integer constant 2 indicating that threads may share this module, and threads may share connections, but threads must not share cursors.


teradatasql.paramstyle

String constant "qmark" indicating that prepared SQL requests use question-mark parameter markers.

Module Exceptions

teradatasql.Error is the base class for other exceptions.

  • teradatasql.InterfaceError is raised for errors related to the driver. Not supported yet.
  • teradatasql.DatabaseError is raised for errors related to the database.
    • teradatasql.DataError is raised for data value errors such as division by zero. Not supported yet.
    • teradatasql.IntegrityError is raised for referential integrity violations. Not supported yet.
    • teradatasql.OperationalError is raised for errors related to the database's operation.
    • teradatasql.ProgrammingError is raised for SQL object existence errors and SQL syntax errors. Not supported yet.

Connection Methods

.close()

Closes the Connection.


.commit()

Commits the current transaction.


.cursor()

Creates and returns a new Cursor object for the Connection.


.rollback()

Rolls back the current transaction.

Cursor Attributes

.arraysize

Read/write attribute specifying the number of rows to fetch at a time with the .fetchmany() method. Defaults to 1 meaning fetch a single row at a time.


.connection

Read-only attribute indicating the Cursor's parent Connection object.


.description

Read-only attribute consisting of a sequence of seven-item sequences that each describe a result set column, available after a SQL request is executed.

  • .description[Column][0] provides the column name.
  • .description[Column][1] provides the column type code as an object comparable to one of the Type Objects listed below.
  • .description[Column][2] provides the column display size in characters. Not implemented yet.
  • .description[Column][3] provides the column size in bytes.
  • .description[Column][4] provides the column precision if applicable, or None otherwise.
  • .description[Column][5] provides the column scale if applicable, or None otherwise.
  • .description[Column][6] provides the column nullability as True or False.

.rowcount

Read-only attribute indicating the number of rows returned from, or affected by, the current SQL statement.

Cursor Methods

.callproc( ProcedureName , OptionalSequenceOfParameterValues )

Calls the stored procedure specified by ProcedureName. Provide the second argument as a sequence of IN and INOUT parameter values to bind the values to question-mark parameter markers in the SQL request. Specifying parameter values as a mapping is not supported. Returns a result set consisting of the INOUT parameter output values, if any, followed by any dynamic result sets.

OUT parameters are not supported by this method. Use .execute to call a stored procedure with OUT parameters.


.close()

Closes the Cursor.


.execute( SQLRequest , OptionalSequenceOfParameterValues , ignoreErrors= OptionalSequenceOfIgnoredErrorCodes )

Executes the SQL request. If a sequence of parameter values is provided as the second argument, the values will be bound to question-mark parameter markers in the SQL request. Specifying parameter values as a mapping is not supported.

The ignoreErrors parameter is optional. The ignored error codes must be specified as a sequence of integers.


.executemany( SQLRequest , SequenceOfSequencesOfParameterValues , ignoreErrors= OptionalSequenceOfIgnoredErrorCodes )

Executes the SQL request as an iterated SQL request for the batch of parameter values. The batch of parameter values must be specified as a sequence of sequences. Specifying parameter values as a mapping is not supported.

The ignoreErrors parameter is optional. The ignored error codes must be specified as a sequence of integers.


.fetchall()

Fetches all remaining rows of the current result set. Returns a sequence of sequences of column values.


.fetchmany( OptionalRowCount )

Fetches the next series of rows of the current result set. The argument specifies the number of rows to fetch. If no argument is provided, then the Cursor's .arraysize attribute will determine the number of rows to fetch. Returns a sequence of sequences of column values, or an empty sequence to indicate that all rows have been fetched.


.fetchone()

Fetches the next row of the current result set. Returns a sequence of column values, or None to indicate that all rows have been fetched.


.nextset()

Advances to the next result set. Returns True if another result set is available, or None to indicate that all result sets have been fetched.


.setinputsizes( SequenceOfTypesOrSizes )

Has no effect.


.setoutputsize( Size , OptionalColumnIndex )

Has no effect.

Type Objects

teradatasql.BINARY

Identifies a SQL BLOB, BYTE, or VARBYTE column as a binary data type when compared with the Cursor's description attribute.

.description[Column][1] == teradatasql.BINARY


teradatasql.DATETIME

Identifies a SQL DATE, TIME, TIME WITH TIME ZONE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE column as a date/time data type when compared with the Cursor's description attribute.

.description[Column][1] == teradatasql.DATETIME


teradatasql.NUMBER

Identifies a SQL BIGINT, BYTEINT, DECIMAL, FLOAT, INTEGER, NUMBER, or SMALLINT column as a numeric data type when compared with the Cursor's description attribute.

.description[Column][1] == teradatasql.NUMBER


teradatasql.STRING

Identifies a SQL CHAR, CLOB, INTERVAL, PERIOD, or VARCHAR column as a character data type when compared with the Cursor's description attribute.

.description[Column][1] == teradatasql.STRING

Escape Syntax

The Teradata SQL Driver for Python accepts most of the JDBC escape clauses offered by the Teradata JDBC Driver.

Date and Time Literals

Date and time literal escape clauses are replaced by the corresponding SQL literal before the SQL request text is transmitted to the database.

Literal Type Format
Date {d 'yyyy-mm-dd'}
Time {t 'hh:mm:ss'}
Timestamp {ts 'yyyy-mm-dd hh:mm:ss'}
Timestamp {ts 'yyyy-mm-dd hh:mm:ss.f'}

For timestamp literal escape clauses, the decimal point and fractional digits may be omitted, or 1 to 6 fractional digits f may be specified after a decimal point.

Scalar Functions

Scalar function escape clauses are replaced by the corresponding SQL expression before the SQL request text is transmitted to the database.

Numeric Function Returns
{fn ABS(number)} Absolute value of number
{fn ACOS(float)} Arccosine, in radians, of float
{fn ASIN(float)} Arcsine, in radians, of float
{fn ATAN(float)} Arctangent, in radians, of float
{fn ATAN2(y,x)} Arctangent, in radians, of y / x
{fn CEILING(number)} Smallest integer greater than or equal to number
{fn COS(float)} Cosine of float radians
{fn COT(float)} Cotangent of float radians
{fn DEGREES(number)} Degrees in number radians
{fn EXP(float)} e raised to the power of float
{fn FLOOR(number)} Largest integer less than or equal to number
{fn LOG(float)} Natural (base e) logarithm of float
{fn LOG10(float)} Base 10 logarithm of float
{fn MOD(integer1,integer2)} Remainder for integer1 / integer2
{fn PI()} The constant pi, approximately equal to 3.14159...
{fn POWER(number,integer)} number raised to integer power
{fn RADIANS(number)} Radians in number degrees
{fn RAND(seed)} A random float value such that 0 ≤ value < 1, and seed is ignored
{fn ROUND(number,places)} number rounded to places
{fn SIGN(number)} -1 if number is negative; 0 if number is 0; 1 if number is positive
{fn SIN(float)} Sine of float radians
{fn SQRT(float)} Square root of float
{fn TAN(float)} Tangent of float radians
{fn TRUNCATE(number,places)} number truncated to places
String Function Returns
{fn ASCII(string)} ASCII code of the first character in string
{fn CHAR(code)} Character with ASCII code
{fn CHAR_LENGTH(string)} Length in characters of string
{fn CHARACTER_LENGTH(string)} Length in characters of string
{fn CONCAT(string1,string2)} String formed by concatenating string1 and string2
{fn DIFFERENCE(string1,string2)} A number from 0 to 4 that indicates the phonetic similarity of string1 and string2 based on their Soundex codes, such that a larger return value indicates greater phonetic similarity; 0 indicates no similarity, 4 indicates strong similarity
{fn INSERT(string1,position,length,string2)} String formed by replacing the length-character segment of string1 at position with string2, available beginning with Teradata Database 15.0
{fn LCASE(string)} String formed by replacing all uppercase characters in string with their lowercase equivalents
{fn LEFT(string,count)} Leftmost count characters of string
{fn LENGTH(string)} Length in characters of string
{fn LOCATE(string1,string2)} Position in string2 of the first occurrence of string1, or 0 if string2 does not contain string1
{fn LTRIM(string)} String formed by removing leading spaces from string
{fn OCTET_LENGTH(string)} Length in octets (bytes) of string
{fn POSITION(string1INstring2)} Position in string2 of the first occurrence of string1, or 0 if string2 does not contain string1
{fn REPEAT(string,count)} String formed by repeating string count times, available beginning with Teradata Database 15.0
{fn REPLACE(string1,string2,string3)} String formed by replacing all occurrences of string2 in string1 with string3
{fn RIGHT(string,count)} Rightmost count characters of string, available beginning with Teradata Database 15.0
{fn RTRIM(string)} String formed by removing trailing spaces from string
{fn SOUNDEX(string)} Soundex code for string
{fn SPACE(count)} String consisting of count spaces
{fn SUBSTRING(string,position,length)} The length-character segment of string at position
{fn UCASE(string)} String formed by replacing all lowercase characters in string with their uppercase equivalents
System Function Returns
{fn DATABASE()} Current default database name
{fn IFNULL(expression,value)} expression if expression is not NULL, or value if expression is NULL
{fn USER()} Logon user name, which may differ from the current authorized user name after SET QUERY_BAND sets a proxy user
Time/Date Function Returns
{fn CURDATE()} Current date
{fn CURRENT_DATE()} Current date
{fn CURRENT_TIME()} Current time
{fn CURRENT_TIMESTAMP()} Current date and time
{fn CURTIME()} Current time
{fn DAYOFMONTH(date)} Integer from 1 to 31 indicating the day of month in date
{fn EXTRACT(YEAR FROMvalue)} The year component of the date and/or time value
{fn EXTRACT(MONTH FROMvalue)} The month component of the date and/or time value
{fn EXTRACT(DAY FROMvalue)} The day component of the date and/or time value
{fn EXTRACT(HOUR FROMvalue)} The hour component of the date and/or time value
{fn EXTRACT(MINUTE FROMvalue)} The minute component of the date and/or time value
{fn EXTRACT(SECOND FROMvalue)} The second component of the date and/or time value
{fn HOUR(time)} Integer from 0 to 23 indicating the hour of time
{fn MINUTE(time)} Integer from 0 to 59 indicating the minute of time
{fn MONTH(date)} Integer from 1 to 12 indicating the month of date
{fn NOW()} Current date and time
{fn SECOND(time)} Integer from 0 to 59 indicating the second of time
{fn TIMESTAMPADD(SQL_TSI_YEAR,count,timestamp)} Timestamp formed by adding count years to timestamp
{fn TIMESTAMPADD(SQL_TSI_MONTH,count,timestamp)} Timestamp formed by adding count months to timestamp
{fn TIMESTAMPADD(SQL_TSI_DAY,count,timestamp)} Timestamp formed by adding count days to timestamp
{fn TIMESTAMPADD(SQL_TSI_HOUR,count,timestamp)} Timestamp formed by adding count hours to timestamp
{fn TIMESTAMPADD(SQL_TSI_MINUTE,count,timestamp)} Timestamp formed by adding count minutes to timestamp
{fn TIMESTAMPADD(SQL_TSI_SECOND,count,timestamp)} Timestamp formed by adding count seconds to timestamp
{fn TIMESTAMPDIFF(SQL_TSI_YEAR,timestamp1,timestamp2)} Number of years by which timestamp2 exceeds timestamp1
{fn TIMESTAMPDIFF(SQL_TSI_MONTH,timestamp1,timestamp2)} Number of months by which timestamp2 exceeds timestamp1
{fn TIMESTAMPDIFF(SQL_TSI_DAY,timestamp1,timestamp2)} Number of days by which timestamp2 exceeds timestamp1
{fn TIMESTAMPDIFF(SQL_TSI_HOUR,timestamp1,timestamp2)} Number of hours by which timestamp2 exceeds timestamp1
{fn TIMESTAMPDIFF(SQL_TSI_MINUTE,timestamp1,timestamp2)} Number of minutes by which timestamp2 exceeds timestamp1
{fn TIMESTAMPDIFF(SQL_TSI_SECOND,timestamp1,timestamp2)} Number of seconds by which timestamp2 exceeds timestamp1
{fn YEAR(date)} The year of date

Conversion Functions

Conversion function escape clauses are replaced by the corresponding SQL expression before the SQL request text is transmitted to the database.

Conversion Function Returns
{fn CONVERT(value, SQL_BIGINT)} value converted to SQL BIGINT
{fn CONVERT(value, SQL_BINARY(size))} value converted to SQL BYTE(size)
{fn CONVERT(value, SQL_CHAR(size))} value converted to SQL CHAR(size)
{fn CONVERT(value, SQL_DATE)} value converted to SQL DATE
{fn CONVERT(value, SQL_DECIMAL(precision,scale))} value converted to SQL DECIMAL(precision,scale)
{fn CONVERT(value, SQL_DOUBLE)} value converted to SQL DOUBLE PRECISION, a synonym for FLOAT
{fn CONVERT(value, SQL_FLOAT)} value converted to SQL FLOAT
{fn CONVERT(value, SQL_INTEGER)} value converted to SQL INTEGER
{fn CONVERT(value, SQL_LONGVARBINARY)} value converted to SQL VARBYTE(64000)
{fn CONVERT(value, SQL_LONGVARCHAR)} value converted to SQL LONG VARCHAR
{fn CONVERT(value, SQL_NUMERIC)} value converted to SQL NUMBER
{fn CONVERT(value, SQL_SMALLINT)} value converted to SQL SMALLINT
{fn CONVERT(value, SQL_TIME(scale))} value converted to SQL TIME(scale)
{fn CONVERT(value, SQL_TIMESTAMP(scale))} value converted to SQL TIMESTAMP(scale)
{fn CONVERT(value, SQL_TINYINT)} value converted to SQL BYTEINT
{fn CONVERT(value, SQL_VARBINARY(size))} value converted to SQL VARBYTE(size)
{fn CONVERT(value, SQL_VARCHAR(size))} value converted to SQL VARCHAR(size)

LIKE Predicate Escape Character

Within a LIKE predicate's pattern argument, the characters % (percent) and _ (underscore) serve as wildcards. To interpret a particular wildcard character literally in a LIKE predicate's pattern argument, the wildcard character must be preceded by an escape character, and the escape character must be indicated in the LIKE predicate's ESCAPE clause.

LIKE predicate escape character escape clauses are replaced by the corresponding SQL clause before the SQL request text is transmitted to the database.

{escape 'EscapeCharacter'}

The escape clause must be specified immediately after the LIKE predicate that it applies to.

Outer Joins

Outer join escape clauses are replaced by the corresponding SQL clause before the SQL request text is transmitted to the database.

{ojTableNameOptionalCorrelationNameLEFT OUTER JOINTableNameOptionalCorrelationNameONJoinCondition}

{ojTableNameOptionalCorrelationNameRIGHT OUTER JOINTableNameOptionalCorrelationNameONJoinCondition}

{ojTableNameOptionalCorrelationNameFULL OUTER JOINTableNameOptionalCorrelationNameONJoinCondition}

Stored Procedure Calls

Stored procedure call escape clauses are replaced by the corresponding SQL clause before the SQL request text is transmitted to the database.

{callProcedureName}

{callProcedureName(CommaSeparatedParameterValues...)}

Native SQL

When a SQL request contains the native SQL escape clause, all escape clauses are replaced in the SQL request text, and the modified SQL request text is returned to the application as a result set containing a single row and a single VARCHAR column. The SQL request text is not transmitted to the database, and the SQL request is not executed. The native SQL escape clause mimics the functionality of the JDBC API Connection.nativeSQL method.

{fn teradata_nativesql}

Connection Functions

The following table lists connection function escape clauses that are intended for use with the native SQL escape clause {fn teradata_nativesql}.

These functions provide information about the connection, or control the behavior of the connection. Functions that provide information return locally-cached information and avoid a round-trip to the database. Connection function escape clauses are replaced by the returned information before the SQL request text is transmitted to the database.

Connection Function Returns
{fn teradata_amp_count} Number of AMPs of the Teradata Database system
{fn teradata_database_version} Version number of the Teradata Database
{fn teradata_driver_version} Version number of the Teradata SQL Driver for Python
{fn teradata_getloglevel} Current log level
{fn teradata_logon_sequence_number} Session's Logon Sequence Number, if available
{fn teradata_provide(config_response)} Config Response parcel contents in JSON format
{fn teradata_provide(connection_id)} Connection's unique identifier within the process
{fn teradata_provide(default_connection)} false indicating this is not a stored procedure default connection
{fn teradata_provide(host_id)} Session's host ID
{fn teradata_provide(java_charset_name)} UTF8
{fn teradata_provide(lob_support)} true or false indicating this connection's LOB support
{fn teradata_provide(local_address)} Local address of the connection's TCP socket
{fn teradata_provide(local_port)} Local port of the connection's TCP socket
{fn teradata_provide(original_hostname)} Original specified Teradata Database hostname
{fn teradata_provide(redrive_active)} true or false indicating whether this connection has Redrive active
{fn teradata_provide(remote_address)} Hostname (if available) and IP address of the connected Teradata Database node
{fn teradata_provide(remote_port)} TCP port number of the Teradata Database
{fn teradata_provide(rnp_active)} true or false indicating whether this connection has Recoverable Network Protocol active
{fn teradata_provide(session_charset_code)} Session character set code 191
{fn teradata_provide(session_charset_name)} Session character set name UTF8
{fn teradata_provide(sip_support)} true or false indicating this connection's StatementInfo parcel support
{fn teradata_provide(transaction_mode)} Session's transaction mode, ANSI or TERA
{fn teradata_session_number} Session number
{fn teradata_setloglevel(LogLevel)} Empty string, and changes the connection's LogLevel

Request-Scope Functions

The following table lists request-scope function escape clauses that are intended for use with the Cursor .execute or .executemany methods.

These functions control the behavior of the corresponding Cursor, and are limited in scope to the particular SQL request in which they are specified. Request-scope function escape clauses are removed before the SQL request text is transmitted to the database.

Request-Scope Function Effect
{fn teradata_failfast} Reject ("fail fast") this SQL request rather than delay by a workload management rule or throttle
{fn teradata_fake_result_sets} A fake result set containing statement metadata precedes each real result set
{fn teradata_lobselect(Option)} Executes the SQL request with LOB select Option S (spool-scoped LOB locators), T (transaction-scoped LOB locators), or the default I (inline materialized LOB values)
{fn teradata_parameter(Index,DataType) Transmits parameter Index bind values as DataType
{fn teradata_provide(request_scope_lob_support_off)} Turns off LOB support for this SQL request
{fn teradata_provide(request_scope_refresh_rsmd)} Executes the SQL request with the default request processing option B (both)
{fn teradata_provide(request_scope_sip_support_off)} Turns off StatementInfo parcel support for this SQL request
{fn teradata_rpo(RequestProcessingOption)} Executes the SQL request with RequestProcessingOption S (prepare), E (execute), or the default B (both)
{fn teradata_untrusted} Marks the SQL request as untrusted; not implemented yet

FastLoad

The Teradata SQL Driver for Python now offers FastLoad.

Please be aware that this is just the initial release of the FastLoad feature. Think of it as a beta or preview version. It works, but does not yet offer all the features that JDBC FastLoad offers. FastLoad is still under active development, and we will continue to enhance it in subsequent builds.

FastLoad has limitations and cannot be used in all cases as a substitute for SQL batch insert:

  • FastLoad can only load into an empty permanent table.
  • FastLoad cannot load additional rows into a table that already contains rows.
  • FastLoad cannot load into a volatile table or global temporary table.
  • FastLoad cannot load duplicate rows into a MULTISET table.
  • Do not use FastLoad to load only a few rows, because FastLoad opens extra connections to the database, which is time consuming.
  • Only use FastLoad to load many rows (at least 100,000 rows) so that the row-loading performance gain exceeds the overhead of opening additional connections.
  • FastLoad does not support all Teradata Database data types. For example, BLOB and CLOB are not supported.
  • FastLoad requires StatementInfo parcel support to be enabled.
  • FastLoad locks the destination table.
  • If Online Archive encounters a table being loaded with FastLoad, online archiving of that table will be bypassed.

Your application can bind a single row of data for FastLoad, but that is not recommended because the overhead of opening additional connections causes FastLoad to be slower than a regular SQL INSERT for a single row.

How to use FastLoad:

  • Auto-commit should be turned off before beginning a FastLoad.
  • FastLoad is intended for binding many rows at a time. Each batch of rows must be able to fit into memory.
  • When auto-commit is turned off, your application can insert multiple batches in a loop for the same FastLoad.
  • Each column's data type must be consistent across every row in every batch over the entire FastLoad.
  • The column values of the first row of the first batch dictate what the column data types must be in all subsequent rows and all subsequent batches of the FastLoad.

FastLoad opens multiple data transfer connections to the database. FastLoad evenly distributes each batch of rows across the available data transfer connections, and uses overlapped I/O to send and receive messages in parallel.

To use FastLoad, your application must prepend one of the following escape functions to the INSERT statement:

  • {fn teradata_try_fastload} tries to use FastLoad for the INSERT statement, and automatically executes the INSERT as a regular SQL statement when the INSERT is not compatible with FastLoad.
  • {fn teradata_require_fastload} requires FastLoad for the INSERT statement, and fails with an error when the INSERT is not compatible with FastLoad.

Your application can prepend other optional escape functions to the INSERT statement:

  • {fn teradata_sessions(n)} specifies the number of data transfer connections to be opened, and is capped at the number of AMPs. The default is the smaller of 8 or the number of AMPs. CHECK WORKLOAD is not yet used, meaning that the driver does not ask the database how many data transfer connections should be used.
  • {fn teradata_error_table_1_suffix(suffix)} specifies what suffix to append to the name of FastLoad error table 1. The default suffix is _ERR_1.
  • {fn teradata_error_table_2_suffix(suffix)} specifies what suffix to append to the name of FastLoad error table 2. The default suffix is _ERR_2.
  • {fn teradata_error_table_database(dbname)} specifies the parent database name for FastLoad error tables 1 and 2. By default, the FastLoad error tables reside in the same database as the destination table.

After beginning a FastLoad, your application can obtain the Logon Sequence Number (LSN) assigned to the FastLoad by prepending the following escape functions to the INSERT statement:

  • {fn teradata_nativesql}{fn teradata_logon_sequence_number} returns the string form of an integer representing the Logon Sequence Number (LSN) for the FastLoad. Returns an empty string if the request is not a FastLoad.

FastLoad does not stop for data errors such as constraint violations or unique primary index violations. After inserting each batch of rows, your application must obtain warning and error information by prepending the following escape functions to the INSERT statement:

  • {fn teradata_nativesql}{fn teradata_get_warnings} returns in one string all warnings generated by FastLoad for the request.
  • {fn teradata_nativesql}{fn teradata_get_errors} returns in one string all data errors observed by FastLoad for the most recent batch. The data errors are obtained from FastLoad error table 1, for problems such as constraint violations, data type conversion errors, and unavailable AMP conditions.

Your application ends FastLoad by committing or rolling back the current transaction. After commit or rollback, your application must obtain warning and error information by prepending the following escape functions to the INSERT statement:

  • {fn teradata_nativesql}{fn teradata_get_warnings} returns in one string all warnings generated by FastLoad for the commit or rollback. The warnings are obtained from FastLoad error table 2, for problems such as duplicate rows.
  • {fn teradata_nativesql}{fn teradata_get_errors} returns in one string all data errors observed by FastLoad for the commit or rollback. The data errors are obtained from FastLoad error table 2, for problems such as unique primary index violations.

Warning and error information remains available until the next batch is inserted or until the commit or rollback. Each batch execution clears the prior warnings and errors. Each commit or rollback clears the prior warnings and errors.

Change Log

16.20.0.62 - May 12, 2020

  • GOSQL-58 support multiple files for Elicit File protocol
  • GOSQL-59 FastLoad accommodate dbscontrol change of COUNT(*) return type

16.20.0.61 - Apr 30, 2020

  • GOSQL-57 Deferred LOB values larger than 1MB

16.20.0.60 - Mar 27, 2020

  • GOSQL-22 enable insert of large LOB values over 64KB
  • GOSQL-52 teradata_try_fastload consider bind value data types
  • GOSQL-54 enforce Decimal value maximum precision 38
  • PYDBAPI-37 Teradata Data Types Support up to 14.10 including LOB data

16.20.0.59 - Jan 8, 2020

  • GOSQL-51 FastLoad fails when table is dropped and recreated
  • PYDBAPI-72 bind value performance improvement
  • PYDBAPI-73 DBAPI fails to insert 16383 rows

16.20.0.58 - Dec 11, 2019

  • PYDBAPI-71 execute and executemany ignoreErrors parameter

16.20.0.57 - Dec 10, 2019

  • GOSQL-50 provide FastLoad duplicate row errors with auto-commit on

16.20.0.56 - Dec 4, 2019

  • PYDBAPI-70 raise error for closed cursor usage

16.20.0.55 - Nov 26, 2019

  • GOSQL-15 add database connection parameter
  • PYDBAPI-66 Better exception when running on 32-bit Python

16.20.0.54 - Nov 21, 2019

  • GOSQL-49 FastLoad support for additional connection parameters

16.20.0.53 - Nov 15, 2019

  • GOSQL-36 segment and iterate parameter batches per batch row limit
  • GOSQL-43 segment and iterate parameter batches per request message size limit for FastLoad

16.20.0.52 - Oct 18, 2019

  • Sample programs for fake result sets

16.20.0.51 - Oct 16, 2019

  • GOSQL-46 LDAP password special characters

16.20.0.50 - Oct 7, 2019

  • PYDBAPI-68 improve performance for batch bind values

16.20.0.49 - Oct 3, 2019

  • GOSQL-45 FastLoad interop with Stored Password Protection

16.20.0.48 - Sep 6, 2019

  • GOSQL-14 add support for FastLoad protocol
  • GOSQL-34 negative scale for Number values
  • PYDBAPI-29 Data Transfer - FastLoad Protocol

16.20.0.47 - Aug 27, 2019

  • GOSQL-40 Skip executing empty SQL request text
  • PYDBAPI-67 teradatasql.connect JSON connection string optional

16.20.0.46 - Aug 16, 2019

  • GOSQL-39 COP Discovery interop with Kerberos

16.20.0.45 - Aug 12, 2019

  • GOSQL-38 timestamp prefix log messages

16.20.0.44 - Aug 7, 2019

  • GOSQL-4 Support COP discovery
  • PYDBAPI-36 COP Discovery

16.20.0.43 - Jul 29, 2019

  • GOSQL-18 Auto-commit
  • PYDBAPI-61 commit and rollback methods

16.20.0.42 - Jun 7, 2019

  • PYDBAPI-65 sample program BatchInsert.py

16.20.0.41 - Feb 14, 2019

  • PYDBAPI-57 fetchmany may return "rows are closed" instead of empty result set

16.20.0.40 - Feb 8, 2019

  • GOSQL-11 JWT authentication method
  • GOSQL-16 tmode connection parameter
  • GOSQL-17 commit and rollback functions

16.20.0.39 - Oct 26, 2018

  • GOSQL-33 CALL to stored procedure INOUT and OUT parameter output values

16.20.0.38 - Oct 25, 2018

  • PYDBAPI-56 Stored Procedure Dynamic Result Sets

16.20.0.37 - Oct 22, 2018

  • Documentation change

16.20.0.36 - Oct 22, 2018

  • GOSQL-5 Create/Replace Procedure MultiTSR protocol

16.20.0.35 - Oct 22, 2018

  • GOSQL-10 Stored Password Protection
  • PYDBAPI-28 Secure Password at rest
  • PYDBAPI-47 Port sample program TJEncryptPassword to Python

16.20.0.34 - Oct 15, 2018

  • Fix for sample program TJEncryptPassword.py

16.20.0.33 - Oct 12, 2018

  • Installation dependency pycryptodome

16.20.0.32 - Sep 19, 2018

  • Sample programs LoadCSVFile.py and MetadataFromPrepare.py
  • Escape function teradata_fake_result_sets

16.20.0.31 - Sep 19, 2018

  • Added function tracing

16.20.0.30 - Sep 14, 2018

  • PYDBAPI-12 Connectivity
  • PYDBAPI-33 Pandas library Interoperability
  • Moved samples directory

16.20.0.29 - Sep 14, 2018

  • Sample program ElicitFile.py

16.20.0.28 - Sep 13, 2018

  • Documentation change

16.20.0.27 - Sep 12, 2018

  • Documentation change

16.20.0.26 - Sep 11, 2018

  • PYDBAPI-8 Documentation
  • PYDBAPI-9 User Guide Content

16.20.0.25 - Sep 10, 2018

  • Documentation change

16.20.0.24 - Sep 6, 2018

  • PYDBAPI-54 Implement cursor rowcount attribute
  • PYDBAPI-55 Improved support for Python data types

16.20.0.23 - Aug 31, 2018

  • KeepResponse only for LOB locators

16.20.0.22 - Aug 30, 2018

  • Fixed NUMBER values

16.20.0.21 - Aug 29, 2018

  • Close orphaned rows

16.20.0.20 - Aug 28, 2018

  • decimal and datetime values

16.20.0.19 - Aug 22, 2018

  • timedelta bind values

16.20.0.18 - Aug 21, 2018

  • datetime.datetime bind values

16.20.0.17 - Aug 20, 2018

  • Version number in errors

16.20.0.16 - Aug 17, 2018

  • SLES 11 SP1 compatibility

16.20.0.15 - Aug 17, 2018

  • Documentation change

16.20.0.14 - Aug 10, 2018

  • Documentation change

16.20.0.13 - Aug 9, 2018

  • Documentation change

16.20.0.12 - Aug 9, 2018

  • PYDBAPI-10 User Guide Delivery and Viewability
  • PYDBAPI-11 Searchability

16.20.0.11 - Aug 8, 2018

  • Documentation change

16.20.0.10 - Aug 8, 2018

  • Documentation change

16.20.0.9 - Aug 7, 2018

  • Install documentation in teradatasql directory

16.20.0.8 - Aug 7, 2018

  • GOSQL-7 TDNEGO authentication method
  • PYDBAPI-42 Teradata Logon mechanism - TDNEGO

16.20.0.7 - Jul 30, 2018

  • GOSQL-8 Support parameter marker batch insert
  • PYDBAPI-45 Parameterized Batch Insertion using executeMany

16.20.0.6 - Jul 25, 2018

  • Thread safety for handle maps

16.20.0.5 - Jul 25, 2018

  • Removed atexit

16.20.0.4 - Jul 23, 2018

  • PYDBAPI-4 Provide Python Driver license file

16.20.0.3 - Jul 19, 2018

  • PYDBAPI-46 Accept subclasses of bytes, int, float, str as bind values

16.20.0.2 - Jul 19, 2018

  • Package attributes change

16.20.0.1 - Jul 18, 2018

  • Version number change

16.20.0.0 - Jul 18, 2018

  • GOSQL-1 Encrypted logon
  • GOSQL-2 LDAP and Kerberos authentication
  • GOSQL-3 Support for 1MB Rows
  • GOSQL-6 Elicit File protocol
  • PYDBAPI-1 Distribution
  • PYDBAPI-5 cursor.execute method return cursor
  • PYDBAPI-6 Install and Deployment
  • PYDBAPI-7 pip install of python driver package
  • PYDBAPI-13 Operating System Platforms
  • PYDBAPI-14 Driver must be available for use by Windows OS Users
  • PYDBAPI-15 Driver must be available for use by OSX (Mac) Users
  • PYDBAPI-16 Driver must be available for use by Linux OS Users
  • PYDBAPI-17 Python Language version
  • PYDBAPI-18 Python language version 3.4
  • PYDBAPI-19 Distribution
  • PYDBAPI-23 Teradata Analytics Platform Interoperability/Support
  • PYDBAPI-24 Works with Teradata Database 16.10, 16.20
  • PYDBAPI-26 Teradata Logon mechanism - Kerberos
  • PYDBAPI-32 Downloadable from pypi.org
  • PYDBAPI-40 Teradata Logon mechanism - LDAP
  • PYDBAPI-41 Teradata Logon mechanism - TD2
  • PYDBAPI-43 parameterized single-row inserts
  • PYDBAPI-44 parameterized queries

Project details


Release history Release notifications | RSS feed

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for teradatasql, version 16.20.0.62
Filename, size File type Python version Upload date Hashes
Filename, size teradatasql-16.20.0.62-py3-none-any.whl (13.4 MB) File type Wheel Python version py3 Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page