shillelagh package¶
Subpackages¶
- shillelagh.adapters package
- Subpackages
- shillelagh.adapters.api package
- Subpackages
- Submodules
- shillelagh.adapters.api.datasette module
- shillelagh.adapters.api.dbt_metricflow module
- shillelagh.adapters.api.generic_json module
- shillelagh.adapters.api.generic_xml module
- shillelagh.adapters.api.github module
- shillelagh.adapters.api.html_table module
- shillelagh.adapters.api.preset module
- shillelagh.adapters.api.s3select module
- shillelagh.adapters.api.socrata module
- shillelagh.adapters.api.system module
- shillelagh.adapters.api.weatherapi module
- Module contents
- shillelagh.adapters.file package
- shillelagh.adapters.memory package
- shillelagh.adapters.api package
- Submodules
- shillelagh.adapters.base module
Adapter
Adapter.close()
Adapter.delete_data()
Adapter.delete_row()
Adapter.drop_table()
Adapter.get_columns()
Adapter.get_cost()
Adapter.get_data()
Adapter.get_metadata()
Adapter.get_rows()
Adapter.insert_data()
Adapter.insert_row()
Adapter.parse_uri()
Adapter.safe
Adapter.supports()
Adapter.supports_limit
Adapter.supports_offset
Adapter.supports_requested_columns
Adapter.update_data()
Adapter.update_row()
- shillelagh.adapters.registry module
- Module contents
- Subpackages
- shillelagh.backends package
Submodules¶
shillelagh.console module¶
shillelagh.exceptions module¶
Exceptions defined in the DB API 2.0 spec.
- exception shillelagh.exceptions.DataError[source]¶
Bases:
DatabaseError
Errors that are due to problems with the processed data.
Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc. It must be a subclass of DatabaseError.
- exception shillelagh.exceptions.DatabaseError[source]¶
Bases:
Error
Errors that are related to the database.
Exception raised for errors that are related to the database. It must be a subclass of Error.
- exception shillelagh.exceptions.Error[source]¶
Bases:
Exception
Base class of all other error exceptions.
Exception that is the base class of all other error exceptions. You can use this to catch all errors with one single except statement. Warnings are not considered errors and thus should not use this class as base. It must be a subclass of the Python StandardError (defined in the module exceptions).
- exception shillelagh.exceptions.IntegrityError[source]¶
Bases:
DatabaseError
Raised when the relational integrity of the database is affected.
Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It must be a subclass of DatabaseError.
- exception shillelagh.exceptions.InterfaceError[source]¶
Bases:
Error
Errors that are related to the database interface.
Exception raised for errors that are related to the database interface rather than the database itself. It must be a subclass of Error.
- exception shillelagh.exceptions.InternalError[source]¶
Bases:
DatabaseError
Raised when the database encounters an internal error.
Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc. It must be a subclass of DatabaseError.
- exception shillelagh.exceptions.NotSupportedError[source]¶
Bases:
DatabaseError
Raised in case a method or database API is not supported.
Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off. It must be a subclass of DatabaseError.
- exception shillelagh.exceptions.OperationalError[source]¶
Bases:
DatabaseError
Errors that are related to the database’s operation.
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of DatabaseError.
- exception shillelagh.exceptions.ProgrammingError[source]¶
Bases:
DatabaseError
Raised for programming errors.
Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. It must be a subclass of DatabaseError.
shillelagh.fields module¶
Fields representing columns of different types and capabilities.
- class shillelagh.fields.Blob(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A blob of bytes.
This is used to represent binary data.
- db_api_type = 'BINARY'¶
- quote(value: bytes | 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 = 'BLOB'¶
- class shillelagh.fields.Boolean(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A boolean.
- db_api_type = 'NUMBER'¶
- quote(value: bool | 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.fields.Date(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A date.
This field is used in adapters that use
datetime.date
as the internal representation of dates.- db_api_type = 'DATETIME'¶
- quote(value: date | 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.fields.DateTime(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
Field
[datetime
,datetime
]A timestamp.
This field is used in adapters that use
datetime.datetime
as the internal representation of timestamps.- db_api_type = 'DATETIME'¶
- quote(value: datetime | 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.fields.Decimal(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
Field
[Decimal
,Decimal
]Decimals.
- db_api_type = 'NUMBER'¶
- quote(value: Decimal | 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 = 'DECIMAL'¶
- class shillelagh.fields.Duration(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
Field
[timedelta
,timedelta
]Shillelagh field used for representing durations as timedelta objects.
- db_api_type = 'DATETIME'¶
- type = 'DURATION'¶
- class shillelagh.fields.FastISODateTime(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
ISODateTime
A faster but not fully compliant ISO timestamp parser.
This uses Python’s native
datetime.datetime.fromisoformat
, which doesn’t support arbitrary ISO 8601 strings. It’s used for serializing and deserializing into SQLite.- 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.
- class shillelagh.fields.Field(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
Generic
[Internal
,External
]Represents a column in a table.
A field is probably the most important concept in Shillelagh. Fields are used to annotate types, indicate which columns are filterable/sortable, and convert data between different format.
Type annotation
The most basic use of field is to indicate the types of columns in a given resource. For example, an adapter that connects to a database with two columns, a string and an integer, could look like this:
class SimpleAdapter(Adapter): string_col = String() integer_col = Integer()
For dynamic resources the columns might be generated dynamically, but the idea is the same:
class DynamicAdapter(Adapter): type_map = { "int": Integer(), "float": Float(), "string": String(), } def get_columns(self) -> Dict[str, Field]: columns = read_columns() return { column.name: type_map[column.type] for column in columns }
Filterable/sortable columns
Most adapters can perform some kind of filtering/sorting on the data, return less data to the backend in order to optimize queries. Adapters indicate this on the fields, eg:
class FilteringAdapter(Adapter): timestamp_col = DateTime( filters=[Equal, Range], order=Order.ANY, exact=True, ) values_col = Float()
The adapter above declares a column called
timestamp_col
that can be filtered using either an equality (== ‘2020-01-01T00:00:00’) or a range (>= ‘2020-01-01T00:00:00’). Because of this declaration the backend will delegate the filtering to the adapter, which will be responsible for translating the filters into API/filesystem calls to fulfill them.Additionally, the timestamp column also declares an order of
ANY
, which means that the adapter can sort the data in any order requested by the backend. Fields can declare a static order (eg,Order.ASCENDING
) or no order at all.Finally, the field also indicates that the filtering is exact, and no post-filtering is needed to be done by the backend. In some cases it’s useful to have adapters perform an initial coarse filtering (say, at the daily level), and have the backend perform the final fine filtering (say, at the second level), to simplify the logic in the adapter.
Data conversion
Fields are also responsible for converting data between different formats, so it can flow through layers. For example, an adapter might return booleans as strings, and these need to be converted back and forth to Python booleans. The
StringBoolean
field should be used in that case, and it will automatically convert the data to the format the adapter understands.Similarly, the APSW backend only accepts types understood by SQLite: ints, floats, strings, and bytes. This means that the backend needs to convert between, eg, native Python booleans and integers. This is also done by using the
parse
andformat
methods from fields (IntBoolean
in this case).When creating new fields, the base class should declare the type of the “internal” representation (used by the adapter) and the “external” representation (native Python types). For example, if we have an adapter that stores numbers as strings we could define a new type:
class StringNumber(Field[str, float]): # Field[internal, external] type = "REAL" db_api_type = "NUMBER" # internal -> external def parse(self, value: Optional[str]) -> Optional[float]: return value if value is None else float(value) # external -> internal def format(self, value: Optional[float]) -> Optional[str]: return value if value is None else str(value)
Then the adapter can declare columns using that field:
class SomeAdapter(Adapter): number_col = StringNumber()
With this, it can simply return rows with the number as a string, without having to explicitly do the conversion:
{"rowid": 0, "number_col": "1.0"}
- db_api_type = 'DBAPIType'¶
- format(value: External | None) Internal | None [source]¶
Convert from a native Python type to a DB type.
This should be the opposite of
parse
.
- parse(value: Internal | None) External | 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: Internal | 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 = ''¶
- class shillelagh.fields.Float(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A float.
- db_api_type = 'NUMBER'¶
- type = 'REAL'¶
- class shillelagh.fields.ISODate(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A date.
This field is used in adapters that use an ISO string as the internal representation of dates. SQLite, for example, has no concept of
datetime.date
objects, so we need to convert between the object and an ISO string when the data flows through SQLite.- db_api_type = 'DATETIME'¶
- format(value: date | None) str | None [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.fields.ISODateTime(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A timestamp.
This field is used in adapters that use an ISO string as the internal representation of dates. SQLite, for example, has no concept of
datetime.datetime
objects, so we need to convert between the object and an ISO string when the data flows through SQLite.- db_api_type = 'DATETIME'¶
- format(value: datetime | None) str | None [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.fields.ISOTime(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A time of the day.
This field is used in adapters that use an ISO string as the internal representation of dates. SQLite, for example, has no concept of
datetime.time
objects, so we need to convert between the object and an ISO string when the data flows through SQLite.- db_api_type = 'DATETIME'¶
- format(value: time | None) str | None [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]¶
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 = 'TIME'¶
- class shillelagh.fields.IntBoolean(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A boolean.
This field is used in adapters that represent booleans as an integer. SQLite, eg, has no boolean type, using 1 and 0 to represent true and false, respectively.
- db_api_type = 'NUMBER'¶
- format(value: bool | None) int | None [source]¶
Convert from a native Python type to a DB type.
This should be the opposite of
parse
.
- parse(value: int | 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: int | 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.fields.Integer(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
An integer.
- db_api_type = 'NUMBER'¶
- type = 'INTEGER'¶
- class shillelagh.fields.Order(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)[source]¶
Bases:
Enum
An enum for different orders a field can have.
- ANY = 'any'¶
- ASCENDING = 'ascending'¶
- DESCENDING = 'descending'¶
- NONE = 'none'¶
- class shillelagh.fields.RowID(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
Integer
Custom field for the row ID.
All Shillelagh adapters return a special column for the row ID. In many cases it’s just an increasing integer, but it’s used for DML in adapters that support it.
- db_api_type = 'ROWID'¶
- class shillelagh.fields.String(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A string.
- db_api_type = 'STRING'¶
- 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.fields.StringBlob(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A blob of bytes.
This field is used in adapters that represent binary data as a string with the hexadecimal representation of the bytes.
- db_api_type = 'BINARY'¶
- format(value: bytes | None) str | None [source]¶
Convert from a native Python type to a DB type.
This should be the opposite of
parse
.
- parse(value: str | None) bytes | 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 = 'BLOB'¶
- class shillelagh.fields.StringBoolean(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A boolean.
This field is used in adapters that represent booleans as strings, eg, “TRUE” and “FALSE”.
- db_api_type = 'NUMBER'¶
- format(value: bool | None) str | None [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.
- static strtobool(val: str) bool [source]¶
Convert a string representation of truth to a boolean. True values are ‘y’, ‘yes’, ‘t’, ‘true’, ‘on’, and ‘1’. False values are ‘n’, ‘no’, ‘f’, ‘false’, ‘off’, and ‘0’. Raises ValueError if ‘val’ is anything else.
- type = 'BOOLEAN'¶
- class shillelagh.fields.StringDate(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
ISODate
A more permissive date format.
- 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.
- class shillelagh.fields.StringDateTime(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
ISODateTime
A more permissive datetime format.
- 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.
- class shillelagh.fields.StringDecimal(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
Decimals as strings.
- db_api_type = 'NUMBER'¶
- format(value: Decimal | None) str | None [source]¶
Convert from a native Python type to a DB type.
This should be the opposite of
parse
.
- parse(value: str | None) Decimal | 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 = 'DECIMAL'¶
- class shillelagh.fields.StringDuration(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A duration.
This field represents durations as a string.
- db_api_type = 'DATETIME'¶
- format(value: timedelta | None) str | None [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.fields.StringInteger(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
Integers stored as strings.
SQLite can’t handle integers bigger than 2**64 (like Parquet’s INT96), so we default to storing them as strings.
- db_api_type = 'NUMBER'¶
- format(value: int | None) str | None [source]¶
Convert from a native Python type to a DB type.
This should be the opposite of
parse
.
- parse(value: str | None) int | 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.
- type = 'INTEGER'¶
- class shillelagh.fields.StringTime(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
Bases:
ISOTime
A more permissive time format.
- parse(value: str | None) time | 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.
- class shillelagh.fields.Time(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
A time of the day.
This field is used in adapters that use
datetime.time
as the internal representation of times of the day.- db_api_type = 'DATETIME'¶
- quote(value: time | 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'¶
- class shillelagh.fields.Unknown(filters: Collection[type[Filter]] | None = None, order: Order = Order.NONE, exact: bool = False)[source]¶
-
An unknown type.
Used in APIs where there’s no information about the column type, and the columns are dynamic. Variables are passed as-is, and quoting is done by the SQLite dialect based on the Python type.
- db_api_type = 'STRING'¶
- quote(value: Any) Any [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'¶
shillelagh.filters module¶
Filters for representing SQL predicates.
- class shillelagh.filters.Endpoint(value: Any, include: bool, side: Side)[source]¶
Bases:
object
One of the two endpoints of a
Range
.Used to compare ranges. Eg, the range
>10
can be represented by:>>> start = Endpoint(10, False, Side.LEFT) >>> end = Endpoint(None, True, Side.RIGHT) >>> print(f'{start},{end}') (10,∞]
The first endpoint represents the value 10 at the left side, in an open interval. The second endpoint represents infinity in this case.
- class shillelagh.filters.Equal(value: Any)[source]¶
Bases:
Filter
Equality comparison.
- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
- class shillelagh.filters.Filter[source]¶
Bases:
object
A filter representing a SQL predicate.
- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
- class shillelagh.filters.Impossible[source]¶
Bases:
Filter
Custom Filter returned when impossible conditions are passed.
- class shillelagh.filters.IsNotNull[source]¶
Bases:
Filter
Filter for
IS NOT NULL
.- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
- class shillelagh.filters.IsNull[source]¶
Bases:
Filter
Filter for
IS NULL
.- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
- class shillelagh.filters.Like(value: Any)[source]¶
Bases:
Filter
Substring searches.
- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
- class shillelagh.filters.NotEqual(value: Any)[source]¶
Bases:
Filter
Inequality comparison.
- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
- class shillelagh.filters.Operator(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)[source]¶
Bases:
Enum
Enum representing support comparisons.
- EQ = '=='¶
- GE = '>='¶
- GT = '>'¶
- IS_NOT_NULL = 'IS NOT NULL'¶
- IS_NULL = 'IS NULL'¶
- LE = '<='¶
- LIKE = 'LIKE'¶
- LIMIT = 'LIMIT'¶
- LT = '<'¶
- NE = '!='¶
- OFFSET = 'OFFSET'¶
- class shillelagh.filters.Range(start: Any | None = None, end: Any | None = None, include_start: bool = False, include_end: bool = False)[source]¶
Bases:
Filter
A range comparison.
This filter represents a range, with an optional start and an optional end. Start and end can be inclusive or exclusive.
Ranges can be combined by adding them:
>>> range1 = Range(start=10) >>> range2 = Range(start=20) >>> print(range1 + range2) >20 >>> range3 = Range(end=40) >>> print(range2 + range3) >20,<40
- classmethod build(operations: set[tuple[Operator, Any]]) Filter [source]¶
Given a set of operations, build a filter:
>>> operations = [(Operator.GT, 10), (Operator.GT, 20)] >>> print(Range.build(operations)) >20
shillelagh.functions module¶
Custom functions available to the SQL backend.
- shillelagh.functions.date_trunc(value: str | None, unit: str) str | None [source]¶
Truncate a datetime to a given unit.
- shillelagh.functions.get_metadata(adapter_kwargs: dict[str, dict[str, Any]], adapters: list[type[Adapter]], uri: str) str [source]¶
Return metadata about a given table.
Returns the name of the adapter that supports the table, as well as any extra metadata provided by the adapter:
sql> SELECT GET_METADATA("https://docs.google.com/spreadsheets/d/1/edit#gid=0"); GET_METADATA("https://docs.google.com/spreadsheets/d/1/edit#gid=0") ------------------------------------------------------------------- { "extra": { "Spreadsheet title": "A title", "Sheet title": "Another title" }, "adapter": "GSheetsAPI" }
- shillelagh.functions.sleep(seconds: int) None [source]¶
Sleep for
n
seconds.This is useful for troubleshooting timeouts:
sql> SELECT sleep(60);
shillelagh.lib module¶
Helper functions for Shillelagh.
- shillelagh.lib.NetworkAPICostModel(download_cost: int, fixed_cost: int = 0)[source]¶
A cost model for adapters with network API calls.
In this case, transferring less data and doing less connections is more efficient.
- class shillelagh.lib.RowIDManager(ranges: list[range])[source]¶
Bases:
object
A row ID manager that tracks insert and deletes.
The
RowIDManager
should be used with an append-only table structure. It assigns a row ID to each row. When a new row is appended it will automatically receive a new ID. And when rows are deleted their ID gets changed to -1 to indicate the deletion.An example:
>>> data = ["zero", "one", "two"] >>> manager = RowIDManager([range(len(data))])
To insert data:
>>> data.append("three") >>> manager.insert() 3 >>> data.append("four") >>> manager.insert(10) # you can specify a row ID 10 >>> for row_id, value in zip(manager, data): ... if row_id != -1: ... print(row_id, value) 0 zero 1 one 2 two 3 three 10 four
To delete data:
>>> manager.delete(data.index("two")) >>> print(data) ['zero', 'one', 'two', 'three', 'four'] >>> for row_id, value in zip(manager, data): ... if row_id != -1: ... print(row_id, value) 0 zero 1 one 3 three 10 four
- shillelagh.lib.SimpleCostModel(rows: int, fixed_cost: int = 0)[source]¶
A simple model for estimating query costs.
The model assumes that each filtering operation is O(n), and each sorting operation is O(n log n), in addition to a fixed cost.
- shillelagh.lib.analyze(data: Iterator[dict[str, Any]]) tuple[int, dict[str, Order], dict[str, type[Field]]] [source]¶
Compute number of rows, order, and types from a stream of rows.
- shillelagh.lib.apply_limit_and_offset(rows: Iterator[T], limit: int | None = None, offset: int | None = None) Iterator[T] [source]¶
Apply limit/offset to a stream of rows.
- shillelagh.lib.best_index_object_available() bool [source]¶
Check if support for best index object is available.
- shillelagh.lib.build_sql(columns: dict[str, Field], bounds: dict[str, Filter], order: list[tuple[str, Literal[Order.ASCENDING, Order.DESCENDING]]], table: str | None = None, column_map: dict[str, str] | None = None, limit: int | None = None, offset: int | None = None, alias: str | None = None) str [source]¶
Build a SQL query.
This is used by adapters which use a simplified SQL dialect to fetch data. For GSheets a column map is required, since the SQL references columns by label (“A”, “B”, etc.) instead of name.
- shillelagh.lib.combine_args_kwargs(func: Callable[[...], Any], *args: Any, **kwargs: Any) tuple[Any, ...] [source]¶
Combine args and kwargs into args.
This is needed because we allow users to pass custom kwargs to adapters, but when creating the virtual table we serialize only args.
- shillelagh.lib.create_namespaced_cache_key(cache_name: str) str [source]¶
Get the cache name with a specific namespace.
This function does nothing. It can be monkeypatched to add a namespace to the cache, if one is needed – eg, when using the library in a multi-tenant environment.
- shillelagh.lib.deserialize(value: str) Any [source]¶
Deserialize adapter arguments.
This function is used by the SQLite backend, in order to deserialize the virtual table definition and instantiate an adapter.
- shillelagh.lib.filter_data(data: Iterator[dict[str, Any]], bounds: dict[str, Filter], order: list[tuple[str, Literal[Order.ASCENDING, Order.DESCENDING]]], limit: int | None = None, offset: int | None = None, requested_columns: set[str] | None = None) Iterator[dict[str, Any]] [source]¶
Apply filtering and sorting to a stream of rows.
This is used mostly as an exercise. It’s probably much more efficient to simply declare fields without any filtering/sorting and let the backend (SQLite, eg) handle it.
- shillelagh.lib.find_adapter(uri: str, adapter_kwargs: dict[str, Any], adapters: list[type[Adapter]]) tuple[type[Adapter], tuple[Any, ...], dict[str, Any]] [source]¶
Find an adapter that handles a given URI.
This is done in 2 passes: first the
supports
method is called withfast=True
. If no adapter returnsTrue
we do a second pass on the plugins that returnedNone
, passingfast=False
so they can do network requests to better inspect the URI.
- shillelagh.lib.flatten(row: dict[str, Any]) dict[str, Any] [source]¶
Function that converts JSON to strings, to flatten rows.
- shillelagh.lib.get_bounds(columns: dict[str, Field], all_bounds: DefaultDict[str, set[tuple[Operator, Any]]]) dict[str, Filter] [source]¶
Combine all filters that apply to each column.
- shillelagh.lib.get_conditions(id_: str, field: Field, filter_: Filter) list[str] [source]¶
Build a SQL condition from a column ID and a filter.
- shillelagh.lib.get_session(request_headers: dict[str, str], cache_name: str, expire_after: timedelta = datetime.timedelta(seconds=180)) CachedSession [source]¶
Return a cached session.
shillelagh.types module¶
DB API 2.0 types for Shillelagh.
- shillelagh.types.Binary(string: str) bytes [source]¶
constructs an object capable of holding a binary (long) string value.
- class shillelagh.types.DBAPIType(name: str)[source]¶
Bases:
object
Constructor for the required DB API 2.0 types.
- shillelagh.types.Date(year: int, month: int, day: int) date [source]¶
Constructs an object holding a date value.
- shillelagh.types.DateFromTicks(ticks: int) date [source]¶
Constructs an object holding a date value from the given ticks value.
Ticks should be in number of seconds since the epoch.
- shillelagh.types.Time(hour: int, minute: int, second: int) time [source]¶
Constructs an object holding a time value.
- shillelagh.types.TimeFromTicks(ticks: int) time [source]¶
Constructs an object holding a time value from the given ticks value.
Ticks should be in number of seconds since the epoch.
shillelagh.typing module¶
Custom types for Shillelagh.
Module contents¶
PyScaffold things.