コード例 #1
0
    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
コード例 #2
0
    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
コード例 #3
0
    def test_makedirs_badengine(self):
        fLOG(__file__,
             self._testMethodName,
             OutputPrint=__name__ == "__main__")
        temp = get_temp_folder(__file__, "temp_missing_makedirs2")
        dbf = os.path.join(temp, 'tr', 'td.db3')

        values = [
            {
                "name": "A",
                "age": 10,
                "score": 34.5
            },
            {
                "name": "B",
                "age": 20,
                "score": -34.5
            },
        ]
        df = pandas.DataFrame(values)
        try:
            Database.fill_sql_table(df, dbf, "newtable", engine='rty')
            raise AssertionError('engine is recognized but should not')
        except DBException:
            pass
コード例 #4
0
    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)
コード例 #5
0
    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)
コード例 #6
0
ファイル: test_database_bug.py プロジェクト: btrspg/pyensae
    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)))
コード例 #7
0
    def test_import_df(self):
        fLOG(
            __file__,
            self._testMethodName,
            OutputPrint=__name__ == "__main__")
        dbf = os.path.join(
            os.path.abspath(
                os.path.split(__file__)[0]),
            "temp_database_df.db3")
        if os.path.exists(dbf):
            os.remove(dbf)

        values = [{"name": "A", "age": 10, "score": 34.5},
                  {"name": "B", "age": 20, "score": -34.5}, ]
        df = pandas.DataFrame(values)
        db = Database.fill_sql_table(df, dbf, "newtable")
        db.execute_view("SELECT * FROM newtable")
        df2 = db.to_df("SELECT * FROM newtable")
        df3 = df2[["age", "name", "score"]]
        assert len(df) > 0
        assert len(df3) == len(df)
        for a, b in zip(df.values, df3.values):
            assert len(a) > 0
            assert len(a) == len(b)
            for c, d in zip(a, b):
                assert c == d
        db.close()
コード例 #8
0
ファイル: test_database_df.py プロジェクト: btrspg/pyensae
    def test_import_df(self):
        fLOG(
            __file__,
            self._testMethodName,
            OutputPrint=__name__ == "__main__")
        dbf = os.path.join(
            os.path.abspath(
                os.path.split(__file__)[0]),
            "temp_database_df.db3")
        if os.path.exists(dbf):
            os.remove(dbf)

        values = [{"name": "A", "age": 10, "score": 34.5},
                  {"name": "B", "age": 20, "score": -34.5}, ]
        df = pandas.DataFrame(values)
        db = Database.fill_sql_table(df, dbf, "newtable")
        db.execute_view("SELECT * FROM newtable")
        df2 = db.to_df("SELECT * FROM newtable")
        df3 = df2[["age", "name", "score"]]
        self.assertGreater(len(df), 0)
        self.assertEqual(len(df3), len(df))
        for a, b in zip(df.values, df3.values):
            self.assertGreater(len(a), 0)
            self.assertEqual(len(a), len(b))
            for c, d in zip(a, b):
                self.assertEqual(c, d)
        db.close()
コード例 #9
0
ファイル: test_database.py プロジェクト: btrspg/pyensae
 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)
コード例 #10
0
    def test_db_index(self):
        fLOG(__file__,
             self._testMethodName,
             OutputPrint=__name__ == "__main__")
        temp = get_temp_folder(__file__, "temp_missing_makedirs")
        dbf = os.path.join(temp, 'tr', 'td.db3')

        values = [
            {
                "name": "A",
                "age": 10,
                "score": 34.5
            },
            {
                "name": "B",
                "age": 20,
                "score": -34.5
            },
        ]
        df = pandas.DataFrame(values)
        db = Database.fill_sql_table(df, dbf, "newtable")
        has = db.has_index('ind')
        self.assertFalse(has)
        resi = db.get_index_on_table('newtable', full=True)
        self.assertEqual(resi, [])
        resi = db.get_index_on_table('newtable', full=False)
        self.assertEqual(resi, [])
        resi = db.get_column_type('newtable', 'name')
        self.assertEqual(resi, str)
        self.assertRaise(lambda: db.get_column_type('newtable', 'name2'),
                         DBException)
        resi = db.get_table_nfirst_lines('newtable', 2)
        self.assertEqual(resi, [(10, 'A', 34.5, 1), (20, 'B', -34.5, 2)])
        cur = db.execute_script('SELECT name FROM newtable')
        self.assertEqual(cur, None)
        cur = db.execute_script('SELECT name FROM newtable', close=False)
        self.assertFalse(cur is None)

        db.create_table("nextable2",
                        columns={
                            -1: ("key", int, "PRIMARYKEY", "AUTOINCREMENT"),
                            0: ("name", str),
                            1: ("number", float),
                            2: ('tint', int),
                            3: ('tint64', numpy.int64),
                            4: ('tfloat64', numpy.float64),
                            5: ('tdt', datetime.datetime),
                            6: ('tdec', decimal.Decimal)
                        })
        cur.close()
        db.close()
