def handle(self, *args, **options):
try:
db_path = args[0]
assert os.path.exists(db_path)
except (AssertionError, IndexError):
raise CommandError(
"Specify path for gyb sqlite database (data.db)")
# confirm that database contains a viable trees_fvsaggregate table
gybconn = sqlite3.connect(db_path)
gybcursor = gybconn.cursor()
gybcursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = [x[0] for x in gybcursor.fetchall()]
if 'trees_fvsaggregate' not in tables:
raise CommandError(
"trees_fvsaggregate table not found in {}".format(db_path))
# Confirm that gyb's schema is sufficient to provide all FP schema fields
gybcursor.execute("PRAGMA table_info(trees_fvsaggregate);")
gyb_fieldnames = [x[1] for x in gybcursor.fetchall()]
pgcursor = connection.cursor()
try:
pgcursor.execute("SELECT * FROM trees_fvsaggregate LIMIT 0;")
# don't track id, autosequenced
pg_fields = [
desc for desc in pgcursor.description if desc[0] != 'id'
]
finally:
pgcursor.close()
pg_fieldnames = [x[0] for x in pg_fields]
# postgres schema is only allowed to deviate from gyb as follows...
# special cases described in match_case function below
assert set(pg_fieldnames) - set([x.lower()
for x in gyb_fieldnames]) == set(
['pp_btl', 'lp_btl'])
pg_insert_cols = ", ".join(['"{}"'.format(f) for f in pg_fieldnames])
def match_case(pgname):
"""return case-sensitive key name from sqlite given a postgres field name"""
for gybfield in gyb_fieldnames:
if pgname == gybfield.lower():
return gybfield
# not present, special cases
# If forestplanner schema calls for pp_btl or lp_btl
# substitute PINEBTL
if pgname == 'pp_btl':
return "PINEBTL"
if pgname == 'lp_btl':
return "PINEBTL"
raise Exception("Can't find {} in sqlite fields".format(pgname))
gyb_values_template = ", ".join(
["%({})s".format(match_case(x[0])) for x in pg_fields])
query_template = """INSERT INTO trees_fvsaggregate ({})
VALUES ({});""".format(pg_insert_cols, gyb_values_template)
pgcursor = connection.cursor()
try:
with transaction.commit_on_success():
pgcursor.executemany(query_template,
get_gyb_rows(db_path, gyb_fieldnames))
finally:
pgcursor.close()
print("Recaching valid_condids.")
FVSAggregate.recache()
def handle(self, *args, **options):
try:
db_path = args[0]
assert os.path.exists(db_path)
except (AssertionError, IndexError):
raise CommandError("Specify path for gyb sqlite database (data.db)")
# confirm that database contains a viable trees_fvsaggregate table
gybconn = sqlite3.connect(db_path)
gybcursor = gybconn.cursor()
gybcursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = [x[0] for x in gybcursor.fetchall()]
if 'trees_fvsaggregate' not in tables:
raise CommandError("trees_fvsaggregate table not found in {}".format(db_path))
# Confirm that gyb's schema is sufficient to provide all FP schema fields
gybcursor.execute("PRAGMA table_info(trees_fvsaggregate);")
gyb_fieldnames = [x[1] for x in gybcursor.fetchall()]
pgcursor = connection.cursor()
try:
pgcursor.execute("SELECT * FROM trees_fvsaggregate LIMIT 0;")
# don't track id, autosequenced
pg_fields = [desc for desc in pgcursor.description if desc[0] != 'id']
finally:
pgcursor.close()
pg_fieldnames = [x[0] for x in pg_fields]
# postgres schema is only allowed to deviate from gyb as follows...
# special cases described in match_case function below
assert set(pg_fieldnames) - set([x.lower() for x in gyb_fieldnames]) == set(['pp_btl', 'lp_btl'])
pg_insert_cols = ", ".join(['"{}"'.format(f) for f in pg_fieldnames])
def match_case(pgname):
"""return case-sensitive key name from sqlite given a postgres field name"""
for gybfield in gyb_fieldnames:
if pgname == gybfield.lower():
return gybfield
# not present, special cases
# If forestplanner schema calls for pp_btl or lp_btl
# substitute PINEBTL
if pgname == 'pp_btl':
return "PINEBTL"
if pgname == 'lp_btl':
return "PINEBTL"
raise Exception("Can't find {} in sqlite fields".format(pgname))
gyb_values_template = ", ".join(
["%({})s".format(match_case(x[0])) for x in pg_fields]
)
query_template = """INSERT INTO trees_fvsaggregate ({})
VALUES ({});""".format(pg_insert_cols, gyb_values_template)
pgcursor = connection.cursor()
try:
with transaction.commit_on_success():
pgcursor.executemany(query_template, get_gyb_rows(db_path, gyb_fieldnames))
finally:
pgcursor.close()
print("Recaching valid_condids.")
FVSAggregate.recache()
