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-')