Advertisement
Guest User

Untitled

a guest
Feb 17th, 2017
1,251
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. import gspread
  2. from oauth2client.service_account import ServiceAccountCredentials
  3. import pymysql
  4.  
  5.  
  6. def get_client():
  7.     keyfile = 'path_to_keyfile.json'
  8.     scope = ['https://spreadsheets.google.com/feeds',
  9.              'https://www.googleapis.com/auth/drive']
  10.     creds = ServiceAccountCredentials.from_json_keyfile_name(keyfile, scope)
  11.     gc = gspread.authorize(creds)
  12.     return gc
  13.  
  14.  
  15. def update_cells_by_chunk(spreadsheet,
  16.                           worksheet,
  17.                           cells,
  18.                           chunk_size=5000):
  19.  
  20.     ws = get_or_create_worksheet(spreadsheet, worksheet)
  21.  
  22.     cell_count = len(cells)
  23.     print('Updating {} cells...'.format(cell_count))
  24.  
  25.     chunks = [cells[i:(i + chunk_size)]
  26.               for i in range(0, len(cells), chunk_size)]
  27.  
  28.     for c in chunks:
  29.         ws.update_cells(c)
  30.         first_cell = (c[0].row, c[0].col)
  31.         last_cell = (c[-1].row, c[-1].col)
  32.         print('> updated {} to {}'.format(first_cell, last_cell))
  33.  
  34.  
  35. def clear_by_chunks(spreadsheet, worksheet, chunk_size=5000):
  36.     print('Clearing worksheet values...')
  37.     ws = get_or_create_worksheet(spreadsheet, worksheet)
  38.     cells = ws.range(create_range(ws.row_count,
  39.                                   ws.col_count))
  40.     for cell in cells:
  41.         cell.value = ''
  42.     update_cells_by_chunk(spreadsheet,
  43.                           worksheet,
  44.                           cells,
  45.                           chunk_size=chunk_size)
  46.  
  47.  
  48. def get_or_create_worksheet(spreadsheet, worksheet):
  49.     client = get_client()
  50.     ss = client.open(spreadsheet)
  51.     for ws in ss.worksheets():
  52.         if ws.title == worksheet:
  53.             return ws
  54.  
  55.     print('Worksheet {} does not exist, creating...'.format(worksheet))
  56.     return ss.add_worksheet(worksheet, 2500, 26)
  57.  
  58.  
  59. def update_cell_values(spreadsheet, worksheet, headers, results):
  60.     ws = get_or_create_worksheet(spreadsheet, worksheet)
  61.  
  62.     # Create table like object, then flatten list
  63.     cell_values = list(results)
  64.     cell_values.insert(0, headers)
  65.     cell_values = [y for x in cell_values for y in x]
  66.  
  67.     # If the worksheet needs to be resized
  68.     if len(headers) > ws.col_count or len(cell_values) > ws.row_count:
  69.         print('Worksheet size insufficient, resizing...')
  70.         ws.resize(rows=len(results) + 1, cols=len(headers) + 1)
  71.  
  72.     cells = ws.range(create_range(len(results) + 1, len(headers)))
  73.     if len(cells) != len(cell_values):
  74.         print('Aborted: cell to value mismatch')
  75.         print('> cells:', len(cells))
  76.         print('> values:', len(cell_values))
  77.         return
  78.  
  79.     print('Updating cell values...')
  80.     for index, cell in enumerate(cells):
  81.         cell.value = default_value(cell_values[index])
  82.  
  83.     print('Syncing with worksheet...')
  84.     update_cells_by_chunk(spreadsheet, worksheet, cells)
  85.  
  86.  
  87. def create_range(rows, cols):
  88.     start = gspread.utils.rowcol_to_a1(1, 1)
  89.     end = gspread.utils.rowcol_to_a1(rows, cols)
  90.     return start + ':' + end
  91.  
  92.  
  93. def run(query):
  94.     '''
  95.    specific for pymysql, returns header, rows values
  96.     '''
  97.  
  98.      # NOT SAFE FOR PRODUCTION
  99.     cred = {
  100.         'user': 'USERNAME',
  101.         'password': 'PASSWORD'
  102.         'database': 'DATABASE',
  103.         'host': 'HOST'
  104.         'charset': 'utf8'
  105.     }
  106.  
  107.     try:
  108.         conn = pymysql.connect(**cred)
  109.         with conn.cursor() as cursor:
  110.             cursor.execute(query)
  111.             headers = [i[0] for i in cursor.description]
  112.             results = cursor.fetchall()
  113.  
  114.             print('Executed DB query, results = {} rows, {} cols'.format(
  115.                 len(results),
  116.                 len(headers)
  117.             ))
  118.             return headers, results
  119.     finally:
  120.         conn.close()
  121.  
  122.  
  123. def default_value(value):
  124.     if not value:
  125.         return ''
  126.  
  127.     return value
  128.  
  129.  
  130. def update_worksheet_with_query(spreadsheet, worksheet, query, clear=True):
  131.     # Grab the data from the database
  132.     headers, results = run(query)
  133.  
  134.     # Clear the old results
  135.     if clear:
  136.         clear_by_chunks(spreadsheet, worksheet)
  137.  
  138.     # Create the cells and insert the new results
  139.     update_cell_values(spreadsheet, worksheet, headers, results)
Advertisement
Advertisement
Advertisement
RAW Paste Data Copied
Advertisement