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
