def get_paths(self, expnum, ccd, tag='Y3A1_FINALCUT'):
try:
ccd = ','.join(map(str, ccd))
except:
pass
inputs = dict(expnum=expnum, ccd=ccd, tag=tag)
self.base_query = self.base_query.format(**inputs)
print(self.base_query)
con = connect(self._db, user=self.user, passwd=self._passwd)
self.data = con.query_to_pandas(self.base_query)
print(self.data)
for j in range(len(self.data)):
self.links.append(self.root_url + self.data.PATH.ix[j])
class TestInterpreter(unittest.TestCase):
conf = ea.config_mod.get_config(ea.config_file)
conf.set('display', 'loading_bar', 'no')
db = conf.get('easyaccess', 'database')
desconf = ea.config_mod.get_desconfig(ea.desfile, db)
con = ea.easy_or(conf,
desconf,
db,
interactive=False,
quiet=True,
refresh=False)
con2 = connect(quiet=True)
tablename = 'testtable'
nrows = 10000
prefetch = 4000
chunk = 1000
memsize = 1
sqlfile = 'temp.sql'
csvfile = 'temp.csv'
fitsfile = 'temp.fits'
h5file = 'temp.h5'
def test_describe(self):
print('\n*** test_describe ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
command = 'describe_table %s;' % self.tablename.upper()
self.con.onecmd(command)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_add_comment(self):
print('\n*** test_add_comment ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "add_comment table %s 'Test table'" % self.tablename.upper()
self.con.onecmd(command)
command = "add_comment column %s.RA 'Coordinate'" % self.tablename.upper(
)
self.con.onecmd(command)
command = 'describe_table %s;' % self.tablename.upper()
self.con.onecmd(command)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_select(self):
print('\n*** test_select ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "select RA,DEC from %s ;" % self.tablename.upper()
self.con.onecmd(command)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_select_csv(self):
print('\n*** test_select_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.csvfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.csvfile))
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_select_fits(self):
print('\n*** test_select_fits ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
os.remove(self.csvfile)
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.fitsfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.fitsfile))
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_select_hdf5(self):
print('\n*** test_select_hdf5 ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
self.con.drop_table(self.tablename)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
os.remove(self.csvfile)
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.h5file)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.h5file))
os.remove(self.h5file)
self.con.drop_table(self.tablename)
def test_select_by_chunks(self):
print('\n*** test_select_by_chunks ***\n')
global load_bar
load_bar = False
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
for i in range(34):
command = "append_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 35)
command = "prefetch set 30000"
self.con.onecmd(command)
self.con.outfile_max_mb = 1
command = "select RA,DEC from %s ; > %s" % (self.tablename.upper(),
self.csvfile)
self.con.onecmd(command)
for i in range(6):
self.assertTrue(
os.path.exists(
os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) +
'.csv'))
os.remove(
os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) +
'.csv')
self.con.outfile_max_mb = 1000
self.con.drop_table(self.tablename)
if os.path.exists(self.csvfile):
os.remove(self.csvfile)
def test_load_append_table_csv(self):
print('\n*** test_load_append_table_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
# name from filename
command = "load_table %s " % self.csvfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s " % self.csvfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
os.remove(self.csvfile)
def test_load_append_table_name_csv(self):
print('\n*** test_load_append_table_name_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
# name from tablename
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_load_append_table_chunk_csv(self):
print('\n*** test_load_append_table_chunk_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
# chunksize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --chunksize %s" % (
self.csvfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s --chunksize %s" % (
self.csvfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
def test_load_append_table_memory_csv(self):
print('\n*** test_load_append_table_memory_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile,
index=False,
float_format='%.8f',
sep=',',
mode='a',
header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s" % (
self.csvfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
command = "append_table %s --tablename %s --memsize %s" % (
self.csvfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
# end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_csv(self):
print('\n*** test_load_append_table_memory_chunk_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile,
index=False,
float_format='%.8f',
sep=',',
mode='a',
header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s --chunksize %s" % (
self.csvfile, self.tablename, self.memsize, self.chunk * 10)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
command = "append_table %s --tablename %s --memsize %s --chunksize %s" % (
self.csvfile, self.tablename, self.memsize, self.chunk * 200)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
# end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_append_table_fits(self):
print('\n*** test_load_append_table_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
# name from filename
command = "load_table %s " % self.fitsfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s " % self.fitsfile
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' %
os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
os.remove(self.fitsfile)
def test_load_append_table_name_fits(self):
print('\n*** test_load_append_table_name_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# name from tablename
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.fitsfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s" % (self.fitsfile,
self.tablename)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_chunk_fits(self):
print('\n*** test_load_append_table_chunk_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# chunksize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --chunksize %s" % (
self.fitsfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
command = "append_table %s --tablename %s --chunksize %s" % (
self.fitsfile, self.tablename, self.chunk)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_fits(self):
print('\n*** test_load_append_table_memory_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s" % (
self.fitsfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
command = "append_table %s --tablename %s --memsize %s" % (
self.fitsfile, self.tablename, self.memsize)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
# end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_fits(self):
print('\n*** test_load_append_table_memory_chunk_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s --memsize %s --chunksize %s" % (
self.fitsfile, self.tablename, self.memsize, self.chunk * 10)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
command = "append_table %s --tablename %s --memsize %s --chunksize %s" % (
self.fitsfile, self.tablename, self.memsize, self.chunk * 200)
self.con.onecmd(command)
cursor = self.con2.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
# end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_loadsql(self):
print('\n*** test_loadsql ***\n')
data = create_test_data()
df = pd.DataFrame(data)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
os.remove(self.csvfile)
query = """
-- This is a comment
select RA, DEC from %s -- this is another comment
; > %s
""" % (self.tablename, self.csvfile)
with open(self.sqlfile, 'w') as F:
F.write(query)
command = "loadsql %s" % (self.sqlfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.csvfile))
df = pd.read_csv(self.csvfile, sep=',')
self.assertEqual(len(df), self.nrows)
os.remove(self.csvfile)
self.assertFalse(os.path.exists(self.csvfile))
os.remove(self.sqlfile)
@unittest.skip("Need to re evaluate")
def test_inline(self):
print('\n*** test_inline ***\n')
data = create_test_data()
df = pd.DataFrame(data)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
command = "load_table %s --tablename %s" % (self.csvfile,
self.tablename)
self.con.onecmd(command)
command = "import wrapped as Y"
self.con.onecmd(command)
command = "select /*p: Y.my_sum(ra,dec) as testcol */, dec from %s ; > %s" % (
self.tablename, self.csvfile)
self.con.onecmd(command)
self.assertTrue(os.path.exists(self.csvfile))
df = pd.read_csv(self.csvfile, sep=',')
self.assertEqual(len(df), self.nrows)
self.assertTrue('TESTCOL' in df.columns.values.tolist())
os.remove(self.csvfile)
self.assertFalse(os.path.exists(self.csvfile))
self.con.drop_table(self.tablename)
parser = argparse.ArgumentParser(description=description)
args = parser.parse_args()
data = create_test_data()
nrows = len(data)
# Create the data files
fitsfile = create_test_fits(data=data)
csvfile = create_test_csv(data=data)
tabfile = create_test_tab(data=data)
filenames = [fitsfile, csvfile, tabfile]
# Try loading through the python interface
# NOTE: This requires a desservice.ini file
conn = connect()
query = 'select * from %s' % BASENAME
# Complains when the table doesn't exist, we could add:
# if conn.check_table_exists(BASENAME): conn.drop_table(BASENAME)
for filename in filenames:
# First try loading through python interface
print("*** TESTING PYTHON INTERFACE ***")
conn.drop_table(BASENAME)
conn.load_table(filename)
df = conn.query_to_pandas(query)
assert len(df) == nrows
# Then try loading with explicit tablename
def test_connect_dessci(self):
con = connect('dessci', quiet=True)
self.assertTrue(con.ping())
class TestApi(unittest.TestCase):
con = connect(quiet=True)
tablename = 'testtable'
nrows = 10000
prefetch = 4000
chunk = 1000
memsize = 1
sqlfile = 'temp.sql'
csvfile = 'temp.csv'
fitsfile = 'temp.fits'
h5file = 'temp.h5'
def test_ea_import(self):
print('\n*** test_ea_import ***\n')
test1 = self.con.ea_import('wrapped')
if test1 is not None:
self.assertTrue(test1)
test2 = self.con.ea_import('wrapped', help=True)
if test2 is not None:
self.assertTrue(test2)
def test_pandas_to_db(self):
print('\n*** test_pandas_to_db ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
cursor = self.con.cursor()
self.assertTrue(self.con.ping())
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename, append=True))
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename, append=True))
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
self.con.drop_table(self.tablename)
cursor.close()
def test_query_to_pandas(self):
print('\n*** test_query_to_pandas ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = 'select RA,DEC from {:}'.format(self.tablename.upper())
df2 = self.con.query_to_pandas(query)
self.assertEqual(len(df), len(df2))
self.assertEqual(df.columns.values.tolist().sort(), df2.columns.values.tolist().sort())
# iterator
df3 = self.con.query_to_pandas(query, prefetch=4000, iterator=True)
self.assertEqual(len(df3.next()), 4000)
self.assertEqual(df3.next().columns.values.tolist().sort(),
df.columns.values.tolist().sort())
self.assertEqual(len(df3.next()), 2000)
self.con.drop_table(self.tablename)
def test_describe_table(self):
print('\n*** test_describe_table ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
self.assertEqual(len(self.con.describe_table(self.tablename)), 2)
self.con.drop_table(self.tablename)
def test_loadsql(self):
print('\n*** test_loadsql ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = """
-- This is a comment
select RA, DEC from %s -- this is another comment
""" % self.tablename
with open(self.sqlfile, 'w') as F:
F.write(query)
df2 = self.con.query_to_pandas(self.con.loadsql(self.sqlfile))
self.assertEqual(len(df), len(df2))
self.assertEqual(df.columns.values.tolist().sort(), df2.columns.values.tolist().sort())
query = """
-- This is a comment
select RA, DEC from %s ; -- this is another comment
""" % self.tablename
with open(self.sqlfile, 'w') as F:
F.write(query)
df2 = self.con.query_to_pandas(self.con.loadsql(self.sqlfile))
self.assertEqual(len(df), len(df2))
self.assertEqual(df.columns.values.tolist().sort(), df2.columns.values.tolist().sort())
self.con.drop_table(self.tablename)
os.remove(self.sqlfile)
def test_mytables(self):
print('\n*** test_mytables ***\n')
df = self.con.mytables()
self.assertTrue('FGOTTENMETADATA' in df['TABLE_NAME'].values.tolist())
def test_load_table_csv(self):
print('\n*** test_load_table_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
self.assertTrue(os.path.exists(self.csvfile))
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
# name from filename
self.assertTrue(self.con.load_table(self.csvfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(self.csvfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % os.path.splitext(self.csvfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.csvfile)[0].upper())
# name from tablename
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.csvfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(self.csvfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
# chunksize
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(
self.csvfile, name=self.tablename, chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(
self.csvfile, name=self.tablename, chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.csvfile)
def test_load_append_table_memory_csv(self):
print('\n*** test_load_append_table_memory_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile, index=False, float_format='%.8f',
sep=',', mode='a', header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(
self.csvfile, name=self.tablename, memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
self.assertTrue(self.con.append_table(
self.csvfile, name=self.tablename, memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
# end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_csv(self):
print('\n*** test_load_append_table_memory_chunk_csv ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_csv(self.csvfile, index=False, float_format='%.8f', sep=',')
for i in range(9):
df.to_csv(self.csvfile, index=False, float_format='%.8f',
sep=',', mode='a', header=False)
self.assertTrue(os.path.exists(self.csvfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.csvfile, name=self.tablename,
memsize=self.memsize, chunksize=self.chunk * 10))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 10)
# appending
self.assertTrue(self.con.append_table(self.csvfile, name=self.tablename,
memsize=self.memsize, chunksize=self.chunk * 200))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 20)
# end
os.remove(self.csvfile)
self.con.drop_table(self.tablename)
def test_load_table_fits(self):
print('\n*** test_load_table_fits ***\n')
data = create_test_data()
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
# name from filename
self.assertTrue(self.con.load_table(self.fitsfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(self.fitsfile))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % os.path.splitext(self.fitsfile)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.fitsfile)[0].upper())
# name from tablename
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.fitsfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(self.fitsfile, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
# chunksize
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(
self.fitsfile, name=self.tablename, chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(
self.fitsfile, name=self.tablename, chunksize=self.chunk))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.fitsfile)
def test_load_append_table_memory_fits(self):
print('\n*** test_load_append_table_memory_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(
self.fitsfile, name=self.tablename, memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
self.assertTrue(self.con.append_table(
self.fitsfile, name=self.tablename, memsize=self.memsize))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
# end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_append_table_memory_chunk_fits(self):
print('\n*** test_load_append_table_memory_chunk_fits ***\n')
data = create_test_data()
for i in range(4):
data = np.concatenate((data, data))
fitsio.write(self.fitsfile, data, clobber=True)
self.assertTrue(os.path.exists(self.fitsfile))
# memsize
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.fitsfile, name=self.tablename,
memsize=self.memsize, chunksize=self.chunk * 10))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 16)
# appending
self.assertTrue(self.con.append_table(self.fitsfile, name=self.tablename,
memsize=self.memsize, chunksize=self.chunk * 200))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2 * 16)
# end
os.remove(self.fitsfile)
self.con.drop_table(self.tablename)
def test_load_table_hdf5(self):
print('\n*** test_load_table_hdf5 ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
df.to_hdf(self.h5file, key='data')
self.assertTrue(os.path.exists(self.h5file))
self.con.drop_table(os.path.splitext(self.h5file)[0].upper())
# name from filename
self.assertTrue(self.con.load_table(self.h5file))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % os.path.splitext(self.h5file)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(self.h5file))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % os.path.splitext(self.h5file)[0].upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(os.path.splitext(self.h5file)[0].upper())
# name from tablename
self.con.drop_table(self.tablename)
self.assertTrue(self.con.load_table(self.h5file, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows)
# appending
self.assertTrue(self.con.append_table(self.h5file, name=self.tablename))
cursor = self.con.cursor()
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 2)
self.con.drop_table(self.tablename)
os.remove(self.h5file)
def test_query_and_save(self):
print('\n*** test_query_and_save ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
cursor = self.con.cursor()
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = 'select RA,DEC from %s' % self.tablename.upper()
self.con.query_and_save(query, self.csvfile, print_time=False)
self.assertTrue(os.path.exists(self.csvfile))
self.con.query_and_save(query, self.fitsfile, print_time=False)
self.assertTrue(os.path.exists(self.fitsfile))
self.con.query_and_save(query, self.h5file, print_time=False)
self.assertTrue(os.path.exists(self.h5file))
os.remove(self.csvfile)
os.remove(self.fitsfile)
os.remove(self.h5file)
for i in range(34):
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename, append=True))
temp = cursor.execute('select RA,DEC from %s' % self.tablename.upper())
fetch = temp.fetchall()
self.assertEqual(len(fetch), self.nrows * 35)
self.con.outfile_max_mb = 1
self.con.query_and_save(query, self.csvfile, print_time=False)
for i in range(4):
self.assertTrue(os.path.exists(os.path.splitext(
self.csvfile)[0] + '_00000' + str(i + 1) + '.csv'))
os.remove(os.path.splitext(self.csvfile)[0] + '_00000' + str(i + 1) + '.csv')
self.con.query_and_save(query, self.fitsfile, print_time=False)
for i in range(4):
self.assertTrue(os.path.exists(os.path.splitext(self.fitsfile)
[0] + '_00000' + str(i + 1) + '.fits'))
os.remove(os.path.splitext(self.fitsfile)[0] + '_00000' + str(i + 1) + '.fits')
self.con.outfile_max_mb = 1000
self.con.drop_table(self.tablename)
@unittest.skip("need to reealuate")
def test_inline_functions(self):
print('\n*** test_inline_functions ***\n')
data = create_test_data()
df = pd.DataFrame(data)
self.assertEqual(len(df), self.nrows)
cursor = self.con.cursor()
try:
self.con.drop_table(self.tablename)
except:
pass
self.assertTrue(self.con.pandas_to_db(df, tablename=self.tablename))
query = 'select /*p: Y.my_sum(ra,dec) as testcol*/ from %s' % self.tablename
self.con.ea_import('wrapped as Y')
df = self.con.query_to_pandas(query)
self.assertEqual(len(df), self.nrows)
self.assertTrue('TESTCOL' in df.columns.values.tolist())
self.con.drop_table(self.tablename)