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