#########################################################################
#    File Name: query_mysql.py
#    Author: eight
#    Mail: 18847097110@163.com 
#    Created Time: Thu 18 Apr 2024 11:12:26 AM CST
#########################################################################
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import pymysql
import configparser
import os
from datetime import datetime
import DefaultColor
import subprocess

# 读取mysql配置文件
def mysql_config():
	config = configparser.ConfigParser()
	config.read("config.ini")
	# 获取配置信息
	host = config.get("MYSQL", "MYSQL_HOST")
	username = config.get("MYSQL", "MYSQL_USER")
	password = config.get("MYSQL", "MYSQL_PASSWORD")
	port = config.get("MYSQL", "MYSQL_PORT")
	db = config.get("MYSQL", "MYSQL_DB")
	print("--------------------------------------------------------")
	return {
			"host": host,
			"username": username,
			"password": password,
			"port": port,
			"db": db
			}

	
# 创建备份目录
def mkdir():
	dir_path = os.getcwd()
	print("--------------------------------------------------------")
	print("当前脚本工作目录：", dir_path)
	mysql_backup_dir = "/opt/mysql_backup"
	print("--------------------------------------------------------")
	if not os.path.exists(mysql_backup_dir):
		os.makedirs(mysql_backup_dir)
	else:
		return f"数据库备份目录 '{mysql_backup_dir}' 已经存在，跳过创建"
	return "数据库备份目录：" + DefaultColor.Color.GREEN + mysql_backup_dir + DefaultColor.Color.END + " 创建成功"


# 查询sql语句
def select():
	mysql_connect_info = mysql_config()
	# 创建mysql连接信息 
	conn = pymysql.connect(host=mysql_connect_info["host"],
							user=mysql_connect_info["username"],
							password=mysql_connect_info["password"],
							port=int(mysql_connect_info["port"]),
							db=mysql_connect_info["db"])																													
	cur = conn.cursor()
	# 执行查询语句
	cur.execute("use docker;")
	cur.execute("select * from DockerImages;")
	print("查询到的数据是：")
	# 接收全部的返回结果行
	result = cur.fetchall()
	while True:
		resp = cur.fetchone()
		if resp is None:
			break
	# 关闭查询游标
	cur.close()
	# 提交
	conn.commit()
	# 关闭链接，释放计算机资源
	conn.close()
	return result


# 备份数据库 
def backup(mysql_backup_dir):
	# 获取配置信息
	current_datetime = datetime.now()
	formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
	mysql_connect_info = mysql_config()
	try:
		command = f'docker exec -it mysql-container /bin/bash -c "mysqldump -h {mysql_connect_info["host"]} -u {mysql_connect_info["username"]} -p{mysql_connect_info["password"]} -P {mysql_connect_info["port"]} -- {mysql_connect_info["db"]} > {mysql_backup_dir}-{mysql_connect_info["db"]}-{formatted_datetime}.sql"'
		subprocess.run(command, shell=True, check=True)
		
	except subprocess.CalledProcessError as e:
		print("Database backup failed:", e)
	return f'数据库：{mysql_connect_info["db"]}备份完成: {mysql_backup_dir}-{mysql_connect_info["db"]}-{formatted_datetime}.sql'



if __name__ == '__main__':
	backup_dir = mkdir()
	print(backup_dir)

	sector = select()
	#循环打印所有数据，如果此代码是封装在函数内部，那么使用return的话，只会输出第一条数据，所以要在外部调用
	for row in sector:
		print(row)
	backup = backup("/opt/mysql_backup")
	print(backup)