Tables

PostGreSQL

class tablecrow.tables.postgres.PostGresTable(hostname: str, table_name: str, database: Optional[str] = None, fields: Optional[Dict[str, type]] = None, primary_key: Optional[Union[str, List[str]]] = None, crs: Optional[CRS] = None, username: Optional[str] = None, users: Optional[List[str]] = None, logger: Optional[Logger] = None, **kwargs)

Bases: DatabaseTable

abstraction of a database table

Parameters:

  • resource – URL of database server as hostname:port

  • table_name – name of table in database

  • database – name of database in server

  • fields – dictionary of fields

  • primary_key – primary key field(s)

  • crs – coordinate reference system of table geometries

  • username – username to connect ot database

  • password – password to connect to database

  • users – list of database users / roles to give access

property connected: bool

whether network connection exists to database server

delete_where(where: Union[Mapping[str, Any], str, List[str]])

delete records from the table matching the given query

Parameters:

where – dictionary mapping keys to values, with which to match records

property geometry_fields: Dict[str, type]

local fields with geometry type

insert(records: List[Dict[str, Any]])

insert the list of records into the table

Parameters:

records – dictionary records

property records: List[Dict[str, Any]]

list of records in the table

records_where(where: Union[Mapping[str, Any], str, List[str]]) → List[Dict[str, Any]]

list of records in the table that match the query

Parameters:

where – dictionary mapping keys to values, with which to match records

Returns:

dictionaries of matching records

property remote_fields: Dict[str, type]

fields at remote table

property schema: str

PostGres schema string

tablecrow.tables.postgres.database_has_table(cursor: cursor, table: str) → bool

whether the given table exists within the given PostGreSQL database

Parameters:

  • cursor – psycopg2 cursor

  • table – name of table

Returns:

whether table exists

tablecrow.tables.postgres.database_table_fields(cursor: cursor, table: str) → Dict[str, str]

field names and data types of the given table, within the given PostGreSQL database

Parameters:

  • cursor – psycopg2 cursor

  • table – name of table

Returns:

mapping of column names to the PostGres data type

tablecrow.tables.postgres.database_table_is_inherited(cursor: cursor, table: str) → bool

whether the given PostGreSQL table is inherited

Parameters:

  • cursor – psycopg2 cursor

  • table – name of table

Returns:

whether table is inherited

tablecrow.tables.postgres.database_tables(cursor: Cursor, user_defined: bool = True) → List[str]

list of tables within the given PostGreSQL database

Parameters:

cursor – psycopg2 cursor

Returns:

list of table names

SQLite

class tablecrow.tables.sqlite.SQLiteTable(path: PathLike, table_name: str, fields: Optional[Dict[str, type]] = None, primary_key: Optional[Union[str, List[str]]] = None, crs: Optional[CRS] = None, logger: Optional[Logger] = None)

Bases: DatabaseTable

abstraction of a database table

Parameters:

  • resource – URL of database server as hostname:port

  • table_name – name of table in database

  • database – name of database in server

  • fields – dictionary of fields

  • primary_key – primary key field(s)

  • crs – coordinate reference system of table geometries

  • username – username to connect ot database

  • password – password to connect to database

  • users – list of database users / roles to give access

DEFAULT_PORT = None
property connected: bool

whether network connection exists to database server

delete_where(where: Union[Mapping[str, Any], str, List[str]])

delete records from the table matching the given query

Parameters:

where – dictionary mapping keys to values, with which to match records

property geometry_fields: Dict[str, type]

local fields with geometry type

insert(records: List[Dict[str, Any]])

insert the list of records into the table

Parameters:

records – dictionary records

property records: List[Dict[str, Any]]

list of records in the table

records_where(where: Union[Mapping[str, Any], str, List[str]]) → List[Dict[str, Any]]

list of records in the table that match the query

Parameters:

where – dictionary mapping keys to values, with which to match records

Returns:

dictionaries of matching records

property remote_fields: Dict[str, type]

fields at remote table

property schema: str

SQLite schema string

tablecrow.tables.sqlite.database_has_table(cursor: Cursor, table: str) → bool

whether the given table exists within the given SQLite database

