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)} # pylint: disable=R1721 db.insert("ACAPA", add) db.commit() db.update("ACAPA", "Date", add["Date"], add) db.commit() db.close()
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_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_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)
import sys import os sys.path.append(r"D:\Dupre\_data\program\python\pyensae\src") import pyensae from time import strftime, strptime import datetime from pyensae.sql.database_main import Database tbl = "stations.txt" if not os.path.exists(tbl): sql = """SELECT DISTINCT address, contract_name,lat,lng,name,number FROM snap ORDER BY name""" db = Database(__file__.replace(".py", ".db3")) db.connect() db.export_view_into_flat_file(sql, tbl, header=True, encoding="utf8") db.close() tbl = "tb8_velib.txt" if True or not os.path.exists(tbl): dt = datetime.datetime.strptime("2013-09-13 11:26:37.738913", "%Y-%m-%d %H:%M:%S.%f") print(dt, type(dt), dt.hour) sql = """SELECT collect_date, --strftime('%Y-%m-%d %H:%M:%S', last_update) AS last_update, available_bike_stands, available_bikes, number