def createtable(name, user, pwd):
try:
print("Creating MySQL table: {}".format(name))
mysql("-u", user, "-p{}".format(pwd), "-e", "CREATE DATABASE IF NOT EXISTS {};".format(name))
print("MySQL table created")
return 0
except:
print("MySQL failed")
return -1
def export_mysql_in_csv(self, tablename):
"""Export mysql database tables using 'mysql' command line tools
:param tablename: table name to export
:type tablename: str
"""
query = "select * from %s.%s" % (self.dbinfo["mysqldb_dbname"], tablename)
res_mysql = mysql(
"-u",
"{0}".format(self.dbinfo["mysqldb_userid"]),
"-p{0}".format(self.dbinfo["mysqldb_passwd"]),
"-h",
"{0}".format(self.dbinfo["mysqldb_hostname"]),
"-e",
"{0}".format(query),
"--batch",
)
# Add quote(") at first
# Then replace tab to ","
# , add quote(") between new line
# remove the last quote by [:-1] since \"\n\" adds dummy quote at last
output = '"' + str(res_mysql).replace("\t", '","').replace("\n", '"\n"')[:-1]
self.csv_data = output
return output
def export_mysql_in_csv(self, tablename):
"""Export mysql database tables using 'mysql' command line tools
:param tablename: table name to export
:type tablename: str
"""
query = "select * from %s.%s" % (self.dbinfo["mysqldb_dbname"],
tablename)
res_mysql = mysql("-u", "{0}".format(self.dbinfo["mysqldb_userid"]),
"-p{0}".format(self.dbinfo["mysqldb_passwd"]), "-h",
"{0}".format(self.dbinfo["mysqldb_hostname"]), "-e",
"{0}".format(query), "--batch")
# Add quote(") at first
# Then replace tab to ","
# , add quote(") between new line
# remove the last quote by [:-1] since \"\n\" adds dummy quote at last
output = "\"" + str(res_mysql) \
.replace("\t", "\",\"") \
.replace("\n", "\"\n\"") \
[:-1]
self.csv_data = output
return output
def copy_db(db, user_config):
domain = user_config['HOST_DOMAIN']
release = user_config['PRESTASHOP_RELEASE']
if db_exists(db):
loge("Database '{}' already exists".format(db['MYSQL_DATABASE']))
else:
logi("Creating db '{}' ...".format(db['MYSQL_DATABASE']))
sql(db, request="CREATE DATABASE {}".format(db['MYSQL_DATABASE']))
copied_sqldump_file = sql_filepath(domain, release)
logi("Importing dump file '{}' to database {}...".format(
copied_sqldump_file, db['MYSQL_DATABASE']))
#mysql("-h", db['MYSQL_HOST'], '-P', '3306', "-u", db['MYSQL_USER'], "-p" + db['MYSQL_PASSWORD'] , db['MYSQL_DATABASE'], '<', copied_sqldump_file )
mysql("-h",
db['MYSQL_HOST'],
'-P',
'3306',
"-u",
db['MYSQL_USER'],
"-p" + db['MYSQL_PASSWORD'],
db['MYSQL_DATABASE'],
_in=sh.cat(copied_sqldump_file))
def migrate():
print("Migrating from previous version")
for api in APIS[0:3]:
print("Migrating {} database...".format(api['name']))
name = api['bbdd']
dump_file_bk = '/tmp/{}_dump.sql'.format(api['name'])
dump_file = '/tmp/{}_dump_cp.sql'.format(api['name'])
cp(dump_file_bk, dump_file)
sed('-i', "s|:([0123456789\.]*)||g", dump_file)
mysql('-u', DBUSER, '-p{}'.format(DBPWD), '-h', DBHOST, '-P', DBPORT, '-e', "DROP DATABASE {}".format(name))
mysql('-u', DBUSER, '-p{}'.format(DBPWD), '-h', DBHOST, '-P', DBPORT, '-e', "CREATE DATABASE {}".format(name))
mysql('-u', DBUSER, '-p{}'.format(DBPWD), '-h', DBHOST, '-P', DBPORT, name, '-e', "source {}".format(dump_file))
if api['name'] == 'inventory':
conn = pymysql.connect(host=DBHOST, port=DBPORT, user=DBUSER, passwd=DBPWD, db=api['bbdd'])
cur = conn.cursor()
cur.execute("select * from PRODUCT_CHARACTERISTIC")
results = cur.fetchall()
for res in results:
sp_name = res[2].split(' ')
cur.execute(
"SELECT PRODUCT_OFFERING.ID FROM PRODUCT INNER JOIN PRODUCT_OFFERING ON PRODUCT.PRODUCT_OFFERING_PRODUCT_HJID=PRODUCT_OFFERING.HJID WHERE PRODUCT.ID={}".format(res[4]))
off_id = cur.fetchone()[0]
new_ids = None
if (res[2].lower().endswith('asset type') or res[2].lower().endswith('media type')) and len(sp_name) > 2:
new_ids = _process_ids(sp_name[0:-2], off_id)
name = '{} {}'.format(sp_name[-2], sp_name[-1])
elif res[2].lower().endswith('location') and len(sp_name) > 1:
new_ids = _process_ids(sp_name[0:-1], off_id)
name = sp_name[-1]
if new_ids is not None:
# Update characteristic name
new_name = ' '.join(new_ids)
new_name = new_name + ' ' + name
cur.execute("UPDATE PRODUCT_CHARACTERISTIC SET NAME_ = '{}' WHERE HJID={}".format(new_name, res[0]))
conn.commit()
conn.close()
print("Database {} migrated".format(api['name']))
def remove_database(db_user, db_password, db_name):
logi("Removing db '{}' ...".format(db_name))
mysql("-u", db_user, "-p" + db_password, "-e",
"DROP DATABASE IF EXISTS {}".format(db_name))
def sql(db, request):
return mysql("-h", db['MYSQL_HOST'], '-P', '3306', "-u", db['MYSQL_USER'],
"-p" + db['MYSQL_PASSWORD'], "-e", request)
import os
from pathlib import Path
import sh
HOME = Path.home()
HOME = Path(os.path.expanduser("~bn"))
DATA_DIR = Path.joinpath(Path(HOME), 'data', 'backup', 'mysql', 'data')
LOG_DIR = Path.joinpath(Path(HOME), 'data', 'backup', 'mysql', 'logs')
print(DATA_DIR)
#cmd = "find ${DATA_DIR} -maxdepth 1 -type d -regextype sed -regex \"^\/.*${DB}\-[0-9].*\" -printf '%Ts\t%p\n' | sort -n | head -n -2 | cut -f 2- | xargs rm -rf"
# a =`echo "show databases;" | mysql `
DATA_BASES = sh.mysql(sh.echo('show databases;'))
DATA_BASES = [el.strip() for el in DATA_BASES]
DATA_BASES = DATA_BASES[
1:] # first entry is 'Database' which is not a Database
DATA_BASES += ['All-Databases']
DATA_BASES = ['trading_oanda_d1']
DATESTAMP = sh.date("+%Y-%m-%d_%H:%M").strip()
for DB in DATA_BASES:
for DD in [DATA_DIR, LOG_DIR]:
# step a): delete all except the latest two files for each database
print(f'database: {DB}; dir: {DD}')
a = sh.find(DATA_DIR, '-maxdepth', '1', '-type', 'f', '-regextype',
'sed', '-regex', f'^/.*{DB}\-[0-9].*', '-printf',
'%Ts\t%p\n')
b = sh.sort(a, '-n')
c = sh.head(b, '-n', '-2')
d = sh.cut(c, '-f', '2-')
