<commit_before>#!/usr/bin/python

import argh
import json
import sys

import pandas as pd

from datetime import *
from sqlalchemy import create_engine, literal, or_, inspection
from os import path
from sqlalchemy.orm import sessionmaker, aliased
import sqlalchemy

try:
	from .common_classes import *
except (ValueError, SystemError):
	from common_classes import *

allowed_classes = {
	"Animal": Animal,
	"AnimalExternalIdentifier": AnimalExternalIdentifier,
	"Arena": Arena,
	"Cage": Cage,
	"CageStay": CageStay,
	"Biopsy": Biopsy,
	"BrainBiopsy": BrainBiopsy,
	"BrainExtractionProtocol": BrainExtractionProtocol,
	"DrinkingMeasurement": DrinkingMeasurement,
	"DNAExtractionProtocol": DNAExtractionProtocol,
	"Evaluation": Evaluation,
	"FluorescentMicroscopyMeasurement": FluorescentMicroscopyMeasurement,
	"FMRIMeasurement": FMRIMeasurement,
	"FMRIScannerSetup": FMRIScannerSetup,
	"FMRIAnimalPreparationProtocol": FMRIAnimalPreparationProtocol,
	"ForcedSwimTestMeasurement": ForcedSwimTestMeasurement,
	"OpenFieldTestMeasurement": OpenFieldTestMeasurement,
	"Genotype": Genotype,
	"HandlingHabituationProtocol": HandlingHabituationProtocol,
	"HandlingHabituation": HandlingHabituation,
	"Ingredient": Ingredient,
	"Irregularity": Irregularity,
	"Incubation": Incubation,
	"LaserStimulationProtocol": LaserStimulationProtocol,
	"MeasurementUnit": MeasurementUnit,
	"Operator": Operator,
	"Observation": Observation,
	"Protocol": Protocol,
	"SectioningProtocol": SectioningProtocol,
	"Substance": Substance,
	"SucrosePreferenceMeasurement": SucrosePreferenceMeasurement,
	"Solution": Solution,
	"Treatment": Treatment,
	"TreatmentProtocol": TreatmentProtocol,
	"WeightMeasurement": WeightMeasurement,
	}

def instructions(kind):
	if kind == "table_identifier":
		print("Make sure you have entered the filter value correctly. This value is supposed to refer to the id column of another table and needs to be specified as \'table_identifier\'.\'field_by_which_to_filter\'.\'target_value\'")

def get_related_id(session, engine, parameters):
	category = parameters.split(":",1)[0]
	sql_query=session.query(allowed_classes[category])
	for field_value in parameters.split(":",1)[1].split("&&"):
		field, value = field_value.split(".",1)
		if ":" in value:
			values = get_related_id(session, engine, value)
			for value in values:
				value=int(value) # the value is returned as a numpy object
				if field[-4:] == "date": # support for date entry matching (the values have to be passes as string but matched as datetime)
					value = datetime(*[int(i) for i in value.split(",")])
				sql_query = sql_query.filter(getattr(allowed_classes[category], field)==value)
		else:
			if field[-4:] == "date": # support for date entry matching (the values have to be passes as string but matched as datetime)
				value = datetime(*[int(i) for i in value.split(",")])
			sql_query = sql_query.filter(getattr(allowed_classes[category], field)==value)
	mystring = sql_query.statement
	mydf = pd.read_sql_query(mystring,engine)
	mydf = mydf.T.groupby(level=0).first().T #awkward hack to deal with polymorphic tables returning multiple IDs
	related_table_ids = mydf["id"]
	input_values = list(related_table_ids)
	if input_values == []:
		raise BaseException("No entry was found with a value of \""+str(value)+"\" on the \""+field+"\" column of the \""+category+"\" CATEGORY, in the database.")
	session.close()
	engine.dispose()
	return input_values

def simple_query(db_path, category, field, value, mask="", first=True):
	session, engine = loadSession(db_path)
	sql_query=session.query(allowed_classes[category]).filter(getattr(allowed_classes[category], field)==value)
	print(sql_query.all()[0].__repr__)
	mystring = sql_query.statement
	mydf = pd.read_sql_query(mystring,engine)
	if mask:
		mydf = mydf[mask]
	if first:
		mydf = mydf[0]
	print(mydf)
	session.close()
	engine.dispose()

def loadSession(db_path):
	db_path = "sqlite:///" + path.expanduser(db_path)
	engine = create_engine(db_path, echo=False)
	Session = sessionmaker(bind=engine)
	session = Session()
	Base.metadata.create_all(engine)
	return session, engine

def commit_and_close(session, engine):
	try:
		session.commit()
	except sqlalchemy.exc.IntegrityError:
		print("Please make sure this was not a double entry.")
	session.close()
	engine.dispose()
<commit_msg>added docstring and nice utput for missing entries<commit_after>#!/usr/bin/python

import argh
import json
import sys

import pandas as pd

from datetime import *
from sqlalchemy import create_engine, literal, or_, inspection
from os import path
from sqlalchemy.orm import sessionmaker, aliased, contains_eager
import sqlalchemy

try:
	from .common_classes import *
except (ValueError, SystemError):
	from common_classes import *

