#NEWLINE# def test_rowset_tables( ) : #NEWLINE# #INDENT# cols = [ ] #NEWLINE# cols . append ( Column ( name = 'name' , columnType = 'STRING' , maximumSize = 1000 ) ) #NEWLINE# cols . append ( Column ( name = 'foo' , columnType = 'STRING' , enumValues = [ 'foo' , 'bar' , 'bat' ] ) ) #NEWLINE# cols . append ( Column ( name = 'x' , columnType = 'DOUBLE' ) ) #NEWLINE# cols . append ( Column ( name = 'age' , columnType = 'INTEGER' ) ) #NEWLINE# cols . append ( Column ( name = 'cartoon' , columnType = 'BOOLEAN' ) ) #NEWLINE# schema1 = syn . store ( Schema ( name = 'Foo Table' , columns = cols , parent = project ) ) #NEWLINE# print ( 'Table Schema:' , schema1 . id ) #NEWLINE# retrieved_cols = list ( syn . getTableColumns ( schema1 ) ) #NEWLINE# assert ( len ( retrieved_cols ) == len ( cols ) ) #NEWLINE# for ( retrieved_col , col ) in zip ( retrieved_cols , cols ) : #NEWLINE# #INDENT# assert ( retrieved_col . name == col . name ) #NEWLINE# assert ( retrieved_col . columnType == col . columnType ) #NEWLINE# #UNINDENT# data1 = [ [ 'Chris' , 'bar' , 11.23 , 45 , False ] , [ 'Jen' , 'bat' , 14.56 , 40 , False ] , [ 'Jane' , 'bat' , 17.89 , 6 , False ] , [ 'Henry' , 'bar' , 10.12 , 1 , False ] ] #NEWLINE# row_reference_set1 = syn . store ( RowSet ( columns = cols , schema = schema1 , rows = [ Row ( r ) for r in data1 ] ) ) #NEWLINE# assert ( len ( row_reference_set1 [ 'rows' ] ) == 4 ) #NEWLINE# data2 = [ [ 'Fred' , 'bat' , 21.45 , 20 , True ] , [ 'Daphne' , 'foo' , 27.89 , 20 , True ] , [ 'Shaggy' , 'foo' , 23.45 , 20 , True ] , [ 'Velma' , 'bar' , 25.67 , 20 , True ] ] #NEWLINE# syn . store ( RowSet ( columns = cols , schema = schema1 , rows = [ Row ( r ) for r in data2 ] ) ) #NEWLINE# results = syn . tableQuery ( ( 'select * from %s order by name' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# assert ( results . count == 8 ) #NEWLINE# assert ( results . tableId == schema1 . id ) #NEWLINE# expected = sorted ( ( data1 + data2 ) ) #NEWLINE# for ( expected_values , row ) in zip ( expected , results ) : #NEWLINE# #INDENT# assert ( expected_values == row [ 'values' ] ) , ( 'got %s but expected %s' % ( row [ 'values' ] , expected_values ) ) #NEWLINE# #UNINDENT# result2 = syn . tableQuery ( ( 'select * from %s where age>18 and age<30' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# rs = result2 . asRowSet ( ) #NEWLINE# for row in rs [ 'rows' ] : #NEWLINE# #INDENT# row [ 'values' ] [ 2 ] = 88.888 #NEWLINE# #UNINDENT# row_reference_set = syn . store ( rs ) #NEWLINE# result3 = syn . tableQuery ( ( 'select name, x, age from %s' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# for row in result3 : #NEWLINE# #INDENT# if ( int ( row [ 'values' ] [ 2 ] ) == 20 ) : #NEWLINE# #INDENT# assert ( row [ 'values' ] [ 1 ] == 88.888 ) #NEWLINE# #UNINDENT# #UNINDENT# bday_column = syn . store ( Column ( name = 'birthday' , columnType = 'DATE' ) ) #NEWLINE# column = syn . getColumn ( bday_column . id ) #NEWLINE# assert ( column . name == 'birthday' ) #NEWLINE# assert ( column . columnType == 'DATE' ) #NEWLINE# schema1 . addColumn ( bday_column ) #NEWLINE# schema1 = syn . store ( schema1 ) #NEWLINE# results = syn . tableQuery ( ( 'select * from %s where cartoon=false order by age' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# rs = results . asRowSet ( ) #NEWLINE# bdays = ( '2013-3-15' , '2008-1-3' , '1973-12-8' , '1969-4-28' ) #NEWLINE# for ( bday , row ) in zip ( bdays , rs . rows ) : #NEWLINE# #INDENT# row [ 'values' ] [ 5 ] = bday #NEWLINE# #UNINDENT# row_reference_set = syn . store ( result3 ) #NEWLINE# date_2008_jan_1 = utils . to_unix_epoch_time ( datetime ( 2008 , 1 , 1 ) ) #NEWLINE# results = syn . tableQuery ( ( 'select name from %s where birthday > %d order by birthday' % ( schema1 . id , date_2008_jan_1 ) ) , resultsAs = 'rowset' ) #NEWLINE# assert ( [ 'Jane' , 'Henry' ] == [ row [ 'values' ] [ 0 ] for row in results ] ) #NEWLINE# try : #NEWLINE# #INDENT# import pandas as pd #NEWLINE# df = results . asDataFrame ( ) #NEWLINE# assert all ( ( df . ix [ : , 'name' ] == [ 'Jane' , 'Henry' ] ) ) #NEWLINE# #UNINDENT# except ImportError as e1 : #NEWLINE# #INDENT# sys . stderr . write ( 'Pandas is apparently not installed, skipping part of test_rowset_tables.\n\n' ) #NEWLINE# #UNINDENT# results = syn . tableQuery ( ( 'select birthday from %s where cartoon=false order by age' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# for ( bday , row ) in zip ( bdays , results ) : #NEWLINE# #INDENT# assert ( row [ 'values' ] [ 0 ] == datetime . strptime ( bday , '%Y-%m-%d' ) ) , ( 'got %s but expected %s' % ( row [ 'values' ] [ 0 ] , bday ) ) #NEWLINE# #UNINDENT# try : #NEWLINE# #INDENT# import pandas as pd #NEWLINE# results = syn . tableQuery ( ( 'select foo, MAX(x), COUNT(foo), MIN(age) from %s group by foo order by foo' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# df = results . asDataFrame ( ) #NEWLINE# print ( df ) #NEWLINE# assert ( df . shape == ( 3 , 4 ) ) #NEWLINE# assert all ( ( df . iloc [ : , 0 ] == [ 'bar' , 'bat' , 'foo' ] ) ) #NEWLINE# assert all ( ( df . iloc [ : , 1 ] == [ 88.888 , 88.888 , 88.888 ] ) ) #NEWLINE# assert all ( ( df . iloc [ : , 2 ] == [ 3 , 3 , 2 ] ) ) #NEWLINE# #UNINDENT# except ImportError as e1 : #NEWLINE# #INDENT# sys . stderr . write ( 'Pandas is apparently not installed, skipping part of test_rowset_tables.\n\n' ) #NEWLINE# #UNINDENT# syn . delete ( syn . tableQuery ( ( 'select name from %s where cartoon = true' % schema1 . id ) , resultsAs = 'rowset' ) ) #NEWLINE# results = syn . tableQuery ( ( 'select name from %s order by birthday' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# assert ( [ 'Chris' , 'Jen' , 'Jane' , 'Henry' ] == [ row [ 'values' ] [ 0 ] for row in results ] ) #NEWLINE# results = syn . tableQuery ( ( 'select * from %s where age > 1000' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# assert ( len ( list ( results ) ) == 0 ) #NEWLINE# try : #NEWLINE# #INDENT# import pandas as pd #NEWLINE# results = syn . tableQuery ( ( 'select * from %s where age > 1000' % schema1 . id ) , resultsAs = 'rowset' ) #NEWLINE# df = results . asDataFrame ( ) #NEWLINE# assert ( df . shape [ 0 ] == 0 ) #NEWLINE# #UNINDENT# except ImportError as e1 : #NEWLINE# #INDENT# sys . stderr . write ( 'Pandas is apparently not installed, skipping part of test_rowset_tables.\n\n' )