def select(self):
'''
select all data from table
:return:
'''
try:
with Lock(self.lock):
with open(self.data_file, "r") as fdata:
models = []
#read field names
nfields = strips(fdata.readline().strip().split(","))
#read data records
data = fdata.readline()
while data:
data = data.strip()
vfields = strips(data.split(","))
model = {}
for idx in range(0, len(nfields)):
model[nfields[idx]] = str2obj(vfields[idx], ',')
models.append(model)
data = fdata.readline()
return models
except Exception as e:
Logger.info("select data from table %s...failed. error: %s", self.name, str(e))
raise e
def load(self, dbpath, name):
'''
load table
:return: self
'''
try:
#initialize table parameters
self.name = name
self.path = join_paths(dbpath, name)
self.table_file = join_paths(self.path, "table")
self.data_file = join_paths(self.path, "data")
self.table = self.desc()
#load data file
if not is_file(self.data_file):
#create data file if not exists
self._create_data_file()
else:
#replace old data file if needed
with open(self.data_file) as fdata:
nfields = strips(fdata.readline().split(","))
if self.table.nfields() != nfields:
if is_subset(nfields, self.table.nfields()):
self._upgrade_data_file()
else:
self._replace_data_file()
Logger.info("loading table %s...success.", self.name)
return self
except Exception as e:
Logger.info("loading table %s...failed. error: %s", self.name, str(e))
raise e
def create(self, dbc, table):
'''
create table
:return self
'''
try:
#initialize table parameters
self.dbc = dbc
self.table = table
self.name = table.name
#check if table has exists
if self._exists_table():
#exists table
old_table = self.desc()
if old_table != self.table:
if is_subset(old_table.nfields(), self.table.nfields()):
#upgrade table
self._upgrade_table()
else:
#replace table
self._replace_table
else:
#table is the same as in database
pass
else:
#create new table
self._create_table()
Logger.info("create table %s...success.", self.name)
return self
except Exception as e:
Logger.error("create table %s...failed. error: %s", self.name,
str(e))
raise e
def select(self):
'''
select all data from table
:return:
'''
try:
nfields = self.table.nfields()
sql, models = "select %s from %s;" % (",".join(quotes(
nfields, '`')), self.name), []
cursor = self.dbc.cursor()
cursor.execute(sql)
results = cursor.fetchall()
for result in results:
model = {}
for idx in range(0, len(result)):
nfield = nfields[idx]
vfield = result[idx]
if isinstance(vfield, str):
vfield = unescapes(vfield)
model[nfield] = vfield
models.append(model)
Logger.info("select from table %s...success", self.name)
return models
except Exception as e:
Logger.error("select from table %s...failed. error %s", self.name,
str(e))
raise e
def drop(self):
'''
drop table
:return:
'''
try:
remove_dir(self.path)
except Exception as e:
Logger.error("drop table %s...failed. error %s", self.name, str(e))
raise e
def truncate(self):
'''
truncate table
:return:
'''
try:
with Lock(self.lock):
remove_files(self.data_file)
self._create_data_file()
except Exception as e:
Logger.error("truncate table %s...failed. error %s", self.name, str(e))
raise e
def desc(self):
'''
descrite table from store
:return: Table
'''
try:
with open(self.table_file) as ftable:
table = Table().fromstr(ftable.read())
return table
except Exception as e:
Logger.info("describe table %s...failed. error: %s", self.name, str(e))
raise e
def drop(self):
'''
drop table
:return:
'''
try:
sql = "drop table if exists %s;" % self.name
self.dbc.cursor().execute(sql)
Logger.info("drop table %s...success", self.name)
except Exception as e:
Logger.error("drop table %s...failed. error %s", self.name, str(e))
raise e
def truncate(self):
'''
truncate table
:return:
'''
try:
sql = "truncate table %s;" % table.name
self.dbc.cursor().execute(sql)
Logger.info("truncate table %s...success", self.name)
except Exception as e:
Logger.error("truncate table %s...failed. error %s", self.name,
str(e))
raise e
def create(self, dbpath, table):
'''
create table
:return self
'''
try:
#initialize table parameters
self.table = table
self.name = table.name
self.path = join_paths(dbpath, table.name)
self.table_file = join_paths(self.path, "table")
self.data_file = join_paths(self.path, "data")
#create table directory if it is not exists
make_dirs(self.path)
#create or replace table file
if is_file(self.table_file):
#replace old table file if needed
old_table = self.desc()
if self.table != old_table:
#replace table file
self._replace_table_file()
else:
#new table is same as exists table
pass
else:
#create new table file
self._create_table_file()
#create or upgrade or replace data file
if is_file(self.data_file):
#replace old data file if needed
with open(self.data_file) as fdata:
nfields = strips(fdata.readline().split(","))
if self.table.nfields() != nfields:
if is_subset(nfields, self.table.nfields()):
self._upgrade_data_file()
else:
self._replace_data_file()
else:
#create new data file
self._create_data_file()
Logger.info("create table %s...success.", self.name)
return self
except Exception as e:
Logger.error("create table %s...failed. error: %s", self.name, str(e))
raise e
def insert(self, models):
'''
insert data to table
:param models:
:return:
'''
try:
with Lock(self.lock):
with open(self.data_file, "a") as fdata:
lines = []
for model in models:
vfields = []
for nfield in self.table.nfields():
vfields.append(objtostr(model.get(nfield), ','))
lines.append("%s\n" % ",".join(vfields))
fdata.writelines(lines)
except Exception as e:
Logger.info("insert data to table %s...failed. error: %s", self.name, str(e))
raise e
def desc(self):
'''
descrite table from store
:return: Table
'''
try:
sql = "show create table %s;" % self.name
cursor = self.dbc.cursor()
cursor.execute(sql)
create_sql = cursor.fetchall()[0][1]
table = Table().fromsql(create_sql)
Logger.info("describe table %s...success", self.name)
return table
except Exception as e:
Logger.error("describe table %s...failed. error %s", self.name,
str(e))
raise e
def load(self, dbc, name):
'''
load table from database
:return: self or None
'''
try:
#initialize table parameters
self.dbc = dbc
self.name = name
#create table structure
sql = "show create table %s;" % self.name
cursor = self.dbc.cursor()
cursor.execute(sql)
create_sql = cursor.fetchall()[0][1]
self.table = Table().fromsql(create_sql)
Logger.info("loading table %s...success.", self.name)
return self
except Exception as e:
Logger.info("loading table %s...failed. error: %s", self.name,
str(e))
raise e
def insert(self, models):
'''
insert data to table
:param models:
:return:
'''
try:
#get fields except default auto increment field
nfields = []
for field in self.table.fields:
if not isinstance(field.default, AutoIncValue):
nfields.append(field.name)
#prepare the values to be inserted
values = []
for model in models:
value = []
for nfield in nfields:
vfield = model.get(nfield)
if isinstance(vfield, str):
value.append(quotes(escapes(vfield)))
else:
value.append(objtostr(vfield))
values.append("(%s)" % ",".join(value))
sql = "insert into %s(%s) values %s;" % (self.name, ",".join(
quotes(nfields, '`')), ",".join(values))
self.dbc.cursor().execute(sql)
self.dbc.commit()
Logger.info("insert into table %s...success", self.name)
except Exception as e:
Logger.error("insert into table %s...failed. error %s", self.name,
str(e))
raise e
def mkdir_by_date(dst, dt):
path = os.path.join(dst, str(dt.year), str(dt.month), str(dt.day))
os.makedirs(path, exist_ok=True)
return path