Statements and Cursors
NAME
turborel.client.Connection: running statements, prepared statements
and their cursors, and what each completion told.
SYNOPSIS
conn.query(sql) -> Rowset | Complete
conn.query_into(sql, sink) -> int | Complete
conn.query_many(sql) -> Ran
conn.set_query_attributes(attributes)
conn.prepare(sql) -> Statement
conn.execute(statement, values=()) -> Rowset | Complete
conn.open_cursor(statement, values=(), rows=0, cursor=0) -> Batch
conn.fetch(statement, rows=0, cursor=0) -> Batch
conn.close_cursor(statement, cursor=0)
conn.describe(statement) -> Statement
conn.describe_cursor(statement, cursor=0) -> tuple[Column, ...]
conn.deallocate(statement)
conn.autocommit, conn.in_transaction
conn.rows_affected, conn.last_generated, conn.server_time
conn.commit_position, conn.warning, conn.conditions, conn.take_conditions()
DESCRIPTION
Running a statement. query sends one statement and reads its
outcome whole: a Rowset of a query's columns and rows, each row a
tuple, or a Complete of another statement, its sqlcode and
sqlstate. query_into gives a query's rows to sink instead, a batch
at a time as they come: sink.columns(columns) once, then
sink.rows(rows) for each batch; it returns the rows given, or another
statement's Complete. A statement that changes no row completes with
SQLCODE 100 and SQLSTATE 02000.
Several statements. query_many sends a text of several
statements as one request, which needs multi-statement. The server
runs them in turn and stops at the first failure. The Ran it gives
holds outcomes, one for each statement run, and failure, the
exception of the one that failed, or None. A text of no statement gives
one Complete. A failure of the connection is raised, not held.
Query attributes. set_query_attributes takes pairs of a name and
a value, which every later statement carries until they are set again,
and which SQL reads by QUERY_ATTRIBUTE; it needs query-attributes
unless the pairs are none.
Prepared statements. prepare prepares sql, its dynamic
parameters written ?, under the connection's next statement number.
The Statement holds its number, its parameters, each a Parameter
of a data_type, in the order of their marks, and a query's columns,
none of another statement. execute runs it with values, one for each
mark, as query runs a statement. A statement lasts until it is
deallocated or the session reset, across transactions.
Cursors. open_cursor executes a query and takes at most rows of
its rows, all if 0; fetch takes the next rows; close_cursor closes
the cursor before its end. Each gives a Batch, its rows and whether
more are left; the cursor closes itself when its last row is read. A
cursor's number is 0 unless cursor gives another, which needs
cursors; with it, several cursors of one statement are open at once.
Describing. describe gives a statement's parameters and columns
as the server has them now; describe_cursor an open cursor's columns.
Both need describe.
Columns. A Column holds its name, None of a column with none,
its data_type, the type its values are read by, and not_null. With
declared-types, which connecting asks for, it holds declared too, a
Declared of the type it was declared with: its name, as the
Information Schema's DATA_TYPE gives it, and its type as SQL written.
A data type stands for several declared types, so INTEGER and
NUMERIC(10,0) are both ExactType(precision=10, scale=0);
type_written gives the declared type where the server told it, else
the type its data type says, NUMERIC(10,0).
Transactions. A transaction begins with the session's first
statement and lasts until COMMIT or ROLLBACK. in_transaction says
whether one is active, as the server said after the last request. With
autocommit true each statement is its own transaction, ended with it:
committed as it succeeds, rolled back as it fails. A cursor opened under
autocommit, the server granting fetch-ends, ends its transaction as it
ends: committed by the fetch that reads its last row or by its close,
rolled back by a fetch that fails. A result that sent a large object's
locator keeps its transaction active.
What a completion told. After each statement:
| Member | What | Capability |
|---|---|---|
rows_affected |
the rows it changed, or a query returned; None if not told | row-counts |
last_generated |
the identity values an INSERT generated, as SQL writes them, in its rows' order | generated-values |
server_time |
a Timing: total, queued and locks, in microseconds; None if not told |
row-counts |
commit_position |
the log position of the last commit, kept until another; None if never told | commit-position |
warning |
the SQLSTATE of its warning, of class 01, or None | |
conditions, take_conditions() |
each condition it raised, a Failure; taken, so each is told once |
conditions |
Without conditions a statement's conditions come as notices, with
notices (Connections).
ERRORS
| Condition | SQLCODE, SQLSTATE |
|---|---|
| The statement's failure, as the server gave it | the server's |
query_many, a numbered cursor, describe, describe_cursor or query attributes without their capability, before anything is sent |
-919, 08006 |
| A cursor not open fetched, closed or described; one open opened again, or its statement deallocated or prepared again | the server's, 24000 |
| A statement deallocated, or of no such number | the server's, 26000 |
| A value of a type the protocol cannot carry | -301, 22000 |
| A value its field cannot hold | -301, 22003 or 22008 |
| A reply holding a value the driver cannot take, the rest of the statement's replies read past, the connection kept | -916, TB916; past a limit, -900, 0A000 |
| A reply out of turn, or malformed | -919, 08006, the connection lost |
EXAMPLES
from turborel.client import Trust, connect
conn = connect(("127.0.0.1", 7422), "ANN", Trust())
statement = conn.prepare("VALUES 1, 2, 3")
conn.open_cursor(statement, rows=1, cursor=1)
conn.open_cursor(statement, rows=2, cursor=2)
print(conn.describe_cursor(statement, cursor=1))
print(conn.fetch(statement, cursor=1))
conn.close_cursor(statement, cursor=2)
conn.deallocate(statement)
(Column(name=None, data_type=ExactType(precision=1, scale=0), not_null=False, declared=Declared(name='NUMERIC', written='NUMERIC(1,0)')),)
Batch(rows=[(2,), (3,)], more=False)
ran = conn.query_many("VALUES 1; VALUES 2")
print([outcome.rows for outcome in ran.outcomes], ran.failure)
print(conn.server_time)
[[(1,)], [(2,)]] None
Timing(total=4, queued=0, locks=0)