コード例 #11
0
 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
コード例 #12
0
ファイル: test_database_bug.py プロジェクト: ped4747/pyensae
    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)))
コード例 #13
0
    def test_makedirs(self):
        fLOG(__file__,
             self._testMethodName,
             OutputPrint=__name__ == "__main__")
        temp = get_temp_folder(__file__, "temp_missing_makedirs")
        dbf = os.path.join(temp, 'tr', 'td.db3')

        values = [
            {
                "name": "A",
                "age": 10,
                "score": 34.5
            },
            {
                "name": "B",
                "age": 20,
                "score": -34.5
            },
        ]
        df = pandas.DataFrame(values)
        db = Database.fill_sql_table(df, dbf, "newtable")
        db.execute_view("SELECT * FROM newtable")
        db.close()
コード例 #14
0
    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)
コード例 #15
0
    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)
コード例 #16
0
    def test_import_index(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_index.db3")
        if os.path.exists(dbf):
            os.remove(dbf)

        fLOG("import flat file")
        ntbl2 = import_flatfile_into_database(dbf, file, fLOG=fLOG)
        fLOG("import flat file pandas")
        ntbl3 = import_flatfile_into_database_pandas(dbf,
                                                     file,
                                                     fLOG=fLOG,
                                                     table="pandas_table",
                                                     chunksize=500,
                                                     sep="\t")
        fLOG("-----------------")
        db = Database(dbf, LOG=fLOG)
        db.connect()
        tbl1 = pandas.read_sql("SELECT * FROM " + ntbl2, db._connection)
        tbl2 = pandas.read_sql("SELECT * FROM " + ntbl3, db._connection)
        db.close()
        self.assertEqual(tbl1.shape, tbl2.shape)
        self.assertEqual(list(tbl1.columns), list(tbl2.columns))
        self.assertEqual(list(tbl1.dtypes), list(tbl2.dtypes))

        assert os.path.exists(dbf)
        db = Database(dbf, LOG=fLOG)
        db.connect()

        db.create_index("index1", "ACAPA", "Date")
        li = db.get_index_list()
        self.assertEqual(
            li, [('index1', 'ACAPA', 'CREATE INDEX index1 ON ACAPA (Date)',
                  ('Date', ))])
        line = db.get_table_nfirst_lines("ACAPA")
        col = [_[0] for _ in db.get_table_columns("ACAPA")]
        line = line[0]
        add = {k: v for k, v in zip(col, line)}
        db.insert("ACAPA", add)
        db.commit()
        db.update("ACAPA", "Date", add["Date"], add)
        db.commit()

        db.close()
コード例 #17
0
    def test_import_index(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_index.db3")
        if os.path.exists(dbf):
            os.remove(dbf)

        fLOG("import flat file")
        ntbl2 = import_flatfile_into_database(dbf, file, fLOG=fLOG)
        fLOG("import flat file pandas")
        ntbl3 = import_flatfile_into_database_pandas(dbf, file, fLOG=fLOG, table="pandas_table",
                                                     chunksize=500, sep="\t")
        fLOG("-----------------")
        db = Database(dbf, LOG=fLOG)
        db.connect()
        tbl1 = pandas.read_sql("SELECT * FROM " + ntbl2, db._connection)
        tbl2 = pandas.read_sql("SELECT * FROM " + ntbl3, db._connection)
        db.close()
        self.assertEqual(tbl1.shape, tbl2.shape)
        self.assertEqual(list(tbl1.columns), list(tbl2.columns))
        self.assertEqual(list(tbl1.dtypes), list(tbl2.dtypes))

        assert os.path.exists(dbf)
        db = Database(dbf, LOG=fLOG)
        db.connect()

        db.create_index("index1", "ACAPA", "Date")
        li = db.get_index_list()
        self.assertEqual(
            li, [('index1', 'ACAPA', 'CREATE INDEX index1 ON ACAPA (Date)', ('Date',))])
        line = db.get_table_nfirst_lines("ACAPA")
        col = [_[0] for _ in db.get_table_columns("ACAPA")]
        line = line[0]
        add = {k: v for k, v in zip(col, line)}
        db.insert("ACAPA", add)
        db.commit()
        db.update("ACAPA", "Date", add["Date"], add)
        db.commit()

        db.close()