def write_sql_file(self,
tic_dat,
sql_file_path,
include_schema=False,
allow_overwrite=False):
"""
write the sql for the ticDat data to a text file
:param tic_dat: the data object to write
:param sql_file_path: the path of the text file to hold the sql statements for the data
:param include_schema: boolean - should we write the schema sql first?
:param allow_overwrite: boolean - are we allowed to overwrite pre-existing file
:return:
caveats : float("inf"), float("-inf") are written as "inf", "-inf" (unless infinity_io_flag
is being applied). True/False are written as "True", "False"
"""
verify(sql, "sqlite3 needs to be installed to use this subroutine")
verify(
allow_overwrite or not os.path.exists(sql_file_path),
"The %s path exists and overwrite is not allowed" % sql_file_path)
must_schema = set(
self.tic_dat_factory.all_tables if include_schema else [])
if self.tic_dat_factory.generic_tables:
gt = self.tic_dat_factory.generic_tables
dat, tdf = create_generic_free(tic_dat, self.tic_dat_factory)
return tdf.sql._write_sql_file(dat, sql_file_path,
must_schema.union(gt))
return self._write_sql_file(tic_dat, sql_file_path, must_schema)
def _test_generic_copy(self, ticDat, tdf, skip_tables=None):
assert all(tdf.primary_key_fields.get(t) for t in tdf.all_tables)
path = makeCleanDir(os.path.join(_scratchDir, "generic_copy"))
replace_name = lambda f : "name_" if f == "name" else f
clean_tdf = TicDatFactory(**{t:[list(map(replace_name, pks)), dfs]
for t,(pks, dfs) in tdf.schema().items()})
temp_tdf = TicDatFactory(**{t:v if t in (skip_tables or []) else '*'
for t,v in clean_tdf.schema().items()})
temp_dat = temp_tdf.TicDat(**{t:getattr(ticDat, t) for t in (skip_tables or [])})
for t in temp_tdf.generic_tables:
setattr(temp_dat, t, getattr(clean_tdf.copy_to_pandas(ticDat, drop_pk_columns=False) ,t))
temp_tdf.sql.write_db_data(temp_dat, os.path.join(path, "f.db"))
temp_tdf.sql.write_sql_file(temp_dat, os.path.join(path, "f1.sql"), include_schema=False)
temp_tdf.sql.write_sql_file(temp_dat, os.path.join(path, "f2.sql"), include_schema=True)
for file_name, includes_schema in [("f.db", False), ("f1.sql", False), ("f2.sql", True)]:
file_path = os.path.join(path, file_name)
if file_path.endswith(".db"):
self.assertFalse(temp_tdf.sql.find_duplicates(file_path))
read_dat = temp_tdf.sql.create_tic_dat(file_path)
else:
read_dat = temp_tdf.sql.create_tic_dat_from_sql(file_path, includes_schema)
generic_free_dat, _ = utils.create_generic_free(read_dat, temp_tdf)
check_dat = clean_tdf.TicDat()
for t in temp_tdf.generic_tables:
for r in getattr(generic_free_dat, t):
pks = clean_tdf.primary_key_fields[t]
getattr(check_dat, t)[r[pks[0]] if len(pks) == 1 else tuple(r[_] for _ in pks)] = \
{df:r[df] for df in clean_tdf.data_fields.get(t, [])}
for t in (skip_tables or []):
for k,v in getattr(generic_free_dat, t).items():
getattr(check_dat, t)[k] = v
self.assertTrue(clean_tdf._same_data(check_dat, clean_tdf.copy_tic_dat(ticDat)))
def _test_generic_free_copy(self, ticDat, tdf, skip_tables=None):
assert all(tdf.primary_key_fields.get(t) for t in tdf.all_tables)
replace_name = lambda f: "name_" if f == "name" else f
clean_tdf = TicDatFactory(
**{
t: [list(map(replace_name, pks)), dfs]
for t, (pks, dfs) in tdf.schema().items()
})
temp_tdf = TicDatFactory(
**{
t: v if t in (skip_tables or []) else '*'
for t, v in clean_tdf.schema().items()
})
temp_dat = temp_tdf.TicDat(
**{t: getattr(ticDat, t)
for t in (skip_tables or [])})
for t in temp_tdf.generic_tables:
setattr(
temp_dat, t,
getattr(
clean_tdf.copy_to_pandas(ticDat, drop_pk_columns=False),
t))
generic_free_dat, _ = utils.create_generic_free(temp_dat, temp_tdf)
check_dat = clean_tdf.TicDat()
for t in temp_tdf.generic_tables:
for r in getattr(generic_free_dat, t):
pks = clean_tdf.primary_key_fields[t]
getattr(check_dat, t)[r[pks[0]] if len(pks) == 1 else tuple(r[_] for _ in pks)] = \
{df:r[df] for df in clean_tdf.data_fields.get(t, [])}
for t in (skip_tables or []):
for k, v in getattr(generic_free_dat, t).items():
getattr(check_dat, t)[k] = v
self.assertTrue(
clean_tdf._same_data(check_dat, clean_tdf.copy_tic_dat(ticDat)))
def write_directory(self,
tic_dat,
dir_path,
allow_overwrite=False,
dialect='excel',
write_header=True):
"""
write the ticDat data to a collection of csv files
:param tic_dat: the data object
:param dir_path: the directory in which to write the csv files
:param allow_overwrite: boolean - are we allowed to overwrite existing
files?
:param dialect: the csv dialect. Consult csv documentation for details.
:param write_header: Boolean. Should the header information be written
as the first row?
:return:
"""
verify(csv, "csv needs to be installed to use this subroutine")
verify(dialect in csv.list_dialects(), "Invalid dialect %s" % dialect)
verify(not os.path.isfile(dir_path),
"A file is not a valid directory path")
if self.tic_dat_factory.generic_tables:
dat, tdf = create_generic_free(tic_dat, self.tic_dat_factory)
return tdf.csv.write_directory(dat, dir_path, allow_overwrite,
dialect, write_header)
tdf = self.tic_dat_factory
msg = []
if not self.tic_dat_factory.good_tic_dat_object(
tic_dat, lambda m: msg.append(m)):
raise TicDatError("Not a valid TicDat object for this schema : " +
" : ".join(msg))
if not allow_overwrite:
for t in tdf.all_tables:
f = os.path.join(dir_path, t + ".csv")
verify(not os.path.exists(f),
"The %s path exists and overwrite is not allowed" % f)
if not os.path.isdir(dir_path):
os.mkdir(dir_path)
for t in tdf.all_tables:
f = os.path.join(dir_path, t + ".csv")
with open(f, 'w') as csvfile:
writer = csv.DictWriter(
csvfile,
dialect=dialect,
fieldnames=tdf.primary_key_fields.get(t, ()) +
tdf.data_fields.get(t, ()))
writer.writeheader() if write_header else None
_t = getattr(tic_dat, t)
if dictish(_t):
for p_key, data_row in _t.items():
primaryKeyDict = {
f: v
for f, v in zip(
tdf.primary_key_fields[t],
p_key if containerish(p_key) else (p_key, ))
}
writer.writerow(dict(data_row, **primaryKeyDict))
else:
for data_row in (_t if containerish(_t) else _t()):
writer.writerow(dict(data_row))
def write_db_data(self, tic_dat, db_file_path, allow_overwrite = False):
"""
write the ticDat data to an SQLite database file
:param tic_dat: the data object to write
:param db_file_path: the file path of the SQLite database to populate
:param allow_overwrite: boolean - are we allowed to overwrite pre-existing data
:return:
caveats : float("inf"), float("-inf") are written as "inf", "-inf"
"""
verify(sql, "sqlite3 needs to be installed to use this subroutine")
msg = []
if not self.tic_dat_factory.good_tic_dat_object(tic_dat, lambda m : msg.append(m)) :
raise TicDatError("Not a valid TicDat object for this schema : " + " : ".join(msg))
verify(not os.path.isdir(db_file_path), "A directory is not a valid SQLite file path")
if self.tic_dat_factory.generic_tables:
dat, tdf = create_generic_free(tic_dat, self.tic_dat_factory)
return tdf.sql.write_db_data(dat, db_file_path, allow_overwrite)
if not os.path.exists(db_file_path) :
self.write_db_schema(db_file_path)
table_names = self._check_tables_fields(db_file_path, self.tic_dat_factory.all_tables)
with _sql_con(db_file_path, foreign_keys=False) as con:
for t in self.tic_dat_factory.all_tables:
verify(table_names[t] == t, "Failed to find table %s in path %s"%
(t, db_file_path))
verify(allow_overwrite or not any(True for _ in con.execute("Select * from %s"%t)),
"allow_overwrite is False, but there are already data records in %s"%t)
con.execute("Delete from %s"%t) if allow_overwrite else None
for sql_str, data in self._get_data(tic_dat, as_sql=False):
con.execute(sql_str, list(data))
def write_file(self,
tic_dat,
file_path,
allow_overwrite=False,
case_space_sheet_names=False):
"""
write the ticDat data to an excel file
:param tic_dat: the data object to write (typically a TicDat)
:param file_path: The file path of the excel file to create
Needs to end in either ".xls" or ".xlsx"
The latter is capable of writing out larger tables,
but the former handles infinity seamlessly.
If ".xlsx", then be advised that +/- float("inf") will be replaced
with "inf"/"-inf", unless infinity_io_flag is being applied.
:param allow_overwrite: boolean - are we allowed to overwrite an
existing file?
case_space_sheet_names: boolean - make best guesses how to add spaces and upper case
characters to sheet names
:return:
caveats: None may be written out as an empty string. This reflects the behavior of xlwt.
"""
self._verify_differentiable_sheet_names()
verify(
utils.stringish(file_path)
and (file_path.endswith(".xls") or file_path.endswith(".xlsx")),
"file_path argument needs to end in .xls or .xlsx")
msg = []
if not self.tic_dat_factory.good_tic_dat_object(
tic_dat, lambda m: msg.append(m)):
raise TicDatError("Not a valid ticDat object for this schema : " +
" : ".join(msg))
verify(not os.path.isdir(file_path),
"A directory is not a valid xls file path")
verify(allow_overwrite or not os.path.exists(file_path),
"The %s path exists and overwrite is not allowed" % file_path)
if self.tic_dat_factory.generic_tables:
dat, tdf = utils.create_generic_free(tic_dat, self.tic_dat_factory)
return tdf.xls.write_file(dat, file_path, allow_overwrite,
case_space_sheet_names)
case_space_sheet_names = case_space_sheet_names and \
len(set(self.tic_dat_factory.all_tables)) == \
len(set(map(case_space_to_pretty, self.tic_dat_factory.all_tables)))
tbl_name_mapping = {
t: case_space_to_pretty(t) if case_space_sheet_names else t
for t in self.tic_dat_factory.all_tables
}
if file_path.endswith(".xls"):
self._xls_write(tic_dat, file_path, tbl_name_mapping)
else:
self._xlsx_write(tic_dat, file_path, tbl_name_mapping)
def write_file(self, tic_dat, file_path, allow_overwrite=False):
"""
write the ticDat data to an excel file
:param tic_dat: the data object to write (typically a TicDat)
:param file_path: The file path of the excel file to create
Needs to end in either ".xls" or ".xlsx"
The latter is capable of writing out larger tables,
but the former handles infinity seamlessly.
If ".xlsx", then be advised that +/- float("inf") will be replaced
with +/- 1e+100
:param allow_overwrite: boolean - are we allowed to overwrite an
existing file?
:return:
caveats: None may be written out as an empty string. This reflects the behavior of xlwt.
"""
self._verify_differentiable_sheet_names()
verify(
utils.stringish(file_path)
and (file_path.endswith(".xls") or file_path.endswith(".xlsx")),
"file_path argument needs to end in .xls or .xlsx")
msg = []
if not self.tic_dat_factory.good_tic_dat_object(
tic_dat, lambda m: msg.append(m)):
raise TicDatError("Not a valid ticDat object for this schema : " +
" : ".join(msg))
verify(not os.path.isdir(file_path),
"A directory is not a valid xls file path")
verify(allow_overwrite or not os.path.exists(file_path),
"The %s path exists and overwrite is not allowed" % file_path)
if self.tic_dat_factory.generic_tables:
dat, tdf = utils.create_generic_free(tic_dat, self.tic_dat_factory)
return tdf.xls.write_file(dat, file_path, allow_overwrite)
if file_path.endswith(".xls"):
self._xls_write(tic_dat, file_path)
else:
self._xlsx_write(tic_dat, file_path)
