def _get_query_from_file(query_arg, missing_value, combine_emits, max_column_length, required_columns):
if os.path.exists(query_arg):
if os.path.splitext(query_arg)[1] in ['.xls', '.xlsx']:
import openpyxl
workbook = openpyxl.load_workbook(query_arg)
return excel_query.get_queries_from_excel(
workbook, missing_value, combine_emits,
max_column_length, required_columns
)
else:
with io.open(query_arg, encoding='utf-8') as fh:
return MiniLinq.from_jvalue(json.loads(fh.read()))
def _get_query_from_file(query_arg, missing_value, combine_emits,
max_column_length, required_columns, column_enforcer):
if os.path.exists(query_arg):
if os.path.splitext(query_arg)[1] in ['.xls', '.xlsx']:
import openpyxl
workbook = openpyxl.load_workbook(query_arg)
return excel_query.get_queries_from_excel(workbook, missing_value,
combine_emits,
max_column_length,
required_columns,
column_enforcer)
else:
with io.open(query_arg, encoding='utf-8') as fh:
return MiniLinq.from_jvalue(json.loads(fh.read()))
def _get_query(args, writer):
# Reads as excel if it is a file name that looks like excel, otherwise reads as JSON,
# falling back to parsing arg directly as JSON, and finally parsing stdin as JSON
if args.query:
return _get_query_from_file(
args.query,
args.missing_value,
writer.supports_multi_table_write,
writer.max_column_length,
writer.required_columns
)
else:
try:
return MiniLinq.from_jvalue(json.loads(sys.stdin.read()))
except Exception as e:
raise Exception(
"Failure reading query from console input. "
"Try using the '--query' parameter to pass your query as an Excel file", e
)
def main_with_args(args):
# Grab the timestamp here so that anything that comes in while this runs will be grabbed next time.
run_start = datetime.utcnow()
# Reads as excel if it is a file name that looks like excel, otherwise reads as JSON,
# falling back to parsing arg directly as JSON, and finally parsing stdin as JSON
if args.query:
if os.path.exists(args.query):
query_file_md5 = misc.digest_file(args.query)
if os.path.splitext(args.query)[1] in [".xls", ".xlsx"]:
import openpyxl
workbook = openpyxl.load_workbook(args.query)
query = excel_query.compile_workbook(workbook)
else:
with io.open(args.query, encoding="utf-8") as fh:
query = MiniLinq.from_jvalue(json.loads(fh.read()))
else:
print("Query file not found: %s" % args.query)
exit(1)
else:
query = MiniLinq.from_jvalue(json.loads(sys.stdin.read()))
if args.dump_query:
print(json.dumps(query.to_jvalue(), indent=4))
exit(0)
if not args.username:
args.username = input("Please provide a username: "******"xlsx":
writer = writers.Excel2007TableWriter(args.output)
elif args.output_format == "xls":
writer = writers.Excel2003TableWriter(args.output)
elif args.output_format == "csv":
writer = writers.CsvTableWriter(args.output)
elif args.output_format == "json":
writer = writers.JValueTableWriter()
elif args.output_format == "markdown":
writer = writers.StreamingMarkdownTableWriter(sys.stdout)
elif args.output_format == "sql":
writer = writers.SqlTableWriter(args.output) # Output should be a connection URL
if not args.since and not args.start_over and os.path.exists(args.query):
connection = sqlalchemy.create_engine(args.output)
# Grab the current list of tables to see if we have already run & written to it
metadata = sqlalchemy.MetaData()
metadata.bind = connection
metadata.reflect()
if "commcare_export_runs" in metadata.tables:
cursor = connection.execute(
sqlalchemy.sql.text(
"SELECT time_of_run FROM commcare_export_runs WHERE query_file_md5 = :query_file_md5 ORDER BY time_of_run DESC"
),
query_file_md5=query_file_md5,
)
for row in cursor:
args.since = row[0]
logger.debug("Last successful run was %s", args.since)
break
cursor.close()
else:
logger.warn("No successful runs found, and --since not specified: will import ALL data")
if args.since:
logger.debug("Starting from %s", args.since)
env = (
BuiltInEnv()
| CommCareHqEnv(api_client, since=dateutil.parser.parse(args.since) if args.since else None)
| JsonPathEnv({})
)
results = query.eval(env)
# Assume that if any tables were emitted, that is the idea, otherwise print the output
if len(list(env.emitted_tables())) > 0:
with writer:
for table in env.emitted_tables():
logger.debug("Writing %s", table["name"])
writer.write_table(table)
if args.output_format == "sql" and os.path.exists(args.query):
writer.write_table(
{
"name": "commcare_export_runs",
"headings": ["id", "query_file_name", "query_file_md5", "time_of_run"],
"rows": [[uuid.uuid4().hex, args.query, query_file_md5, run_start.isoformat()]],
}
)
if args.output_format == "json":
print(json.dumps(writer.tables, indent=4, default=RepeatableIterator.to_jvalue))
else:
print(json.dumps(list(results), indent=4, default=RepeatableIterator.to_jvalue))
def main_with_args(args):
# Reads as excel if it is a file name that looks like excel, otherwise reads as JSON,
# falling back to parsing arg directly as JSON, and finally parsing stdin as JSON
if args.query:
if os.path.exists(args.query):
if os.path.splitext(args.query)[1] in ['.xls', '.xlsx']:
import openpyxl
workbook = openpyxl.load_workbook(args.query)
query = excel_query.compile_workbook(workbook)
else:
with open(args.query) as fh:
query = MiniLinq.from_jvalue(json.loads(fh.read()))
else:
query = MiniLinq.from_jvalue(json.loads(args.query))
else:
query = MiniLinq.from_jvalue(json.loads(sys.stdin.read()))
if args.dump_query:
print json.dumps(query.to_jvalue(), indent=4)
exit(0)
if not args.username:
args.username = raw_input('Please provide a username: '******'Please enter your password: '******'xlsx':
writer = writers.Excel2007TableWriter(args.output)
elif args.output_format == 'xls':
writer = writers.Excel2003TableWriter(args.output)
elif args.output_format == 'csv':
writer = writers.CsvTableWriter(args.output)
elif args.output_format == 'json':
writer = writers.JValueTableWriter()
elif args.output_format == 'markdown':
writer = writers.StreamingMarkdownTableWriter(sys.stdout)
elif args.output_format == 'sql':
writer = writers.SqlTableWriter(args.output) # Output should be a connection URL
env = BuiltInEnv() | CommCareHqEnv(api_client, since=dateutil.parser.parse(args.since) if args.since else None) | JsonPathEnv({})
results = query.eval(env)
# Assume that if any tables were emitted, that is the idea, otherwise print the output
if len(list(env.emitted_tables())) > 0:
with writer:
for table in env.emitted_tables():
logging.debug('Writing %s', table['name'])
writer.write_table(table)
if args.output_format == 'json':
print json.dumps(writer.tables, indent=4, default=RepeatableIterator.to_jvalue)
else:
print json.dumps(list(results), indent=4, default=RepeatableIterator.to_jvalue)
def main_with_args(args):
# Grab the timestamp here so that anything that comes in while this runs will be grabbed next time.
run_start = datetime.utcnow()
# Reads as excel if it is a file name that looks like excel, otherwise reads as JSON,
# falling back to parsing arg directly as JSON, and finally parsing stdin as JSON
if args.query:
if os.path.exists(args.query):
query_file_md5 = misc.digest_file(args.query)
if os.path.splitext(args.query)[1] in ['.xls', '.xlsx']:
import openpyxl
workbook = openpyxl.load_workbook(args.query)
query = excel_query.compile_workbook(workbook)
else:
with io.open(args.query, encoding='utf-8') as fh:
query = MiniLinq.from_jvalue(json.loads(fh.read()))
else:
print('Query file not found: %s' % args.query)
exit(1)
else:
query = MiniLinq.from_jvalue(json.loads(sys.stdin.read()))
if args.dump_query:
print(json.dumps(query.to_jvalue(), indent=4))
exit(0)
if not args.username:
args.username = input('Please provide a username: '******'xlsx':
writer = writers.Excel2007TableWriter(args.output)
elif args.output_format == 'xls':
writer = writers.Excel2003TableWriter(args.output)
elif args.output_format == 'csv':
if not args.output.endswith(".zip"):
print("WARNING: csv output is a zip file, but "
"will be written to %s" % args.output)
print("Consider appending .zip to the file name to avoid confusion.")
writer = writers.CsvTableWriter(args.output)
elif args.output_format == 'json':
writer = writers.JValueTableWriter()
elif args.output_format == 'markdown':
writer = writers.StreamingMarkdownTableWriter(sys.stdout)
elif args.output_format == 'sql':
# Output should be a connection URL
# Writer had bizarre issues so we use a full connection instead of passing in a URL or engine
import sqlalchemy
engine = sqlalchemy.create_engine(args.output)
writer = writers.SqlTableWriter(engine.connect())
if not args.since and not args.start_over and os.path.exists(args.query):
connection = sqlalchemy.create_engine(args.output)
# Grab the current list of tables to see if we have already run & written to it
metadata = sqlalchemy.MetaData()
metadata.bind = connection
metadata.reflect()
if 'commcare_export_runs' in metadata.tables:
cursor = connection.execute(sqlalchemy.sql.text('SELECT time_of_run FROM commcare_export_runs WHERE query_file_md5 = :query_file_md5 ORDER BY time_of_run DESC'), query_file_md5=query_file_md5)
for row in cursor:
args.since = row[0]
logger.debug('Last successful run was %s', args.since)
break
cursor.close()
else:
logger.warn('No successful runs found, and --since not specified: will import ALL data')
if args.since:
logger.debug('Starting from %s', args.since)
env = BuiltInEnv() | CommCareHqEnv(api_client, since=dateutil.parser.parse(args.since) if args.since else None) | JsonPathEnv({})
results = query.eval(env)
# Assume that if any tables were emitted, that is the idea, otherwise print the output
if len(list(env.emitted_tables())) > 0:
with writer:
for table in env.emitted_tables():
logger.debug('Writing %s', table['name'])
writer.write_table(table)
if args.output_format == 'sql' and os.path.exists(args.query):
writer.write_table({
'name': 'commcare_export_runs',
'headings': ['id', 'query_file_name', 'query_file_md5', 'time_of_run'],
'rows': [ [uuid.uuid4().hex, args.query, query_file_md5, run_start.isoformat()] ]
})
if args.output_format == 'json':
print(json.dumps(writer.tables, indent=4, default=RepeatableIterator.to_jvalue))
else:
print(json.dumps(list(results), indent=4, default=RepeatableIterator.to_jvalue))
def main_with_args(args):
# Grab the timestamp here so that anything that comes in while this runs will be grabbed next time.
run_start = datetime.utcnow()
# Reads as excel if it is a file name that looks like excel, otherwise reads as JSON,
# falling back to parsing arg directly as JSON, and finally parsing stdin as JSON
if args.query:
if os.path.exists(args.query):
query_file_md5 = misc.digest_file(args.query)
if os.path.splitext(args.query)[1] in ['.xls', '.xlsx']:
import openpyxl
workbook = openpyxl.load_workbook(args.query)
query = excel_query.compile_workbook(workbook,
args.missing_value)
else:
with io.open(args.query, encoding='utf-8') as fh:
query = MiniLinq.from_jvalue(json.loads(fh.read()))
else:
print('Query file not found: %s' % args.query)
exit(1)
else:
query = MiniLinq.from_jvalue(json.loads(sys.stdin.read()))
if args.dump_query:
print(json.dumps(query.to_jvalue(), indent=4))
exit(0)
if not args.username:
args.username = input('Please provide a username: '******'xlsx':
writer = writers.Excel2007TableWriter(args.output)
elif args.output_format == 'xls':
writer = writers.Excel2003TableWriter(args.output)
elif args.output_format == 'csv':
if not args.output.endswith(".zip"):
print("WARNING: csv output is a zip file, but "
"will be written to %s" % args.output)
print(
"Consider appending .zip to the file name to avoid confusion.")
writer = writers.CsvTableWriter(args.output)
elif args.output_format == 'json':
writer = writers.JValueTableWriter()
elif args.output_format == 'markdown':
writer = writers.StreamingMarkdownTableWriter(sys.stdout)
elif args.output_format == 'sql':
# Output should be a connection URL
# Writer had bizarre issues so we use a full connection instead of passing in a URL or engine
import sqlalchemy
engine = sqlalchemy.create_engine(args.output)
is_mysql = 'mysql' in args.output
collation = 'utf8_bin' if is_mysql else None
writer = writers.SqlTableWriter(engine.connect(),
args.strict_types,
collation=collation)
if not args.since and not args.start_over and os.path.exists(
args.query):
connection = sqlalchemy.create_engine(args.output)
# Grab the current list of tables to see if we have already run & written to it
metadata = sqlalchemy.MetaData()
metadata.bind = connection
metadata.reflect()
if 'commcare_export_runs' in metadata.tables:
cursor = connection.execute(sqlalchemy.sql.text(
'SELECT time_of_run FROM commcare_export_runs WHERE query_file_md5 = :query_file_md5 ORDER BY time_of_run DESC'
),
query_file_md5=query_file_md5)
for row in cursor:
args.since = row[0]
logger.debug('Last successful run was %s', args.since)
break
cursor.close()
else:
logger.warn(
'No successful runs found, and --since not specified: will import ALL data'
)
if args.since:
logger.debug('Starting from %s', args.since)
since = dateutil.parser.parse(args.since) if args.since else None
until = dateutil.parser.parse(args.until) if args.until else None
env = BuiltInEnv({
'commcarehq_base_url': commcarehq_base_url
}) | CommCareHqEnv(api_client, since=since, until=until) | JsonPathEnv({})
results = query.eval(env)
# Assume that if any tables were emitted, that is the idea, otherwise print the output
if len(list(env.emitted_tables())) > 0:
with writer:
api_client.set_checkpointer(writer,
query=args.query,
query_md5=query_file_md5)
for table in env.emitted_tables():
logger.debug('Writing %s', table['name'])
if table['name'] != table['name'].lower():
logger.warning("Caution: Using upper case letters in a "
"table name is not advised: {}".format(
table['name']))
writer.write_table(table)
if os.path.exists(args.query):
writer.set_checkpoint(args.query, query_file_md5, run_start,
True)
if args.output_format == 'json':
print(
json.dumps(writer.tables,
indent=4,
default=RepeatableIterator.to_jvalue))
else:
print(
json.dumps(list(results),
indent=4,
default=RepeatableIterator.to_jvalue))