def __main__():
# Parse Command Line
parser = optparse.OptionParser()
parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None,
help='The SQLite Database')
parser.add_option('-q', '--query', dest='query', default=None,
help='SQL query')
parser.add_option('-Q', '--query_file', dest='query_file', default=None,
help='SQL query file')
parser.add_option('-n', '--no_header', dest='no_header', default=False,
action='store_true',
help='Include a column headers line')
parser.add_option('-c', '--comment_char', dest='comment_char', default='',
help='comment character to prefix column header line')
parser.add_option('-o', '--output', dest='output', default=None,
help='Output file for query results')
(options, args) = parser.parse_args()
# determine output destination
if options.output is not None:
try:
outputPath = os.path.abspath(options.output)
outputFile = open(outputPath, 'w')
except Exception as e:
exit('Error: %s' % (e))
else:
outputFile = sys.stdout
query = None
if options.query_file is not None:
with open(options.query_file, 'r') as fh:
query = fh.read()
elif options.query is not None:
query = options.query
if query is None:
try:
describe_tables(get_connection(options.sqlitedb), outputFile)
except Exception as e:
exit('Error: %s' % (e))
exit(0)
else:
try:
run_query(get_connection(options.sqlitedb), query, outputFile,
no_header=options.no_header,
comment_char=options.comment_char)
except Exception as e:
exit('Error: %s' % (e))
def add_member():
if request.json:
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
firstname = json.get("firstname")
lastname = json.get("lastname")
handicap = json.get("handicap")
if not firstname:
return Response(status=MISSING_PARAM_CODE)
if not lastname:
return Response(status=MISSING_PARAM_CODE)
if not handicap:
return Response(status=MISSING_PARAM_CODE)
else:
connection = query_db.get_connection(current_db_location())
if connection is not None:
query_db.insert_into_member(connection, firstname, lastname, handicap)
connection.close()
return Response(status=SUCCESS_CODE)
else: # Connection to database failed
return Response(status=FAILURE_CODE)
return Response(status=FAILURE_CODE)
def edit_member(identifier):
if not identifier:
return Response(status=MISSING_PARAM_CODE)
if request.json:
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
connection = query_db.get_connection(current_db_location())
if connection is not None:
if request.method == 'PUT': # Edit member
member = None
member = member_obj_from_json(json, identifier)
if not member:
return Response(status=MISSING_PARAM_CODE)
query_db.update_member(connection, identifier, member)
connection.close()
return Response(status=SUCCESS_CODE)
elif request.method == 'DELETE': # Delete member
query_db.delete_member(connection, identifier)
return Response(status=SUCCESS_CODE)
# Failure
return Response(status=FAILURE_CODE)
def add_result():
if request.json:
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
event_id = json.get("event_id")
member_id = json.get("member_id")
score = json.get("score")
if not event_id:
return Response(status=MISSING_PARAM_CODE)
if not member_id:
return Response(status=MISSING_PARAM_CODE)
if not score:
return Response(status=MISSING_PARAM_CODE)
else:
connection = query_db.get_connection(current_db_location())
if connection is not None:
query_db.insert_into_result(connection, event_id, member_id, score)
connection.close()
return Response(status=SUCCESS_CODE)
else: # Connection to database failed
return Response(status=FAILURE_CODE)
# Failure
return Response(status=FAILURE_CODE)
def get_all_results_for_member(identifier):
if not identifier:
return Response(status=MISSING_PARAM_CODE)
connection = query_db.get_connection(current_db_location())
json = '{ "results": [ '
if connection is not None:
results = query_db.get_all_results_for_member(connection, identifier)
connection.close()
if len(results) > 0:
for count, result in enumerate(results, start=1):
if result:
json += result.jsonify()
# Add comma after json object created
# up until the last one, then add }
if count is not len(results):
json += ',' + "\r\n"
else:
json += ' ] }'
else: # There are no records in the database for that indentifier.
json = empty_json_for_array("results")
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
# Failure
return Response(status=FAILURE_CODE)
def get_all_events():
connection = query_db.get_connection(current_db_location())
json = '{"events": ['
if connection is not None:
events = query_db.get_all_events(connection)
connection.close()
if len(events) > 0:
for count, event in enumerate(events, start=1):
if event:
# Get the event date and change the date format back to dd-mm-yyyy.
unformatted_date = event.date
formatted_date = Event.date_format_ddmmyyyy(unformatted_date)
event.date = formatted_date
json += event.jsonify()
# Add comma after json object created
# up until the last one, then add }
if count is not len(events):
json += ',' + "\r\n"
else:
json += ' ] }'
else:
json = empty_json_for_array("events")
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
# Failure
return Response(status=FAILURE_CODE)
def get_event_image(image_id):
if not image_id:
return Response(status=MISSING_PARAM_CODE)
connection = query_db.get_connection(current_db_location())
if connection is not None:
image_details = query_db.get_event_image_details(connection, image_id)
# Get the directory the image is located.
image_directory = image_details[0]
image_encoding = image_details[1]
image_id = image_details[2]
event_id = image_details[3]
# Get the extension for the image encoding
image_extension = image_util.enconding_extensions(image_encoding)
image_file = image_directory + str(image_id) + image_extension
image_base64_encoding = image_util.get_image_base64(image_file, image_encoding)
if image_base64_encoding:
json = '{"image_id": "%d", "event_id": "%d", "image_data": "%s"}' % (image_id, event_id, image_base64_encoding)
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
else:
return Response(status=FAILURE_CODE)
else:
return Response(status=SUCCESS_CODE)
def _create_table(ti, table):
path = table['file_path']
table_name =\
table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
comment_lines =\
table['comment_lines'] if 'comment_lines' in table else 0
comment_char =\
table['comment_char'] if 'comment_char' in table else None
column_names =\
table['column_names'] if 'column_names' in table else None
if column_names:
load_named_columns =\
table['load_named_columns']\
if 'load_named_columns' in table else False
else:
load_named_columns = False
unique_indexes = table['unique'] if 'unique' in table else []
indexes = table['index'] if 'index' in table else []
filters = table['filters'] if 'filters' in table else None
pkey_autoincr = \
table['pkey_autoincr'] if 'pkey_autoincr' in table else None
create_table(get_connection(options.sqlitedb), path, table_name,
pkey_autoincr=pkey_autoincr,
column_names=column_names,
skip=comment_lines,
comment_char=comment_char,
load_named_columns=load_named_columns,
filters=filters,
unique_indexes=unique_indexes,
indexes=indexes)
def add_poy():
if request.json:
json = request.json
access_token = json.get('accessToken')
# Check that this user is admin.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
member_id = json.get('member_id')
year = json.get('year')
score = json.get('score')
if not member_id:
return Response(status=MISSING_PARAM_CODE)
else:
connection = query_db.get_connection(current_db_location())
if connection is not None:
query_db.insert_into_poy(connection, member_id, year, score)
connection.close()
return Response(status=SUCCESS_CODE)
else:
return Response(status=FAILURE_CODE)
else:
return Response(status=FAILURE_CODE)
def _create_table(ti, table):
path = table['file_path']
table_name =\
table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
comment_lines =\
table['comment_lines'] if 'comment_lines' in table else 0
comment_char =\
table['comment_char'] if 'comment_char' in table else None
column_names =\
table['column_names'] if 'column_names' in table else None
firstlinenames =\
table['firstlinenames'] if 'firstlinenames' in table else False
if column_names:
load_named_columns =\
table['load_named_columns']\
if 'load_named_columns' in table else False
else:
load_named_columns = False
unique_indexes = table['unique'] if 'unique' in table else []
indexes = table['index'] if 'index' in table else []
filters = table['filters'] if 'filters' in table else None
pkey_autoincr = \
table['pkey_autoincr'] if 'pkey_autoincr' in table else None
create_table(get_connection(options.sqlitedb),
path,
table_name,
pkey_autoincr=pkey_autoincr,
firstlinenames=firstlinenames,
column_names=column_names,
skip=comment_lines,
comment_char=comment_char,
load_named_columns=load_named_columns,
filters=filters,
unique_indexes=unique_indexes,
indexes=indexes)
def edit_result():
result = None
if request.method == 'PUT':
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
result = result_obj_from_json(json)
# All fields of the result object should be filled.
if not result:
return Response(status=MISSING_PARAM_CODE)
connection = query_db.get_connection(current_db_location())
if connection is None:
return Response(status=FAILURE_CODE)
query_db.update_result(connection, result, member_id_key, event_id_key)
connection.close()
return Response(status=SUCCESS_CODE)
elif request.method == 'DELETE':
json = request.json
member_id = json.get("member_id")
event_id = json.get("event_id")
if not member_id or not event_id:
return Response(status=MISSING_PARAM_CODE)
connection = query_db.get_connection(current_db_location())
if connection is None:
return Response(status=FAILURE_CODE)
query_db.delete_result(connection, member_id, event_id)
connection.close()
return Response(status=SUCCESS_CODE)
else:
# Failure
return Response(status=FAILURE_CODE)
def edit_poy(member_id):
if request.method == 'PUT':
json = request.json
access_token = json.get('accessToken')
new_member_id = json.get('member_id')
year = json.get('year')
score = json.get('score')
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
connection = query_db.get_connection(current_db_location())
if connection is None:
return Response(status=FAILURE_CODE)
query_db.update_poy(connection, member_id, new_member_id, year, score)
connection.close()
return Response(status=SUCCESS_CODE)
elif request.method == 'DELETE':
json = request.json
access_token = json.get('accessToken')
year = json.get('year')
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
connection = query_db.get_connection(current_db_location())
if connection is None:
return Response(status=FAILURE_CODE)
query_db.delete_poy(connection, member_id, year)
connection.close()
return Response(status=SUCCESS_CODE)
else: # Failure
return Response(status=FAILURE_CODE)
def get_member(identifier):
if not identifier:
return Response(status=MISSING_PARAM_CODE)
connection = query_db.get_connection(current_db_location())
if connection is not None:
member = query_db.get_member(connection, identifier)
connection.close()
if member:
json = member.jsonify()
else:
json = empty_json_for_object("member")
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
# Failure
return Response(status=FAILURE_CODE)
def get_image_ids_for_event(event_id):
if not event_id:
return Response(status=MISSING_PARAM_CODE)
json = '{ "image_ids": ['
connection = query_db.get_connection(current_db_location())
if connection is not None:
identifiers = query_db.get_event_image_ids(connection, event_id)
for count, image_id in enumerate(identifiers, start=1):
json += str(image_id)
if count is not len(identifiers):
json += ','
else:
json += ']}'
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
else:
return Response(status=FAILURE_CODE)
def get_upcoming_event():
connection = query_db.get_connection(current_db_location())
if connection is not None:
event = query_db.get_upcoming_event(connection)
connection.close()
if event:
# Get the event date and change the date format back to dd-mm-yyyy.
unformatted_date = event.date
formatted_date = Event.date_format_ddmmyyyy(unformatted_date)
event.date = formatted_date
json = event.jsonify()
else:
json = empty_json_for_object("event")
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
# Failure
return Response(status=FAILURE_CODE)
def add_event():
if request.json:
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
title = json.get("title")
location = json.get("location")
start_tee = json.get("startTeeTime")
end_tee = json.get("endTeeTime")
date = json.get("date")
formatted_date = None
# Check date format.
if datetime.strptime(date, '%d-%m-%Y'):
formatted_date = Event.date_format_yyyymmdd(date)
else:
return Response(FAILURE_CODE)
if not title:
return Response(status=MISSING_PARAM_CODE)
if not location:
return Response(status=MISSING_PARAM_CODE)
if not start_tee:
return Response(status=MISSING_PARAM_CODE)
if not date:
return Response(status=MISSING_PARAM_CODE)
else:
connection = query_db.get_connection(current_db_location())
if connection is not None:
query_db.insert_into_event(connection, title, location, formatted_date, start_tee, end_tee)
connection.close()
return Response(status=SUCCESS_CODE)
else: # Connection to database failed
return Response(status=FAILURE_CODE)
# Failure
return Response(status=FAILURE_CODE)
def removeImage():
if request.method == 'DELETE':
json = request.json
access_token = json.get('accessToken')
event_id = json.get('event_id')
image_id = json.get('image_id')
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
connection = query_db.get_connection(current_db_location())
if connection is None:
return Response(status=FAILURE_CODE)
else:
query_db.delete_event_image(connection, image_id)
connection.close()
# Remove image from disk aswell.
image_parent_dir = IMAGE_BASE_DIRECTORY + str(event_id) + '/'
image_util.delete_image(image_parent_dir, str(image_id) + '.jpg')
return Response(status=SUCCESS_CODE)
def get_all_poy():
connection = query_db.get_connection(current_db_location())
if connection is not None:
results = query_db.get_all_poy(connection)
connection.close()
if len(results) > 0:
# Firstly add all the years the json will contain.
json = '{ "years": ['
for index, (key, value) in enumerate(results.items()):
if index == len(results) - 1:
json += ('"' + str(key) + '"')
else:
json += ('"' + str(key) + '",')
# Now add all the values for the years listed in the json above
json += '], "poys": [{'
for index, (key, value) in enumerate(results.items()):
year = key
poys = value
json += '"' + str(year) + '":['
for count, poy in enumerate(poys, start=1):
json += '{ "member":'
if count < len(poys):
json += poy.member.jsonify() + ','
json += '"score":' + str(poy.score) + '},'
else:
json += poy.member.jsonify() + ','
json += '"score":' + str(poy.score) + '}]' # End array for this year.
if index == len(results) - 1:
json += '}]' # End the array object for all years
else:
json += '}, {'
json += '}' # End poys:
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json');
def add_event_image():
if request.json:
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
# Get json objects
image_encoding = json.get("image_encoding")
event_id = json.get("event_id")
image_base64_data = json.get("image_data")
# Images should be stored in a directory
# under IMAGE_BASE_DIRECTORY > event id > filename.
# Where the filename is the actual id of the image given by the database.
# e.g. files will be stored IMAGE_BASE_DIRECTORY/EVENT_ID/2.jpeg,
# IMAGE_BASE_DIRECTORY/EVENT_ID/3.jpeg etc...
# Create parent directory where the image file will reside.
image_parent_dir = IMAGE_BASE_DIRECTORY + str(event_id) + '/'
# Get database connection
connection = query_db.get_connection(current_db_location())
# If there is not image type, encode as jpg.
if image_encoding is None:
# Insert into the database and retrieve the id for the image.
# The id will be used as the image filename.
image_filename = query_db.insert_into_event_image(connection, event_id, image_parent_dir, 'JPEG')
image_util.jpeg_and_write_image(image_parent_dir, image_filename, image_base64_data)
return Response(status=SUCCESS_CODE)
else:
return Response(status=FAILURE_CODE)
def get_all_results():
connection = query_db.get_connection(current_db_location())
json = '{ "results": [ '
if connection is not None:
results = query_db.get_all_results(connection)
connection.close()
if len(results) > 0:
for count, result in enumerate(results, start=1):
if result:
json += result.jsonify()
# Add comma after json object created
# up until the last one, then add }
if count is not len(results):
json += ',' + "\r\n"
else:
json += ' ] }'
else:
json = empty_json_for_array("results")
return Response(status=SUCCESS_CODE, response=json, mimetype='application/json')
# Failure
return Response(status=FAILURE_CODE)
def edit_event(identifier):
if not identifier:
return Response(status=MISSING_PARAM_CODE)
if request.json:
json = request.json
access_token = json.get("accessToken")
# Check that this is an admin user.
if auth.is_admin(access_token) is False:
return Response(status=PERMISSION_DENIED)
connection = query_db.get_connection(current_db_location())
if connection is not None:
if request.method == 'PUT': # Edit existing event.
event = event_obj_from_json(json, identifier)
if not event:
return Response(status=MISSING_PARAM_CODE)
# Check date format.
if datetime.strptime(event.date, '%d-%m-%Y'):
formatted_date = Event.date_format_yyyymmdd(event.date)
event.date = formatted_date
else:
return Response(FAILURE_CODE)
query_db.update_event(connection, identifier, event)
connection.close()
return Response(status=SUCCESS_CODE)
elif request.method == 'DELETE': # Delete existing event
query_db.delete_event(connection, identifier)
connection.close()
return Response(status=SUCCESS_CODE)
# Failure
return Response(status=FAILURE_CODE)
def __main__():
# Parse Command Line
parser = optparse.OptionParser()
parser.add_option('-s',
'--sqlitedb',
dest='sqlitedb',
default=None,
help='The SQLite Database')
parser.add_option('-j',
'--jsonfile',
dest='jsonfile',
default=None,
help='JSON dict of table specifications')
parser.add_option('-q',
'--query',
dest='query',
default=None,
help='SQL query')
parser.add_option('-Q',
'--query_file',
dest='query_file',
default=None,
help='SQL query file')
parser.add_option('-n',
'--no_header',
dest='no_header',
default=False,
action='store_true',
help='Include a column headers line')
parser.add_option('-c',
'--comment_char',
dest='comment_char',
default='',
help='comment character to prefix column header line')
parser.add_option('-o',
'--output',
dest='output',
default=None,
help='Output file for query results')
parser.add_option('-d',
'--debug',
dest='debug',
default=False,
action='store_true',
help='Output info to stderr')
(options, args) = parser.parse_args()
# determine output destination
if options.output is not None:
try:
outputPath = os.path.abspath(options.output)
outputFile = open(outputPath, 'w')
except Exception as e:
exit('Error: %s' % (e))
else:
outputFile = sys.stdout
def _create_table(ti, table):
path = table['file_path']
table_name =\
table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
comment_lines =\
table['comment_lines'] if 'comment_lines' in table else 0
comment_char =\
table['comment_char'] if 'comment_char' in table else None
column_names =\
table['column_names'] if 'column_names' in table else None
firstlinenames =\
table['firstlinenames'] if 'firstlinenames' in table else False
if column_names:
load_named_columns =\
table['load_named_columns']\
if 'load_named_columns' in table else False
else:
load_named_columns = False
unique_indexes = table['unique'] if 'unique' in table else []
indexes = table['index'] if 'index' in table else []
filters = table['filters'] if 'filters' in table else None
pkey_autoincr = \
table['pkey_autoincr'] if 'pkey_autoincr' in table else None
create_table(get_connection(options.sqlitedb),
path,
table_name,
pkey_autoincr=pkey_autoincr,
firstlinenames=firstlinenames,
column_names=column_names,
skip=comment_lines,
comment_char=comment_char,
load_named_columns=load_named_columns,
filters=filters,
unique_indexes=unique_indexes,
indexes=indexes)
if options.jsonfile:
try:
with open(options.jsonfile) as fh:
tdef = json.load(fh)
if options.debug:
print('JSON: %s' % tdef, file=sys.stderr)
if 'tables' in tdef:
for ti, table in enumerate(tdef['tables']):
_create_table(ti, table)
if 'sql_stmts' in tdef:
for si, stmt in enumerate(tdef['sql_stmts']):
rowcount = run_query(get_connection(options.sqlitedb),
stmt, None)
if options.debug:
print('\nDB modification: %s \nrowcount: %s' %
(stmt, rowcount),
file=sys.stderr)
if 'queries' in tdef:
for qi, qstmt in enumerate(tdef['queries']):
if 'header' in qstmt:
no_header = False
comment_char = qstmt['header']
else:
no_header = True
comment_char = None
with open(qstmt['result_file'], 'w') as fh:
query = qstmt['query']
rowcount = run_query(get_connection(
options.sqlitedb),
query,
fh,
no_header=no_header,
comment_char=comment_char)
if options.debug:
print('\nSQL: %s \nrowcount: %s' %
(query, rowcount),
file=sys.stderr)
except Exception as e:
exit('Error: %s' % (e))
query = None
if options.query_file is not None:
with open(options.query_file, 'r') as fh:
query = ''
for line in fh:
query += line
elif options.query is not None:
query = options.query
if query is None:
try:
describe_tables(get_connection(options.sqlitedb), outputFile)
except Exception as e:
exit('Error: %s' % (e))
else:
try:
rowcount = run_query(get_connection(options.sqlitedb),
query,
outputFile,
no_header=options.no_header,
comment_char=options.comment_char)
if options.debug:
print('\nSQL: %s \nrowcount: %s' % (query, rowcount),
file=sys.stderr)
except Exception as e:
exit('Error: %s' % (e))
def __main__():
# Parse Command Line
parser = optparse.OptionParser()
parser.add_option('-s',
'--sqlitedb',
dest='sqlitedb',
default=None,
help='The SQLite Database')
parser.add_option('-j',
'--jsonfile',
dest='jsonfile',
default=None,
help='JSON dict of table specifications')
parser.add_option('-q',
'--query',
dest='query',
default=None,
help='SQL query')
parser.add_option('-Q',
'--query_file',
dest='query_file',
default=None,
help='SQL query file')
parser.add_option('-n',
'--no_header',
dest='no_header',
default=False,
action='store_true',
help='Include a column headers line')
parser.add_option('-o',
'--output',
dest='output',
default=None,
help='Output file for query results')
(options, args) = parser.parse_args()
# determine output destination
if options.output is not None:
try:
outputPath = os.path.abspath(options.output)
outputFile = open(outputPath, 'w')
except Exception as e:
exit('Error: %s' % (e))
else:
outputFile = sys.stdout
def _create_table(ti, table):
path = table['file_path']
table_name =\
table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
comment_lines =\
table['comment_lines'] if 'comment_lines' in table else 0
comment_char =\
table['comment_char'] if 'comment_char' in table else None
column_names =\
table['column_names'] if 'column_names' in table else None
if column_names:
load_named_columns =\
table['load_named_columns']\
if 'load_named_columns' in table else False
else:
load_named_columns = False
unique_indexes = table['unique'] if 'unique' in table else []
indexes = table['index'] if 'index' in table else []
filters = table['filters'] if 'filters' in table else None
pkey_autoincr = \
table['pkey_autoincr'] if 'pkey_autoincr' in table else None
create_table(get_connection(options.sqlitedb),
path,
table_name,
pkey_autoincr=pkey_autoincr,
column_names=column_names,
skip=comment_lines,
comment_char=comment_char,
load_named_columns=load_named_columns,
filters=filters,
unique_indexes=unique_indexes,
indexes=indexes)
if options.jsonfile:
try:
fh = open(options.jsonfile)
tdef = json.load(fh)
if 'tables' in tdef:
for ti, table in enumerate(tdef['tables']):
_create_table(ti, table)
except Exception as e:
exit('Error: %s' % (e))
query = None
if (options.query_file is not None):
with open(options.query_file, 'r') as fh:
query = ''
for line in fh:
query += line
elif (options.query is not None):
query = options.query
if (query is None):
try:
describe_tables(get_connection(options.sqlitedb), outputFile)
except Exception as e:
exit('Error: %s' % (e))
else:
try:
run_query(get_connection(options.sqlitedb),
query,
outputFile,
no_header=options.no_header)
except Exception as e:
exit('Error: %s' % (e))
def __main__():
# Parse Command Line
parser = optparse.OptionParser()
parser.add_option('-s', '--sqlitedb', dest='sqlitedb', default=None,
help='The SQLite Database')
parser.add_option('-j', '--jsonfile', dest='jsonfile', default=None,
help='JSON dict of table specifications')
parser.add_option('-q', '--query', dest='query', default=None,
help='SQL query')
parser.add_option('-Q', '--query_file', dest='query_file', default=None,
help='SQL query file')
parser.add_option('-n', '--no_header', dest='no_header', default=False,
action='store_true',
help='Include a column headers line')
parser.add_option('-o', '--output', dest='output', default=None,
help='Output file for query results')
(options, args) = parser.parse_args()
# determine output destination
if options.output is not None:
try:
outputPath = os.path.abspath(options.output)
outputFile = open(outputPath, 'w')
except Exception as e:
exit('Error: %s' % (e))
else:
outputFile = sys.stdout
def _create_table(ti, table):
path = table['file_path']
table_name =\
table['table_name'] if 'table_name' in table else 't%d' % (ti + 1)
comment_lines =\
table['comment_lines'] if 'comment_lines' in table else 0
comment_char =\
table['comment_char'] if 'comment_char' in table else None
column_names =\
table['column_names'] if 'column_names' in table else None
if column_names:
load_named_columns =\
table['load_named_columns']\
if 'load_named_columns' in table else False
else:
load_named_columns = False
unique_indexes = table['unique'] if 'unique' in table else []
indexes = table['index'] if 'index' in table else []
filters = table['filters'] if 'filters' in table else None
pkey_autoincr = \
table['pkey_autoincr'] if 'pkey_autoincr' in table else None
create_table(get_connection(options.sqlitedb), path, table_name,
pkey_autoincr=pkey_autoincr,
column_names=column_names,
skip=comment_lines,
comment_char=comment_char,
load_named_columns=load_named_columns,
filters=filters,
unique_indexes=unique_indexes,
indexes=indexes)
if options.jsonfile:
try:
with open(options.jsonfile) as fh:
tdef = json.load(fh)
if 'tables' in tdef:
for ti, table in enumerate(tdef['tables']):
_create_table(ti, table)
except Exception as e:
exit('Error: %s' % (e))
query = None
if options.query_file is not None:
with open(options.query_file, 'r') as fh:
query = ''
for line in fh:
query += line
elif options.query is not None:
query = options.query
if query is None:
try:
describe_tables(get_connection(options.sqlitedb), outputFile)
except Exception as e:
exit('Error: %s' % (e))
else:
try:
run_query(get_connection(options.sqlitedb), query, outputFile,
no_header=options.no_header)
except Exception as e:
exit('Error: %s' % (e))
