def merge_to_united_db(self, file_paths):
if not file_paths:
return False
if not self.connect:
self.connect_to_unite_db()
cursor = self.connect.cursor()
# read files
for file_path in file_paths:
sql_1 = 'attach "' + file_path + '" as toMerge'
sql_2 = 'BEGIN'
sql_3 = 'insert into ' + self.SQLITE_TABLE_NAME + ' select * from toMerge.' + self.SQLITE_TABLE_NAME
sql_4 = 'COMMIT'
sql_5 = 'detach toMerge'
try:
cursor.execute(sql_1)
cursor.execute(sql_2)
cursor.execute(sql_3)
cursor.execute(sql_4)
cursor.execute(sql_5)
except sqlite3.Error as e:
Debug.log(self, 'SQLite: ' + e.msg)
return self.united_db_file
def create_table(self, table_name):
cursor = self.connect.cursor()
sql = '''
CREATE TABLE IF NOT EXISTS "''' + table_name + '''"
(
"unix_time" INT PRIMARY KEY,
"customer_id" INTEGER,
"cs_av_customer" TEXT,
"website_id" INTEGER,
"product_id" INTEGER,
"category_id" INTEGER,
"country" TEXT,
"city" TEXT,
"device_type" TEXT,
"device_brand" TEXT,
"device_model" TEXT,
"is_bot" INTEGER,
"month_current" INTEGER,
"bought_product" INTEGER
)
'''
try:
cursor.execute(sql)
except sqlite3.Error as e:
Debug.log(self, 'SQLite: ' + str(e))
return True
def connect_to_db(self, file_path):
try:
self.connect = sqlite3.connect(file_path)
except sqlite3.Error as e:
Debug.log(self, 'SQLite: ' + str(e))
return False
return self.connect
def __init__(self):
# Create a client and connect it to the cluster
try:
self.client = aerospike.client(self.config).connect()
except Exception as e:
error = 'AeroSpike: failed connect to cluster : ' + str(
self.config['hosts'])
Debug.log(self, error)
sys.exit(error)
def as_row_write(self, row, primary_key, table='cs_merchant_events'):
# Records are addressable via a tuple of (namespace(DB name), set(Table Name), key(Primary Key))
param = (self.namespace, table, str(primary_key))
try:
return self.client.put(param, row)
except Exception as e:
if hasattr(e, 'msg'):
Debug.log(self, 'AeroSpike: as_row_write - ' + e.msg)
else:
Debug.log(self, 'AeroSpike: as_row_write - ' + str(e))
return False
def get_db_files(self, mode='product'):
path_to_data = self.get_merchant_files_dir(mode)
path_to_files = path_to_data + self.DS + self.FILES_MASK
file_paths = glob.glob(path_to_files)
# Debug.log(self, 'Files: ' + str(file_paths))
if not file_paths:
Debug.log(self,
'FileSQLite: no files in dir: ' + str(path_to_files))
return False
return file_paths
def get_merchant_files_dir(self, directory='product'):
merchant_path = os.path.join(
self.path_to_project,
self.DIR + self.DS + str(self.merchant_id) + self.DS + directory)
if not os.path.isdir(merchant_path):
Debug.log(
self, 'FileSQLite: merchant id: ' + str(self.merchant_id) +
' dir: ' + directory + ' is invalid')
return False
return merchant_path
def as_row_read(self, primary_key, table='cs_merchant_events'):
# Records are addressable via a tuple of (namespace(DB name), set(Table Name), key(Primary Key))
param = (self.namespace, table, primary_key)
try:
(key, metadata, row) = self.client.get(param)
except Exception as e:
if hasattr(e, 'msg'):
Debug.log(self, 'AeroSpike: as_row_read - ' + e.msg)
else:
Debug.log(self, 'AeroSpike: as_row_read - ' + str(e))
return False
return row
def sqlite_select(self, sql, return_pandas=True, associated=True):
if associated:
self.connect.row_factory = sqlite3.Row
try:
if return_pandas:
return pd.read_sql_query(sql, self.connect)
else:
cursor = self.connect.cursor()
cursor.execute(sql)
return cursor.fetchall()
except sqlite3.Error as e:
Debug.log(self, 'SQLite: ' + str(e))