Importing CSV data

A table of a SQLite model can import CSV data from a file or a URL. These sections cover the table, its sources of CSV data, and how it finds the CSV dialect.

A CSV table

A CSVSqliteTable imports CSV data into a table of the model. An attrs row class gives the table its columns: the table converts each CSV row into the class before it inserts it. So the converters, defaults, factories, and validators of the class apply.

from datetime import datetime
from io import StringIO

import attrs

from composeui.core.sqlitemodel import CSVSqliteTable, SqliteModel, csv_field, define_csv_row, table


@define_csv_row
class Reading:
    recorded: datetime = csv_field(name="Recorded", date_format="%d/%m/%Y %H:%M")
    value: float = csv_field(name="Value", validator=attrs.validators.ge(0))
    station: str = csv_field(name="Station", default="unknown")


class Readings(CSVSqliteTable, row_type=Reading, name="reading"):
    pass


class ReadingModel(SqliteModel):
    readings = table(Readings)


model = ReadingModel()
try:
    text = StringIO("Recorded;Value\n25/10/2026 17:00;12.5\n")
    for count in model.readings.fill(text, delimiter=";", batch_size=1000):
        print(f"Submitted {count} rows")
    source = model.readings.source(headers={"recorded": "Recorded at", "value": "Value"})
    print(f"Source contains {source.count()} rows")
finally:
    model.close()

  • The row class. @define_csv_row applies attrs.define with its default options. It also tells type checkers that csv_field declares a field, not a default value. csv_field(name="Header") gives the CSV header of the field. A row class with @attrs.define also works when the app runs, but a type checker then reads each csv_field as a default value.

  • Type checks with mypy. Turn on the plugin of ComposeUI:

    [tool.mypy]
plugins = ["composeui.mypy"]

    Install mypy yourself: ComposeUI does not import it when the app runs. The plugin gives mypy the CSV fields, as attrs fields, and the types of converters such as str.strip. So mypy checks the arguments of the class as attrs builds them: with their converters, defaults, factories, aliases, and init=False fields.

  • Columns. Each field is a column of the same name. The CSV header of a column is its csv_field name, else its field name: plain attrs fields work too.

    • The types of the fields are str, int, bool, float, bytes, decimal.Decimal, datetime.date, and datetime.datetime, or an Optional of one of them.

    • An Optional field accepts SQL NULL. The other fields are NOT NULL.

    • A default fills a column that the CSV data doesn’t have, not an empty cell.

    • A computed field (init=False) is stored too.

    • The table has no primary key and no index. Use SQLite’s rowid to identify a row, and add the indexes in the table’s create script.

  • A source for a large table. model.readings.source() gives a SqliteSource of the rows, for a SourceTableView.

    • The types of its columns come from the row class, and their headers are the CSV headers.

    • headers={"field": "Label", ...} selects, orders, and labels the columns. An unknown field raises KeyError.

    • The source identifies the rows by SQLite’s hidden row id, and follows the table’s changed signal. When the fields take all three names of the row id (rowid, _rowid_, and oid, in any case), source() raises ValueError.

    • For a query of your own, or a key of your own, build a SqliteSource yourself.

  • Conversion. A field without a converter goes through the cattrs converter of the views. csv_field passes the options of attrs, such as converter, validator, default, and factory. Give a converter for other values, for example to turn “yes” and “no” into booleans. The validators run after the conversion.

  • Dates. date_format reads the text with datetime.strptime. The field gets a date or a datetime, as its annotation says. Without a format, the text is ISO 8601.

    • A field with a date_format must be a date or a datetime. The mypy plugin refuses another type, and the table refuses it when Python defines it.

    • A field has a date_format or a converter, not both.

    • SQLite keeps the dates as ISO 8601 text. A time zone offset that the format reads is kept.

  • Input. fill reads an iterable of lines of text, which you open and close. Open a file with newline="" and its encoding. To import a file, use fill_from_file, which opens and closes it. To import a URL, use fill_from_url. The other keyword arguments, such as delimiter and fieldnames, go to csv.DictReader.

  • Progress and transactions. fill(text, batch_size=100_000, limit=None) follows the rules of insert_batches: it yields the rows sent so far, and commits all the batches together once the loop reads it to the end. limit sets the number of rows to import. fill does not close the input.

  • Invalid rows. An error of conversion or of validation (ValueError or TypeError) becomes a RefusedError, caused by the original error. The whole import rolls back, with the batches before it. In a callback, self.refusing(...) shows the refusal. In a job, let it reach job.failed, and show it in the callback of that signal. An empty or wrong row class is an error of the code, not a refusal.

  • Studies. A CSV table is part of the study, like the other tables. After Open or a reload, build the sources again from the table’s changed signal: the study does not save them with the views. Use the rows that the study holds: do not import them again.

