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.

Copying, Importing and Exporting

Rows go into a table in bulk by a copy, with no reply for each; a file of CSV or tab-separated text goes in by an import; a query's rows come out by an export, as CSV or as the protocol's own frames. This chapter uses the low-level client, turborel.client.

Copying rows in

copy_in(table, columns) begins a copy into the table, as SQL names it, of the columns named, in that order, or all of them. send sends a row, many rows going in one message; finish ends the copy and gives the rows inserted. copy_rows does all three for the rows an iterator gives.

from turborel.client import Trust, connect

conn = connect(("127.0.0.1", 7422), "ANN", Trust())
conn.query("CREATE TABLE PARTS (ID INTEGER PRIMARY KEY, NAME VARCHAR(10))")
conn.query("COMMIT")
with conn.copy_in("PARTS", ("ID", "NAME")) as copy:
    for part, name in [(1, "bolt"), (2, "nut")]:
        copy.send((part, name))
    print(copy.finish())
print(conn.copy_rows("PARTS", (), iter([(3, "gear"), (4, "cam")])))
conn.query("COMMIT")
2
2

A copy is all or nothing. A row the server refuses fails the copy at its end, every row of it undone, and the failure's detail names the row. Within a transaction the copy runs under a savepoint of its own, so a copy that fails undoes its own rows alone; under autocommit it is its own transaction. A copy left neither finished nor abandoned when its with block ends is abandoned. Until a copy ends the connection takes only its rows and ping; another request fails with SQLSTATE TB910.

try:
    conn.copy_rows("PARTS", (), [(5, "pin"), (1, "again")])
except Exception as e:
    print(e.sqlstate, e.diagnostics.detail)
print(conn.query("SELECT COUNT(*) FROM PARTS").rows)
conn.query("COMMIT")
23000 row 2 of the copy
[(4,)]

Importing delimited text

import_rows(conn, source, Import(table)) inserts a source's records into a table. Delimited reads CSV, as RFC 4180 writes it, or tab-separated text, from a binary stream, a record at a time; its Dialect says the delimiter, whether a header names the columns, and the text of a null, an empty field by default. Without a header the fields are the table's columns in order.

import io

from turborel.client import Delimited, Dialect, Import, import_rows

data = io.BytesIO(b"id,name\n10,washer\n11,spring\n")
imported = import_rows(
    conn, Delimited(data, Dialect.csv().with_header()), Import("PARTS")
)
print(imported)
Imported(rows=2, rejected=0)

An import commits its rows a batch at a time, 1000 by default, or all at once with batch=0; it begins only with no transaction active. A string for a character column is assigned to it as INSERT assigns, so one too long is refused, not cut. Where the server grants copy and no column is a large object's, the rows go by copy, a batch to each. A field longer than 64 KiB for a large object column is sent ahead as a large object, by its locator.

A row that fails stops the import, the batch under way rolled back, with an ImportFailure that says where the record began, why, and how many rows were committed and rolled back. With skip=True a failing row is skipped instead, and given to the program.

skipped = []
data = io.BytesIO(b"12,clip\n13,a name too long\n14\n")
print(import_rows(conn, Delimited(data), Import("PARTS", skip=True), skipped.append))
for row in skipped:
    print(row)
Imported(rows=1, rejected=2)
record 2, line 2: SQLCODE -301 (SQLSTATE 22001): a value of 15 characters, not spaces beyond 10, does not fit CHARACTER VARYING(10) [ISO/IEC 9075:1992 §13.8 GR4b iii, §9.2 GR3e ii]; skipped
record 3, line 3: 1 fields, for 2 columns; skipped

A source is any object with the methods headings and record, as Copy, Import and Export describes, so a program imports from a format of its own.

Exporting a query's rows

export(sql, out) runs a query and writes its rows to a binary stream as they come: as CSV, a header of the columns' names, then a record for each row; or, with ExportFormat.NATIVE, as the protocol's own frames, which read_native reads back with every value as it was.

from turborel.client import ExportFormat, read_native

out = io.BytesIO()
print(conn.export("SELECT ID, NAME FROM PARTS WHERE ID < 3 ORDER BY ID", out))
print(out.getvalue())
native = io.BytesIO()
conn.export(
    "SELECT ID, NAME FROM PARTS WHERE ID < 3 ORDER BY ID", native, ExportFormat.NATIVE
)
native.seek(0)
print(read_native(native)[1])
conn.query("COMMIT")
2
b'ID,NAME\r\n1,bolt\r\n2,nut\r\n'
[(1, 'bolt'), (2, 'nut')]

A value is written in CSV as Turborel writes it as text: an approximate number as 5E-1, a binary string in hexadecimal, an array as its literal, and a null as an empty field.

See also

Copy, Import and Export, the reference page; Large Objects.