def insert_historical_option_data(rows): account = ut.get_azure_account() table_service = None table_name = 'optionSandbox' # ut.TABLE_NAME_OPTIONS try: if config.IS_EMULATED: table_service = TableService(is_emulated=True) else: table_service = TableService( account_name=config.STORAGE_ACCOUNT_NAME, account_key=config.STORAGE_ACCOUNT_KEY) if not table_service.exists(table_name): # create the table try: table_service.create_table(table_name) except Exception as err: print('Error creating table, ' + table_name + 'check if it already exists') lg.error( 'Tried and failed to create the table for the symbols. Program terminating...' ) exit() batch = TableBatch() batchCount = 0 rowCount = 0 for row in rows: option = Entity() callPut = str(row[5]).strip() optionDate = row[7] expiration = row[6] strike = float(row[8]) option.PartitionKey = str(row[0]).strip() # rowkey comprises the concatination of symbols to ensure the key is unique for the symbol. #option.RowKey = callPut + optionDate.strftime('%Y%m%d') + expiration.strftime('%Y%m%d') + str(strike) optionDateYYYY = optionDate[-4:] optionDateMM = optionDate[:2] optionDateDD = optionDate[3:5] optionDateRowKey = optionDateYYYY + optionDateMM + optionDateDD expDateYYYY = expiration[-4:] expDateMM = expiration[:2] expDateDD = expiration[3:5] expDateRowKey = expDateYYYY + expDateMM + expDateDD option.RowKey = callPut + optionDateRowKey + expDateRowKey + str( strike) option.OptionDate = ut.historicalLoadDates[ optionDate] # ut.date_for_azure(optionDate) option.Expiration = ut.historicalLoadDates[ expiration] # ut.date_for_azure(expiration) option.CallPut = callPut option.Strike = strike option.Bid = float(row[10]) option.Ask = float(row[11]) option.LastPrice = float(row[9]) option.Volume = float(row[12]) option.OpenInterest = int(row[13]) option.StockPrice = float(row[1]) batch.insert_or_replace_entity(option) table_service.commit_batch(table_name, batch) except Exception as e: print('Error importing option ' + symbol + '. Error is: ', e) lg.error('Error importing rows to the options table')
def insert_options_azure(optionChain): # receives the optionChain object containing all options for all expiration dates # for the selected symbol. inserts rows into the database options table for # each option. Performs a db INSERT statement. If the row already exists, # the database will generate an invalid key error to prevent the row from # being duplicated in the table. In this case, the error is ignored. # account = ut.get_azure_account() table_service = None table_name = ut.TABLE_NAME_OPTIONS try: if config.IS_EMULATED: table_service = TableService(is_emulated=True) else: table_service = TableService( account_name=config.STORAGE_ACCOUNT_NAME, account_key=config.STORAGE_ACCOUNT_KEY) if not table_service.exists(table_name): # create the table try: table_service.create_table(table_name) except Exception as err: print('Error creating table, ' + table_name + 'check if it already exists') lg.error( 'Tried and failed to create the table for the symbols. Program terminating...' ) exit() batch = TableBatch() batchCount = 0 rowCount = 0 for o in optionChain.options: rowCount += 1 if rowCount > 100: # Azure restricts the batch size to a max of a hundred entries. Since we're at our # limit, we'll commit these and start a new batch table_service.commit_batch(table_name, batch) batch = TableBatch() rowCount = 1 batchCount += 1 option = Entity() option.PartitionKey = o.PartitionKey # rowkey comprises the concatination of symbols to ensure the key is unique for the symbol. # we'll use the callPut, optionDate, expirationDate, and strike price. Dates will be in format yyyymmdd option.RowKey = o.RowKey option.OptionDate = o.optionDate # dates are already cast as Entity Property with an aware date value option.Expiration = o.expiration option.CallPut = o.callPut option.Strike = o.strike option.Bid = o.bid option.Ask = o.ask option.LastPrice = o.lastPrice option.Volume = o.volume option.OpenInterest = o.openInterest option.IV = o.impliedVolatility option.StockPrice = o.stockPrice batch.insert_entity(option) table_service.commit_batch(table_name, batch) except Exception as e: print('Error adding option ' + symbol + '. Error is: ', e) lg.error('Error adding rows to the options table')
def main(req: func.HttpRequest) -> func.HttpResponse: TABLE_NAME = getenv('table_name', 'parking') SCHEMA = { "type": "object", "properties": { "lat": { "type": "number" }, "lon": { "type": "number" }, "country": { "type": "string" }, "city": { "type": "string" }, "radius": { "type": "number" }, "time_from": { "type": "number" }, "time_from": { "type": "number" }, }, "required": ["lat", "lon", "country", "city", "time_from", "time_to"] } DEFAULT_RADIUS = 1000 # in meters TOP_RESULTS = 3 def calculate_distance(geo_from: dict, geo_to: dict) -> float: # in meters from math import sin, cos, sqrt, atan2, radians # approximate radius of earth in km R = 6373.0 lat1 = radians(geo_from.get('lat')) lon1 = radians(geo_from.get('lon')) lat2 = radians(geo_to.get('lat')) lon2 = radians(geo_to.get('lon')) dlon = lon2 - lon1 dlat = lat2 - lat1 a = sin(dlat / 2)**2 + cos(lat1) * cos(lat2) * sin(dlon / 2)**2 c = 2 * atan2(sqrt(a), sqrt(1 - a)) distance = 1000 * R * c return round(distance, 2) logging.info('Python HTTP trigger function processed a request.') # REQUIRED ARGS ---------------------------------------------------------------------------------------------------- req_body = req.get_json() validate(instance=req_body, schema=SCHEMA) logging.info(f"Succesfully validated request {req_body}") from_lon = float(req_body.get('lon')) from_lat = float(req_body.get('lat')) time_from = int(req_body.get('time_from')) time_to = int(req_body.get('time_to')) partition_key = req_body.get('country') + '_' + req_body.get('city') request_body_distance = float(req_body.get('radius', DEFAULT_RADIUS)) top_results = req_body.get('top_results', TOP_RESULTS) # ------------------------------------------------------------------------------------------------------------------ result = list() ts = TableService(account_name=getenv('TABLE_SERVICE_ACCOUNT_NAME'), account_key=getenv('TABLE_SERVICE_ACCOUNT_KEY')) if not ts.exists(TABLE_NAME): ts.create_table(TABLE_NAME) logging.info(f"Table {TABLE_NAME} created.") else: logging.info(f"Table {TABLE_NAME} already exists.") try: filter = f"PartitionKey eq '{partition_key}' and free_spots ne 0 and time_from ge {time_from} and time_to le {time_to}" entities = ts.query_entities(TABLE_NAME, filter=filter) logging.info(f"Results obtained {list(entities)}") except Exception: logging.error("Error obtaining entities", exc_info=True) for e in entities: logging.info(f"Entity received {e}") to_lon = e.lon to_lat = e.lat distance = calculate_distance(dict(lat=from_lat, lon=from_lon), dict(lat=to_lat, lon=to_lon)) if distance <= request_body_distance: id = e.id description = e.description g = gm.Client(getenv('GOOGLE_DIRECTIONS_API')) d = g.directions(f'{to_lat}, {to_lon}', f'{from_lat}, {from_lon}', mode="transit") if len(d) > 0: directions = d[0] l = directions['legs'] if len(l) > 0: legs = l[0] duration = legs.get('duration').get('text') steps = legs.get('steps') result.append( dict(id=id, description=description, duration=duration, steps=steps, distance=distance, lat=to_lat, lon=to_lon)) else: pass result = sorted(result, key=lambda k: k['distance']) if len(result) > top_results: body = dumps(result[:min(top_results, len(result))]) else: body = dumps(result) return func.HttpResponse(body=body, status_code=200)
class TableStorage(): def __init__(self, CONNECTION_STRING): """ Constructor. Espera el Connection String del Azure Storage Account. Se obtiene ingresando al recurso de Storage -> Access Keys Parametros: CONNECTION_STRING = El string que incluye el AccountName, AccountKey y el EndPointSuffix """ self.CONNECTION_STRING = CONNECTION_STRING # Separa por partes el string de conexión Config = dict( s.split('=', 1) for s in CONNECTION_STRING.split(';') if s) # Obtiene el nombre de la cuenta de storage y en EndpointSuffix self.AccountName = Config.get('AccountName') self.EndPointSuffix = Config.get('EndpointSuffix') def CreateTableServices(self): """ Inicializa una instancia del Table Services para poder comunicarse con el storage en Azure """ self.TableService = TableService( account_name=self.AccountName, connection_string=self.CONNECTION_STRING, endpoint_suffix=self.EndPointSuffix) def createTable(self, TableName): """ Revisa si la tabla no exista ya y la crea. De lo contrario, avisa que ya existe. Paramentros: TableName = Nombre de la tabla que se quiere crear """ print('\nCreate a table with name - ' + TableName) if (self.TableService.exists(TableName) != True): self.TableService.create_table(TableName) print("Table created succesfully!") else: print('Error creating table, ' + TableName + ' check if it already exists') def insertEntity(self, TableName, Entity): """ Se inserta una entidad a la tabla especificada. Paramentros: TableName = Nombre de la tabla que se quiere crear Entity = El objecto con la entidad que se quiere agregar """ print('\nInserting a new entity into table - ' + TableName) self.TableService.insert_or_merge_entity(TableName, Entity) print('Successfully inserted the new entity') def getEntity(self, TableName, PartitionKey, RowKey): """ Traerse la entidad completa en base a la Partition Key y Row Key. Regresa un objeto como tal, no hay que hacer json.loads() Paramentros: TableName = Nombre de la tabla que se quiere crear PartitionKey = String con la partition key de la entidad deseada RowKey = String con la row key de la entidad deseada """ print('\nGetting entity.') Entity = self.TableService.get_entity(TableName, PartitionKey, RowKey) return Entity def updateEntity(self, TableName, NewEntity): """ Toma el objeto con los datos actualizados y hace update en la table storage. Paramentros: TableName = Nombre de la tabla que se quiere crear NewEntity = El objecto con la entidad que se quiere hacer update """ print('\nUpdating entity. PK: ' + NewEntity.PartitionKey + ' RK: ' + NewEntity.RowKey) self.TableService.update_entity(TableName, NewEntity) def deleteEntity(self, TableName, PartitionKey, RowKey): """ Borrar la entidad que coincida en Partition Key y Row Key Paramentros: TableName = Nombre de la tabla que se quiere crear PartitionKey = String con la partition key de la entidad RowKey = String con la row key de la entidad """ print('\nDeleting entity') self.TableService.delete_entity(TableName, PartitionKey, RowKey) def deleteTable(self, TableName): """ Revisa si la tabla existe y la borra, en caso contrario solo avisa que no existe. Paramentros: TableName = Nombre de la tabla que se quiere borrar """ print('\nDeleting the table.') if (self.TableService.exists(TableName)): self.TableService.delete_table(TableName) print('Successfully deleted the table') else: print('The table does not exists')
import requests import time import random import string import json import threading import asyncio from azure.servicebus import ServiceBusService, Message, Queue from azure.storage.table import TableService, Entity table_service = TableService(account_name='gregseon4e059a98c11c',\ account_key='yE7Kuy0xVxUDR+wHGoWPjSpOhFO9WLd9b+t3+RI9C8tuBNbuLwEtWSQGERiO7LJRE1cFTGB0/TT4+CYGhtMfww==') if not table_service.exists('Transactions'): table_service.create_table('Transactions') bus_service = ServiceBusService(service_namespace='gregseon4e059a98c11c',\ shared_access_key_name='RootManageSharedAccessKey',\ shared_access_key_value='d8PrqA7to95t0wUFywAfhNDcbUwvh2sIpiHqvUdbPSQ=') bus_service.create_queue('test4scaling') products = [ "Financial Trap", "Insurance", "Bitcoin", "Timeshares", "Food", "Clothes" ] def enqueue(start, stop): for x in range(start, stop): userid = "A" + str(random.randint(1, 1000)) sellerid = "S" + str(random.randint(1, 1000)) data = {"TransactionID":int(time.time()),"UserId":userid,'SellerID':sellerid,'ProductName':random.choice(products),"SalePrice":random.randint(1000,1000000),\ 'TransactionDate':time.strftime("%Y-%m-%d")}
import time import random import json import asyncio from aiohttp import ClientSession from sastoken import get_auth_token from config import queuename, tablename, failqueue from azure.storage.table import TableService from azure.servicebus import ServiceBusService # make Table if it doesn't exist table_service = TableService(account_name='gregseon4e059a98c11c',\ account_key='yE7Kuy0xVxUDR+wHGoWPjSpOhFO9WLd9b+t3+RI9C8tuBNbuLwEtWSQGERiO7LJRE1cFTGB0/TT4+CYGhtMfww==') if not table_service.exists(tablename): table_service.create_table(tablename) # make queues if they dont exist bus_service = ServiceBusService(service_namespace='gregseon4e059a98c11c',\ shared_access_key_name='RootManageSharedAccessKey',\ shared_access_key_value='d8PrqA7to95t0wUFywAfhNDcbUwvh2sIpiHqvUdbPSQ=') bus_service.create_queue(queuename) bus_service.create_queue(failqueue) #generate token for https comms sas = get_auth_token("gregseon4e059a98c11c", queuename, "RootManageSharedAccessKey", "d8PrqA7to95t0wUFywAfhNDcbUwvh2sIpiHqvUdbPSQ=") sas2 = get_auth_token("gregseon4e059a98c11c", failqueue, "RootManageSharedAccessKey", "d8PrqA7to95t0wUFywAfhNDcbUwvh2sIpiHqvUdbPSQ=")
class PersistentSubscriber(subscriber.AzureSubscriber): __metaclass__ = ABCMeta def __init__(self, tableName, topicName, subscriptionName, ruleName = None, rule = None, table_cred = None): # Call super class constructor subscriber.AzureSubscriber.__init__(self, topicName, subscriptionName, ruleName, rule) # Table Service and operations self.tableName = tableName if table_cred is None: table_cred = azurehook.table_cred self.table = TableService(account_name=table_cred['account_name'], account_key=table_cred['mykey']) if not self.table.exists(tableName): self.table.create_table(tableName) self.dump = False # Specify behavior on message received (from subscription). # Default is insert entity. def onNewMessage(self, dic): entity = self.dictToEntity(dic) # print("INSERT CAMERA IN TABLE") self.table.insert_entity(self.tableName, entity) # Wrapper function for querying the table # Azure limitation: only a maximum of 1000 entities can be retrieved per query # # Reference: # http://stackoverflow.com/questions/28019437/python-querying-all-rows-of-azure-table def queryTable(self, query_string): if not self.table.exists(self.TABLE): raise ValueError('Table %s does not exist', self.TABLE) # hasRows = True marker = None results = [] entities = self.table.query_entities( self.TABLE, query_string, marker = marker, num_results=1000) for entity in entities: results.append(entity) return results # Retrieve all entities from a given partition (i.e. that match a given partitionkey) def retrievePartition(self, partitionKey): return self.queryTable("PartitionKey eq '%s'" % partitionKey); # Flush all entities from a given partition (i.e. that match a given partitionkey) def flushPartition(self, partitionKey): if not self.table.exists(self.tableName): raise ValueError("Given table does not exist") entities = self.retrievePartition(partitionKey) for entity in entities: self.table.delete_entity(self.tableName, entity.PartitionKey, entity.RowKey) # To be implemented by child classes: return an entity given the body # of the message (as a dictionary). @abstractmethod def dictToEntity(self, dic): pass