CSV files

fill_from_file(path, ...) imports a CSV file, plain or gzip. The path is a string or a pathlib.Path, absolute or from the current directory. The method tells a gzip file by its content, not by its suffix:

from contextlib import closing

model = ReadingModel()  # the model of the previous example
try:
    with closing(
        model.readings.fill_from_file("readings.csv.gz", sniff=True, batch_size=1000)
    ) as batches:
        for count in batches:
            print(f"Submitted {count} rows")
finally:
    model.close()

  • encoding="utf-8" sets the encoding of the text. batch_size, limit, and the CSV options are those of fill.

  • The method adds the rows to the table, and follows the transaction rules of fill. Closing it early closes the file, and rolls the import back.

  • An error of the file, of the decompression, or of the decoding goes to the caller. A missing file raises FileNotFoundError.

CSV URLs

fill_from_url(url, ...) imports CSV data from an HTTP, HTTPS, or file URL. It tells gzip data by its content, also without a .gz suffix. It reads no other compression:

from contextlib import closing
from pathlib import Path

model = ReadingModel()  # the model of the first example
url = Path("readings.csv.gz").resolve().as_uri()  # or an https:// URL
try:
    with closing(model.readings.fill_from_url(url, delimiter=";", batch_size=1000)) as batches:
        for count in batches:
            print(f"Submitted {count} rows")
finally:
    model.close()

  • encoding="utf-8" sets the encoding of the text. timeout=30.0 is the time, in seconds, that each read of the socket waits, not the time of the whole download. batch_size, limit, and the CSV options are those of fill.

  • The method adds the rows to the table, and follows the transaction rules of fill. Closing it early closes the response, and rolls the import back.

  • It keeps no copy of the download, and imports the rows again into a table that already has them.

To read CSV data from a URL without importing it, use open_csv_url from the same module. It keeps the newlines for the CSV reader, and closes the text and the response at the end of the block:

import csv

from composeui.core.sqlitemodel import open_csv_url

with open_csv_url(url) as text:
    print(next(csv.DictReader(text, delimiter=";")))

  • An error of the network, of the decompression, or of the decoding goes to the caller. An error of conversion or of validation in fill_from_url becomes a RefusedError, as in fill. A URL of another scheme raises ValueError.

  • Give these functions trusted URLs only. They accept every host, follow redirects, and read local files.

A download takes time: run it in a job. The Uber pickups example imports its CSV with fill_from_url, and shows the count of rows in a reusable loading task.

CSV dialect detection

sniff=True makes fill_from_file and fill_from_url find the CSV dialect, such as the delimiter, with csv.Sniffer.

  • The sample. sniff_sample_size=8192 is the number of characters that the sniffer reads, after the decompression and the decoding. For long headers or rows, give a larger sample, for example sniff_sample_size=65536. With sniff=True, a size that is not positive raises ValueError. Without it, the size is ignored.

  • No seek. The import reads the sample again from memory, with its partial lines and its quoted newlines. So an HTTP stream works too. The sample can read past the rows of a batch or of limit, but the import keeps only the rows asked for.

  • Your options win. delimiter, quotechar, and dialect replace what the sniffer finds. Without sniff=True, the import reads commas, or the options that you give.

  • The header. The sniffer does not guess whether the first row is a header: the first row names the columns, unless you give fieldnames.

  • When it fails. When the sniffer finds no dialect, the import raises a RefusedError. Its message says to give a larger sample, or to turn sniffing off and give the CSV options. A larger sample can help, but the sniffer can still guess wrong. For a known format, leave sniffing off and give its options.

  • Errors. An import with sniffing rolls back as one without: on an error, or when the loop stops early.