def main(host='localhost', port=5432): """Convert YAML specifications to database DDL.""" parser = ArgumentParser(parents=[parent_parser()], description="Generate SQL statements to update a " "PostgreSQL database to match the schema specified" " in a YAML file") parser.add_argument('spec', nargs='?', type=FileType('r'), default=sys.stdin, help='YAML specification') parser.add_argument('-1', '--single-transaction', action='store_true', dest='onetrans', help="wrap commands in BEGIN/COMMIT") parser.add_argument('-u', '--update', action='store_true', help="apply changes to database (implies -1)") parser.add_argument('-n', '--schema', dest='schlist', action='append', help="only for named schemas (default all)") parser.set_defaults(host=host, port=port, username=os.getenv("USER")) args = parser.parse_args() dbconn = DbConnection(args.dbname, args.username, args.password, args.host, args.port) db = Database(dbconn) inmap = yaml.load(args.spec) if args.schlist: kschlist = ['schema ' + sch for sch in args.schlist] for sch in inmap.keys(): if sch not in kschlist: del inmap[sch] stmts = db.diff_map(inmap, args.schlist) if args.output: fd = args.output sys.stdout = fd if stmts: if args.onetrans or args.update: print("BEGIN;") print(";\n".join(stmts) + ';') if args.onetrans or args.update: print("COMMIT;") if args.update: dbconn.connect() try: for stmt in stmts: dbconn.conn.cursor().execute(stmt) except: dbconn.conn.rollback() raise else: dbconn.conn.commit() print("Changes applied", file=sys.stderr) if args.output: fd.close()
def main(host='localhost', port=5432, schema=None): """Convert database table specifications to YAML.""" parser = ArgumentParser(parents=[parent_parser()], description="Extract the schema of a PostgreSQL " "database in YAML format") parser.add_argument('-n', '--schema', help="only for named schema (default %(default)s)") parser.add_argument('-t', '--table', dest='tablist', action='append', help="only for named tables (default all)") parser.set_defaults(host=host, port=port, username=os.getenv("USER"), schema=schema) args = parser.parse_args() db = Database(DbConnection(args.dbname, args.username, args.password, args.host, args.port)) dbmap = db.to_map() # trim the map of schemas/tables not selected if args.schema: skey = 'schema ' + args.schema for sch in dbmap.keys(): if sch[:7] == 'schema ' and sch != skey: del dbmap[sch] if args.tablist: ktablist = ['table ' + tbl for tbl in args.tablist] for sch in dbmap.keys(): if sch[:7] == 'schema ': for tbl in dbmap[sch].keys(): if tbl not in ktablist: del dbmap[sch][tbl] if not dbmap[sch]: del dbmap[sch] if args.output: fd = args.output sys.stdout = fd print(yaml.dump(dbmap, default_flow_style=False)) if args.output: fd.close()
def main(host='localhost', port=5432, schema=None): """Convert database table specifications to YAML.""" parser = ArgumentParser(parents=[parent_parser()], description="Extract the schema of a PostgreSQL " "database in YAML format") parser.add_argument('-n', '--schema', help="only for named schema (default %(default)s)") parser.add_argument('-t', '--table', dest='tablist', action='append', help="only for named tables (default all)") parser.set_defaults(host=host, port=port, username=os.getenv("USER"), schema=schema) args = parser.parse_args() pswd = (args.password and getpass.getpass() or '') db = Database(args.dbname, args.username, pswd, args.host, args.port) dbmap = db.to_map([args.schema], args.tablist) if args.output: fd = args.output sys.stdout = fd print(yaml.dump(dbmap, default_flow_style=False)) if args.output: fd.close()
def main(host='localhost', port=5432, schema=None): """Convert database table specifications to YAML.""" parser = ArgumentParser(parents=[parent_parser()], description="Extract the schema of a PostgreSQL " "database in YAML format") group = parser.add_argument_group("Object inclusion/exclusion options", "(each can be given multiple times)") group.add_argument('-n', '--schema', metavar='SCHEMA', dest='schemas', action='append', default=[], help="extract the named schema(s) (default all)") group.add_argument('-N', '--exclude-schema', metavar='SCHEMA', dest='excl_schemas', action='append', default=[], help="do NOT extract the named schema(s) " "(default none)") group.add_argument('-t', '--table', metavar='TABLE', dest='tables', action='append', default=[], help="extract the named table(s) (default all)") group.add_argument('-T', '--exclude-table', metavar='TABLE', dest='excl_tables', action='append', default=[], help="do NOT extract the named table(s) " "(default none)") parser.set_defaults(host=host, port=port, schema=schema, username=os.getenv("PGUSER") or os.getenv("USER")) args = parser.parse_args() pswd = (args.password and getpass.getpass() or None) db = Database(args.dbname, args.username, pswd, args.host, args.port) dbmap = db.to_map(schemas=args.schemas, tables=args.tables, exclude_schemas=args.excl_schemas, exclude_tables=args.excl_tables) print(yaml.dump(dbmap, default_flow_style=False), file=args.output or sys.stdout) if args.output: args.output.close()