def export_shp_with_pgsql2shp(self, table_or_sql: str, filepath: Path) -> None:
"""
Use pgsql2shp to export a shapefile from the database.
Valid arguments for `table_or_sql` are the name of a table or a full query.
e.g. "pa.centerlines"
"SELECT * FROM pa.centerlines WHERE some_column = 'some value'"
"""
print(
"WARNING! pgsql2shp creates shapefiles that do not contain EPSG values"
)
print("As an alternative that preserves EPSG values, use either:")
print("\t -> Database.export_gis(method='geopandas')")
print("\t -> Database.export_gis(method='ogr2ogr')")
if helpers.this_is_raw_sql(table_or_sql):
query = table_or_sql
else:
query = f"SELECT * FROM {table_or_sql}"
params = self.connection_params()
command = f'pgsql2shp -f "{filepath}" -h {params["host"]} -u {params["un"]} -P {params["pw"]} -p {params["port"]} {params["db_name"]} "{query}" '
print(command)
helpers.run_command_in_shell(command)
def shp2pgsql(self,
filepath: str,
srid: int,
sql_tablename: str,
new_srid: int = None):
"""
Use the shp2pgsql command to import a shapefile into the database
"""
# Ensure that the schema provided in the 'tablename' exists
schema, _ = helpers.convert_full_tablename_to_parts(sql_tablename)
self.schema_add(schema)
# If 'new_srid' is provided, use 'old:new' to project on the fly
srid_arg = f"{srid}:{new_srid}" if new_srid else srid
command = (
f'{self.cmd.shp2pgsql} -I -s {srid_arg} "{filepath}" {sql_tablename} | psql {self.uri}'
)
print(command)
helpers.run_command_in_shell(command)
self.gis_table_lint_geom_colname(sql_tablename)
def export_table_to_another_db(self, table_to_copy: str, target_db) -> None:
"""
- Pipe data directly from a pg_dump of one DB into another using psql
Arguments:
table_to_copy (str): name of table that you want to copy
target_db (Database): database where you want the data copied to
Returns:
None: although it makes a copy of the table inside `target_db`
"""
# If the table_to_copy has a schema, ensure that the schema also exists in the target db
if "." in table_to_copy:
schema = table_to_copy.split(".")[0]
target_db.schema_add(schema)
pg_dump = self.cmd.pg_dump
command = f"{pg_dump} --no-owner --no-acl -t {table_to_copy} {self.uri} | psql {target_db.uri}"
print(command)
helpers.run_command_in_shell(command)
self.gis_table_lint_geom_colname(target_db, table_to_copy)
return None
def export_shp_with_ogr2ogr(self,
table_or_sql: str,
filepath: Path,
filetype: str = "ESRI Shapefile") -> None:
"""
Use ogr2ogr to export a shapefile from the database.
Valid arguments for `table_or_sql` are the name of a table or a full query.
e.g. "pa.centerlines"
"SELECT * FROM pa.centerlines WHERE some_column = 'some value'"
"""
params = self.connection_params
pg_params_for_ogr = f'PG:"host={params["host"]} user={params["un"]} password={params["pw"]} port={params["port"]} dbname={params["db_name"]}"'
cmd = f'{self.cmd.ogr2ogr} -f "{filetype}" "{filepath}" {pg_params_for_ogr} '
# If a query is passed, append cmd with ' -sql QUERY'
if helpers.this_is_raw_sql(table_or_sql):
sql = table_or_sql
cmd += f' -sql "{sql}"'
# Otherwise, just append the tablename to the cmd
else:
tablename = table_or_sql
cmd += f" {tablename}"
print(cmd)
helpers.run_command_in_shell(cmd)
def dump(self, output_folder: str = ".") -> Path:
"""
- Create a standalone text file backup of the entire database.
- Returns the full filepath to the newly created file.
Arguments:
output_folder (str): folder where output file should go. Defaults to active directory.
Returns:
Path: to the newly created output file
"""
db_name = self.connection_params["db_name"]
timestamp = helpers.timestamp_for_filepath()
filename = f"{db_name}_{timestamp}.sql"
output_filepath = Path(output_folder) / filename
command = f'"{self.cmd.pg_dump}" --no-owner --no-acl {self.uri} > "{output_filepath}"'
print(command)
helpers.run_command_in_shell(command)
return output_filepath
def drop_database(self) -> None:
"""
- Drop the database if it exists, via `psql`
"""
if self.exists():
db_name = self.connection_params["db_name"]
command = f'{self.cmd.psql} -c "DROP DATABASE {db_name};" {self.uri_superuser}'
helpers.run_command_in_shell(command)
def create_database(self) -> None:
"""
- Create the database if it doesn't exist yet, via `psql`
"""
if not self.exists():
db_name = self.connection_params["db_name"]
# Create the database
commands = [
f'{self.cmd.psql} -c "CREATE DATABASE {db_name};" {self.uri_superuser}',
f'{self.cmd.psql} -c "CREATE EXTENSION postgis;" {self.uri}',
]
for cmd in commands:
helpers.run_command_in_shell(cmd)
def load_from_dumpfile(self, filepath: str | Path) -> None:
"""
- Load a `.sql` file into a new database
- The database must not already exist
Arguments:
filepath (str): full path to the SQL file
Returns:
loads the `.sql` file into an empty database
"""
db_name = self.connection_params["db_name"]
if self.exists():
print(f"Database {db_name} already exists. Use a different name.")
else:
command = f'"{self.cmd.psql}" -f "{filepath}" {self.uri}'
helpers.run_command_in_shell(command)
return None
def export_entire_db_to_another_db(self, target_db) -> None:
"""
- Copy an entire database to a new database.
- To get around memory error limitations, this is done in two steps as opposed to a single
command with a pipe:
Step 1) Backup the source db to .sql file with pg_dump
Step 2) Load the .sql file into the target db with psql
Arguments:
target_db (Database): new database (that doesn't exist yet) where you want the data
Returns:
None: although it makes a full copy the source database in `target_db`
"""
if target_db.exists():
target_db_name = target_db.connection_params["db_name"]
print(f"A database named '{target_db_name}' already exists.")
print(
"Use a different name or drop this database first before copying into it."
)
return None
else:
sql_filepath = self.dump()
target_db.admin("CREATE")
command = f'{target_db.cmd.psql} -f "{sql_filepath}" {target_db.uri}'
helpers.run_command_in_shell(command)
# Ensure that spatial tables have 'geom' instead of 'shape' columns
for table in target_db.tables(spatial_only=True):
target_db.gis_table_lint_geom_colname(table)
# Delete the .sql file from disk
sql_filepath.unlink()
return None