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()