shillelagh.adapters.api.gsheets package

Subpackages

Submodules

shillelagh.adapters.api.gsheets.adapter module

shillelagh.adapters.api.gsheets.exceptions module

Exceptions specific to the Google Sheets adapter.

exception shillelagh.adapters.api.gsheets.exceptions.DateParseError[source]

Bases: Error

Raised when a date/time value cannot be parsed from a given pattern.

shillelagh.adapters.api.gsheets.fields module

Custom fields for the GSheets adapter.

class shillelagh.adapters.api.gsheets.fields.GSheetsBoolean(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, bool]

A GSheets boolean.

Booleans in the Google Chart API are return as a string, either “TRUE” of “FALSE”.

db_api_type = 'NUMBER'
format(value: bool | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → bool | None[source]

Convert from a DB type to a native Python type.

Some databases might represent booleans as integers, or timestamps as strings. To convert those values to native Python types we call the parse method in the field associated with the column. Custom adapters can define their own derived fields to handle special formats.

Eg, the Google Sheets API returns dates as strings in its response, using the format “Date(2018,0,1)” for “2018-01-01”. A custom field allows the adapter to simply return the original value, and have it being automatically converted to a datetime.date object.

This is not a staticmethod because some types need extra information in order to parse a value. Eg, GSheets takes into consideration the timezone of the sheet when parsing timestamps.

quote(value: str | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'BOOLEAN'
class shillelagh.adapters.api.gsheets.fields.GSheetsDate(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, date]

A GSheets date.

The Google Chart API returns dates as a string encoded using an ICU pattern. The default format is “M/d/yyyy”, and values look like this:

“9/1/2018”

db_api_type = 'DATETIME'
format(value: date | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → date | None[source]

Convert from a DB type to a native Python type.

Some databases might represent booleans as integers, or timestamps as strings. To convert those values to native Python types we call the parse method in the field associated with the column. Custom adapters can define their own derived fields to handle special formats.

Eg, the Google Sheets API returns dates as strings in its response, using the format “Date(2018,0,1)” for “2018-01-01”. A custom field allows the adapter to simply return the original value, and have it being automatically converted to a datetime.date object.

This is not a staticmethod because some types need extra information in order to parse a value. Eg, GSheets takes into consideration the timezone of the sheet when parsing timestamps.

quote(value: str | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'DATE'
class shillelagh.adapters.api.gsheets.fields.GSheetsDateTime(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, datetime]

A GSheets timestamp.

The Google Chart API returns timestamps as a string encoded using an ICU pattern. The default format is “M/d/yyyy H:mm:ss”, and values look like this:

“9/1/2018 0:00:00”

There are no timezones; instead, there is a global timezone for the whole spreadsheet. The timezone can only be read if the user has set their credentials, since the Google Sheets API used to read metadata about the sheet requires authentication.

When the timezone is present and read successfully all timestamps are converted to it, both when fetching data as well as when inserting rows.

db_api_type = 'DATETIME'
format(value: datetime | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → datetime | None[source]

Convert from a DB type to a native Python type.

Some databases might represent booleans as integers, or timestamps as strings. To convert those values to native Python types we call the parse method in the field associated with the column. Custom adapters can define their own derived fields to handle special formats.

Eg, the Google Sheets API returns dates as strings in its response, using the format “Date(2018,0,1)” for “2018-01-01”. A custom field allows the adapter to simply return the original value, and have it being automatically converted to a datetime.date object.

This is not a staticmethod because some types need extra information in order to parse a value. Eg, GSheets takes into consideration the timezone of the sheet when parsing timestamps.

quote(value: str | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'TIMESTAMP'
class shillelagh.adapters.api.gsheets.fields.GSheetsDuration(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, timedelta]

A GSheets duration.

db_api_type = 'DATETIME'
format(value: timedelta | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → timedelta | None[source]

Convert from a DB type to a native Python type.

Some databases might represent booleans as integers, or timestamps as strings. To convert those values to native Python types we call the parse method in the field associated with the column. Custom adapters can define their own derived fields to handle special formats.

Eg, the Google Sheets API returns dates as strings in its response, using the format “Date(2018,0,1)” for “2018-01-01”. A custom field allows the adapter to simply return the original value, and have it being automatically converted to a datetime.date object.

This is not a staticmethod because some types need extra information in order to parse a value. Eg, GSheets takes into consideration the timezone of the sheet when parsing timestamps.

quote(value: str | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'DURATION'
class shillelagh.adapters.api.gsheets.fields.GSheetsField(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: Field[Internal, External]

A base class for GSheets fields.

pattern: str | None
pattern_substitutions = {'M/d/yyyy': 'm/d/yyyy', 'M/d/yyyy H:mm:ss': 'm/d/yyyy h:mm:ss'}
class shillelagh.adapters.api.gsheets.fields.GSheetsNumber(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, float]

A GSheets number.

The Google Chart/Sheets APIs return “numbers” only, encoded as strings.

db_api_type = 'NUMBER'
format(value: float | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → float | None[source]

Convert from a DB type to a native Python type.

Some databases might represent booleans as integers, or timestamps as strings. To convert those values to native Python types we call the parse method in the field associated with the column. Custom adapters can define their own derived fields to handle special formats.

Eg, the Google Sheets API returns dates as strings in its response, using the format “Date(2018,0,1)” for “2018-01-01”. A custom field allows the adapter to simply return the original value, and have it being automatically converted to a datetime.date object.

This is not a staticmethod because some types need extra information in order to parse a value. Eg, GSheets takes into consideration the timezone of the sheet when parsing timestamps.

quote(value: str | int | float | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'REAL'
class shillelagh.adapters.api.gsheets.fields.GSheetsString(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, str]

A GSheets string.

db_api_type = 'STRING'
format(value: str | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → str | None[source]

Convert from a DB type to a native Python type.

Some databases might represent booleans as integers, or timestamps as strings. To convert those values to native Python types we call the parse method in the field associated with the column. Custom adapters can define their own derived fields to handle special formats.

Eg, the Google Sheets API returns dates as strings in its response, using the format “Date(2018,0,1)” for “2018-01-01”. A custom field allows the adapter to simply return the original value, and have it being automatically converted to a datetime.date object.

This is not a staticmethod because some types need extra information in order to parse a value. Eg, GSheets takes into consideration the timezone of the sheet when parsing timestamps.

quote(value: str | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'TEXT'
class shillelagh.adapters.api.gsheets.fields.GSheetsTime(filters: list[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False, pattern: str | None = None, timezone: tzinfo | None = None)[source]

Bases: GSheetsField[str, time]

A GSheets time.

The Google Chart API returns times as a string encoded using an ICU pattern. The default format is “h:mm:ss am/pm”, and values look like this:

“5:00:00 PM”

db_api_type = 'DATETIME'
format(value: time | None) → str[source]

Convert from a native Python type to a DB type.

This should be the opposite of parse.

parse(value: str | None) → time | None[source]

Parse time of day as returned from the Google Chart API.

quote(value: str | None) → str[source]

Quote values.

This method is used by some adapters to build a SQL expression. Eg, GSheets represents dates (and other time related types) with the prefix “date”:

SELECT A, B WHERE C = date '2018-01-01'

In order to handle that, the adapter defines its own time fields with custom quote methods.

This is only needed for adapters that use the build_sql helper function, where SQL is generated manually and sent to an API endpoint.

type = 'TIME'

shillelagh.adapters.api.gsheets.lib module

shillelagh.adapters.api.gsheets.types module

Types for the GSheets adapter.

class shillelagh.adapters.api.gsheets.types.SyncMode(value)[source]

Bases: Enum

Different synchronization modes for GSheets.

There are 3 different synchronization modes in which the adapter can operate. Each one has different tradeoffs.

The BIDIRECTIONAL mode is useful for pushing small changes, and working interactively with the spreadsheets. All changes are pushed immediately to the sheet, and the sheet is fully downloaded before every UPDATE or DELETE.

The BATCH mode is useful for one-off large changes, as the name suggests. Changes are push at once when the adapter closes, and the sheet is only downloaded once, before the first UPDATE or DELETE.

Finally, UNIDIRECTIONAL is a compromise between the other two modes. Changes are pushed immediately, since they’re usually small. The sheet is downloaded in full only once, before the first UPDATE or DELETE.

BATCH = 3
BIDIRECTIONAL = 1
UNIDIRECTIONAL = 2

shillelagh.adapters.api.gsheets.typing module

Custom types for the GSheets adapter.

class shillelagh.adapters.api.gsheets.typing.QueryResults[source]

Bases: TypedDict

Query results from the Google Chart API.

Successful query:

{
    "version": "0.6",
    "reqId": "0",
    "status": "ok",
    "sig": "1453301915",
    "table": {
        "cols": [
            {"id": "A", "label": "country", "type": "string"},
            {"id": "B", "label": "cnt", "type": "number", "pattern": "General"},
        ],
        "rows": [{"c": [{"v": "BR"}, {"v": 1.0, "f": "1"}]}],
        "parsedNumHeaders": 0,
    },
}

Failed:

{
    "version": "0.6",
    "reqId": "0",
    "status": "error",
    "errors": [
        {
            "reason": "invalid_query",
            "message": "INVALID_QUERY",
            "detailed_message": "Invalid query: NO_COLUMN: C",
        }
    ],
}
errors: list[QueryResultsError]
reqId: str
sig: str
status: Literal['ok', 'error']
table: QueryResultsTable
version: str
class shillelagh.adapters.api.gsheets.typing.QueryResultsCell[source]

Bases: TypedDict

A single cell from the Google Chart API.

An example:

{"v": 1.0, "f": 1 }
{"v": "BR"}
f: str
v: Any
class shillelagh.adapters.api.gsheets.typing.QueryResultsColumn[source]

Bases: TypedDict

Metadata describing a column from the Google Chart API.

An example:

{"id": "A", "label": "country", "type": "string"}
{"id": "B", "label": "cnt", "type": "number", "pattern": "General"}
id: str
label: str
pattern: str
type: str
class shillelagh.adapters.api.gsheets.typing.QueryResultsError[source]

Bases: TypedDict

Query errors from the Google Chart API.

The API returns a list of errors like this:

{
    "reason": "invalid_query",
    "message": "INVALID_QUERY",
    "detailed_message": "Invalid query: NO_COLUMN: C",
}
detailed_message: str
message: str
reason: str
class shillelagh.adapters.api.gsheets.typing.QueryResultsRow[source]

Bases: TypedDict

A row of results from the Google Chart API.

An example:

{
    "c": [{"v": "BR"}, {"v": 1.0, "f": "1"}]
}
c: list[QueryResultsCell]
class shillelagh.adapters.api.gsheets.typing.QueryResultsTable[source]

Bases: TypedDict

A table with results from the Google Chart API.

An example:

{
    "cols": [
        {"id": "A", "label": "country", "type": "string"},
        {"id": "B", "label": "cnt", "type": "number", "pattern": "General"},
    ],
    "rows": [{"c": [{"v": "BR"}, {"v": 1.0, "f": "1"}]}],
    "parsedNumHeaders": 0,
}
cols: list[QueryResultsColumn]
parsedNumHeaders: int
rows: list[QueryResultsRow]
class shillelagh.adapters.api.gsheets.typing.UrlArgs[source]

Bases: TypedDict

URL arguments for a sheet.

The sheet will have either a “gid” (sheet ID) or a sheet name under “sheet”. Optionally, it can also have the number of header rows.

gid: int
headers: int
sheet: str

Module contents