def test_query(self):
args = [
'--query',
'select m.usda_id, avg(i.sepal_length) as mean_sepal_length from iris as i join irismeta as m on (i.species = m.species) group by m.species',
'examples/iris.csv', 'examples/irismeta.csv'
]
output_file = six.StringIO()
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
utility = CSVSQL(args, output_file)
utility.main()
sql = output_file.getvalue()
if six.PY2:
self.assertTrue('usda_id,mean_sepal_length' in sql)
self.assertTrue('IRSE,5.006' in sql)
self.assertTrue('IRVE2,5.936' in sql)
self.assertTrue('IRVI,6.588' in sql)
else:
self.assertTrue('usda_id,mean_sepal_length' in sql)
self.assertTrue('IRSE,5.005' in sql)
self.assertTrue('IRVE2,5.936' in sql)
self.assertTrue('IRVI,6.587' in sql)
def test_geojson_no_inference(self):
input_file = six.StringIO(
'{"a": 1, "b": 2, "type": "FeatureCollection", "features": [{"geometry": {}, "properties": {"a": 1, "b": 2, "c": 3}}]}'
)
with stdin_as_string(input_file):
self.assertLines(["--no-inference", "-f", "geojson"], ["id,a,b,c,geojson", ",1,2,3,{}"])
def test_stdin(self):
input_file = six.StringIO('select cast(3.1415 * 13.37 as integer) as answer')
with stdin_as_string(input_file):
csv = self.get_output([])
self.assertTrue('answer' in csv)
self.assertTrue('42' in csv)
def test_geojson_no_inference(self):
input_file = six.StringIO('{"a": 1, "b": 2, "type": "FeatureCollection", "features": [{"geometry": {}, "properties": {"a": 1, "b": 2, "c": 3}}]}')
with stdin_as_string(input_file):
self.assertLines(['--no-inference', '-f', 'geojson'], [
'id,a,b,c,geojson,type,longitude,latitude',
',1,2,3,{},,,',
])
def test_fixed_no_inference(self):
input_file = six.StringIO(' 1 2 3')
with stdin_as_string(input_file):
self.assertLines(['--no-inference', '-f', 'fixed', '--schema', 'examples/testfixed_schema_no_inference.csv'], [
'a,b,c',
'1,2,3',
])
def test_query_empty(self):
input_file = six.StringIO()
with stdin_as_string(input_file):
output = self.get_output(['--query', 'SELECT 1'])
self.assertEqual(output, '1\n1\n')
input_file.close()
def test_csv_datetime_inference(self):
input_file = six.StringIO('a\n2015-01-01T00:00:00Z')
with stdin_as_string(input_file):
self.assertLines(['-f', 'csv'], [
'a',
'2015-01-01T00:00:00+00:00',
])
def test_csv_datetime_inference(self):
input_file = six.StringIO('a\n2015-01-01T00:00:00Z')
with stdin_as_string(input_file):
self.assertLines(['-f', 'csv'], [
'a',
'2015-01-01T00:00:00+00:00',
])
def test_stdin_and_filename(self):
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
sql = self.get_output(['-', 'examples/dummy.csv'])
self.assertTrue('CREATE TABLE stdin' in sql)
self.assertTrue('CREATE TABLE dummy' in sql)
def test_ndjson_no_inference(self):
input_file = six.StringIO('{"a": 1, "b": 2, "c": 3}')
with stdin_as_string(input_file):
self.assertLines(['--no-inference', '-f', 'ndjson'], [
'a,b,c',
'1,2,3',
])
def test_ndjson_no_inference(self):
input_file = six.StringIO('{"a": 1, "b": 2, "c": 3}')
with stdin_as_string(input_file):
self.assertLines(['--no-inference', '-f', 'ndjson'], [
'a,b,c',
'1,2,3',
])
def test_doublequote(self):
input_file = six.StringIO('a\n"a ""quoted"" string"')
with stdin_as_string(input_file):
self.assertLines(['-P', '#', '-B'], [
'a',
'a #"quoted#" string',
])
def test_escapechar(self):
input_file = six.StringIO('a,b,c\n1"2,3,4\n')
with stdin_as_string(input_file):
self.assertLines(['-P', '#', '-U', '3'], [
'a,b,c',
'1#"2,3,4',
])
def test_stdin_with_query(self):
input_file = six.StringIO('select cast(3.1415 * 13.37 as integer) as answer')
with stdin_as_string(input_file):
csv = self.get_output(['--query', 'select 6*9 as question'])
self.assertTrue('question' in csv)
self.assertTrue('42' not in csv)
def test_quotechar(self):
input_file = six.StringIO('a,b,c\n1*2,3,4\n')
with stdin_as_string(input_file):
self.assertLines(['-Q', '*'], [
'a,b,c',
'*1**2*,3,4',
])
def test_query_empty(self):
input_file = six.StringIO()
with stdin_as_string(input_file):
output = self.get_output(['--query', 'SELECT 1'])
self.assertEqual(output, '1\n1\n')
input_file.close()
def test_stdin_with_query(self):
input_file = six.StringIO("select cast(3.1415 * 13.37 as integer) as answer")
with stdin_as_string(input_file):
csv = self.get_output(["--query", "select 6*9 as question"])
self.assertTrue("question" in csv)
self.assertTrue("42" not in csv)
def test_stdin_and_filename(self):
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
sql = self.get_output(['-', 'examples/dummy.csv'])
self.assertTrue('CREATE TABLE stdin' in sql)
self.assertTrue('CREATE TABLE dummy' in sql)
def test_escapechar(self):
input_file = six.StringIO('a,b,c\n1"2,3,4\n')
with stdin_as_string(input_file):
self.assertLines(['-P', '#', '-U', '3'], [
'a,b,c',
'1#"2,3,4',
])
def test_quoting(self):
input_file = six.StringIO('a,b,c\n1*2,3,4\n')
with stdin_as_string(input_file):
self.assertLines(['-Q', '*', '-U', '0', '-B'], [
'a,b,c',
'*1**2*,3,4',
])
def test_stdin(self):
input_file = six.StringIO('a,b,c\n4,5,6\n1,2,3\n')
with stdin_as_string(input_file):
self.assertLines([], [
'a,b,c',
'1,2,3',
'4,5,6',
])
def test_stdin(self):
input_file = six.StringIO(
'select cast(3.1415 * 13.37 as integer) as answer')
with stdin_as_string(input_file):
csv = self.get_output([])
self.assertTrue('answer' in csv)
self.assertTrue('42' in csv)
def test_stdin_with_query(self):
input_file = six.StringIO(
'select cast(3.1415 * 13.37 as integer) as answer')
with stdin_as_string(input_file):
csv = self.get_output(['--query', 'select 6*9 as question'])
self.assertTrue('question' in csv)
self.assertTrue('42' not in csv)
def test_stdin(self):
input_file = six.StringIO('a,b,c\n1,2,3\n4,5,6\n')
with stdin_as_string(input_file):
self.assertLines([], [
'| a | b | c |',
'| - | - | - |',
'| 1 | 2 | 3 |',
'| 4 | 5 | 6 |',
])
def test_stdin(self):
input_file = six.StringIO('a,b,c\n1,2,3\n')
with stdin_as_string(input_file):
sql = self.get_output(['--table', 'foo'])
self.assertTrue('CREATE TABLE foo' in sql)
self.assertTrue('a INTEGER NOT NULL' in sql)
self.assertTrue('b INTEGER NOT NULL' in sql)
self.assertTrue('c INTEGER NOT NULL' in sql)
def test_geojson_no_inference(self):
input_file = six.StringIO(
'{"a": 1, "b": 2, "type": "FeatureCollection", "features": [{"geometry": {}, "properties": {"a": 1, "b": 2, "c": 3}}]}'
)
with stdin_as_string(input_file):
self.assertLines(['--no-inference', '-f', 'geojson'], [
'id,a,b,c,geojson',
',1,2,3,{}',
])
def test_stdin(self):
input_file = six.StringIO('a,b,c\n1,2,3\n')
with stdin_as_string(input_file):
sql = self.get_output(['--table', 'foo'])
self.assertTrue('CREATE TABLE foo' in sql)
self.assertTrue('a INTEGER NOT NULL' in sql)
self.assertTrue('b INTEGER NOT NULL' in sql)
self.assertTrue('c INTEGER NOT NULL' in sql)
def test_quotechar(self):
input_file = six.StringIO('a,b,c\n1*2,3,4\n')
with stdin_as_string(input_file):
self.assertLines(['-Q', '*'], [
'a,b,c',
'*1**2*,3,4',
])
input_file.close()
def test_doublequote(self):
input_file = six.StringIO('a\n"a ""quoted"" string"')
with stdin_as_string(input_file):
self.assertLines(['-P', '#', '-B'], [
'a',
'a #"quoted#" string',
])
input_file.close()
def test_empty_with_query(self):
input_file = six.StringIO()
with stdin_as_string(input_file):
output_file = six.StringIO()
utility = CSVSQL(['--query', 'select 1'], output_file)
utility.run()
output_file.close()
input_file.close()
def test_stdin_with_file_and_query(self):
input_file = six.StringIO('select cast(3.1415 * 13.37 as integer) as answer')
with stdin_as_string(input_file):
csv = self.get_output(['examples/test.sql', '--query', 'select 6*9 as question'])
self.assertTrue('question' in csv)
self.assertTrue('54' in csv)
input_file.close()
def test_empty_with_query(self):
input_file = six.StringIO()
with stdin_as_string(input_file):
output_file = six.StringIO()
utility = CSVSQL(['--query', 'select 1'], output_file)
utility.run()
output_file.close()
input_file.close()
def test_stdin_with_file_and_query(self):
input_file = six.StringIO('select cast(3.1415 * 13.37 as integer) as answer')
with stdin_as_string(input_file):
csv = self.get_output(['examples/test.sql', '--query', 'select 6*9 as question'])
self.assertTrue('question' in csv)
self.assertTrue('54' in csv)
input_file.close()
def test_fixed_no_inference(self):
input_file = six.StringIO(' 1 2 3')
with stdin_as_string(input_file):
self.assertLines([
'--no-inference', '-f', 'fixed', '--schema',
'examples/testfixed_schema_no_inference.csv'
], [
'a,b,c',
'1,2,3',
])
def test_stdin(self):
output_file = StringIO()
input_file = StringIO("select cast(3.1415 * 13.37 as integer) as answer")
with stdin_as_string(input_file):
utility = SQL2CSV([], output_file)
utility.main()
csv = output_file.getvalue()
self.assertTrue("answer" in csv)
self.assertTrue("42" in csv)
def test_out_quoting_no_header_row(self):
input_file = six.StringIO('4,5,6,7\na,2,3,d\n8,9,,z\n')
with stdin_as_string(input_file):
self.assertLines(['-U', '2', '--no-header-row'], [
'"4",5,6,"7"',
'"a",2,3,"d"',
'"8",9,"","z"',
])
input_file.close()
def test_stdin(self):
output_file = six.StringIO()
input_file = six.StringIO("select cast(3.1415 * 13.37 as integer) as answer")
with stdin_as_string(input_file):
utility = SQL2CSV([], output_file)
utility.main()
csv = output_file.getvalue()
self.assertTrue('answer' in csv)
self.assertTrue('42' in csv)
def test_stdin_with_query(self):
args = ["--query", "select 6*9 as question"]
output_file = StringIO()
input_file = StringIO("select cast(3.1415 * 13.37 as integer) as answer")
with stdin_as_string(input_file):
utility = SQL2CSV(args, output_file)
utility.main()
csv = output_file.getvalue()
self.assertTrue("question" in csv)
self.assertTrue("42" not in csv)
def test_stdin(self):
input_file = six.StringIO('a,b,c\n4,2,3\n')
with stdin_as_string(input_file):
sql = self.get_output(['--tables', 'foo'])
self.assertTrue('CREATE TABLE foo' in sql)
self.assertTrue('a DECIMAL NOT NULL' in sql)
self.assertTrue('b DECIMAL NOT NULL' in sql)
self.assertTrue('c DECIMAL NOT NULL' in sql)
input_file.close()
def test_stdin_with_query(self):
args = ['--query', 'select 6*9 as question']
output_file = six.StringIO()
input_file = six.StringIO("select cast(3.1415 * 13.37 as integer) as answer")
with stdin_as_string(input_file):
utility = SQL2CSV(args, output_file)
utility.main()
csv = output_file.getvalue()
self.assertTrue('question' in csv)
self.assertTrue('42' not in csv)
def test_stdin_with_query(self):
args = ['--query', 'select 6*9 as question']
output_file = six.StringIO()
input_file = six.StringIO("select cast(3.1415 * 13.37 as integer) as answer")
with stdin_as_string(input_file):
utility = SQL2CSV(args, output_file)
utility.main()
csv = output_file.getvalue()
self.assertTrue('question' in csv)
self.assertTrue('42' not in csv)
def test_query(self):
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
sql = self.get_output(['--query', 'SELECT m.usda_id, avg(i.sepal_length) AS mean_sepal_length FROM iris AS i JOIN irismeta AS m ON (i.species = m.species) GROUP BY m.species', 'examples/iris.csv', 'examples/irismeta.csv'])
self.assertTrue('usda_id,mean_sepal_length' in sql)
self.assertTrue('IRSE,5.00' in sql)
self.assertTrue('IRVE2,5.936' in sql)
self.assertTrue('IRVI,6.58' in sql)
input_file.close()
def test_stdin(self):
input_file = six.StringIO('a,b,c\n4,2,3\n')
with stdin_as_string(input_file):
sql = self.get_output(['--tables', 'foo'])
self.assertTrue('CREATE TABLE foo' in sql)
self.assertTrue('a DECIMAL NOT NULL' in sql)
self.assertTrue('b DECIMAL NOT NULL' in sql)
self.assertTrue('c DECIMAL NOT NULL' in sql)
input_file.close()
def test_out_quoting(self):
input_file = six.StringIO(
'4,b,6,d,e\na,2,3,d,NA\n8,9,,z,10\n1.1,2.0,3.00,,11\n')
with stdin_as_string(input_file):
self.assertLines(['-U', '2'], [
'"4","b","6","d","e"',
'"a",2,3,"d","NA"',
'"8",9,"","z","10"',
'"1.1",2.0,3.00,"","11"',
])
input_file.close()
def test_stdin(self):
input_file = six.StringIO('a,b,c\n4,2,3\n')
with stdin_as_string(input_file):
sql = self.get_output(['--tables', 'foo'])
self.assertEqual(sql.replace('\t', ' '), '''CREATE TABLE foo (
a DECIMAL NOT NULL,
b DECIMAL NOT NULL,
c DECIMAL NOT NULL
);
''') # noqa
input_file.close()
def test_quoting(self):
args = ['-Q', '*', '-U', '0', '-B']
output_file = six.StringIO()
input_file = six.StringIO('a,b,c\n1*2,3,4\n')
with stdin_as_string(input_file):
utility = CSVFormat(args, output_file)
utility.main()
lines = output_file.getvalue().split('\n')
self.assertEqual(lines[0], 'a,b,c')
self.assertEqual(lines[1], '*1**2*,3,4')
def test_escapechar(self):
args = ['-P', '#', '-U', '3']
output_file = six.StringIO()
input_file = six.StringIO('a,b,c\n1"2,3,4\n')
with stdin_as_string(input_file):
utility = CSVFormat(args, output_file)
utility.main()
lines = output_file.getvalue().split('\n')
self.assertEqual(lines[0], 'a,b,c')
self.assertEqual(lines[1], '1#"2,3,4')
def test_stdin(self):
input_file = six.StringIO('a,b,c\n4,2,3\n')
with stdin_as_string(input_file):
sql = self.get_output(['--tables', 'foo'])
self.assertEqual(sql.replace('\t', ' '), '''CREATE TABLE foo (
a DECIMAL NOT NULL,
b DECIMAL NOT NULL,
c DECIMAL NOT NULL
);
''') # noqa
input_file.close()
def test_stdin_and_filename(self):
args = ['examples/dummy.csv']
output_file = six.StringIO()
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
utility = CSVSQL(args, output_file)
utility.main()
sql = output_file.getvalue()
self.assertTrue('CREATE TABLE stdin' in sql)
self.assertTrue('CREATE TABLE dummy' in sql)
def test_stdin_and_filename(self):
args = ['examples/dummy.csv']
output_file = six.StringIO()
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
utility = CSVSQL(args, output_file)
utility.main()
sql = output_file.getvalue()
self.assertTrue('CREATE TABLE stdin' in sql)
self.assertTrue('CREATE TABLE dummy' in sql)
def test_escapechar(self):
args = ['-P', '#', '-U', '3']
output_file = six.StringIO()
input_file = six.StringIO('a,b,c\n1"2,3,4\n')
with stdin_as_string(input_file):
utility = CSVFormat(args, output_file)
utility.main()
lines = output_file.getvalue().split('\n')
self.assertEqual(lines[0], 'a,b,c')
self.assertEqual(lines[1], '1#"2,3,4')
def test_quoting(self):
args = ['-Q', '*', '-U', '0', '-B']
output_file = six.StringIO()
input_file = six.StringIO('a,b,c\n1*2,3,4\n')
with stdin_as_string(input_file):
utility = CSVFormat(args, output_file)
utility.main()
lines = output_file.getvalue().split('\n')
self.assertEqual(lines[0], 'a,b,c')
self.assertEqual(lines[1], '*1**2*,3,4')
def test_stdin(self):
args = ['--table', 'foo']
output_file = six.StringIO()
input_file = six.StringIO('a,b,c\n1,2,3\n')
with stdin_as_string(input_file):
utility = CSVSQL(args, output_file)
utility.main()
sql = output_file.getvalue()
self.assertTrue('CREATE TABLE foo' in sql)
self.assertTrue('a INTEGER NOT NULL' in sql)
self.assertTrue('b INTEGER NOT NULL' in sql)
self.assertTrue('c INTEGER NOT NULL' in sql)
def test_query(self):
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
sql = self.get_output(['--query', 'select m.usda_id, avg(i.sepal_length) as mean_sepal_length from iris as i join irismeta as m on (i.species = m.species) group by m.species', 'examples/iris.csv', 'examples/irismeta.csv'])
if six.PY2:
self.assertTrue('usda_id,mean_sepal_length' in sql)
self.assertTrue('IRSE,5.006' in sql)
self.assertTrue('IRVE2,5.936' in sql)
self.assertTrue('IRVI,6.588' in sql)
else:
self.assertTrue('usda_id,mean_sepal_length' in sql)
self.assertTrue('IRSE,5.005' in sql)
self.assertTrue('IRVE2,5.936' in sql)
self.assertTrue('IRVI,6.587' in sql)
def test_stdin(self):
args = ['--table', 'foo']
output_file = six.StringIO()
input_file = six.StringIO('a,b,c\n1,2,3\n')
with stdin_as_string(input_file):
utility = CSVSQL(args, output_file)
utility.main()
sql = output_file.getvalue()
self.assertTrue('CREATE TABLE foo' in sql)
self.assertTrue('a INTEGER NOT NULL' in sql)
self.assertTrue('b INTEGER NOT NULL' in sql)
self.assertTrue('c INTEGER NOT NULL' in sql)
def test_query(self):
input_file = six.StringIO("a,b,c\n1,2,3\n")
with stdin_as_string(input_file):
sql = self.get_output([
'--query',
'SELECT m.usda_id, avg(i.sepal_length) AS mean_sepal_length FROM iris AS i JOIN irismeta AS m ON (i.species = m.species) GROUP BY m.species',
'examples/iris.csv', 'examples/irismeta.csv'
])
self.assertTrue('usda_id,mean_sepal_length' in sql)
self.assertTrue('IRSE,5.00' in sql)
self.assertTrue('IRVE2,5.936' in sql)
self.assertTrue('IRVI,6.58' in sql)
input_file.close()
def test_empty_with_query(self):
with stdin_as_string(six.StringIO()):
utility = CSVSQL(['--query', 'select 1'], six.StringIO())
utility.main()
