def test_lookup(): """Test the lookup function.""" t1 = (('foo', 'bar'), ('a', 1), ('b', 2), ('b', 3)) # lookup one column on another actual = lookup(t1, 'foo', 'bar') expect = {'a': [1], 'b': [2, 3]} eq_(expect, actual) # test default value - tuple of whole row actual = lookup(t1, 'foo') # no value selector expect = {'a': [('a', 1)], 'b': [('b', 2), ('b', 3)]} eq_(expect, actual) t2 = (('foo', 'bar', 'baz'), ('a', 1, True), ('b', 2, False), ('b', 3, True), ('b', 3, False)) # test value selection actual = lookup(t2, 'foo', ('bar', 'baz')) expect = {'a': [(1, True)], 'b': [(2, False), (3, True), (3, False)]} eq_(expect, actual) # test compound key actual = lookup(t2, ('foo', 'bar'), 'baz') expect = {('a', 1): [True], ('b', 2): [False], ('b', 3): [True, False]} eq_(expect, actual)
def test_lookup(): """Test the lookup function.""" t1 = (("foo", "bar"), ("a", 1), ("b", 2), ("b", 3)) # lookup one column on another actual = lookup(t1, "foo", "bar") expect = {"a": [1], "b": [2, 3]} eq_(expect, actual) # test default value - tuple of whole row actual = lookup(t1, "foo") # no value selector expect = {"a": [("a", 1)], "b": [("b", 2), ("b", 3)]} eq_(expect, actual) t2 = (("foo", "bar", "baz"), ("a", 1, True), ("b", 2, False), ("b", 3, True), ("b", 3, False)) # test value selection actual = lookup(t2, "foo", ("bar", "baz")) expect = {"a": [(1, True)], "b": [(2, False), (3, True), (3, False)]} eq_(expect, actual) # test compound key actual = lookup(t2, ("foo", "bar"), "baz") expect = {("a", 1): [True], ("b", 2): [False], ("b", 3): [True, False]} eq_(expect, actual)
def test_lookup(): t1 = (('foo', 'bar'), ('a', 1), ('b', 2), ('b', 3)) # lookup one column on another actual = lookup(t1, 'foo', 'bar') expect = {'a': [1], 'b': [2, 3]} eq_(expect, actual) # test default value - tuple of whole row actual = lookup(t1, 'foo') # no value selector expect = {'a': [('a', 1)], 'b': [('b', 2), ('b', 3)]} eq_(expect, actual) # test default value - key only actual = lookup(cut(t1, 'foo'), 'foo') expect = {'a': [('a',)], 'b': [('b',), ('b',)]} eq_(expect, actual) t2 = (('foo', 'bar', 'baz'), ('a', 1, True), ('b', 2, False), ('b', 3, True), ('b', 3, False)) # test value selection actual = lookup(t2, 'foo', ('bar', 'baz')) expect = {'a': [(1, True)], 'b': [(2, False), (3, True), (3, False)]} eq_(expect, actual) # test compound key actual = lookup(t2, ('foo', 'bar'), 'baz') expect = {('a', 1): [True], ('b', 2): [False], ('b', 3): [True, False]} eq_(expect, actual)
def _restock_level(id, product_type, source_db): """ Since G1 does not have a global setting for low inventory settings, we will need to populate all products with the MMJ inventory settings. """ sql = ("SELECT dispensary_id, grams_hold_at, units_hold_at " "FROM dispensary_details " "WHERE dispensary_id={0}").format(id) data = etl.fromdb(source_db, sql) restock = [] if product_type == 1: level = etl.lookup(data, 'dispensary_id', 'grams_hold_at') else: level = etl.lookup(data, 'dispensary_id', 'units_hold_at') return level[id][0]
def lookup_and_transform(ts_kv_table): """The table has the following structure: +---------------------------------+---------------+---------------+--------+ | entity_id | key | ts | value | +=================================+===============+===============+========+ | 1ea47494dc14d40bd76a73c738b665f | Temperature | 1583010011665 | -1.8 | +---------------------------------+---------------+---------------+--------+ | 1ea47494dc14d40bd76a73c738b665f | WindDirection | 1583010000692 | 227 | +---------------------------------+---------------+---------------+--------+ The output is a dictionary {device_id:table} of tables like that: +--------------+--------------+---------------+ | ts | Temperature | WindDirection | +--------------+--------------+---------------+ |1583010011665 | -1.8 | 230 | +--------------+--------------+---------------+ |1583010000692 | -2.5 | 227 | +--------------+--------------+---------------+ """ lkp = petl.lookup(ts_kv_table, 'entity_id', value=('key', 'ts', 'value')) for entity_id in lkp: tbl = [('key', 'ts', 'value')] + lkp[entity_id] tbl = petl.recast(tbl, variablefield='key', valuefield='value') cut_keys = KEYS_TO_REMOVE & set(petl.fieldnames(tbl)) tbl = petl.cutout(tbl, *cut_keys) tbl = petl.transform.headers.sortheader(tbl) tbl = petl.transform.basics.movefield(tbl, 'ts', 0) lkp[entity_id] = petl.sort(tbl, 'ts') return lkp
def get(self, index, column): ''' Gets an individual entry from the UrbanETL object based on its index. Entry value MUST be unique, else returns the first entry found for that value. Supports compound indices, just pass a tuple for index and column. ''' try: lkp = petl.lookup(self._data, column, strict=True) return list(lkp[str(index)]) except petl.errors.DuplicateKeyError as e: print e
def _wm_integration(id, source_db): """ If menu_item_id exists in menu_item_weedmaps_integrations then shareOnWm is true. """ sql = ("SELECT DISTINCT menu_item_id id " "FROM menu_item_weedmaps_integrations " "WHERE menu_item_id={0}").format(id) data = etl.fromdb(source_db, sql) exists = etl.lookup(data, 'id') if exists[id][0] is not None: return True return False
def _active(id, source_db): """ This exists because the 'active' field is on the dispensary_users table in MMJ. The extract method queries the 'users' table. We have no way to know which user_id to use because our util script only loads from the sources limit 10 when we need to query related table by user_id """ sql = ("SELECT DISTINCT active, user_id " "FROM dispensary_users " "WHERE user_id={0}").format(id) data = etl.fromdb(source_db, sql) try: lookup_active = etl.lookup(data, 'user_id', 'active') if lookup_active[id][0] == 1: return 'ACTIVE' except KeyError: return "INACTIVE"
def _map_uom(category_id, source_db): """ Maps the UOM. This is going to look backwards but it's because on G1 the enum for UOM is: GRAM: 1 EACH: 2 but MMJ uses: UNITS: 1 GRAM: 2 """ sql = ("SELECT DISTINCT measurement, id " "FROM categories " "WHERE id={0}").format(category_id) data = etl.fromdb(source_db, sql) measurement = etl.lookup(data, 'id', 'measurement') if measurement[category_id][0] == 1: return 2 return 1
def _assign_role(id, source_db): """ This exists because the 'access' field is on the dispensary_users table in MMJ. The extract method queries the 'users' table. We have no way to know which user_id to use because our util script only loads from the sources limit 10 when we need to query related table by user_id """ sql = ("SELECT DISTINCT access, user_id " "FROM dispensary_users " "WHERE user_id={0}").format(id) data = etl.fromdb(source_db, sql) try: role = etl.lookup(data, 'user_id', 'access') role_id = role[id][0] if role_id == 1 or role_id == 2: return 'site-admin' elif id == 3: return 'store-manager' else: return 'budtender' except KeyError: return 'budtender' # only gets here if we get a null
from __future__ import division, print_function, absolute_import # lookup() ########## import petl as etl table1 = [['foo', 'bar'], ['a', 1], ['b', 2], ['b', 3]] lkp = etl.lookup(table1, 'foo', 'bar') lkp['a'] lkp['b'] # if no valuespec argument is given, defaults to the whole # row (as a tuple) lkp = etl.lookup(table1, 'foo') lkp['a'] lkp['b'] # compound keys are supported table2 = [['foo', 'bar', 'baz'], ['a', 1, True], ['b', 2, False], ['b', 3, True], ['b', 3, False]] lkp = etl.lookup(table2, ('foo', 'bar'), 'baz') lkp[('a', 1)] lkp[('b', 2)] lkp[('b', 3)] # data can be loaded into an existing dictionary-like # object, including persistent dictionaries created via the
from __future__ import division, print_function, absolute_import # lookup() ########## import petl as etl table1 = [['foo', 'bar'], ['a', 1], ['b', 2], ['b', 3]] lkp = etl.lookup(table1, 'foo', 'bar') lkp['a'] lkp['b'] # if no valuespec argument is given, defaults to the whole # row (as a tuple) lkp = etl.lookup(table1, 'foo') lkp['a'] lkp['b'] # compound keys are supported table2 = [['foo', 'bar', 'baz'], ['a', 1, True], ['b', 2, False], ['b', 3, True], ['b', 3, False]] lkp = etl.lookup(table2, ('foo', 'bar'), 'baz') lkp[('a', 1)] lkp[('b', 2)] lkp[('b', 3)] # data can be loaded into an existing dictionary-like # object, including persistent dictionaries created via the # shelve module import shelve lkp = shelve.open('example.dat', flag='n') lkp = etl.lookup(table1, 'foo', 'bar', lkp) lkp.close() lkp = shelve.open('example.dat', flag='r') lkp['a']