def countWithConditionQueries():
countWithCondition = [{
'input':
'Count how many city there are where the name is Matthew ?',
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.name = 'matthew';"
}, {
'input':
'Count how many city there are where the score is greater than 2 ?',
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score > '2';"
}, {
'input':
"Combien y a t'il de client dont le nom est Jean ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT COUNT(*) FROM client WHERE client.nom = 'jean';"
}]
for test in countWithCondition:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def countQueries():
count = [{
'input': 'What is the number of the city in this database?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT COUNT(*) FROM city;'
}, {
'input': "Combien y a t'il de client ?",
'database': DATABASE_PATH + 'hotel.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT COUNT(*) FROM client;"
}, {
'input':
"Compte les nom des élève dont les nom sont BELLE",
'database':
DATABASE_PATH + 'ecole.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT COUNT(eleve.nom) FROM eleve WHERE eleve.nom = 'belle';"
}]
for test in countQueries:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def oneConditionQueries():
oneCondition = [{
'input': 'What is the emp with the name is rupinder ?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM emp WHERE emp.name = 'rupinder';"
}, {
'input':
"Show data for city where cityName is 'Pune Agra'",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT * FROM city WHERE city.cityName = 'pune agra';"
}, {
'input':
"Quel est l'age du client dont le nom est Jean ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT client.age FROM client WHERE client.nom = 'jean';"
}]
for test in oneCondition:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def test_exception():
errorTest = [
{
'input':
'Quel est le nom des reservation ?', # No table name found in sentence!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
},
{
'input': 'Affiche moi.', # No keyword found in sentence!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
},
{
'input':
'Affiche moi les étudiants', # No keyword found in sentence!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
},
{
'input': "Quel est le professeur qui enseigne la matière SVT ?",
# There is at least column `matiere` that is unreachable from table `PROFESSEUR`!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
},
{
'input': "compte les salle des élève",
# There is at least column `salle` that is unreachable from table `ELEVE`!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
}
]
for test in errorTest:
with pytest.raises(Exception):
Ln2sql(test['database'], test['language']).get_query(test['input'])
def orderByQueries():
orderBy = [{
'input':
"count how many city there are ordered by name",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score in ascending order",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}]
for test in orderBy:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def noTableFoundExceptionQueries():
noTable = [{
'input':
'Quel est le nom des reservation ?', # No table name found in sentence!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
}]
for test in noTable:
with pytest.raises(Exception):
Ln2sql(test['database'], test['language']).get_query(test['input'])
def selectAllQueries():
selectAll = [{
'input': 'List me the info of city table',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT * FROM city;'
}]
for test in selectAll:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def multiColumnsSelectQueries():
multiColumnsSelect = [{
'input': 'List all name and score of all emp',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT emp.name, emp.score FROM emp;'
}]
for test in multiColumnsSelect:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def aggregateQueries():
aggregate = [{
'input': "Quel est la moyenne d'age des eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT AVG(eleve.age) FROM eleve;"
}]
for test in aggregate:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def formquery():
if request.method == 'GET':
sentence = request.args.get('message')
print("Method calling")
return json.dumps({'query_result': Ln2sql(
database_path='database_store/developerSurvey_Schema.sql',
language_path='lang_store/english.csv',
json_output_path='./output.json',
thesaurus_path=None,
stopwords_path=None,
).get_query(sentence).replace('\n', ' ')})
def t2db():
print('inside')
sentence = request.form['sentence']
ln2sql = Ln2sql(
database_path='',
# database_path='../people.sql',
language_path='lang_store/english.csv',
userid='fdf19c90-914d-11e9-8a3a-05a1d557cb54').get_query(sentence)
connection = pg.connect("host='" + DB['host'] + "' dbname=" +
DB['dbname'] + " user="******" password='******'password'] + "'")
df = pd.read_sql_query(ln2sql, con=connection)
#print(df)
resp = jsonpify(df.values.tolist())
return (resp)
def groupByQueries():
groupBy = [{
'input':
"Quel est l'adresse et le numéro de téléphone du client dont le nom est Marc et dont l'age est supérieur à 14 groupé par adresse ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT client.adresse, client.telephone FROM client WHERE client.nom = 'marc' AND client.age > '14' GROUP BY client.adresse;"
}]
for test in groupBy:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def distinctQueries():
distinct = [{
'input': "Quels sont distinctivement les age des eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT DISTINCT eleve.age FROM eleve;"
}, {
'input': "compte distinctivement les eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT COUNT(*) FROM eleve;"
}, {
'input': "Compte distinctivement les age des eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT COUNT(DISTINCT eleve.age) FROM eleve;"
}, {
'input':
"count distinctly how many city there are ordered by name in descending and ordered by score?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"Count distinctly how many different name of city there are ordered by name in descending and ordered by score?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(DISTINCT emp.name) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"What are the distinct name of city with a score equals to 9?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT DISTINCT emp.name FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score = '9';"
}]
for test in distinct:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def oneColumnSelectQueries():
oneColumnSelect = [{
'input': 'Tell me all id from city',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT city.id FROM city;'
}, {
'input': 'What are the name of emp',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT emp.name FROM emp;'
}]
for test in oneColumnSelect:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def noKeywordFoundExceptionQueries():
noKeyword = [
{
'input': 'Affiche moi.', # No keyword found in sentence!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
},
{
'input':
'Affiche moi les étudiants', # No keyword found in sentence!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
}
]
for test in noKeyword:
with pytest.raises(Exception):
Ln2sql(test['database'], test['language']).get_query(test['input'])
def noLinkForJoinFoundExceptionQueries():
noLink = [
{
'input': "Quel est le professeur qui enseigne la matière SVT ?",
# There is at least column `matiere` that is unreachable from table `PROFESSEUR`!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
},
{
'input': "compte les salle des élève",
# There is at least column `salle` that is unreachable from table `ELEVE`!
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv'
}
]
for test in noLink:
with pytest.raises(Exception):
Ln2sql(test['database'], test['language']).get_query(test['input'])
def lanch_parsing(self):
try:
thesaurus_path = None
if str(self.thesaurus_path_label["text"]) != "No thesaurus selected...":
thesaurus_path = str(self.thesaurus_path_label["text"])
if (str(self.database_path_label["text"]) != "No SQL dump selected...") and (
str(self.language_path_label["text"]) != "No configuration file selected...") and (
str(self.input_sentence_string.get()) != "Enter a sentence..."):
Ln2sql(self.database_path_label["text"],
self.language_path_label["text"], thesaurus_path=thesaurus_path,
json_output_path='./output.json').get_query(self.input_sentence_string.get())
showinfo('Result', 'Parsing done!')
else:
showwarning('Warning', 'You must fill in all fields, please.')
except Exception as e:
showwarning('Error', e)
return
def budchat():
resp = rephrase(request.json['sentence'], request.json['user_id'], model)
print(resp)
if (resp['Class'] == 'Prediction'):
connection = pg.connect("host='" + DB['host'] + "' dbname=" +
DB['dbname'] + " user="******" password='******'password'] + "'")
df = pd.read_sql_query(
"select column_name from column_meta where table_meta_id='" +
request.json['table_meta_id'] + "';",
con=connection)
resp['Columns'] = df['column_name'].values.tolist()
return Response(response=json.dumps(resp), status=200)
else:
try:
ln2sql = Ln2sql(database_path='',
language_path='lang_store/english.csv',
userid=resp['user_id']).get_query(
resp['Mapped Query'])
connection = pg.connect("host='" + DB['host'] + "' dbname=" +
DB['dbname'] + " user="******" password='******'password'] + "'")
df = pd.read_sql_query(ln2sql, con=connection)
res = {'data': df.head(20).values.tolist(), 'Reply': 'Here you go'}
if (len(res['data']) == 0):
response = {
'Reply': 'We are not able to find any relevant Data'
}
return Response(response=json.dumps(response), status=200)
res['data'].insert(0, df.columns.tolist())
resp = jsonpify(res)
return (resp)
except Exception as e:
print(e)
response = chat(resp['Mapped Query'], resp['user_id'], st)
return Response(response=json.dumps(response), status=200)
def multiConditionQueries():
multiCondition = [{
'input':
"Show data for city where cityName is not Pune and id like 1",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT * FROM city WHERE city.cityName != 'pune' AND city.id LIKE '%1%';"
}, {
'input':
"Quel est l'adresse du client dont le nom est Jean et dont l'age est supérieur à 14 ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT client.adresse FROM client WHERE client.nom = 'jean' AND client.age > '14';"
}]
for test in multiCondition:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
def joinQueries():
join = [{
'input':
'What is the cityName and the score of the emp whose name is matthew',
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'matthew';"
}, {
'input':
"What is the cityName and the score of the emp whose name is rupinder",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'rupinder';"
}]
for test in join:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']
from ln2sql.ln2sql import Ln2sql
# database_path='database_store/school.sql',
#database_path='../ism.sql',
sql = ln2sql = Ln2sql(
database_path='database_store/ism.sql',
language_path='lang_store/english.csv',
json_output_path=None,
thesaurus_path=None,
stopwords_path=None,
).get_query("what is Symptom and CategoryName for all Incident whose ID is 1")
print("gen:")
print(sql)
def test_main():
thesaurusTest = [
{
'input': "Compte le nombre d'étudiant",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT COUNT(*) FROM eleve;"
},
{
'input': "Compte le nombre des dénomination des étudiant",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT COUNT(eleve.nom) FROM eleve;"
},
{
'input': "Quelles sont les ancienneté et les dénomination des élève ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT eleve.age, eleve.nom FROM eleve;"
},
{
'input': "Quelles sont les ancienneté et les dénomination des étudiant ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT eleve.age, eleve.nom FROM eleve;"
},
{
'input': "Quelles sont les salle des classe",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT classe.salle FROM classe;"
},
{
'input': "Quelles sont les salle des cours",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT classe.salle FROM classe;"
},
{
'input': "Quelles sont les pièce des cours",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT classe.salle FROM classe;"
},
{
'input': "Compte les dénomination des étudiant dont les dénomination sont BELLE",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT COUNT(eleve.nom) FROM eleve WHERE eleve.nom = 'belle';"
},
{
'input': "Compte les dénomination des étudiant dont les dénomination sont BELLE et l'ancienneté est 25",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT COUNT(eleve.nom) FROM eleve WHERE eleve.nom = 'belle' AND eleve.age = '25';"
}
]
thesaurusTest2 = [
{
'input': "Quel est le cours où la pièce est B45",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'thesaurus': THESAURUS_PATH + 'th_french.dat',
'output': "SELECT * FROM classe WHERE classe.salle = 'b45';"
}
]
for test in thesaurusTest:
assert _clean_output(Ln2sql(
test['database'], test['language'], thesaurus_path=test['thesaurus']
).get_query(test['input'])) == test['output']
def test_main():
correctTest = [{
'input': 'List me the info of city table',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT * FROM city;'
}, {
'input': 'What is the number of the city in this database?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT COUNT(*) FROM city;'
}, {
'input': 'Tell me all id from city',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT city.id FROM city;'
}, {
'input': 'What are the name of emp',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT emp.name FROM emp;'
}, {
'input': 'List all name and score of all emp',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': 'SELECT emp.name, emp.score FROM emp;'
}, {
'input':
'Count how many city there are where the name is Matthew ?',
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.name = 'matthew';"
}, {
'input': 'What is the emp with the name is rupinder ?',
'database': DATABASE_PATH + 'city.sql',
'language': LANG_PATH + 'english.csv',
'output': "SELECT * FROM emp WHERE emp.name = 'rupinder';"
}, {
'input':
'What is the cityName and the score of the emp whose name is matthew',
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'matthew';"
}, {
'input':
'Count how many city there are where the score is greater than 2 ?',
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score > '2';"
}, {
'input':
"Show data for city where cityName is 'Pune Agra'",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT * FROM city WHERE city.cityName = 'pune agra';"
}, {
'input':
"Show data for city where cityName is not Pune and id like 1",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT * FROM city WHERE city.cityName != 'pune' AND city.id LIKE '%1%';"
}, {
'input':
"What is the cityName and the score of the emp whose name is rupinder",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT city.cityName, emp.score FROM emp INNER JOIN city ON emp.cityId = city.id WHERE emp.name = 'rupinder';"
}, {
'input':
"count how many city there are ordered by name",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score in ascending order",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"count how many city there are ordered by name in descending order and ordered by score?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input': "Combien y a t'il de client ?",
'database': DATABASE_PATH + 'hotel.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT COUNT(*) FROM client;"
}, {
'input':
"Combien y a t'il de client dont le nom est Jean ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT COUNT(*) FROM client WHERE client.nom = 'jean';"
}, {
'input':
"Quel est l'age du client dont le nom est Jean ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT client.age FROM client WHERE client.nom = 'jean';"
}, {
'input':
"Quel est l'adresse du client dont le nom est Jean et dont l'age est supérieur à 14 ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT client.adresse FROM client WHERE client.nom = 'jean' AND client.age > '14';"
}, {
'input':
"Quel est l'adresse et le numéro de téléphone du client dont le nom est Marc et "
"dont l'age est supérieur à 14 groupé par adresse ?",
'database':
DATABASE_PATH + 'hotel.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT client.adresse, client.telephone FROM client WHERE client.nom = 'marc' AND c"
"lient.age > '14' GROUP BY client.adresse;"
}, {
'input': "Quel est la moyenne d'age des eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT AVG(eleve.age) FROM eleve;"
}, {
'input': "Quels sont distinctivement les age des eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT DISTINCT eleve.age FROM eleve;"
}, {
'input': "compte distinctivement les eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT COUNT(*) FROM eleve;"
}, {
'input': "Compte distinctivement les age des eleve ?",
'database': DATABASE_PATH + 'ecole.sql',
'language': LANG_PATH + 'french.csv',
'output': "SELECT COUNT(DISTINCT eleve.age) FROM eleve;"
}, {
'input':
"count distinctly how many city there are ordered by name in descending and ordered by score?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(*) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"Count distinctly how many different name of city there are ordered by name in descending and ordered by score?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT COUNT(DISTINCT emp.name) FROM city INNER JOIN emp ON city.id = emp.cityId ORDER BY emp.name DESC, emp.score ASC;"
}, {
'input':
"What are the distinct name of city with a score equals to 9?",
'database':
DATABASE_PATH + 'city.sql',
'language':
LANG_PATH + 'english.csv',
'output':
"SELECT DISTINCT emp.name FROM city INNER JOIN emp ON city.id = emp.cityId WHERE emp.score = '9';"
}, {
'input':
"Compte les nom des élève dont les nom sont BELLE",
'database':
DATABASE_PATH + 'ecole.sql',
'language':
LANG_PATH + 'french.csv',
'output':
"SELECT COUNT(eleve.nom) FROM eleve WHERE eleve.nom = 'belle';"
}]
for test in correctTest:
assert _clean_output(
Ln2sql(test['database'], test['language']).get_query(
test['input'])) == test['output']