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.x, and runs on Windows, macOS, and Linux. 32-bit Python is not supported.
For community support, please visit the Connectivity Forum.
Copyright 2018 Teradata. All Rights Reserved.
Features
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.
- Encrypted logon using the
TD2
,LDAP
,KRB5
(Kerberos), orTDNEGO
logon mechanisms. - Data encryption enabled via the
encryptdata
connection parameter. - Unicode character data transferred via the UTF8 session character set.
- 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.
Limitations
- The Teradata Database's default transaction mode (ANSI or TERA) is always used. The
tmode
connection parameter is not supported yet. - The UTF8 session character set is always used. The
charset
connection parameter is not supported. - The following complex data types are not supported yet:
XML
,JSON
,DATASET STORAGE FORMAT AVRO
, andDATASET STORAGE FORMAT CSV
. - The
CREATE PROCEDURE
andREPLACE PROCEDURE
commands are not supported yet. These commands use a different wire protocol from other DDL commands. - COP Discovery is not supported yet. You must specify the hostname or IP address of a specific Teradata Database node to connect to.
- No support yet for data encryption that is governed by central administration. To enable data encryption, you must specify a
true
value for theencryptdata
connection parameter. - Laddered Concurrent Connect is not supported yet.
- The JWT logon mechanism is not supported yet.
- No support yet for Recoverable Network Protocol and Redrive.
- Auto-commit for ANSI transaction mode is not offered yet. You must explicitly execute a
commit
command when using ANSI transaction mode. - FastLoad is not available yet.
- FastExport is not available yet.
- Monitor partition support is not available yet.
Installation
Use pip to install the Teradata SQL Driver for Python.
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.
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.
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 kwargs
, as a JSON string, or using a combination of the two approaches. The teradatasql.connect
function's first argument is a JSON string. The teradatasql.connect
function's second and subsequent arguments are optional kwargs
.
-
Connection parameters specified only as
kwargs
con = teradatasql.connect(None, 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')
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. |
dbs_port |
"1025" |
quoted integer | Specifies 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. Note that COP Discovery is not implemented yet. | |
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, and the 4-bit governs transmit and receive message hex dumps. |
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), LDAP , KRB5 for Kerberos, or TDNEGO . Note that JWT authentication is not supported yet. |
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 table below for details. |
user |
string | Specifies the Teradata Database username. Equivalent to the Teradata JDBC Driver USER connection parameter. |
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 |
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.
Module Constructors
teradatasql.connect(
JSONConnectionString ,
Parameters... )
Creates a connection to the database and returns a Connection object.
The first parameter is a JSON string or None
. The second and subsequent arguments are optional kwargs
. Specify connection parameters as a JSON string, as kwargs
, or a combination of the two.
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. Not implemented yet.
.cursor()
Creates and returns a new Cursor object for the Connection.
.rollback()
Rolls back the current transaction. Not implemented yet.
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, orNone
otherwise..description[
Column][5]
provides the column scale if applicable, orNone
otherwise..description[
Column][6]
provides the column nullability asTrue
orFalse
.
.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. 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. Returns a result set consisting of the parameter output values, if any, followed by any dynamic result sets.
.close()
Closes the Cursor.
.execute(
SQLRequest ,
OptionalSequenceOfParameterValues )
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.
.executemany(
SQLRequest ,
SequenceOfSequencesOfParameterValues )
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.
.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
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.
Source Distributions
Built Distribution
Hashes for teradatasql-16.20.0.27-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 2aa9a57af0230ae2a70dc90a67ffb5e958457a3f472ee9d03ba0ed6227f53df6 |
|
MD5 | 1e4582ad3405b3afc473052ead02e227 |
|
BLAKE2b-256 | f8eab6882b08c805976f1362e96908d2f5ff30c273fc306d83c0784dd748ea11 |