aisdb.database.dbqry module

class to convert a dictionary of input parameters into SQL code, and generate queries

class aisdb.database.dbqry.DBQuery(*, dbconn, dbpath=None, dbpaths=[], **kwargs)

Bases: UserDict

A database abstraction allowing the creation of SQL code via arguments passed to __init__(). Args are stored as a dictionary (UserDict).

Parameters:

• dbconn (aisdb.database.dbconn.ConnectionType) – database connection object

• callback (function) –

  anonymous function yielding SQL code specifying “WHERE” clauses. common queries are included in aisdb.database.sqlfcn_callbacks, e.g. >>> from aisdb.database.sqlfcn_callbacks import in_timerange_validmmsi >>> callback = in_timerange_validmmsi

  this generates SQL code to apply filtering on columns (mmsi, time), and requires (start, end) as arguments in datetime format.

• limit (int) – Optionally limit the database query to a finite number of rows

• **kwargs (dict) – more arguments that will be supplied to the query function and callback function

Custom SQL queries are supported by modifying the fcn supplied to .gen_qry(), or by supplying a callback function. Alternatively, the database can also be queried directly, see dbconn.py for more info

complete example:

>>> import os
>>> from datetime import datetime
>>> from aisdb import SQLiteDBConn, DBQuery, decode_msgs
>>> from aisdb.database.sqlfcn_callbacks import in_timerange_validmmsi

>>> dbpath = './testdata/test.db'
>>> start, end = datetime(2021, 7, 1), datetime(2021, 7, 7)
>>> filepaths = ['aisdb/tests/testdata/test_data_20210701.csv', 'aisdb/tests/testdata/test_data_20211101.nm4']
>>> with SQLiteDBConn(dbpath) as dbconn:
...     decode_msgs(filepaths=filepaths, dbconn=dbconn, source='TESTING', verbose=False)
...     q = DBQuery(dbconn=dbconn, callback=in_timerange_validmmsi, start=start, end=end)
...     for rows in q.gen_qry():
...         assert dict(rows[0]) == {'mmsi': 204242000, 'time': 1625176725,
...                                  'longitude': -8.93166666667, 'latitude': 41.45,
...                                  'sog': 4.0, 'cog': 176.0}
...         break

check_marinetraffic(trafficDBpath, boundary, retry_404=False)

scrape metadata for vessels in domain from marinetraffic

Parameters:

• trafficDBpath (string) – marinetraffic database path

• boundary (dict) – uses keys xmin, xmax, ymin, and ymax to denote the region of vessels that should be checked. if using aisdb.gis.Domain, the Domain.boundary attribute can be supplied here

create_qry_params()

gen_qry(fcn=<function crawl_dynamic>, reaggregate_static=False, verbose=False)

queries the database using the supplied SQL function.

Parameters:

• self (UserDict) – Dictionary containing keyword arguments

• fcn (function) – Callback function that will generate SQL code using the args stored in self

• reaggregate_static (bool) – If True, the metadata aggregate tables will be regenerated from

• verbose (bool) – Log info to stdout

Yields:

numpy array of rows for each unique MMSI arrays are sorted by MMSI rows are sorted by time