shillelagh.adapters.api.gsheets package¶
Subpackages¶
- shillelagh.adapters.api.gsheets.parsing package
- Submodules
- shillelagh.adapters.api.gsheets.parsing.base module
- shillelagh.adapters.api.gsheets.parsing.date module
- shillelagh.adapters.api.gsheets.parsing.number module
- Module contents
Submodules¶
shillelagh.adapters.api.gsheets.adapter module¶
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_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, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)[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 everyUPDATE
orDELETE
.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 firstUPDATE
orDELETE
.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 firstUPDATE
orDELETE
.- 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]¶
- table: QueryResultsTable¶
- 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"}
- 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"}
- 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", }
- 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"}] }
- 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]¶
- rows: List[QueryResultsRow]¶