def insert_into(cursor, table, payload):
cols = payload['table']['cols']
values = ', '.join('?' for col in cols)
query = 'INSERT INTO "{table}" VALUES ({values})'.format(table=table,
values=values)
rows = convert_rows(cols, payload['table']['rows'])
logger.info(query)
cursor.executemany(query, rows)
def execute(query, headers=0, credentials=None):
try:
parsed_query = parse_sql(query)
except pyparsing.ParseException as e:
raise ProgrammingError(format_moz_error(query, e))
# fetch aliases, since they will be removed by the translator
original_aliases = extract_column_aliases(parsed_query)
# extract URL from the `FROM` clause
from_ = extract_url(query)
baseurl = get_url(from_, headers)
# verify that URL is actually a Google spreadsheet
parsed = parse.urlparse(baseurl)
if not parsed.netloc == 'docs.google.com':
raise InterfaceError('Invalid URL, must be a docs.google.com URL!')
# map between labels and ids, eg, `{ 'country': 'A' }`
column_map = get_column_map(baseurl, credentials)
# preprocess
used_processors = []
for cls in processors:
if cls.match(parsed_query):
processor = cls()
parsed_query = processor.pre_process(parsed_query, column_map)
used_processors.append(processor)
processed_aliases = extract_column_aliases(parsed_query)
# translate colum names to ids and remove aliases
translated_query = translate(parsed_query, column_map)
logger.info('Original query: {}'.format(query))
logger.info('Translated query: {}'.format(translated_query))
# run query
payload = run_query(baseurl, translated_query, credentials)
if payload['status'] == 'error':
raise ProgrammingError(
format_gsheet_error(query, translated_query, payload['errors']))
# postprocess
for processor in used_processors:
payload = processor.post_process(payload, processed_aliases)
# add aliases back
cols = payload['table']['cols']
for alias, col in zip(original_aliases, cols):
if alias is not None:
col['label'] = alias
description = get_description_from_payload(payload)
# convert rows to proper type (datetime, eg)
rows = payload['table']['rows']
results = convert_rows(cols, rows)
return results, description