def test_sheet_load_with_schema(self):
_sheet = "no-header"
_range = "A:B"
_tb = "no_header"
sheet = Sheet(os.getenv("GOOGLE_APPLICATION_CREDENTIALS")).sheet_id(
TEST_UID).worksheet(_sheet).range(_range).schema([
bigquery.SchemaField(name="name",
field_type="STRING",
mode="REQUIRED",
description="student name"),
bigquery.SchemaField(name="age",
field_type="STRING",
mode="REQUIRED",
description="student age as string"),
])
self.bq.table(f"{self.dataset_name}.{_tb}")
sheet.bq(self.bq).load(location="EU")
rows = BQ(project=self.project_id).sql(
f"select * from {self.dataset_name}.{_tb}").query()
assert rows.total_rows == 3
def test_invalid_sheet_id(self):
with self.assertRaises(TypeError):
_ = Sheet(SA_PATH).sheet_id(123)
with self.assertRaises(ValueError):
_ = Sheet(SA_PATH).worksheet("132")
with self.assertRaises(ValueError):
_ = Sheet(SA_PATH).sheet_id(SHEET_ID).range("A:C")
with self.assertRaises(TypeError):
_ = Sheet(SA_PATH, sheet_id=SHEET_ID, worksheet="Sheet1", bq="bq")
def test_sheet_load(self):
sheet = Sheet(os.getenv("GOOGLE_APPLICATION_CREDENTIALS")).sheet_id(
TEST_UID).worksheet("data!A1:B4")
self.bq.table(f"{self.dataset_name}.target_table")
sheet.bq(self.bq).load(location="EU")
rows = BQ(project=self.project_id).sql(
f"select * from {self.dataset_name}.target_table").query()
assert rows.total_rows == 3
def test_with_kwargs(self):
bq_project = 'here-is-project-id'
table = "dataset.table"
bq = BQ(bq_project, table=table)
sheet = Sheet(SA_PATH,
sheet_id=SHEET_ID,
worksheet="Sheet1",
range="A:C",
bq=bq)
self.assertEqual(sheet._sheet_id, SHEET_ID)
self.assertEqual(sheet._worksheet, "Sheet1")
self.assertEqual(sheet._range, "A:C")
self.assertEqual(bq._table, table)
def test_sheet_load_exist_exception(self):
_tb = "duplicated_table_name"
sheet = Sheet(os.getenv("GOOGLE_APPLICATION_CREDENTIALS")).sheet_id(
TEST_UID).worksheet("data!A1:B4")
self.bq.table(f"{self.dataset_name}.{_tb}")
# create the table
table = f"{self.dataset_name}.{_tb}"
schema = [
bigquery.SchemaField(name="name",
field_type="STRING",
mode="REQUIRED",
description="student name"),
bigquery.SchemaField(name="age",
field_type="INTEGER",
mode="REQUIRED",
description="student age"),
]
bq = BQ(project=self.project_id).table(table).schema(schema).create()
with pytest.raises(Conflict):
sheet.bq(self.bq).load(location="EU")
def test_twice_range_exception(self):
sheet = Sheet(SA_PATH).sheet_id(SHEET_ID).worksheet("Sheet1!A2:C6")
with self.assertRaises(ValueError):
sheet.range("B3:B8")
def test_call_range_first_exception(self):
sheet = Sheet(SA_PATH).sheet_id(SHEET_ID)
with self.assertRaises(ValueError):
sheet.range("B3:B8")
def test_range_keyword(self):
sheet = Sheet(SA_PATH).sheet_id(SHEET_ID).worksheet("Sheet1").range(
"A:C")
self.assertEqual(sheet._range, "A:C")
def test_worksheet_keyword(self):
sheet = Sheet(SA_PATH).sheet_id(SHEET_ID).worksheet("Sheet1")
self.assertEqual(sheet._worksheet, "Sheet1")
def test_sheet_url(self):
sheet = Sheet(SA_PATH).url(SHEET_URL)
self.assertEqual(sheet._sheet_id, SHEET_ID)
def test_sheet_id_keyword(self):
sheet = Sheet(SA_PATH).sheet_id(SHEET_ID)
self.assertEqual(sheet._sheet_id, SHEET_ID)
def test_init_with_cred(self):
cred = service_account.Credentials.from_service_account_file(
SA_PATH, scopes=SCOPES)
sheet = Sheet(cred)
self.assertEqual(_GOOGLESERVICE, sheet._service.__class__)
def test_init_with_sa_path(self):
sheet = Sheet(SA_PATH)
self.assertEqual(_GOOGLESERVICE, sheet._service.__class__)