def main(url, schema=None, apply=False, alphabetical=False, quiet=False):
"""
Print or apply a reflected or loaded database schema.
"""
# output
from cargo.log import (
get_logger,
enable_default_logging,
)
enable_default_logging()
# build the particular database engine
from cargo.sql.alchemy import make_engine
engine = make_engine(url)
# load the appropriate schema
if schema is None:
# examine the database to construct a schema
from sqlalchemy.schema import MetaData
metadata = MetaData(bind=engine.connect(), reflect=True)
else:
# load an already-defined schema
from cargo.sugar import value_by_name
metadata = value_by_name(schema)
# print or apply the schema
if apply:
if not quiet:
get_logger("sqlalchemy.engine", level="DEBUG")
metadata.create_all(engine)
else:
# print the DDL
from sqlalchemy.schema import CreateTable
if alphabetical:
sorted_tables = sorted(metadata.sorted_tables,
key=lambda t: t.name)
else:
sorted_tables = metadata.sorted_tables
for table in sorted_tables:
print CreateTable(table).compile(engine)
def main(url, schema = None, apply = False, alphabetical = False, quiet = False):
"""
Print or apply a reflected or loaded database schema.
"""
# output
from cargo.log import (
get_logger,
enable_default_logging,
)
enable_default_logging()
# build the particular database engine
from cargo.sql.alchemy import make_engine
engine = make_engine(url)
# load the appropriate schema
if schema is None:
# examine the database to construct a schema
from sqlalchemy.schema import MetaData
metadata = MetaData(bind = engine.connect(), reflect = True)
else:
# load an already-defined schema
from cargo.sugar import value_by_name
metadata = value_by_name(schema)
# print or apply the schema
if apply:
if not quiet:
get_logger("sqlalchemy.engine", level = "DEBUG")
metadata.create_all(engine)
else:
# print the DDL
from sqlalchemy.schema import CreateTable
if alphabetical:
sorted_tables = sorted(metadata.sorted_tables, key = lambda t: t.name)
else:
sorted_tables = metadata.sorted_tables
for table in sorted_tables:
print CreateTable(table).compile(engine)
def main(url, quiet=False):
"""
Drop unused tables from database.
"""
# basic setup
from cargo.log import enable_default_logging
enable_default_logging()
if not quiet:
get_logger("sqlalchemy.engine", level="WARNING")
get_logger("cargo.sql.actions", level="DETAIL")
# connect
from sqlalchemy import create_engine
engine = create_engine(url)
connection = engine.connect()
with connection.begin():
# reflect the schema
from sqlalchemy.schema import MetaData
metadata = MetaData(bind=connection, reflect=True)
# look for and drop empty tables
from sqlalchemy.sql import select
from sqlalchemy.sql.functions import count
for table in metadata.sorted_tables:
((size, ), ) = connection.execute(select([count()], None, table))
if size == 0:
log.note("table %s is empty; dropping", table.name)
table.drop()
else:
log.note("table %s has %i rows", table.name, size)
# done
engine.dispose()
def main(url, quiet = False):
"""
Drop unused tables from database.
"""
# basic setup
from cargo.log import enable_default_logging
enable_default_logging()
if not quiet:
get_logger("sqlalchemy.engine", level = "WARNING")
get_logger("cargo.sql.actions", level = "DETAIL")
# connect
from sqlalchemy import create_engine
engine = create_engine(url)
connection = engine.connect()
with connection.begin():
# reflect the schema
from sqlalchemy.schema import MetaData
metadata = MetaData(bind = connection, reflect = True)
# look for and drop empty tables
from sqlalchemy.sql import select
from sqlalchemy.sql.functions import count
for table in metadata.sorted_tables:
((size,),) = connection.execute(select([count()], None, table))
if size == 0:
log.note("table %s is empty; dropping", table.name)
table.drop()
else:
log.note("table %s has %i rows", table.name, size)
# done
engine.dispose()
def main(
to_url,
schema = None,
tables = None,
quiet = False,
where = None,
fetch = 8192,
*from_urls
):
"""
Copy data from source database(s) to some single target.
"""
# basic setup
from cargo.log import enable_default_logging
enable_default_logging()
if not quiet:
get_logger("sqlalchemy.engine", level = "WARNING")
get_logger("cargo.sql.actions", level = "DETAIL")
if where is not None and (tables is None or len(tables) != 1):
raise ValueError("exactly one table must be specified with where clause")
# copy as requested
from cargo.sql.alchemy import make_engine
to_engine = make_engine(to_url)
to_connection = to_engine.connect()
with to_connection.begin():
if tables is not None:
log.debug("permitting only tables: %s", tables)
for from_url in from_urls:
# normalize the URL
log.info("copying from %s, fetching %i at a time", from_url, fetch)
# connect to this source
from_engine = make_engine(from_url)
from_connection = from_engine.connect()
# load the appropriate schema
if schema is None:
from sqlalchemy.schema import MetaData
metadata = MetaData(bind = from_connection, reflect = True)
else:
from cargo.sugar import value_by_name
metadata = value_by_name(schema)
# copy its data
for sorted_table in metadata.sorted_tables:
if tables is None or sorted_table.name in tables:
if sorted_table.exists(bind = from_connection):
from cargo.sql.actions import copy_table
log.debug("copying table %s", sorted_table.name)
copy_table(
from_connection,
to_connection,
sorted_table,
where = where,
fetch = fetch,
)
else:
log.debug("table %s does not exist in %s", sorted_table.name, from_url)
# done
from_engine.dispose()
# done
to_engine.dispose()
def main(to_url,
schema=None,
tables=None,
quiet=False,
where=None,
fetch=8192,
*from_urls):
"""
Copy data from source database(s) to some single target.
"""
# basic setup
from cargo.log import enable_default_logging
enable_default_logging()
if not quiet:
get_logger("sqlalchemy.engine", level="WARNING")
get_logger("cargo.sql.actions", level="DETAIL")
if where is not None and (tables is None or len(tables) != 1):
raise ValueError(
"exactly one table must be specified with where clause")
# copy as requested
from cargo.sql.alchemy import make_engine
to_engine = make_engine(to_url)
to_connection = to_engine.connect()
with to_connection.begin():
if tables is not None:
log.debug("permitting only tables: %s", tables)
for from_url in from_urls:
# normalize the URL
log.info("copying from %s, fetching %i at a time", from_url, fetch)
# connect to this source
from_engine = make_engine(from_url)
from_connection = from_engine.connect()
# load the appropriate schema
if schema is None:
from sqlalchemy.schema import MetaData
metadata = MetaData(bind=from_connection, reflect=True)
else:
from cargo.sugar import value_by_name
metadata = value_by_name(schema)
# copy its data
for sorted_table in metadata.sorted_tables:
if tables is None or sorted_table.name in tables:
if sorted_table.exists(bind=from_connection):
from cargo.sql.actions import copy_table
log.debug("copying table %s", sorted_table.name)
copy_table(
from_connection,
to_connection,
sorted_table,
where=where,
fetch=fetch,
)
else:
log.debug("table %s does not exist in %s",
sorted_table.name, from_url)
# done
from_engine.dispose()
# done
to_engine.dispose()