API reference¶
-
turbodbc.
connect
(*args, **kwds)¶ Create a connection with the database identified by the
dsn
or theconnection_string
.Parameters: - dsn – Data source name as given in the (unix) odbc.ini file or (Windows) ODBC Data Source Administrator tool.
- turbodbc_options – Options that control how turbodbc interacts with the database. Create such a struct with turbodbc.make_options() or leave this blank to take the defaults.
- connection_string – Preformatted ODBC connection string. Specifying this and dsn or kwargs at the same time raises ParameterError.
- **kwargs – You may specify additional options as you please. These options will go into the connection string that identifies the database. Valid options depend on the specific database you would like to connect with (e.g. user and password, or uid and pwd)
Returns: A connection to your database
-
turbodbc.
make_options
(read_buffer_size=None, parameter_sets_to_buffer=None, varchar_max_character_limit=None, prefer_unicode=None, use_async_io=None, autocommit=None, large_decimals_as_64_bit_types=None, limit_varchar_results_to_max=None, force_extra_capacity_for_unicode=None, fetch_wchar_as_char=None)¶ Create options that control how turbodbc interacts with a database. These options affect performance for the most part, but some options may require adjustment so that all features work correctly with certain databases.
If a parameter is set to None, this means the default value is used.
Parameters: - read_buffer_size – Affects performance. Controls the size of batches fetched from the
database when reading result sets. Can be either an instance of
turbodbc.Megabytes
(recommended) orturbodbc.Rows
. - parameter_sets_to_buffer – Affects performance. Number of parameter sets (rows) which shall be
transferred to the server in a single batch when
executemany()
is called. Must be an integer. - varchar_max_character_limit – Affects behavior/performance. If a result set contains fields
of type
VARCHAR(max)
orNVARCHAR(max)
or the equivalent type of your database, buffers will be allocated to hold the specified number of characters. This may lead to truncation. The default value is65535
characters. Please note that large values reduce the risk of truncation, but may affect the number of rows in a batch of result sets (seeread_buffer_size
). Please note that this option only relates to retrieving results, not sending parameters to the database. - use_async_io – Affects performance. Set this option to
True
if you want to use asynchronous I/O, i.e., while Python is busy converting database results to Python objects, new result sets are fetched from the database in the background. - prefer_unicode – May affect functionality and performance. Some databases do not support
strings encoded with UTF-8, leading to UTF-8 characters being misinterpreted, misrepresented, or
downright rejected. Set this option to
True
if you want to transfer character data using the UCS-2/UCS-16 encoding that use (multiple) two-byte instead of (multiple) one-byte characters. - autocommit – Affects behavior. If set to
True
, all queries and commands executed withcursor.execute()
orcursor.executemany()
will be succeeded by an implicitCOMMIT
operation, persisting any changes made to the database. If not set or set toFalse
, users has to take care of callingcursor.commit()
themselves. - large_decimals_as_64_bit_types – Affects behavior. If set to
True
,DECIMAL(x, y)
results withx > 18
will be rendered as 64 bit integers (y == 0
) or 64 bit floating point numbers (y > 0
), respectively. Use this option if your decimal data types are larger than the data they actually hold. Using this data type can lead to overflow errors and loss of precision. If not set or set toFalse
, large decimals are rendered as strings. - limit_varchar_results_to_max – Affects behavior/performance. If set to
True
, any text-like fields such asVARCHAR(n)
andNVARCHAR(n)
will be limited to a maximum size ofvarchar_max_character_limit
characters. This may lead to values being truncated, but reduces the amount of memory required to allocate string buffers, leading to larger, more efficient batches. If not set or set toFalse
, strings can exceedvarchar_max_character_limit
in size if the database reports them this way. For fields such asTEXT
, some databases report a size of 2 billion characters. Please note that this option only relates to retrieving results, not sending parameters to the database. - force_extra_capacity_for_unicode – Affects behavior/performance. Some ODBC drivers report the
length of the
VARCHAR
/NVARCHAR
field rather than the number of code points for which space is required to be allocated, resulting in string truncations. Set this option toTrue
to increase the memory allocated forVARCHAR
andNVARCHAR
fields and prevent string truncations. Please note that this option only relates to retrieving results, not sending parameters to the database. - fetch_wchar_as_char – Affects behavior. Some ODBC drivers retrieve single byte encoded strings
into
NVARCHAR
fields of result sets, which are decoded incorrectly by turbodbc default settings, resulting in corrupt strings. Set this option toTrue
to have turbodbc treatNVARCHAR
types as narrow character types when decoding the fields in result sets. Please note that this option only relates to retrieving results, not sending parameters to the database.
Returns: An option struct that is suitable to pass to the
turbodbc_options
parameter ofturbodbc.connect()
- read_buffer_size – Affects performance. Controls the size of batches fetched from the
database when reading result sets. Can be either an instance of
-
class
turbodbc.connection.
Connection
(impl)¶ -
autocommit
¶ This attribute controls whether changes are automatically committed after each execution or not.
-
close
()¶ Close the connection and all associated cursors. This will implicitly roll back any uncommitted operations.
-
commit
(**kwds)¶ Commits the current transaction
-
cursor
(**kwds)¶ Create a new
Cursor
instance associated with thisConnection
Returns: A new Cursor
instance
-
rollback
(**kwds)¶ Roll back all changes in the current transaction
-
-
class
turbodbc.cursor.
Cursor
(impl)¶ This class allows you to send SQL commands and queries to a database and retrieve associated result sets.
-
close
()¶ Close the cursor.
-
description
¶ Retrieve a description of the columns in the current result set
Returns: A tuple of seven elements. Only some elements are meaningful: - Element #0 is the name of the column
- Element #1 is the type code of the column
- Element #6 is true if the column may contain
NULL
values
-
execute
(**kwds)¶ Execute an SQL command or query
Parameters: - sql – A (unicode) string that contains the SQL command or query. If you would like to
use parameters, please use a question mark
?
at the location where the parameter shall be inserted. - parameters – An iterable of parameter values. The number of values must match the number of parameters in the SQL string.
Returns: The
Cursor
object to allow chaining of operations.- sql – A (unicode) string that contains the SQL command or query. If you would like to
use parameters, please use a question mark
-
executemany
(**kwds)¶ Execute an SQL command or query with multiple parameter sets passed in a row-wise fashion. This function is part of PEP-249.
Parameters: - sql – A (unicode) string that contains the SQL command or query. If you would like to
use parameters, please use a question mark
?
at the location where the parameter shall be inserted. - parameters – An iterable of iterable of parameter values. The outer iterable represents separate parameter sets. The inner iterable contains parameter values for a given parameter set. The number of values of each set must match the number of parameters in the SQL string.
Returns: The
Cursor
object to allow chaining of operations.- sql – A (unicode) string that contains the SQL command or query. If you would like to
use parameters, please use a question mark
-
executemanycolumns
(**kwds)¶ Execute an SQL command or query with multiple parameter sets that are passed in a column-wise fashion as opposed to the row-wise parameters in
executemany()
. This function is a turbodbc-specific extension to PEP-249.Parameters: - sql – A (unicode) string that contains the SQL command or query. If you would like to
use parameters, please use a question mark
?
at the location where the parameter shall be inserted. - columns – An iterable of NumPy MaskedArrays. The Arrays represent the columnar parameter data,
Returns: The
Cursor
object to allow chaining of operations.- sql – A (unicode) string that contains the SQL command or query. If you would like to
use parameters, please use a question mark
-
fetchall
(**kwds)¶ Fetches a list of all rows in the active result set generated with
execute()
orexecutemany()
.Returns: A list of rows
-
fetchallarrow
(strings_as_dictionary=False, adaptive_integers=False)¶ Fetches all rows in the active result set generated with
execute()
orexecutemany()
.Parameters: - strings_as_dictionary – If true, fetch string columns as dictionary[string] instead of a plain string column.
- adaptive_integers – If true, instead of the integer type returned by the database (driver), this produce integer columns with the smallest possible integer type in which all values can be stored. Be aware that here the type depends on the resulting data.
Returns: pyarrow.Table
-
fetchallnumpy
()¶ Fetches all rows in the active result set generated with
execute()
orexecutemany()
.Returns: An OrderedDict
of columns, where the keys of the dictionary are the column names. The columns are of NumPy’sMaskedArray
type, where the optimal data type for each result set column is chosen automatically.
-
fetcharrowbatches
(strings_as_dictionary=False, adaptive_integers=False)¶ Fetches rows in the active result set generated with
execute()
orexecutemany()
as an iterable of arrow tables.Parameters: - strings_as_dictionary – If true, fetch string columns as dictionary[string] instead of a plain string column.
- adaptive_integers – If true, instead of the integer type returned by the database (driver), this produce integer columns with the smallest possible integer type in which all values can be stored. Be aware that here the type depends on the resulting data.
Returns: generator of
pyarrow.Table
-
fetchmany
(**kwds)¶ Fetches a batch of rows in the active result set generated with
execute()
orexecutemany()
.Parameters: size – Controls how many rows are returned. The default None
means that the value of Cursor.arraysize is used.Returns: A list of rows
-
fetchnumpybatches
()¶ Returns an iterator over all rows in the active result set generated with
execute()
orexecutemany()
.Returns: An iterator you can use to iterate over batches of rows of the result set. Each batch consists of an OrderedDict
of NumPyMaskedArray
instances. Seefetchallnumpy()
for details.
-
fetchone
(**kwds)¶ Returns a single row of a result set. Requires an active result set on the database generated with
execute()
orexecutemany()
.Returns: Returns None
when no more rows are available in the result set
-
setinputsizes
(sizes)¶ Has no effect since turbodbc automatically picks appropriate return types and sizes. Method exists since PEP-249 requires it.
-
setoutputsize
(size, column=None)¶ Has no effect since turbodbc automatically picks appropriate input types and sizes. Method exists since PEP-249 requires it.
-
-
class
turbodbc.exceptions.
Error
¶ turbodbc’s basic error class
-
class
turbodbc.exceptions.
InterfaceError
¶ An error that is raised whenever you use turbodbc incorrectly
-
class
turbodbc.exceptions.
DatabaseError
¶ An error that is raised when the database encounters an error while processing your commands and queries
-
class
turbodbc.exceptions.
ParameterError
¶ An error that is raised when you use connection arguments that are supposed to be mutually exclusive