def fetch_authorization_row(email):
CLIENT_EMAIL = app.config['GOOGLE_CLIENT_EMAIL']
PRIVATE_KEY = app.config['GOOGLE_PRIVATE_KEY']
SCOPE = "https://spreadsheets.google.com/feeds/"
credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY,
SCOPE)
token = OAuth2TokenFromCredentials(credentials)
client = SpreadsheetsClient()
token.authorize(client)
# Load worksheet with auth info
spreadsheet_id = app.config['GOOGLE_SPREADSHEET_ID']
worksheets = client.get_worksheets(spreadsheet_id)
worksheet = worksheets.entry[0]
# worksheet.id.text takes the form of a full url including the spreadsheet, while
# we only need the last part of that
id_parts = worksheet.id.text.split('/')
worksheet_id = id_parts[len(id_parts) - 1]
list_feed = client.get_list_feed(spreadsheet_id, worksheet_id)
rows = [row.to_dict() for row in list_feed.entry]
user_auth_row = None
for row in rows:
if row['email'] == email:
user_auth_row = row
break
return user_auth_row
def fetch_authorization_row(email):
CLIENT_EMAIL = app.config['GOOGLE_CLIENT_EMAIL']
PRIVATE_KEY = app.config['GOOGLE_PRIVATE_KEY']
SCOPE = "https://spreadsheets.google.com/feeds/"
credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY, SCOPE)
token = OAuth2TokenFromCredentials(credentials)
client = SpreadsheetsClient()
token.authorize(client)
# Load worksheet with auth info
spreadsheet_id = app.config['GOOGLE_SPREADSHEET_ID']
worksheets = client.get_worksheets(spreadsheet_id)
worksheet = worksheets.entry[0]
# worksheet.id.text takes the form of a full url including the spreadsheet, while
# we only need the last part of that
id_parts = worksheet.id.text.split('/')
worksheet_id = id_parts[len(id_parts) - 1]
list_feed = client.get_list_feed(spreadsheet_id, worksheet_id)
rows = [row.to_dict() for row in list_feed.entry]
user_auth_row = None
for row in rows:
if row['email'] == email:
user_auth_row = row
break
return user_auth_row
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._client.client_login(
google_credentials.USERNAME,
google_credentials.PASSWORD,
'katholiekkwartetten'
)
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key,
self._google_worksheet_id
).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._client.client_login(google_credentials.USERNAME,
google_credentials.PASSWORD,
'katholiekkwartetten')
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key, self._google_worksheet_id).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key,
self._google_worksheet_id
).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self,
google_spreadsheet_key,
google_worksheet_id,
oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
self.sync_table()
def sync_table(self):
del self.table[:] # table = [] would break the references!
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key, self._google_worksheet_id).entry
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
class Spreadsheet_index():
"""Read a published google spreadsheet into a list of dicts.
Each dict is a row of the spreadsheet.
Repeated properties are represented as a list.
The list is then available as the table attribute."""
def __init__(self, google_spreadsheet_key, google_worksheet_id, oauth_decorator=None):
"""google_spreadsheet_key is the key of the spreadsheet (can be read from the url)."""
self._google_spreadsheet_key = google_spreadsheet_key
self._google_worksheet_id = google_worksheet_id
self._client = SpreadsheetsClient()
# somewhere read that following line help solving "ParseError: no element found: line 1, column 0" error,
# in atom/core.py in atom/core.py line 520, but no
self._client.additional_headers = {
'Authorization': 'Bearer %s' % oauth_decorator.credentials.access_token,
}
self._token = OAuth2TokenFromCredentials(oauth_decorator.credentials)
self._token.authorize(self._client)
self.table = []
# self.sync_table() must be done explicitly!
def sync_table(self):
del self.table[:] # table = [] would break the references!
for attempt in range(5):
try:
self._rows = self._client.get_list_feed(
self._google_spreadsheet_key,
self._google_worksheet_id
).entry
except httplib.HTTPException as e:
logging.info(e.message)
time.sleep(0.5)
continue
else:
break
else:
# we failed all the attempts - deal with the consequences.
logging.info("Retried downloading the spreadsheet data three times in vain.")
return self.table
for row in self._rows:
self.table.append(import_from_spreadsheet(row.to_dict()))
return self.table
def update_fields(self, updates, id_name):
"""
@param id_name: the name of the field by which the rows can be queried
@param updates: a dict of dicts. The index is the value of the id_name field. Each dict contains the fields of
a row that must be updated.
@return: nothing
"""
# update the in-memory table
for entry in self.table:
id = entry[id_name]
if id in updates:
for label in updates[id]:
entry[label] = updates[id][label]
# update the spreadsheet
for entry in self._rows:
id = entry.get_value(id_name)
if id and id in updates:
entry.from_dict(export_for_spreadsheet(updates[id]))
for attempt in range(5):
try:
self._client.update(entry)
except httplib.HTTPException as e:
logging.info(e.message)
time.sleep(0.5)
continue
else:
break
else:
# we failed all the attempts - deal with the consequences.
logging.info("Retried updating the spreadsheet data three times in vain.")
return
logging.info('On index updated row with ' + id_name + '=' + id)
if updates:
self.sync_table()
def add_rows(self, additions, id_name):
"""
@param additions: a dict of dicts. Each dict contains the fields of a row that must be added.
@return:
"""
# update the in-memory table
for id in additions:
self.table.append(additions[id])
# update the spreadsheet
for id in additions:
additions[id][id_name] = id # to make sure this field is also filled in if it wasn't explicitly in the dict!
entry = ListEntry()
entry.from_dict(export_for_spreadsheet(additions[id]))
for attempt in range(5):
try:
self._client.add_list_entry(
entry,
self._google_spreadsheet_key,
google_spreadsheet_first_worksheet_id
)
except httplib.HTTPException as e:
logging.info(e.message)
time.sleep(0.5)
continue
else:
break
else:
# we failed all the attempts - deal with the consequences.
logging.info("Retried adding rows to the spreadsheet data three times in vain.")
return
logging.info('On index added row with id = ' + id)
if additions:
self.sync_table()
class Spreadsheet:
def __init__(self, *args, **kwargs):
if len(kwargs) == 0:
if len(args) == 1:
config_path = args[0]
else:
config_path = CONFIG_PATH
print 'loading config from %s...' % config_path
with open(config_path, 'r') as fp:
self.config = json.load(fp)
else:
self.config = kwargs
if 'oauth2_key_file' not in self.config:
raise Exception('oauth2_key_file is not given')
if 'client_email' not in self.config:
raise Exception('client_email is not given')
self.default_spreadsheet_key = self.config.get(
'default_spreadsheet_key', None)
self.default_worksheet_id = self.config.get('default_worksheet_id',
None)
if _use_old_oauth2client:
# use .pem
key_file_path = self.config['oauth2_key_file']
if key_file_path[-4:] == '.p12':
key_file_path = key_file_path.replace('.p12', '.pem')
with open(key_file_path, 'rb') as f:
private_key = f.read()
self.credentials = SignedJwtAssertionCredentials(
self.config['client_email'],
private_key,
scope=["https://spreadsheets.google.com/feeds"])
else:
# use .p12
self.credentials = ServiceAccountCredentials.from_p12_keyfile(
self.config['client_email'],
self.config['oauth2_key_file'],
scopes=['https://spreadsheets.google.com/feeds'])
self.client = SpreadsheetsClient()
self.auth_token = OAuth2TokenFromCredentials(self.credentials)
self.auth_token.authorize(self.client)
def get_spreadsheet_infos(self):
infos = []
spreadsheets_feed = self.client.get_spreadsheets()
for spreadsheet in spreadsheets_feed.entry:
spreadsheet_key = spreadsheet.id.text.split('/')[-1]
infos.append({
'id': spreadsheet_key,
'title': spreadsheet.title.text,
'sheets': self.get_worksheet_infos(spreadsheet_key)
})
return infos
def get_worksheet_infos(self, spreadsheet_key=None):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
infos = []
worksheets_feed = self.client.get_worksheets(spreadsheet_key)
for worksheet_entry in worksheets_feed.entry:
worksheet_id = worksheet_entry.id.text.split('/')[-1]
worksheet_info = self.get_worksheet_info(spreadsheet_key,
worksheet_id)
infos.append(worksheet_info)
return infos
def get_worksheet_info(self, spreadsheet_key=None, worksheet_id=None):
try:
worksheet_entry = self.client.get_worksheet(
spreadsheet_key, worksheet_id)
if not worksheet_entry:
return None
return _strip_worksheet_entry(worksheet_entry)
except:
return None
def update_entry(self, entry):
if entry:
self.client.update(entry)
def iter_rows(self, spreadsheet_key=None, worksheet_id=None):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
list_feed = self.client.get_list_feed(spreadsheet_key, worksheet_id)
for entry in list_feed.entry:
yield entry.to_dict()
def add_header(self, spreadsheet_key=None, worksheet_id=None, header=[]):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
cell_query = CellQuery(min_row=1,
max_row=1,
min_col=1,
max_col=len(header),
return_empty=True)
cells = self.client.GetCells(spreadsheet_key,
worksheet_id,
q=cell_query)
for i, name in enumerate(header):
cell_entry = cells.entry[i]
cell_entry.cell.input_value = name
self.client.update(cell_entry) # This is the
# add_list_entry
def add_row(self, spreadsheet_key=None, worksheet_id=None, values={}):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
worksheet_id = worksheet_id or self.default_worksheet_id
if worksheet_id is None:
raise Exception('sheet_id is not given')
if len(values) == 0:
return
list_entry = ListEntry()
list_entry.from_dict(
{key: _convert(value)
for key, value in values.iteritems()})
self.client.add_list_entry(list_entry, spreadsheet_key, worksheet_id)
def add_worksheet(self,
spreadsheet_key=None,
title=None,
rows=1,
cols=1): # worksheet_id, values):
spreadsheet_key = spreadsheet_key or self.default_spreadsheet_key
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
if not title:
raise Exception('title is not given')
worksheet_entry = self.client.add_worksheet(spreadsheet_key, title,
rows, cols)
return _strip_worksheet_entry(worksheet_entry)
def delete_worksheet(self, spreadsheet_key, worksheet_id):
if spreadsheet_key is None:
raise Exception('spreadsheet_key is not given')
if worksheet_id is None:
raise Exception('worksheet_id is not given')
worksheet_entry = self.client.get_worksheet(spreadsheet_key,
worksheet_id)
self.client.delete(worksheet_entry)
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
