Alpha

These pages are built from turborel-py’s main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Running Queries

A connection runs one statement at a time: a query, which gives columns and rows, or any other statement, which gives its completion. This chapter runs statements with the low-level client, turborel.client; the standard API runs them by its cursors (Cursor).

A statement and its outcome

query runs one statement of SQL text. A query's outcome is a Rowset, its columns and all its rows; another statement's is a Complete, its SQLCODE and SQLSTATE.

from turborel.client import Trust, connect

conn = connect(("127.0.0.1", 7422), "ANN", Trust())
conn.query(
    "CREATE TABLE STAFF (ID INTEGER GENERATED ALWAYS AS IDENTITY,"
    " NAME VARCHAR(20), SALARY DECIMAL(9,2))"
)
done = conn.query(
    "INSERT INTO STAFF (NAME, SALARY) VALUES ('Ada', 5200.00), ('Grace', 6100.50)"
)
print(done.sqlcode, done.sqlstate)
staff = conn.query("SELECT NAME, SALARY FROM STAFF ORDER BY NAME")
print([column.name for column in staff.columns])
print(staff.rows)
0 00000
['NAME', 'SALARY']
[('Ada', Decimal('5200.00')), ('Grace', Decimal('6100.50'))]

A row is a tuple of its columns' values, null as None. A statement that finds no row to change completes with SQLCODE 100 and SQLSTATE 02000; a query that returns none gives a Rowset of no rows.

none = conn.query("UPDATE STAFF SET SALARY = 0 WHERE NAME = 'Nobody'")
print(none.sqlcode, none.sqlstate)
print(conn.query("SELECT NAME FROM STAFF WHERE SALARY > 9000").rows)
100 02000
[]

Each column's data_type is the type the server gave it, and each value is of the Python type Appendix A gives for it: an exact number a decimal.Decimal, or an int of scale 0; a character string a str; a datetime a datetime.date, time or datetime; an interval a turborel.Interval; and so on.

Rows as they come

query_into gives a query's rows to a sink of the program's, a batch at a time as they come, so a large result is never held whole. The sink has two methods: columns, called once before the rows, and rows, called with each batch. query_into returns the number of rows given, or, for a statement not a query, its completion.

class Printed:
    def columns(self, columns):
        print("columns:", [column.name for column in columns])

    def rows(self, rows):
        print("batch of", len(rows))


print(conn.query_into("SELECT NAME FROM STAFF", Printed()))
columns: ['NAME']
batch of 2
2

What a completion tells

After each statement the connection keeps what its completion told.

Member What
rows_affected the rows the statement changed, or a query returned
last_generated the identity values an INSERT generated, as SQL writes them, in its rows' order
server_time the server's time for the statement in microseconds: total, queued, and waiting for locks
commit_position the log position of the last commit
warning the SQLSTATE of the statement's warning, of class 01, or None
conditions each condition the statement raised
conn.query(
    "INSERT INTO STAFF (NAME, SALARY) VALUES ('Edsger', 4800), ('Barbara', 5900)"
)
print(conn.rows_affected, conn.last_generated)
conn.query("COMMIT")
print(conn.commit_position)
conn.query("SELECT CAST(NAME AS CHAR(3)) FROM STAFF")
print(conn.warning, [c.message for c in conn.conditions])
2 ('3', '4')
2083
01004 ['the statement completed with warning: string data, right truncation']

Each is told only where the server granted its capability, as it does by default: row-counts, generated-values, commit-position and conditions. Without conditions, a statement's conditions come as notices, with notices (Errors and Diagnostics).

Several statements at once

query_many sends a text of several statements, separated by semicolons, as one request. The server runs them in turn and stops at the first that fails; the outcome, a Ran, holds each statement's outcome and the failure that stopped the rest.

ran = conn.query_many("VALUES 1; SELECT * FROM NOWHERE; VALUES 3")
print(len(ran.outcomes), ran.failure.sqlstate, ran.failure.message)
1 2A000 table ANN.NOWHERE is not defined

A text of no statement gives one completion.

Query attributes

set_query_attributes gives name and value pairs that every later statement carries, until they are set again; SQL reads them by QUERY_ATTRIBUTE. An application may label its statements so.

conn.set_query_attributes([("request", "r-118")])
print(conn.query("VALUES QUERY_ATTRIBUTE('request')").rows)
conn.set_query_attributes([])
[('r-118',)]

XML and UUID values

An XML value is a turborel.Xml, holding its text; nodes() parses that text into its text and its elements, as xml.etree.ElementTree elements. A document type declaration is refused, so a value cannot declare an entity, and text not well-formed fails with SQLSTATE 2200M. A UUID is a uuid.UUID, made of its 16 octets or of its text.

import uuid

from turborel import Xml

conn.query("CREATE TABLE ORDERS (ID UUID, BODY XML)")
insert = conn.prepare("INSERT INTO ORDERS VALUES (?, ?)")
conn.execute(
    insert,
    (
        uuid.UUID("919108f7-52d1-4320-9bac-f847db4148a8"),
        Xml('<order no="18"><item>jam</item></order>'),
    ),
)
[(order_id, body)] = conn.query("SELECT ID, BODY FROM ORDERS").rows
print(order_id, body.text)
[order] = body.nodes()
print(order.tag, order.get("no"), order.findtext("item"))
919108f7-52d1-4320-9bac-f847db4148a8 <order no="18"><item>jam</item></order>
order 18 jam

A value the server cannot serialize, such as an attribute standing alone, fails the fetch with SQLSTATE 2200W.

See also

Prepared Statements, Transactions, Statements and Cursors.