<commit_before># Python 3
import psycopg2
import psycopg2.extras

# Database user credentials
DATABASE = ""
USER	 = ""

def query(parsed_url):
	'''
		Handle parsed URL data and query the database
		as appropriate
	'''
	if 'type' in parsed_url.keys():
		results = retrieve_by_type(
			parsed_url['device_id'],
			parsed_url['start_time'],
			parsed_url['end_time'],
			parsed_url['type'],
		)
		header = ['time', parsed_url['type']]
	elif 'start_time' in parsed_url.keys():
		results = retrieve_within_timeframe(
			parsed_url['device_id'],
			parsed_url['start_time'],
			parsed_url['end_time']
		)
		header = ['time', 'I', 'W', 'V', 'T']
	elif 'device_id' in parsed_url.keys():
		results = retrieve_historical(parsed_url['device_id'])
		header = ['time', 'I', 'W', 'V', 'T']
	else:
		raise Exception("Recieved malform URL data")

	return format_data(header, results)

def retrieve_by_type(device_id, start_time, end_time, data_type):
	'''
	   Return sensor data of a specific type for a device
	   within a specified timeframe
	'''
	query = "SELECT time, data FROM data_raw WHERE serial = %s AND time BETWEEN to_timestamp(%s) AND to_timestamp(%s) AND type = %s;"
	params = (device_id, start_time, end_time, data_type)
	rows = perform_query(query, params)
	return rows

def retrieve_within_timeframe(device_id, start_time, end_time):
	'''
	   Return sensor data for a device within a specified timeframe
	'''
	query = write_crosstab("WHERE serial = %s AND time BETWEEN to_timestamp(%s) AND to_timestamp(%s)")
	params = (device_id, start_time, end_time)
	rows = perform_query(query, params)
	return rows

def retrieve_historical(device_id):
	'''
	   Return sensor data for a specific device
	   TODO: add a page size limit?
	'''
	query = write_crosstab("WHERE serial = %s")
	params = (device_id, )
	rows = perform_query(query, params)
	return rows

def write_crosstab(where):
	'''
	   Write a PostgreSQL crosstab() query to create a pivot table
	   and rearrage the data into a more useful form
	'''
	query = "SELECT * FROM crosstab(" +\
				"'SELECT time, type, data from data_raw " + where + "'," +\
			    " 'SELECT unnest(ARRAY[''I'', ''W'', ''V'', ''T''])') " + \
            "AS ct_result(time TIMESTAMP, I SMALLINT, W SMALLINT, V SMALLINT, T SMALLINT);"<commit_msg>Spaces -> Tabs<commit_after># Python 3
import psycopg2
import psycopg2.extras

# Database user credentials
DATABASE = ""
USER	 = ""

def query(parsed_url):
	'''
		Handle parsed URL data and query the database
		as appropriate
	'''
	if 'type' in parsed_url.keys():
		results = retrieve_by_type(
			parsed_url['device_id'],
			parsed_url['start_time'],
			parsed_url['end_time'],
			parsed_url['type'],
		)
		header = ['time', parsed_url['type']]
	elif 'start_time' in parsed_url.keys():
		results = retrieve_within_timeframe(
			parsed_url['device_id'],
			parsed_url['start_time'],
			parsed_url['end_time']
		)
		header = ['time', 'I', 'W', 'V', 'T']
	elif 'device_id' in parsed_url.keys():
		results = retrieve_historical(parsed_url['device_id'])
		header = ['time', 'I', 'W', 'V', 'T']
	else:
		raise Exception("Recieved malform URL data")

	return format_data(header, results)

def retrieve_by_type(device_id, start_time, end_time, data_type):
	'''
	   Return sensor data of a specific type for a device
	   within a specified timeframe
	'''
	query = "SELECT time, data FROM data_raw WHERE serial = %s AND time BETWEEN to_timestamp(%s) AND to_timestamp(%s) AND type = %s;"
	params = (device_id, start_time, end_time, data_type)
	rows = perform_query(query, params)
	return rows

def retrieve_within_timeframe(device_id, start_time, end_time):
	'''
	   Return sensor data for a device within a specified timeframe
	'''
	query = write_crosstab("WHERE serial = %s AND time BETWEEN to_timestamp(%s) AND to_timestamp(%s)")
	params = (device_id, start_time, end_time)
	rows = perform_query(query, params)
	return rows

def retrieve_historical(device_id):
	'''
	   Return sensor data for a specific device
	   TODO: add a page size limit?
	'''
	query = write_crosstab("WHERE serial = %s")
	params = (device_id, )
	rows = perform_query(query, params)
	return rows

def write_crosstab(where):
	'''
	   Write a PostgreSQL crosstab() query to create a pivot table
	   and rearrage the data into a more useful form
	'''
	query = "SELECT * FROM crosstab(" +\
				"'SELECT time, type, data from data_raw " + where + "'," +\
				" 'SELECT unnest(ARRAY[''I'', ''W'', ''V'', ''T''])') " + \
			"AS ct_result(time TIMESTAMP, I SMALLINT, W SMALLINT, V SMALLINT, T SMALLINT);"
{'fraction_non_alphanumeric': 0.13462811741337452, 'fraction_numerical': 0.0013242109909512249, 'mean_word_length': 4.553921568627451, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 4, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '168377', 'n_tokens_mistral': 1657, 'n_tokens_neox': 1544, 'n_words': 454}