def test_process_cml_args_when_statement_error(capsys):
clargs = [ 'csvsqlcli.py', '-s', 'select foo from bar;' ]
with pytest.raises(SystemExit):
csvsqlcli.csvsql_process_cml_args(clargs)
captured = capsys.readouterr()
assert captured[0] == ''
assert 'error' in captured[1].lower()
def test_process_cml_args_when_no_input_file(capsys):
clargs = [ 'csvsqlcli.py', '-s', 'select tbl_name from sqlite_master;' ]
expected_output = 'tbl_name\n'
csvsqlcli.csvsql_process_cml_args(clargs)
captured = capsys.readouterr()
assert captured[0].replace('\r','') == expected_output
assert captured[1] == ''
def test_process_cml_args_with_multiple_statements_in_one_s(tmpdir):
tmppath = pathlib.Path(str(tmpdir.realpath()))
out_path = pathlib.Path(tmppath) / 'outputfile.csv'
clargs = [ 'csvsqlcli.py',
'-s', 'create table mytable (one, two, three); insert into mytable values (1, 2, 3); select one from mytable',
'-o', str(out_path)]
csvsqlcli.csvsql_process_cml_args(clargs)
expected_output = 'one\n1\n'
assert out_path.read_text() == expected_output
def test_process_cml_args_simple_query(capsys, tmpdir):
contents = 'one,two,three\n1,2,3\n4,5,6'
fd = tmpdir.mkdir('subdir').join('myfile.csv')
fd.write(contents)
clargs = [ 'csvsqlcli.py', '-i', str(fd.realpath()), '-s', 'select one from myfile;' ]
expected_output = 'one\n1\n4\n'
csvsqlcli.csvsql_process_cml_args(clargs)
captured = capsys.readouterr()
assert captured[0].replace('\r','') == expected_output
assert captured[1] == ''
def test_process_cml_args_for_input_containing_less_headers_than_columns(tmpdir):
contents = "one,two,three\n1,2,3\n4,5,6,7\n8,9,10,11,12\n13,14"
fin = tmpdir.join('mytable.csv')
fin.write(contents)
expected_output = "one,two,three,__COL4,__COL5\n1,2,3,,\n4,5,6,7,\n8,9,10,11,12\n13,14,,,\n"
fout = tmpdir.join('outputfile.csv')
clargs = [ 'csvsqlcli.py',
'-o', str(fout.realpath()),
'-i', str(fin.realpath()),
'-s', 'select * from mytable' ]
csvsqlcli.csvsql_process_cml_args(clargs)
assert pathlib.Path(str(fout.realpath())).read_text() == expected_output
def test_process_cml_args_for_unheaded_input(tmpdir):
contents= '1,2,3\n4,5,6\n'
fin = tmpdir.join('mytable.csv')
fout = tmpdir.join('outputfile.csv')
output_file_path = pathlib.Path(str(tmpdir.realpath())) / 'outputfile.csv'
fin.write(contents)
clargs = [ 'csvsqlcli.py',
'-u', str(fin.realpath()),
'-o', str(fout.realpath()),
'-s', 'select * from mytable;' ]
expected_output = '__COL1,__COL2,__COL3\n1,2,3\n4,5,6\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert output_file_path.read_text() == expected_output
def test_process_cml_args_for_union_of_two_inputs(tmpdir):
""" This test case is addapted from querycsv original tests test_query_csv3 """
contents_foo = 'a, b, c\n1,2,3\n4,5,6\n'
fin_foo = tmpdir.join('foo.csv')
fout = tmpdir.join('outputfile.csv')
output_file_path = pathlib.Path(str(tmpdir.realpath())) / 'outputfile.csv'
fin_foo.write(contents_foo)
clargs = [ 'csvsqlcli.py',
'-i', str(fin_foo.realpath()),
'-o', str(fout.realpath()),
'-s', 'update foo set a=(a+b+c); select a from foo;' ]
expected_output = 'a\n6\n15\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert output_file_path.read_text() == expected_output
def test_process_cml_args_when_non_existing_output_file(capsys, tmpdir):
contents = 'one,two,three\n1,2,3\n4,5,6'
mysubdir = tmpdir.mkdir('subdir')
fd = mysubdir.join('myfile.csv')
fd.write(contents)
output_file_path = pathlib.Path(str(mysubdir.realpath())) / 'outputfile.csv'
clargs = [ 'csvsqlcli.py', '-i', str(fd.realpath()),
'-o', str(output_file_path),
'-s', 'select one from myfile;' ]
expected_output = 'one\n1\n4\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert output_file_path.read_text() == expected_output
captured = capsys.readouterr()
assert captured[0] == ''
assert captured[1] == ''
def test_process_cml_args_when_unheaded_output_already_exists_and_not_forced(capsys, tmpdir):
contents = 'one,two,three\n1,2,3\n4,5,6'
mysubdir = tmpdir.mkdir('subdir')
fin = mysubdir.join('myfile.csv')
fout = mysubdir.join('outputfile.csv')
fin.write(contents)
fout.write("anything")
clargs = [ 'csvsqlcli.py',
'-i', str(fin.realpath()),
'--unheadedOutput', str(fout.realpath()),
'-s', 'select one from myfile;' ]
with pytest.raises(SystemExit):
csvsqlcli.csvsql_process_cml_args(clargs)
captured = capsys.readouterr()
assert captured[0] == ''
assert captured[1] != ''
def test_process_cml_args_with_database(tmpdir):
tmppath = pathlib.Path(str(tmpdir.realpath()))
db_path = pathlib.Path(tmppath) / 'mydb.sqlite3'
out_path = pathlib.Path(tmppath) / 'outputfile.csv'
clargs = [ 'csvsqlcli.py',
'-d', str(db_path),
'-s', 'create table mytable (one, two, three);',
'-s', 'insert into mytable values (1, 2, 3);' ]
csvsqlcli.csvsql_process_cml_args(clargs)
clargs = [ 'csvsqlcli.py',
'-d', str(db_path),
'-s', 'select one from mytable;',
'-o', str(out_path)]
csvsqlcli.csvsql_process_cml_args(clargs)
expected_output = 'one\n1\n'
assert out_path.read_text() == expected_output
def test_process_cml_args_when_multiple_entries_on_same_input_option(tmpdir):
contents_en = 'one,two,three\n1,2,3\n4,5,6\n'
contents_nh = 'ichi,ni,san\n3,2,1\n6,5,4\n'
fin_en = tmpdir.join('file_en.csv')
fin_nh = tmpdir.join('file_nh.csv')
fout = tmpdir.join('outputfile.csv')
output_file_path = pathlib.Path(str(tmpdir.realpath())) / 'outputfile.csv'
fin_en.write(contents_en)
fin_nh.write(contents_nh)
clargs = [ 'csvsqlcli.py',
'-i', str(fin_en.realpath()), str(fin_nh.realpath()),
'-o', str(fout.realpath()),
'-s', 'select one+ichi as oneichi from file_en, file_nh where two = ni' ]
expected_output = 'oneichi\n4\n10\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert output_file_path.read_text() == expected_output
def test_process_cml_args_for_mixed_headed_and_unheaded_inputs(tmpdir):
contents_headed = 'one,two,three\n1,2,3\n4,5,6\n'
contents_unheaded = '1,10\n2,20\n'
fin_headed = tmpdir.join('headed.csv')
fin_unheaded = tmpdir.join('unheaded.csv')
fout = tmpdir.join('outputfile.csv')
output_file_path = pathlib.Path(str(tmpdir.realpath())) / 'outputfile.csv'
fin_headed.write(contents_headed)
fin_unheaded.write(contents_unheaded)
clargs = [ 'csvsqlcli.py',
'--input', str(fin_headed.realpath()),
'--unheaded', str(fin_unheaded.realpath()),
'--output', str(fout.realpath()),
'--statement', 'select two, __COL2 as dos from headed, unheaded where one=__COL1;' ]
expected_output = 'two,dos\n2,10\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert output_file_path.read_text() == expected_output
def test_uc_compute_scores_avg(tmpdir):
""" Input:
- students (id, name)
- scores (student_id, score01, score02, score03)
Output:
- results (student_id, name, result)
Where result = avg(score«i»)
"""
students_contents = ('id,name\n'
'gerard.funny,"Funny, Gerard"\n'
'miriam.racket, "Racket Marlopint, Miriam"\n'
'jashid.ahmed, "Ahmed Gómez, Jashid"\n'
'moises.gomez, "Gómez Girón, Moisès"\n'
) # missing in scores
scores_contents = (
'student_id,score01,score02,score03\n'
'cristina.cifuentes,34,45,51\n' # missing in students
'gerard.funny, 20,30,40\n'
'jashid.ahmed, 30,40,50\n' # different order in students
'miriam.racket, 50,60,70\n') # different order in students
sql_statement = (
'select student_id, cast(((score01 + score02 + score03) / 3) as float) as result '
'from students, scores '
'where id = student_id '
'order by id')
expected_results = ('student_id,result\n'
'gerard.funny,30.0\n'
'jashid.ahmed,40.0\n'
'miriam.racket,60.0\n')
fin_students = tmpdir.join('students.csv')
fin_students.write(students_contents)
fin_scores = tmpdir.join('scores.csv')
fin_scores.write(scores_contents)
output_file_path = pathlib.Path(str(tmpdir.realpath())) / 'results.csv'
clargs = [
'csvsqlcli.py', '-i',
str(fin_students.realpath()), '-i',
str(fin_scores.realpath()), '-o',
str(output_file_path), '-s', sql_statement
]
csvsqlcli.csvsql_process_cml_args(clargs)
assert output_file_path.read_text() == expected_results
def test_process_cml_args_when_both_headed_and_unheaded_outputs_do_not_exist(capsys, tmpdir):
contents = 'one,two,three\n1,2,3\n4,5,6'
mysubdir = tmpdir.mkdir('subdir')
fd = mysubdir.join('myfile.csv')
fd.write(contents)
headed_output_file_path = pathlib.Path(str(mysubdir.realpath())) / 'headed.csv'
unheaded_output_file_path = pathlib.Path(str(mysubdir.realpath())) / 'unheaded.csv'
clargs = [ 'csvsqlcli.py', '-i', str(fd.realpath()),
'-o', str(headed_output_file_path),
'-O', str(unheaded_output_file_path),
'-s', 'select one,three from myfile;' ]
expected_headed = 'one,three\n1,3\n4,6\n'
expected_unheaded = '1,3\n4,6\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert headed_output_file_path.read_text() == expected_headed
assert unheaded_output_file_path.read_text() == expected_unheaded
captured = capsys.readouterr()
assert captured[0] == ''
assert captured[1] == ''
def test_process_cml_args_with_database_expanded_options(tmpdir):
fin_contents = "one,two,three\n4,5,6\n"
fin = tmpdir.join('myoutertable.csv')
fin.write(fin_contents)
tmppath = pathlib.Path(str(tmpdir.realpath()))
db_path = pathlib.Path(tmppath) / 'mydb.sqlite3'
out_path = pathlib.Path(tmppath) / 'outputfile.csv'
clargs = [ 'csvsqlcli.py',
'--database', str(db_path),
'--statement', 'create table myinnertable (one, two, three);',
'--statement', 'insert into myinnertable values (1, 2, 3);' ]
csvsqlcli.csvsql_process_cml_args(clargs)
clargs = [ 'csvsqlcli.py',
'--database', str(db_path),
'--input', str(fin.realpath()),
'--statement', 'select * from myinnertable union all select * from myoutertable',
'--output', str(out_path)]
csvsqlcli.csvsql_process_cml_args(clargs)
expected_output = 'one,two,three\n1,2,3\n4,5,6\n'
assert out_path.read_text() == expected_output
def test_process_cml_args_multiple_files_in_same_f(tmpdir):
fin_contents = "one,two,three\n1,2,3\n"
fin = tmpdir.join('mytable.csv')
fin.write(fin_contents)
contents_f1 = "insert into mytable values (4, 5, 6);"
fstatements1 = tmpdir.join('statements01.sql')
fstatements1.write(contents_f1)
contents_f2 = "create table anothertable (one, two);" \
"insert into anothertable select one, two from mytable;"
fstatements2 = tmpdir.join('statements02.sql')
fstatements2.write(contents_f2)
fout = tmpdir.join('outputfile.csv')
clargs = [ 'csvsqlcli.py',
'-o', str(fout.realpath()),
'-i', str(fin.realpath()),
'-f', str(fstatements1.realpath()), str(fstatements2.realpath()),
'-s', 'select * from anothertable;',
]
expected_output = 'one,two\n1,2\n4,5\n'
csvsqlcli.csvsql_process_cml_args(clargs)
assert pathlib.Path(str(fout.realpath())).read_text() == expected_output
def test_process_cml_args_with_non_a_database(tmpdir):
db_path = tmpdir.join('fake.sqlite3')
db_path.write("Some non sqlite3 contents")
clargs = [ 'csvsqlcli.py', '-d', str(db_path.realpath()), '-s', 'pragma integrity_check;' ]
with pytest.raises(SystemExit):
csvsqlcli.csvsql_process_cml_args(clargs)
def test_process_cml_args_no_args_provided(capsys):
clargs = [ 'csvsqlcli.py' ]
with pytest.raises(SystemExit):
csvsqlcli.csvsql_process_cml_args(clargs)
