def test_write_postgis_in_chunks(self, df_mixed_single_and_multi): """ Tests that writing a LinearRing. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "geomtype_tests" try: write_postgis( df_mixed_single_and_multi, con=engine, name=table, if_exists="replace", chunksize=1, ) # Validate row count sql = "SELECT COUNT(geometry) FROM {table};".format(table=table) row_cnt = engine.execute(sql).fetchone()[0] # Validate geometry type sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format( table=table) geom_type = engine.execute(sql).fetchone()[0] assert row_cnt == 3 assert geom_type.upper() == "LINESTRING" except AssertionError as e: raise e finally: engine.dispose()
def test_write_postgis_in_chunks(self, engine_postgis, df_mixed_single_and_multi): """ Tests writing a LinearRing works. """ engine = engine_postgis table = "geomtype_tests" write_postgis( df_mixed_single_and_multi, con=engine, name=table, if_exists="replace", chunksize=1, ) # Validate row count sql = "SELECT COUNT(geometry) FROM {table};".format(table=table) row_cnt = engine.execute(sql).fetchone()[0] assert row_cnt == 3 # Validate geometry type sql = "SELECT DISTINCT GeometryType(geometry) FROM {table} ORDER BY 1;".format( table=table) res = engine.execute(sql).fetchall() assert res[0][0].upper() == "LINESTRING" assert res[1][0].upper() == "MULTILINESTRING" assert res[2][0].upper() == "POINT"
def test_write_postgis_mixed_geometry_types(self, df_mixed_single_and_multi): """ Tests that writing a mix of single and MultiGeometries is possible. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "geomtype_tests" try: write_postgis(df_mixed_single_and_multi, con=engine, name=table, if_exists="replace") # Validate geometry type sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format( table=table) res = engine.execute(sql).fetchall() geom_type_1 = res[0][0] geom_type_2 = res[1][0] assert geom_type_1.upper() == "LINESTRING", ( "Geometry type should be 'LINESTRING',", "found: {gt}".format(gt=geom_type_1), ) assert geom_type_2.upper() == "MULTILINESTRING", ( "Geometry type should be 'MULTILINESTRING',", "found: {gt}".format(gt=geom_type_1), ) except AssertionError as e: raise e finally: engine.dispose()
def test_write_postgis_linear_ring(self, df_linear_ring): """ Tests that writing a LinearRing. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "geomtype_tests" try: write_postgis(df_linear_ring, con=engine, name=table, if_exists="replace") # Validate geometry type sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format( table=table) geom_type = engine.execute(sql).fetchone()[0] assert geom_type.upper() == "LINESTRING" except AssertionError as e: raise e finally: engine.dispose()
def test_write_postgis_geometry_collection(self, df_geom_collection): """ Tests that writing a mix of different geometry types is possible. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "geomtype_tests" try: write_postgis(df_geom_collection, con=engine, name=table, if_exists="replace") # Validate geometry type sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table};".format( table=table) geom_type = engine.execute(sql).fetchone()[0] sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") assert geom_type.upper() == "GEOMETRYCOLLECTION" assert df.geom_type.unique()[0] == "GeometryCollection" except AssertionError as e: raise e finally: engine.dispose()
def test_write_postgis_append_when_table_exists(self, df_nybb): """ Tests that appending to existing table produces correct results when: if_replace='append'. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "nybb" try: orig_rows, orig_cols = df_nybb.shape write_postgis(df_nybb, con=engine, name=table, if_exists="replace") write_postgis(df_nybb, con=engine, name=table, if_exists="append") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") new_rows, new_cols = df.shape # There should be twice as many rows in the new table assert new_rows == orig_rows * 2, ( "There should be {target} rows,", "found: {current}".format(target=orig_rows * 2, current=new_rows), ) # Number of columns should stay the same assert new_cols == orig_cols, ( "There should be {target} columns,", "found: {current}".format(target=orig_cols, current=new_cols), ) except AssertionError as e: raise e finally: engine.dispose()
def test_write_postgis_append_when_table_exists(self, engine_postgis, df_nybb): """ Tests that appending to existing table produces correct results when: if_replace='append'. """ engine = engine_postgis table = "nybb" orig_rows, orig_cols = df_nybb.shape write_postgis(df_nybb, con=engine, name=table, if_exists="replace") write_postgis(df_nybb, con=engine, name=table, if_exists="append") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") new_rows, new_cols = df.shape # There should be twice as many rows in the new table assert new_rows == orig_rows * 2, ( "There should be {target} rows,", "found: {current}".format(target=orig_rows * 2, current=new_rows), ) # Number of columns should stay the same assert new_cols == orig_cols, ( "There should be {target} columns,", "found: {current}".format(target=orig_cols, current=new_cols), )
def test_write_postgis_3D_geometries(self, engine_postgis, df_3D_geoms): """ Tests writing a geometries with 3 dimensions works. """ engine = engine_postgis table = "geomtype_tests" write_postgis(df_3D_geoms, con=engine, name=table, if_exists="replace") # Check that all geometries have 3 dimensions sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") assert list(df.geometry.has_z) == [True, True, True]
def test_write_postgis_default(self, engine_postgis, df_nybb): """Tests that GeoDataFrame can be written to PostGIS with defaults.""" engine = engine_postgis table = "nybb" # If table exists, delete it before trying to write with defaults drop_table_if_exists(engine, table) # Write to db write_postgis(df_nybb, con=engine, name=table, if_exists="fail") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df)
def test_write_postgis_sqlalchemy_connection(self, engine_postgis, df_nybb): """Tests that GeoDataFrame can be written to PostGIS with defaults.""" with engine_postgis.begin() as con: table = "nybb_con" # If table exists, delete it before trying to write with defaults drop_table_if_exists(con, table) # Write to db write_postgis(df_nybb, con=con, name=table, if_exists="fail") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, con, geom_col="geometry") validate_boro_df(df)
def test_write_postgis_uppercase_tablename(self, engine_postgis, df_nybb): """Tests writing GeoDataFrame to PostGIS with uppercase tablename.""" engine = engine_postgis table = "aTestTable" # If table exists, delete it before trying to write with defaults drop_table_if_exists(engine, table) # Write to db write_postgis(df_nybb, con=engine, name=table, if_exists="fail") # Validate sql = 'SELECT * FROM "{table}";'.format(table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df)
def test_append_with_different_crs(self, engine_postgis, df_nybb): """ Tests that the warning is raised if table CRS differs from frame. """ engine = engine_postgis table = "nybb" write_postgis(df_nybb, con=engine, name=table, if_exists="replace") # Reproject df_nybb2 = df_nybb.to_crs(epsg=4326) # Should raise error when appending with pytest.raises(ValueError, match="CRS of the target table"): write_postgis(df_nybb2, con=engine, name=table, if_exists="append")
def test_row_order(self, engine_postgis, df_nybb): """ Tests that the row order in db table follows the order of the original frame. """ engine = engine_postgis table = "row_order_test" correct_order = df_nybb["BoroCode"].tolist() write_postgis(df_nybb, con=engine, name=table, if_exists="replace") # Check that the row order matches sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") assert df["BoroCode"].tolist() == correct_order
def test_write_postgis_replace_when_table_exists(self, engine_postgis, df_nybb): """ Tests that replacing a table is possible when: if_replace='replace'. """ engine = engine_postgis table = "nybb" # Ensure table exists write_postgis(df_nybb, con=engine, name=table, if_exists="replace") # Overwrite write_postgis(df_nybb, con=engine, name=table, if_exists="replace") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df)
def test_append_before_table_exists(self, engine_postgis, df_nybb): """ Tests that insert works with if_exists='append' when table does not exist yet. """ engine = engine_postgis table = "nybb" # If table exists, delete it before trying to write with defaults drop_table_if_exists(engine, table) write_postgis(df_nybb, con=engine, name=table, if_exists="append") # Check that the row order matches sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df)
def test_write_postgis_fail_when_table_exists(self, engine_postgis, df_nybb): """ Tests that uploading the same table raises error when: if_replace='fail'. """ engine = engine_postgis table = "nybb" # Ensure table exists write_postgis(df_nybb, con=engine, name=table, if_exists="replace") try: write_postgis(df_nybb, con=engine, name=table, if_exists="fail") except ValueError as e: if "already exists" not in str(e): raise e
def test_write_postgis_linear_ring(self, engine_postgis, df_linear_ring): """ Tests that writing a LinearRing. """ engine = engine_postgis table = "geomtype_tests" write_postgis(df_linear_ring, con=engine, name=table, if_exists="replace") # Validate geometry type sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table} ORDER BY 1;".format( table=table ) geom_type = engine.execute(sql).fetchone()[0] assert geom_type.upper() == "LINESTRING"
def test_write_postgis_without_crs(self, engine_postgis, df_nybb): """ Tests that GeoDataFrame can be written to PostGIS without CRS information. """ engine = engine_postgis table = "nybb" # Write to db df_nybb = df_nybb df_nybb.crs = None write_postgis(df_nybb, con=engine, name=table, if_exists="replace") # Validate that srid is -1 target_srid = engine.execute( "SELECT Find_SRID('{schema}', '{table}', '{geom_col}');".format( schema="public", table=table, geom_col="geometry")).fetchone()[0] assert target_srid == 0, "SRID should be 0, found %s" % target_srid
def test_write_postgis_fail_when_table_exists(self, df_nybb): """ Tests that uploading the same table raises error when: if_replace='fail'. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "nybb" try: write_postgis(df_nybb, con=engine, name=table, if_exists="fail") except ValueError as e: if "already exists" in str(e): pass else: raise e finally: engine.dispose()
def test_write_postgis_to_different_schema_when_table_exists( self, engine_postgis, df_nybb): """ Tests writing data to alternative schema. """ engine = engine_postgis table = "nybb" schema_to_use = "test" sql = "CREATE SCHEMA IF NOT EXISTS {schema};".format( schema=schema_to_use) engine.execute(sql) try: write_postgis(df_nybb, con=engine, name=table, if_exists="fail", schema=schema_to_use) # Validate sql = "SELECT * FROM {schema}.{table};".format( schema=schema_to_use, table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df) # Should raise a ValueError when table exists except ValueError: pass # Try with replace flag on write_postgis(df_nybb, con=engine, name=table, if_exists="replace", schema=schema_to_use) # Validate sql = "SELECT * FROM {schema}.{table};".format(schema=schema_to_use, table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df)
def test_write_postgis_replace_when_table_exists(self, df_nybb): """ Tests that replacing a table is possible when: if_replace='replace'. """ engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "nybb" try: write_postgis(df_nybb, con=engine, name=table, if_exists="replace") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df) except ValueError as e: raise e finally: engine.dispose()
def test_write_postgis_default(self, df_nybb): """Tests that GeoDataFrame can be written to PostGIS with defaults.""" engine = connect_engine("test_geopandas") if engine is None: raise pytest.skip() table = "nybb" # If table exists, delete it before trying to write with defaults drop_table_if_exists(engine, table) try: # Write to db write_postgis(df_nybb, con=engine, name=table, if_exists="fail") # Validate sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df) finally: engine.dispose()
def test_write_postgis_to_different_schema(self, engine_postgis, df_nybb): """ Tests writing data to alternative schema. """ engine = engine_postgis table = "nybb" schema_to_use = "test" sql = "CREATE SCHEMA IF NOT EXISTS {schema};".format(schema=schema_to_use) engine.execute(sql) write_postgis( df_nybb, con=engine, name=table, if_exists="replace", schema=schema_to_use ) # Validate sql = "SELECT * FROM {schema}.{table};".format( schema=schema_to_use, table=table ) df = read_postgis(sql, engine, geom_col="geometry") validate_boro_df(df)
def test_write_postgis_mixed_geometry_types(self, engine_postgis, df_mixed_single_and_multi): """ Tests that writing a mix of single and MultiGeometries is possible. """ engine = engine_postgis table = "geomtype_tests" write_postgis(df_mixed_single_and_multi, con=engine, name=table, if_exists="replace") # Validate geometry type sql = "SELECT DISTINCT GeometryType(geometry) FROM {table} ORDER BY 1;".format( table=table) res = engine.execute(sql).fetchall() assert res[0][0].upper() == "LINESTRING" assert res[1][0].upper() == "MULTILINESTRING" assert res[2][0].upper() == "POINT"
def test_write_postgis_geometry_collection( self, engine_postgis, df_geom_collection ): """ Tests that writing a mix of different geometry types is possible. """ engine = engine_postgis table = "geomtype_tests" write_postgis(df_geom_collection, con=engine, name=table, if_exists="replace") # Validate geometry type sql = "SELECT DISTINCT(GeometryType(geometry)) FROM {table} ORDER BY 1;".format( table=table ) geom_type = engine.execute(sql).fetchone()[0] sql = "SELECT * FROM {table};".format(table=table) df = read_postgis(sql, engine, geom_col="geometry") assert geom_type.upper() == "GEOMETRYCOLLECTION" assert df.geom_type.unique()[0] == "GeometryCollection"