def data_backup(self, table_name):
filename = table_name + '.txt'
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
source_path = self.source_dir + filename # /tmp/product.txt
# if os.path.exists(source_path):
# os.chown(source_path, uid, gid)
# os.remove(source_path)
backup_sql = "SELECT * FROM {} INTO OUTFILE '{}' {}".format(
table_name, source_path, CoffeeProj.OPTION)
print(backup_sql)
cursor.execute(backup_sql)
# if not os.path.exists(self.data_dir):
# os.makedirs(os.path.join('data'))
# shutil.move(source_path, self.data_dir + '/' + filename) # 파일이 존재하면 overwrite
# shutil.copy(source_path, self.data_dir + '/' + filename)
print(table_name, "backup complete!")
except Error as err:
print(err)
finally:
if conn.is_connected():
cursor.close()
conn.close()
def data_backup(self, table_name):
filename = table_name + '.txt'
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute("USE {}".format(self._db['database_name']))
source_path = os.path.abspath(self.data_dir + filename).replace(
'\\', '/')
# source_path = self.source_dir + filename
# print('source_path =', source_path)
# if os.path.exists(source_path):
# os.remove(source_path)
backup_sql = "SELECT * FROM {} INTO OUTFILE '{}' {}".format(
table_name, source_path, DBInitservice.OPTION)
# print("backup_sql ", backup_sql)
cursor.execute(backup_sql)
print(table_name, "backup complete!")
except Error as err:
print(err)
finally:
if conn.is_connected():
cursor.close()
conn.close()
def __create_procedure(self):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute("use {}".format(self._db['database_name']))
for procedure_name, procedure_sql in self._db['procedure'].items():
try:
print("creating procedure {}: ".format(procedure_name),
end='')
cursor.execute(procedure_sql)
except Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("already exists.")
self.idx_trigger = 0
else:
print(err.msg)
self.idx_trigger = 0
else:
print("OK")
except Error as err:
print(err)
self.idx_trigger = 0
finally:
cursor.close()
conn.close()
def __create_user(self):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
print("Creating user: "******"OK")
except Error as err:
print(err)
finally:
cursor.close()
conn.close()
def __create_database(self):
try:
sql = read_ddl_file()
conn = ConnectionPool().get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(
"create database {} default character set 'utf8'".format(
self._db['database_name']))
print("create database {}".format(self._db['database_name']))
except Error as err:
if err.errno == errorcode.ER_DB_CREATE_EXISTS:
cursor.execute("drop database {} ".format(
self._db['database_name']))
print("drop database {}".format(self._db['database_name']))
cursor.execute(
"create database {} default character set 'utf8'".format(
self._db['database_name']))
print("create database {}".format(self._db['database_name']))
else:
print(err.msg)
self.idx_database = 0
finally:
cursor.close()
conn.close()
def data_restore(self, table_name):
filename = table_name + '.txt'
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
source_path = self.source_dir + filename # /tmp/product.txt
restore_sql = "LOAD DATA INFILE '{}' INTO TABLE {} {}".format(
source_path, table_name, CoffeeProj.OPTION)
print(restore_sql)
cursor.execute(restore_sql)
conn.commit()
print(table_name, "restore complete!")
except Error as err:
print(err)
finally:
if conn.is_connected():
cursor.close()
conn.close()
def __create_table(self):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute("USE {}".format(self._db['database_name']))
for table_name, table_sql in self._db['sql'].items():
try:
print("Creating table {}:".format(table_name), end='')
cursor.execute(table_sql)
except Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("already exists.")
else:
print(err.msg)
else:
print("OK")
except Error as err:
print(err)
finally:
cursor.close()
conn.close()
def __create_database(self):
try:
sql = read_ddl_file()
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(
"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(
self._db['database_name']))
print("CREATE DATABASE {}".format(self._db['database_name']))
except Error as err:
if err.errno == errorcode.ER_DB_CREATE_EXISTS:
cursor.execute("DROP DATABASE {} ".format(
self._db['database_name']))
print("DROP DATABASE {}".format(self._db['database_name']))
cursor.execute(
"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(
self._db['database_name']))
print("CREATE DATABASE {}".format(self._db['database_name']))
else:
print(err.msg)
finally:
cursor.close()
conn.close()
def data_backup(self, table_name):
filename = table_name + '.csv'
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute("use {}".format(self._db['database_name']))
source_path = self.source_dir + filename
if os.path.exists(source_path):
os.remove(source_path)
backup_sql = "SELECT * FROM {} INTO OUTFILE '{}' {}".format(
table_name, source_path, BackupRestore.OPTION)
cursor.execute(backup_sql)
print(table_name, "backup complete!")
except Error as err:
print(err)
print(table_name, "backup fail!")
self.idx_backup = 0
finally:
if conn.is_connected():
cursor.close()
conn.close()
def data_restore(self, table_name):
filename = table_name + '.txt'
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute("USE {}".format(self._db['database_name']))
data_path = os.path.abspath(self.data_dir + filename).replace(
'\\', '/')
if not os.path.exists(data_path):
print("파일 '{}' 이 존재하지 않음".format(data_path))
return
restore_sql = "LOAD DATA INFILE '{}' INTO TABLE {} {}".format(
data_path, table_name, DBInitservice.OPTION) # ubuntu
cursor.execute(restore_sql)
conn.commit()
print(table_name, "restore complete!")
except Error as err:
print(err)
print(table_name, "restore Fail!")
finally:
if conn.is_connected():
cursor.close()
conn.close()
def data_restore(self, table_name):
filename = table_name + '.csv'
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
data_path = os.path.abspath(self.source_dir + filename).replace(
'\\', '/')
cursor.execute("use {}".format(self._db['database_name']))
if not os.path.exists(data_path):
print("file '{}' does not exist.".format(data_path))
return
restore_sql = "LOAD DATA INFILE '{}' INTO TABLE {} {}".format(
data_path, table_name, BackupRestore.OPTION)
cursor.execute(restore_sql)
conn.commit()
print(table_name, "restore complete!")
except Error as err:
print(err)
print(table_name, "restore fail!")
self.idx_restore = 0
finally:
if conn.is_connected():
cursor.close()
conn.close()