def test_copy_json(dbapi): with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: test.upload_to_internal(LOCAL_FILE_JSON, "@~/staged/") test.execute("USE SCHEMA {}".format(CREDS_DICT["schema"])) test.execute( "CREATE OR REPLACE TEMPORARY TABLE locopy_integration_testing (variable VARIANT)" ) test.copy( "locopy_integration_testing", "@~/staged/mock_file.json.gz", file_type="json", copy_options=["PURGE = TRUE"], ) test.execute( "SELECT variable:location:city, variable:price FROM locopy_integration_testing ORDER BY variable" ) results = test.cursor.fetchall() expected = [ ('"Belmont"', '"92567"'), ('"Lexington"', '"75836"'), ('"Winchester"', '"89921"'), ] for i, result in enumerate(results): assert result[0] == expected[i][0] assert result[1] == expected[i][1]
def test_copy(dbapi): with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: test.upload_to_internal(LOCAL_FILE, "@~/staged/") test.execute("USE SCHEMA {}".format(CREDS_DICT["schema"])) test.execute( "CREATE OR REPLACE TEMPORARY TABLE locopy_integration_testing (id INTEGER, variable VARCHAR(20))" ) test.copy( "locopy_integration_testing", "@~/staged/mock_file.txt.gz", copy_options=["PURGE = TRUE"], ) test.execute("SELECT * FROM locopy_integration_testing ORDER BY id") results = test.cursor.fetchall() expected = [ (1, "This iš line 1"), (2, "This is liné 2"), (3, "This is line 3"), (4, "This is lïne 4"), ] for i, result in enumerate(results): assert result[0] == expected[i][0] assert result[1] == expected[i][1]
def test_snowflake_execute_single_rows(dbapi): expected = pd.DataFrame({"field_1": [1], "field_2": [2]}) with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: test.execute("SELECT 1 AS field_1, 2 AS field_2 ") df = test.to_dataframe() assert np.allclose(df["field_1"], expected["field_1"])
def test_insert_dataframe_to_table(dbapi): with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: test.insert_dataframe_to_table(TEST_DF, "test", create=True) test.execute("SELECT a, b, c FROM test ORDER BY a ASC") results = test.cursor.fetchall() test.execute("drop table if exists test") expected = [ (1, "x", pd.to_datetime("2011-01-01").date()), (2, "y", pd.to_datetime("2001-04-02").date()), ] assert len(expected) == len(results) for i, result in enumerate(results): assert result[0] == expected[i][0] assert result[1] == expected[i][1] assert result[2] == expected[i][2] test.insert_dataframe_to_table(TEST_DF_2, "test_2", create=True) test.execute("SELECT col1, col2 FROM test_2 ORDER BY col1 ASC") results = test.cursor.fetchall() test.execute("drop table if exists test_2") expected = [(1, "a"), (2, "b"), (3, "c"), (4, "d"), (5, "e"), (6, "f"), (7, "g")] assert len(expected) == len(results) for i, result in enumerate(results): assert result[0] == expected[i][0] assert result[1] == expected[i][1] from decimal import Decimal TEST_DF_3 = pd.DataFrame({ "a": [1, 2], "b": [pd.to_datetime("2013-01-01"), pd.to_datetime("2019-01-01")], "c": ["1.2", "3.5"], "d": [Decimal(2), Decimal(3)], "e": pd.Series([0, 1], dtype="category"), }) test.insert_dataframe_to_table(TEST_DF_3, "test_3", create=True) test.execute("SELECT a, b, c, d, e FROM test_3 ORDER BY a ASC") results = test.cursor.fetchall() test.execute("drop table if exists test_3") expected = [ (1, pd.to_datetime("2013-01-01"), 1.2, 2, "0"), (2, pd.to_datetime("2019-01-01"), 3.5, 3, "1"), ] assert len(expected) == len(results) for i, result in enumerate(results): for j, item in enumerate(result): assert result[j] == expected[i][j]
def test_to_dataframe(dbapi): with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: test.upload_to_internal(LOCAL_FILE_JSON, "@~/staged/") test.execute("USE SCHEMA {}".format(CREDS_DICT["schema"])) test.execute( "CREATE OR REPLACE TEMPORARY TABLE locopy_integration_testing (variable VARIANT)" ) test.copy( "locopy_integration_testing", "@~/staged/mock_file.json.gz", file_type="json", copy_options=["PURGE = TRUE"], ) # get all test.execute( "SELECT variable:location:city, variable:price FROM locopy_integration_testing ORDER BY variable" ) result = test.to_dataframe() result.columns = [c.lower() for c in result.columns] expected = pd.DataFrame( [ ('"Belmont"', '"92567"'), ('"Lexington"', '"75836"'), ('"Winchester"', '"89921"'), ], columns=["variable:location:city", "variable:price"], ) assert (result["variable:location:city"] == expected["variable:location:city"]).all() assert (result["variable:price"] == expected["variable:price"]).all() # with size of 2 test.execute( "SELECT variable:location:city, variable:price FROM locopy_integration_testing ORDER BY variable" ) result = test.to_dataframe(size=2) result.columns = [c.lower() for c in result.columns] expected = pd.DataFrame( [ ('"Belmont"', '"92567"'), ('"Lexington"', '"75836"'), ], columns=["variable:location:city", "variable:price"], ) assert (result["variable:location:city"] == expected["variable:location:city"]).all() assert (result["variable:price"] == expected["variable:price"]).all() # with non-select query test.execute("DROP table locopy_integration_testing") result = test.to_dataframe() assert result["status"][ 0] == "LOCOPY_INTEGRATION_TESTING successfully dropped."
def test_snowflake_execute_multiple_rows(dbapi): expected = pd.DataFrame({"field_1": [1, 2], "field_2": [1, 2]}) with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: test.execute("SELECT 1 AS field_1, 1 AS field_2 " "UNION " "SELECT 2 AS field_1, 2 AS field_2") df = test.to_dataframe() df.columns = [c.lower() for c in df.columns] assert np.allclose(df["field_1"], expected["field_1"]) assert np.allclose(df["field_2"], expected["field_2"])
def test_upload_download_internal(dbapi): with locopy.Snowflake(dbapi=dbapi, **CREDS_DICT) as test: # delete if exists test.execute("REMOVE @~/staged/mock_file_dl.txt") # test shutil.copy(LOCAL_FILE, LOCAL_FILE_DL) test.upload_to_internal(LOCAL_FILE_DL, "@~/staged/", auto_compress=False) test.execute("LIST @~/staged/mock_file_dl.txt") res = test.cursor.fetchall() assert res[0][0] == "staged/mock_file_dl.txt" test.download_from_internal("@~/staged/mock_file_dl.txt", os.path.dirname(LOCAL_FILE_DL) + os.sep) assert filecmp.cmp(LOCAL_FILE, LOCAL_FILE_DL) # clean up test.execute("REMOVE @~/staged/mock_file_dl.txt") os.remove(LOCAL_FILE_DL)