class SpreadSheetDBConnection(object):
"""
Spreadsheet wrapper class
"""
default_connection = None
def __init__(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
self._username = username
self._password = password
self._spreadsheet_key = spreadsheet_key
self._source = source
self._is_connected = False
self._worksheets = []
self._client = SpreadsheetsClient()
#self._tables = []
def connect(self, username=None, password=None, spreadsheet_key=None, source="SpreadSheetDBConnection"):
"""
make a connection with google api, and load spreadsheet information.
"""
if self._is_connected:
raise SpreadSheetDBError()
if spreadsheet_key:
self._spreadsheet_key = spreadsheet_key
if username:
self._username = username
if password:
self._password = password
if source:
self._source = source
if not self._spreadsheet_key or not self._username or not self._password or not self._source:
raise SpreadSheetDBError()
self._client.client_login(self._username, self._password, source=self._source)
self._is_connected = True
self.select_spreadsheet(self._spreadsheet_key)
def select_spreadsheet(self, spreadsheet_key):
"""
select spreadsheet
"""
if not self._is_connected:
raise SpreadSheetDBError()
self._worksheets = {}
if spreadsheet_key:
self._spreadsheet_key = spreadsheet_key
self._refresh_worksheets_info()
def _refresh_worksheets_info(self):
if not self._is_connected:
raise SpreadSheetDBError()
if not self._spreadsheet_key:
raise SpreadSheetDBError()
worksheets = self._client.get_worksheets(self._spreadsheet_key)
for worksheet in worksheets.entry:
self._worksheets[worksheet.title.text] = {'key': worksheet.get_worksheet_id(), 'row_count': worksheet.row_count.text,
'col_count': worksheet.col_count.text}
def create_list(self, model):
"""
make a new worksheet for model
"""
if not self._is_connected:
raise SpreadSheetDBError()
list_name = getattr(model, "worksheet_name")()
if not list_name:
raise SpreadSheetDBError()
fields = []
for index, value in model.__dict__.iteritems():
if isinstance(value, Field):
if value.field_name:
fields.append(value.field_name)
else:
fields.append(index)
if not list_name in self._worksheets.iterkeys():
worksheet = self._client.add_worksheet(self._spreadsheet_key, list_name, 1, len(fields))
self._worksheets[worksheet.title.text] = {'key': worksheet.get_worksheet_id(), 'row_count': worksheet.row_count.text,
'col_count': worksheet.col_count.text}
cell_feed = gdata.spreadsheets.data.build_batch_cells_update(self._spreadsheet_key, self._worksheets[list_name]['key'])
col = 1
for index in fields:
cell_feed.add_set_cell(1, col, index)
col += 1
self._client.batch(cell_feed, force=True)
def add_entry(self, list_name, fields):
"""
add new entry to worksheet
"""
if not self._is_connected:
raise SpreadSheetDBError()
try:
current_worksheet = self._worksheets[list_name]
except KeyError:
raise SpreadSheetNotExists()
inserting_row = ListEntry()
for key, val in fields.iteritems():
inserting_row.set_value(key, val)
ret = self._client.add_list_entry(inserting_row, self._spreadsheet_key, current_worksheet['key'])
return ret
def update_entry(self, entry, data):
"""
update entry
"""
for key, val in data.iteritems():
entry.set_value(key, val)
self._client.update(entry)
def delete_entry(self, entry):
"""
delete entry
"""
self._client.delete(entry)
def all_entries(self, list_name):
"""
load all entries from worksheet.
"""
list_feed = self._client.get_list_feed(self._spreadsheet_key, self._worksheets[list_name]['key'])
return list_feed.entry
def filtered_entries(self, list_name, sq=None, order_by=None, reverse=None):
"""
load filtered entries
https://developers.google.com/google-apps/spreadsheets/?hl=uk-UA&csw=1#sending_a_structured_query_for_rows
"""
query = ListQuery(order_by=order_by, reverse=reverse, sq=sq)
list_feed = self._client.get_list_feed(self._spreadsheet_key, self._worksheets[list_name]['key'], query=query)
return list_feed.entry
class UserSpreadsheet:
def __init__(self, credentials, sheet_name, Alt, BT, Miners):
self.credentials = credentials
self.gd_client = SpreadsheetsClient()
self.spreadsheet_name = sheet_name
self.Alt = Alt
self.BT = BT
self.Miners=Miners
auth2token = gauth.OAuth2TokenFromCredentials(self.credentials)
auth2token.authorize(self.gd_client)
def create_spreadsheet(self):
from apiclient.discovery import build
service = build('drive', 'v2')
http = self.credentials.authorize(httplib2.Http())
body = {
'mimeType': 'application/vnd.google-apps.spreadsheet',
'title': self.spreadsheet_name,
}
file = service.files().insert(body=body).execute(http=http)
def Run(self):
num_coins = len(self.Alt.currency)
#query for spreadsheet
q = SpreadsheetQuery(title= self.spreadsheet_name,title_exact=True)
try:
self.gd_client.get_spreadsheets(query = q).entry[0].get_spreadsheet_key()
except:
return logging.error("Error: No ExcellCoin SpreadSheet")
self.spreadsheet_key = self.gd_client.get_spreadsheets(query = q).entry[0].get_spreadsheet_key()
#get the first row data to figure out what coins to update.
#if blank update all
cq = CellQuery(range='A1',return_empty=True)
cs = self.gd_client.GetCells(self.spreadsheet_key,'od6',q=cq)
coins = cs.entry[0].cell.input_value
if coins != '':
num_coins = len(coins.split())
cq = CellQuery(range=('R1C1:R%iC3'%(num_coins+13)),return_empty=True)
cs = self.gd_client.GetCells(self.spreadsheet_key,'od6',q=cq)
count = 1
cs.entry[count*3].cell.input_value='%s'%str(datetime.now())
cs.entry[count*3+1].cell.input_value='USD/BTC'
#update the bitcoin to usd rates
count+=1
for i,coin in enumerate(self.BT.currency):
cs.entry[3*count].cell.input_value=self.BT.currency[i]
cs.entry[3*count+1].cell.input_value='%.2f'%self.BT.price[i]
count+=1
#update the Altcoin to BTC rates
count+=1
cs.entry[3*count].cell.input_value='Altcoins'
cs.entry[3*count+1].cell.input_value='Exchange Rate'
cs.entry[3*count+2].cell.input_value='Volume in BTC'
count+=1
if coins == '':
for i,coin in enumerate(self.Alt.currency):
cs.entry[3*count].cell.input_value='%s'%coin
cs.entry[3*count+1].cell.input_value='%.8f'%self.Alt.price[i]
cs.entry[3*count+2].cell.input_value='%.2f'%self.Alt.volume[i]
count+=1
else:
for i,coin in enumerate(coins.split()):
try:
if coin in self.Alt.currency:
index = self.Alt.currency.index(coin)
cs.entry[3*count].cell.input_value=self.Alt.currency[index]
cs.entry[3*count+1].cell.input_value='%.8f'%self.Alt.price[index]
cs.entry[3*count+2].cell.input_value='%.2f'%self.Alt.volume[index]
count+=1
except:
logging.error("Error: unable to update coins%s"%coin)
count+=2
cs.entry[3*count].cell.input_value='MiningPool'
cs.entry[3*count+1].cell.input_value='Workers'
cs.entry[3*count+2].cell.input_value='HashRate'
count+=1
for Miners in self.Miners:
############ Hash Rate ###################
try:
if Miners != 'none':
site = Miners.split(',')[0]
nickname = Miners.split(',')[1]
mining_token = Miners.split(',')[2]
workers, hash_rate = gb.get_stats(site,mining_token)
cs.entry[3*count].cell.input_value=nickname
cs.entry[3*count+1].cell.input_value='%i'%int(workers)
cs.entry[3*count+2].cell.input_value='%i'%int(hash_rate)
count+=1
except:
logging.error("Error: unable to update hashrate %s"%Miners.split(',')[0])
count+=1
#send a batch job to update everything in spreadsheet at once
try:
objData = gdata.spreadsheets.data
batch = objData.BuildBatchCellsUpdate(self.spreadsheet_key, 'od6')
for cell in cs.entry:
batch.add_batch_entry(cell, cell.id.text, batch_id_string=cell.title.text, operation_string='update')
self.gd_client.batch(batch, force=True)
except:
return logging.error("Error: Unable to send batch operation")