Parameters:

  • cursor – sqlite3 cursor

  • table – name of table

Returns:

whether table exists

tablecrow.tables.sqlite.database_table_fields(cursor: Cursor, table: str) → Dict[str, str]

field names and data types of the given table, within the given SQLite database

Parameters:

  • cursor – sqlite3 cursor

  • table – name of table

Returns:

mapping of column names to the SQLite data type

tablecrow.tables.sqlite.database_tables(cursor: Cursor) → List[str]

list of tables within the given SQLite database

Parameters:

cursor – sqlite3 cursor

Returns:

list of table names

abstract classes and functions

class tablecrow.tables.base.DatabaseTable(resource: str, table_name: str, database: Optional[str] = None, fields: Optional[Dict[str, type]] = None, primary_key: Optional[Union[str, List[str]]] = None, crs: Optional[CRS] = None, username: Optional[str] = None, password: Optional[str] = None, users: Optional[List[str]] = None, logger: Optional[Logger] = None)

Bases: ABC

abstraction of a database table

Parameters:

  • resource – URL of database server as hostname:port

  • table_name – name of table in database

  • database – name of database in server

  • fields – dictionary of fields

  • primary_key – primary key field(s)

  • crs – coordinate reference system of table geometries

  • username – username to connect ot database

  • password – password to connect to database

  • users – list of database users / roles to give access

DEFAULT_PORT

alias of NotImplementedError

FIELD_TYPES

alias of NotImplementedError

property connected: bool

whether network connection exists to database server

abstract delete_where(where: Union[Mapping[str, Any], str, List[str]])

delete records from the table matching the given query

Parameters:

where – dictionary mapping keys to values, with which to match records

property geometry_fields: Dict[str, type]

local fields with geometry type

abstract insert(records: List[Dict[str, Any]])

insert the list of records into the table

Parameters:

records – dictionary records

property records: List[Dict[str, Any]]

list of records in the table

abstract records_where(where: Union[Mapping[str, Any], str, List[str]]) → List[Dict[str, Any]]

list of records in the table that match the query

Parameters:

where – dictionary mapping keys to values, with which to match records

Returns:

dictionaries of matching records

abstract property remote_fields: Dict[str, type]

fields at remote table

abstract property schema: str

SQL schema string

exception tablecrow.tables.base.TableNotFoundError

Bases: FileNotFoundError

errno

POSIX exception code

filename

exception filename

filename2

second exception filename

strerror

exception strerror

with_traceback()

Exception.with_traceback(tb) – set self.__traceback__ to tb and return self.

tablecrow.tables.base.compound_crs(crs_list: List[CRS], key: Optional[str] = None) → CRS

build a compound coordinate reference system from the provided list of constituent CRSs

Parameters:

  • crs_list – list of coordinate reference systems

  • key – name of CRS

Returns:

compound CRS

tablecrow.tables.base.crs_key(crs: CRS) → str
Parameters:

crs – coordinate reference system

Returns:

CRS key

tablecrow.tables.base.flatten_geometry(geometry: BaseGeometry) → BaseGeometry
Parameters:

geometry – Shapely geometry object

Returns:

geometry with Z values removed

tablecrow.tables.base.is_compound_crs(crs: CRS) → bool
Parameters:

crs – coordinate reference system

Returns:

whether the CRS is compound

tablecrow.tables.base.parse_crs(crs: Union[str, int]) → CRS

parse a CRS object from the given well-known text or EPSG code

Parameters:

crs – coordinate reference system; either well-known text or an EPSG code

Returns:

CRS object

tablecrow.tables.base.parse_record_values(record: Dict[str, Any], field_types: Dict[str, type]) → Dict[str, Any]

parse the values in the given record into their respective field types

Parameters:

  • record – dictionary mapping fields to values

  • field_types – dictionary mapping fields to types

Returns:

record with values parsed into their respective types

tablecrow.tables.base.random_open_tcp_port() → int
Returns:

get an open TCP port on the current host

tablecrow.tables.base.split_compound_crs(crs: CRS) → List[CRS]

split the given compound coordinate reference system into its constituent CRS parts

Parameters:

crs – compound coordinate reference system

Returns:

list of CRS parts