allowed_classes = {
	"Animal": Animal,
	"AnimalExternalIdentifier": AnimalExternalIdentifier,
	"Arena": Arena,
	"Cage": Cage,
	"CageStay": CageStay,
	"Biopsy": Biopsy,
	"BrainBiopsy": BrainBiopsy,
	"BrainExtractionProtocol": BrainExtractionProtocol,
	"DrinkingMeasurement": DrinkingMeasurement,
	"DNAExtractionProtocol": DNAExtractionProtocol,
	"Evaluation": Evaluation,
	"FluorescentMicroscopyMeasurement": FluorescentMicroscopyMeasurement,
	"FMRIMeasurement": FMRIMeasurement,
	"FMRIScannerSetup": FMRIScannerSetup,
	"FMRIAnimalPreparationProtocol": FMRIAnimalPreparationProtocol,
	"ForcedSwimTestMeasurement": ForcedSwimTestMeasurement,
	"OpenFieldTestMeasurement": OpenFieldTestMeasurement,
	"Genotype": Genotype,
	"HandlingHabituationProtocol": HandlingHabituationProtocol,
	"HandlingHabituation": HandlingHabituation,
	"Ingredient": Ingredient,
	"Irregularity": Irregularity,
	"Incubation": Incubation,
	"LaserStimulationProtocol": LaserStimulationProtocol,
	"MeasurementUnit": MeasurementUnit,
	"Operator": Operator,
	"Observation": Observation,
	"Protocol": Protocol,
	"SectioningProtocol": SectioningProtocol,
	"Substance": Substance,
	"SucrosePreferenceMeasurement": SucrosePreferenceMeasurement,
	"Solution": Solution,
	"Treatment": Treatment,
	"TreatmentProtocol": TreatmentProtocol,
	"WeightMeasurement": WeightMeasurement,
	}

def instructions(kind):
	if kind == "table_identifier":
		print("Make sure you have entered the filter value correctly. This value is supposed to refer to the id column of another table and needs to be specified as \'table_identifier\'.\'field_by_which_to_filter\'.\'target_value\'")

def get_related_id(session, engine, parameters):
	category = parameters.split(":",1)[0]
	sql_query=session.query(allowed_classes[category])
	for field_value in parameters.split(":",1)[1].split("&&"):
		field, value = field_value.split(".",1)
		if ":" in value:
			values = get_related_id(session, engine, value)
			for value in values:
				value=int(value) # the value is returned as a numpy object
				if field[-4:] == "date": # support for date entry matching (the values have to be passes as string but matched as datetime)
					value = datetime(*[int(i) for i in value.split(",")])
				sql_query = sql_query.filter(getattr(allowed_classes[category], field)==value)
		else:
			if field[-4:] == "date": # support for date entry matching (the values have to be passes as string but matched as datetime)
				value = datetime(*[int(i) for i in value.split(",")])
			sql_query = sql_query.filter(getattr(allowed_classes[category], field)==value)
	mystring = sql_query.statement
	mydf = pd.read_sql_query(mystring,engine)
	mydf = mydf.T.groupby(level=0).first().T #awkward hack to deal with polymorphic tables returning multiple IDs
	related_table_ids = mydf["id"]
	input_values = list(related_table_ids)
	if input_values == []:
		raise BaseException("No entry was found with a value of \""+str(value)+"\" on the \""+field+"\" column of the \""+category+"\" CATEGORY, in the database.")
	session.close()
	engine.dispose()
	return input_values

def animal_info(db_path, identifier,
	database=None,
	):
	"""Return the __str__ attribute of an Animal object query filterd by the id column OR by arguments of the external_id objects.

	Parameters
	----------

	db_path : string
	Path to a LabbookDB formatted database.

	identifier : int or string
	The identifier of the animal

	database : string or None, optional
	If specified gives a constraint on the AnimalExternalIdentifier.database colun AND truns the identifier attribute into a constraint on the AnimalExternalIdentifier.identifier column. If unspecified, the identfier argument is used as a constraint on the Animal.id column.
	"""

	session, engine = loadSession(db_path)
	sql_query = session.query(Animal)
	if database and identifier:
		sql_query = sql_query.join(Animal.external_ids)\
		.filter(AnimalExternalIdentifier.database == database).filter(AnimalExternalIdentifier.identifier == identifier)\
		.options(contains_eager('external_ids'))
	else:
		sql_query = sql_query.filter(Animal.id == identifier)
	try:
		animal = [i.__str__() for i in sql_query][0]
	except:
		if database == None:
			print("No entry was found with {id} in the Animal.id column of the database located at {loc}.".format(id=identifier,loc=db_path))
		else:
			print("No entry was found with {id} in the AnimalExternalIdentifier.identifier column and {db} in the AnimalExternalIdentifier.database column of the database located at {loc}.".format(id=identifier,db=database,loc=db_path))
	else:
		print(animal)
	session.close()
	engine.dispose()

def loadSession(db_path):
	db_path = "sqlite:///" + path.expanduser(db_path)
	engine = create_engine(db_path, echo=False)
	Session = sessionmaker(bind=engine)
	session = Session()
	Base.metadata.create_all(engine)
	return session, engine

def commit_and_close(session, engine):
	try:
		session.commit()
	except sqlalchemy.exc.IntegrityError:
		print("Please make sure this was not a double entry.")
	session.close()
	engine.dispose()

{'fraction_non_alphanumeric': 0.11262135922330097, 'fraction_numerical': 0.001941747572815534, 'mean_word_length': 6.143065693430657, 'pattern_counts': {'":': 82, '<': 3, '<?xml version=': 0, '>': 3, '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': '1510487', 'n_tokens_mistral': 3211, 'n_tokens_neox': 3007, 'n_words': 834}