def test_sqllite_sql(self): fLOG(__file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "taxi_trip.csv") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_taxi.db3") if os.path.exists(dbf): os.remove(dbf) try: import_flatfile_into_database(dbf, file, fLOG=fLOG, header=True) except NoHeaderException: return assert os.path.exists(dbf) db = Database(dbf, LOG=fLOG) db.connect() view = db.execute_view("SELECT * FROM taxi_trip") fLOG(len(view)) fLOG(view) exp = ('1B5C0970F2AE8CFFBA8AE4584BEAED29', 'D961332334524990D1BBD462E2EFB8A4', 'CMT', '2013-02-08 23:35:14', 'CRD', 6.0, 0.5, 0.5, 0.0, 0, 7.0) assert len(view) > 0 assert len(view[0]) == len(exp)
def test_import_flatflitand_copy(self) : fLOG (__file__, self._testMethodName, OutputPrint = __name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_database_copy.db3") if os.path.exists(dbf) : os.remove(dbf) dbf2 = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "out_copy.db3") if os.path.exists(dbf2) : os.remove(dbf2) import_flatfile_into_database(dbf, file, fLOG = fLOG) assert os.path.exists(dbf) db = Database(dbf, LOG = fLOG) dbm = Database(dbf2, LOG = fLOG) db.copy_to(dbm) db.connect() dbm.connect() tbls = dbm.get_table_list() if len(tbls)!=1 : raise Exception("expects one table not %d" % len(tbls)) view = db.execute_view("SELECT * FROM ACAPA") viewm = dbm.execute_view("SELECT * FROM ACAPA") db.close() dbm.close() assert len(view) == len(viewm) dbm2 = Database(":memory:", LOG = fLOG) db.copy_to(dbm2) dbm2.connect() tbls = dbm2.get_table_list() if len(tbls)!=1 : raise Exception("expects one table not %d" % len(tbls)) viewm2 = dbm2.execute_view("SELECT * FROM ACAPA") dbm2.close() assert len(view) == len(viewm2)
def test_sqllite_sql(self): fLOG( __file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "data", "taxi_trip.csv") dbf = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "temp_taxi.db3") if os.path.exists(dbf): os.remove(dbf) try: import_flatfile_into_database( dbf, file, fLOG=fLOG, header=True) except NoHeaderException: return assert os.path.exists(dbf) db = Database(dbf, LOG=fLOG) db.connect() view = db.execute_view("SELECT * FROM taxi_trip") fLOG(len(view)) fLOG(view) exp = ('1B5C0970F2AE8CFFBA8AE4584BEAED29', 'D961332334524990D1BBD462E2EFB8A4', 'CMT', '2013-02-08 23:35:14', 'CRD', 6.0, 0.5, 0.5, 0.0, 0, 7.0) assert len(view) > 0 assert len(view[0]) == len(exp)
def test_import_person(self): fLOG(__file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "person.txt") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_person.db3") if os.path.exists(dbf): os.remove(dbf) columns = "sequence tag timestamp dateformat x y z activity".split() try: import_flatfile_into_database(dbf, file, fLOG=fLOG, columns=columns, header=False) except NoHeaderException: return assert os.path.exists(dbf) db = Database(dbf, LOG=fLOG) db.connect() view = db.execute_view("SELECT * FROM ACAPA") assert len(view) > 0 assert len(view[0]) == 7
def test_import_person(self): fLOG( __file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "data", "person.txt") dbf = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "temp_person.db3") if os.path.exists(dbf): os.remove(dbf) columns = "sequence tag timestamp dateformat x y z activity".split() try: import_flatfile_into_database( dbf, file, fLOG=fLOG, columns=columns, header=False) except NoHeaderException: return assert os.path.exists(dbf) db = Database(dbf, LOG=fLOG) db.connect() view = db.execute_view("SELECT * FROM ACAPA") assert len(view) > 0 assert len(view[0]) == 7
def test_import_flatflit(self): fLOG(__file__, self._testMethodName, OutputPrint=__name__ == "__main__") fold = os.path.abspath(os.path.split(__file__)[0]) temp = os.path.join(fold, "temp_db_bug") if not os.path.exists(temp): os.mkdir(temp) text = [ "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten" ] data = [{ "name": text[random.randint(0, 9)], "number": random.randint(0, 99) } for i in range(0, 10000)] filename = os.path.join(temp, "out_flatfile_tab_pos2.txt") datatab = data[:1] + [{"name": " one\ttab", "number": 100}] + data[1:] df = pandas.DataFrame(datatab) df.to_csv(filename, sep="\t", encoding="utf8", header=True, index=False) with open(filename, "r", encoding="utf8") as f: content = f.read() content = content.replace('"', '') with open(filename + ".2.txt", "w", encoding="utf8") as f: f.write(content) dbfile = os.path.join(fold, "out_db.db3") if os.path.exists(dbfile): os.remove(dbfile) import_flatfile_into_database(dbfile, filename + ".2.txt", table="example", fLOG=fLOG) db = Database(dbfile, LOG=fLOG) db.connect() count = db.get_table_nb_lines("example") sch = db.get_table_columns("example") values = db.execute_view("SELECT * FROM example") db.close() if count != 10001: rows = [str(v) for v in values][:10] mes = "\n".join(rows) fLOG(datatab[:3]) raise Exception("expect:10001 not {0}\nROWS:\n{1}".format( count, mes)) exp = [('name', str), ('number', int)] if sch != exp: raise Exception("{0}!={1} ({2})".format(sch, exp, len(datatab)))
def test_import_flatflit(self): fLOG(__file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_database.db3") if os.path.exists(dbf): os.remove(dbf) import_flatfile_into_database(dbf, file, fLOG=fLOG) self.assertExists(dbf) db = Database(dbf, LOG=fLOG) db.connect() view = db.execute_view("SELECT * FROM ACAPA") self.assertGreater(len(view), 0) self.assertEqual(len(view[0]), 7)
def test_import_flatflitand_copy(self): fLOG(__file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_database_copy.db3") if os.path.exists(dbf): os.remove(dbf) dbf2 = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "out_copy.db3") if os.path.exists(dbf2): os.remove(dbf2) import_flatfile_into_database(dbf, file, fLOG=fLOG) assert os.path.exists(dbf) db = Database(dbf, LOG=fLOG) dbm = Database(dbf2, LOG=fLOG) db.copy_to(dbm) db.connect() dbm.connect() tbls = dbm.get_table_list() if len(tbls) != 1: raise Exception("expects one table not %d" % len(tbls)) view = db.execute_view("SELECT * FROM ACAPA") viewm = dbm.execute_view("SELECT * FROM ACAPA") db.close() dbm.close() assert len(view) == len(viewm) dbm2 = Database(":memory:", LOG=fLOG) db.copy_to(dbm2) dbm2.connect() tbls = dbm2.get_table_list() if len(tbls) != 1: raise Exception("expects one table not %d" % len(tbls)) viewm2 = dbm2.execute_view("SELECT * FROM ACAPA") dbm2.close() assert len(view) == len(viewm2)
def test_interface_sql(self) : fLOG (__file__, self._testMethodName, OutputPrint = __name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_database_int.db3") if not os.path.exists(dbf) : import_flatfile_into_database(dbf, file, fLOG = fLOG) assert os.path.exists(dbf) face = InterfaceSQL.create(dbf) face.connect() tbls = face.get_table_list() fLOG(tbls) assert tbls == ['ACAPA'] cols = face.get_table_columns('ACAPA') fLOG(cols ) assert cols == {0: ('Date', str), 1: ('Open', float), 2: ('High', float), 3: ('Low', float), 4: ('Close', float), 5: ('Volume', int), 6: ('Adj_Close', float)} assert face.CC.ACAPA._ == "ACAPA" assert face.CC.ACAPA.Date._ == "Date" sql = "SELECT COUNT(*) FROM ACAPA" df = face.execute(sql) fLOG(df) assert df.columns== ["COUNT(*)"] assert len(df) == 1 assert df.values[0][0] == 2333 sql = "SELECT COUNT(*) FROM DB.CC.ACAPA" df2 = face.execute(sql) fLOG(df) assert df.columns== ["COUNT(*)"] assert len(df) == 1 assert df.values[0][0] == 2333 face.close()
def test_import_flatflit(self) : fLOG (__file__, self._testMethodName, OutputPrint = __name__ == "__main__") fold = os.path.abspath(os.path.split(__file__)[0]) temp = os.path.join(fold, "temp_db_bug") if not os.path.exists(temp) : os.mkdir(temp) text = [ "one","two","three","four","five","six","seven","eight","nine","ten" ] data = [ { "name": text[random.randint(0,9)], "number": random.randint(0,99)} \ for i in range(0,10000) ] filename = os.path.join(temp, "out_flatfile_tab_pos2.txt") datatab = data[:1] + [ {"name": " one\ttab", "number":100 } ] + data[1:] df = pandas.DataFrame(datatab) df.to_csv(filename, sep="\t", encoding="utf8", header=True, index=False) with open(filename,"r",encoding="utf8") as f : content = f.read() content = content.replace('"','') with open(filename + ".2.txt","w",encoding="utf8") as f : f.write(content) dbfile = os.path.join(fold, "out_db.db3") if os.path.exists(dbfile) : os.remove(dbfile) import_flatfile_into_database(dbfile, filename + ".2.txt", table="example", fLOG = fLOG) db = Database(dbfile, LOG = fLOG) db.connect() count = db.get_table_nb_lines("example") sch = db.get_table_columns("example") values = db.execute_view("SELECT * FROM example") db.close() if count != 10001 : rows = [ str(v) for v in values ] [:10] mes = "\n".join(rows) fLOG(datatab[:3]) raise Exception("expect:10001 not {0}\nROWS:\n{1}".format(count,mes)) exp = [('name', str), ('number', int)] if sch != exp: raise Exception("{0}!={1} ({2})".format(sch, exp, len(datatab)))
def test_import_flatflit(self): fLOG( __file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "temp_database.db3") if os.path.exists(dbf): os.remove(dbf) import_flatfile_into_database(dbf, file, fLOG=fLOG) assert os.path.exists(dbf) db = Database(dbf, LOG=fLOG) db.connect() view = db.execute_view("SELECT * FROM ACAPA") assert len(view) > 0 assert len(view[0]) == 7
def test_interface_sql(self): fLOG( __file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join( os.path.abspath( os.path.split(__file__)[0]), "temp_database_int.db3") if not os.path.exists(dbf): import_flatfile_into_database(dbf, file, fLOG=fLOG) assert os.path.exists(dbf) face = InterfaceSQL.create(dbf) face.connect() tbls = face.get_table_list() fLOG(tbls) assert 'ACAPA' in tbls cols = face.get_table_columns('ACAPA') fLOG(cols) assert cols == {0: ('Date', str), 1: ('Open', float), 2: ('High', float), 3: ('Low', float), 4: ('Close', float), 5: ('Volume', int), 6: ('Adj_Close', float)} assert face.CC.ACAPA._ == "ACAPA" assert face.CC.ACAPA.Date._ == "Date" sql = "SELECT COUNT(*) FROM ACAPA" df = face.execute(sql) fLOG(df) assert df.columns == ["COUNT(*)"] assert len(df) == 1 assert df.values[0][0] == 2333 sql = "SELECT COUNT(*) FROM DB.CC.ACAPA" face.execute(sql) fLOG(df) assert df.columns == ["COUNT(*)"] assert len(df) == 1 assert df.values[0][0] == 2333 def minc(x): return x + 1 face.add_function(minc) sql = "SELECT minc(nb) FROM ( SELECT COUNT(*) AS nb FROM ACAPA )" df = face.execute(sql) assert df.values[0][0] == 2334 if 'newtable' in face.get_table_list(): face.drop_table('newtable') face.import_dataframe("newtable", df) assert "newtable" in face.get_table_list() sql = "SELECT blblable" try: df = face.execute(sql) except InterfaceSQLException: pass face.close()
def test_interface_sql(self): fLOG(__file__, self._testMethodName, OutputPrint=__name__ == "__main__") file = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "data", "ACA.PA.txt") dbf = os.path.join(os.path.abspath(os.path.split(__file__)[0]), "temp_database_int.db3") if not os.path.exists(dbf): import_flatfile_into_database(dbf, file, fLOG=fLOG) assert os.path.exists(dbf) face = InterfaceSQL.create(dbf) face.connect() tbls = face.get_table_list() fLOG(tbls) assert 'ACAPA' in tbls cols = face.get_table_columns('ACAPA') fLOG(cols) assert cols == { 0: ('Date', str), 1: ('Open', float), 2: ('High', float), 3: ('Low', float), 4: ('Close', float), 5: ('Volume', int), 6: ('Adj_Close', float) } assert face.CC.ACAPA._ == "ACAPA" assert face.CC.ACAPA.Date._ == "Date" sql = "SELECT COUNT(*) FROM ACAPA" df = face.execute(sql) fLOG(df) assert df.columns == ["COUNT(*)"] assert len(df) == 1 assert df.values[0][0] == 2333 sql = "SELECT COUNT(*) FROM DB.CC.ACAPA" face.execute(sql) fLOG(df) assert df.columns == ["COUNT(*)"] assert len(df) == 1 assert df.values[0][0] == 2333 def minc(x): return x + 1 face.add_function(minc) sql = "SELECT minc(nb) FROM ( SELECT COUNT(*) AS nb FROM ACAPA )" df = face.execute(sql) assert df.values[0][0] == 2334 if 'newtable' in face.get_table_list(): face.drop_table('newtable') face.import_dataframe("newtable", df) assert "newtable" in face.get_table_list() sql = "SELECT blblable" try: df = face.execute(sql) except InterfaceSQLException: pass face.close()