Database Utility Functions

mtldp.database.utils.create_table(engine, table_name, column_names, column_types, primary_keys=None, not_nullable_columns=None, defaults=None, unique_keys=None, keys=None, execute=True)

Create a mysql or sqlite table

Parameters

  • engine – sqlalchemy.engine.Engine

  • table_name (str) – str, name of the table

  • column_names (list) – list of str

  • column_types (list) – list of sqlalchemy types, ie. sqlalchemy.VARCHAR(32), sqlalchemy.SMALLINT

  • primary_keys (Optional[list]) – list of column names to be primary key

  • not_nullable_columns (Optional[list]) – list of column names that are not nullable

  • defaults (Optional[dict]) – dict, key: column name, value: default value of the column

  • unique_keys (Optional[list]) – list, list of column names to be unique key

  • keys (Optional[dict]) – dict, key: name of the key, value: list of column names to be the in the key

  • execute (bool) – bool, False if the user only wants to get the SQL code, default True

Returns

sqlalchemy.schema.CreateTable, dict. If keys is not specified, the second return value is None

mtldp.database.utils.create_tables(engine, table_dict, if_exists='skip')

Create a dictionary of tables

Parameters

  • engine – sqlalchemy.engine.Engine

  • table_dict (dict) – key: table name, value: sql code {“table1”: “create table …”}

  • if_exists (str) – if there exits such a table, the method that the user wants to choose, default “skip”

Returns

None

mtldp.database.utils.df_to_sql(engine, table_name, df, if_exists='append', update_fields=None, on_conflict=None, execute=True)

Insert DataFrame data to table

Parameters

  • engine – sqlalchemy.engine.Engine

  • table_name – str, name of the table

  • df – pandas.DataFrame

  • if_exists – “append”/”replace”/”update”/”skip”

  • update_fields (Optional[list]) – list of column names to be updated if duplicate

  • on_conflict (Optional[list]) – list of column names to be updated if conflict

  • execute (bool) – bool, False if the user only wants to get the SQL code, default True

Returns

sqlalchemy.dialects.mysql.dml.Insert or sqlalchemy.dialects.sqlite.dml.Insert, list of dict

mtldp.database.utils.query_rectangular_data(engine, table_name, bounding_box, start_time=None, end_time=None, execute=True)

Query data in bounded by given locations and times

Parameters

  • engine – sqlalchemy.engine.Engine

  • table_name – str, name of the table

  • bounding_box – mtldp.mtlmap.BoundingBox

  • start_time – float, timestamp

  • end_time – float, timestamp

  • execute – bool, False if the user only wants to get the SQL code, default True

Returns

sqlalchemy.sql.selectable.Select, pandas.DataFrame or None if execute is False

GPS utilities Time Utilities