def gs(ctx, credentials, title, output_path):
"""
Convert a spreadsheet in Google Sheets to a SQLite database file.
CREDENTIALS: OAuth2 Google credentials file.
TITLE: Title of the Google Sheets to convert.
"""
con = create_database(ctx, output_path)
verbosity_level = ctx.obj.get(Context.VERBOSITY_LEVEL)
schema_extractor = get_schema_extractor(con, verbosity_level)
result_counter = ResultCounter()
logger = make_logger("{:s} gs".format(PROGRAM_NAME),
ctx.obj[Context.LOG_LEVEL])
table_creator = TableCreator(logger=logger, dst_con=con)
loader = ptr.GoogleSheetsTableLoader()
loader.source = credentials
loader.title = title
# if typepy.is_null_string(loader.source):
# loader.source = app_config_manager.load().get(
# ConfigKey.GS_CREDENTIALS_FILE_PATH)
try:
for tabledata in loader.load():
logger.debug(u"loaded tabledata: {}".format(
six.text_type(tabledata)))
sqlite_tabledata = ptr.SQLiteTableDataSanitizer(
tabledata).sanitize()
try:
table_creator.create(sqlite_tabledata,
ctx.obj.get(Context.INDEX_LIST))
except (ptr.ValidationError, ptr.InvalidDataError):
result_counter.inc_fail()
logger.info(
get_success_message(
verbosity_level, "google sheets",
schema_extractor.get_table_schema_text(
tabledata.table_name).strip()))
except ptr.OpenError as e:
logger.error(e)
result_counter.inc_fail()
except AttributeError:
logger.error(u"invalid credentials data: path={}".format(credentials))
result_counter.inc_fail()
except (ptr.ValidationError, ptr.InvalidDataError) as e:
logger.error(u"invalid credentials data: path={}, message={}".format(
credentials, str(e)))
result_counter.inc_fail()
write_completion_message(logger, output_path, result_counter)
sys.exit(result_counter.get_return_code())
def convert(self, credentials, title: str) -> None:
logger = self._logger
result_counter = self._result_counter
source_id = self._fetch_next_source_id()
loader = ptr.GoogleSheetsTableLoader()
loader.source = credentials
loader.title = title
# if typepy.is_null_string(loader.source):
# loader.source = app_config_mgr.load().get(
# ConfigKey.GS_CREDENTIALS_FILE_PATH)
try:
for table_data in loader.load():
logger.debug("loaded table_data: {}".format(str(table_data)))
sqlite_tabledata = self.normalize_table(table_data)
source_info = SourceInfo(
base_name=title,
dst_table=sqlite_tabledata.table_name,
format_name="google sheets",
source_id=source_id,
)
try:
self._table_creator.create(
sqlite_tabledata, self._index_list, source_info=source_info
)
SourceInfo.insert(source_info)
except (ptr.ValidationError, ptr.DataError):
result_counter.inc_fail()
except ptr.OpenError as e:
logger.error(msgfy.to_error_message(e))
result_counter.inc_fail()
except (ptr.ValidationError, ptr.DataError) as e:
logger.error(
"invalid credentials data: path={}, message={}".format(credentials, str(e))
)
result_counter.inc_fail()
except ptr.APIError as e:
logger.error(msgfy.to_error_message(e))
result_counter.inc_fail()
except ImportError as e:
logger.error(
"{}: try to install dependencies with 'pip install sqlitebiter[gs]'".format(
msgfy.to_error_message(e)
)
)
except OverflowError as e:
logger.error("{}: {}".format(title, e))
result_counter.inc_fail()
def gs(ctx, credentials, title, output_path):
"""
Convert a spreadsheet in Google Sheets to a SQLite database file.
CREDENTIALS: OAuth2 Google credentials file.
TITLE: Title of the Google Sheets to convert.
"""
con = create_database(ctx, output_path)
verbosity_level = ctx.obj.get(Context.VERBOSITY_LEVEL)
extractor = get_schema_extractor(con, verbosity_level)
result_counter = ResultCounter()
logger = logbook.Logger("sqlitebiter gs")
_setup_logger(logger, ctx.obj[Context.LOG_LEVEL])
loader = ptr.GoogleSheetsTableLoader()
loader.source = credentials
loader.title = title
try:
for tabledata in loader.load():
try:
con.create_table_from_tabledata(tabledata)
result_counter.inc_success()
except (ptr.ValidationError, ptr.InvalidDataError):
result_counter.inc_fail()
logger.info(
get_success_message(
verbosity_level, "google sheets",
extractor.get_table_schema_text(
tabledata.table_name).strip()))
except ptr.OpenError as e:
logger.error(e)
result_counter.inc_fail()
except AttributeError:
logger.error(u"invalid credentials data: path={}".format(credentials))
result_counter.inc_fail()
except (ptr.ValidationError, ptr.InvalidDataError) as e:
logger.error(u"invalid credentials data: path={}, message={}".format(
credentials, str(e)))
result_counter.inc_fail()
write_completion_message(logger, output_path, result_counter)
sys.exit(result_counter.get_return_code())
def main():
credentials_file = "sample-xxxxxxxxxxxx.json"
# create table ---
con = simplesqlite.SimpleSQLite("sample.sqlite", "w")
loader = ptr.GoogleSheetsTableLoader(credentials_file)
loader.title = "samplebook"
for table_data in loader.load():
con.create_table_from_tabledata(table_data)
# output ---
for table_name in con.fetch_table_names():
print("table: " + table_name)
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
print()
#!/usr/bin/env python
# encoding: utf-8
from __future__ import print_function
import pytablereader as ptr
import simplesqlite
credentials_file = "sample-xxxxxxxxxxxx.json"
# create table ---
con = simplesqlite.SimpleSQLite("sample.sqlite", "w")
loader = ptr.GoogleSheetsTableLoader(credentials_file)
loader.title = "samplebook"
for table_data in loader.load():
con.create_table_from_tabledata(table_data)
# output ---
for table_name in con.fetch_table_names():
print("table: " + table_name)
print(con.fetch_attr_names(table_name))
result = con.select(select="*", table_name=table_name)
for record in result.fetchall():
print(record)
print()