Python连接MySQL数据库

连接Mysql数据库

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb

# 连接数据库
db = MySQLdb.connect(host="localhost", user="zabbix", passwd="123123", db="zabbix")

# 创建cursor对象
cursor = db.cursor()

# 执行SQL查询
cursor.execute("SELECT VERSION()")

# 获取查询结果
data = cursor.fetchone()
print("Database version : %s " % data)

# 关闭数据库连接
db.close()

 

连接Mysql数据库并在脚本中调用SQL文件

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb

# 数据库连接参数
config = {
'host': 'localhost',
'user': 'zabbix',
'passwd': '123123',
'db': 'zabbix'
}

# 创建数据库连接
conn = MySQLdb.connect(**config)
cursor = conn.cursor()

# 读取并执行SQL文件
with open('insert.sql', 'r') as sql_file:
sql_script = sql_file.read()

# 执行SQL脚本
for statement in sql_script.split(';'):
if statement.strip():
cursor.execute(statement)

# 提交事务
conn.commit()

# 关闭连接
cursor.close()
conn.close()

在Python脚本中实现对MySQL的备份

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import time

# 数据库连接信息
HOST = "localhost"
USER = "zabbix"
PASSWORD = "123123"
DB = "zabbix"

# 备份文件名（包含时间戳）
backup_file = "/root/zabbix_backup_" + time.strftime("%Y%m%d%H%M%S") + ".sql"

# 构建 mysqldump 命令
mysqldump_cmd = (
    'mysqldump -h {host} -u {user} -p{password} {db} > "{backup_file}"'
).format(
    host=HOST,
    user=USER,
    password=PASSWORD,
    db=DB,
    backup_file=backup_file
)

try:
    # 执行 mysqldump 命令
    os.system(mysqldump_cmd)
    print("backup successful:", backup_file)
except Exception as e:
    print("error:", e)

 在Python脚本中查询MySQL状态信息并输出结果集

监控数据库的查询SQL脚本可以写进Monitor.sql 如下

-- Server Version
SELECT VERSION();

-- Database Size
SELECT table_schema AS 'Database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY 'Size (MB)' DESC;

-- Table Status
SHOW TABLE STATUS;

-- InnoDB Status
SHOW ENGINE INNODB STATUS;

-- Connection Status
SHOW STATUS LIKE 'Threads_connected';

-- Query Status
SHOW STATUS LIKE 'Queries';

-- Lock Status
SHOW STATUS LIKE 'Innodb_row_lock_waits';

-- Cache Hit Rate
SHOW STATUS LIKE 'Qcache_hits';
SHOW STATUS LIKE 'Qcache_inserts';
SHOW STATUS LIKE 'Qcache_lowmem_prunes';

-- Error Log Location
SHOW VARIABLES LIKE 'log_error';

接下来，更 Python 脚本以读取和执行 .sql 文件中的查询

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import MySQLdb

# 数据库连接参数
HOST = "localhost"
USER = "zabbix"
PASSWORD = "123123"
DB = "zabbix"

# 输出文件路径
OUTPUT_FILE = "/root/mysql_status.txt"
SQL_FILE = "/root/monitor.sql"

def read_sql_file(file_path):
    """读取 SQL 文件内容"""
    with open(file_path, 'r') as file:
        return file.read()

def execute_query(cursor, query, f):
    """执行查询并将结果写入文件"""
    try:
        cursor.execute(query)
        while True:
            results = cursor.fetchall()
            if not results:
                break
            for row in results:
                f.write(str(row) + "\n")
            f.write("\n")
    except MySQLdb.Error as e:
        f.write("Error: %s\n" % e)
        f.write("\n")
    finally:
        # Ensure all results are fetched and cleared
        cursor.nextset()

def query_mysql_status():
    # 连接到 MySQL 数据库
    conn = MySQLdb.connect(host=HOST, user=USER, passwd=PASSWORD, db=DB)
    cursor = conn.cursor()

    # 读取 SQL 文件
    sql_content = read_sql_file(SQL_FILE)
    queries = sql_content.split(';')

    with open(OUTPUT_FILE, 'w') as f:
        for query in queries:
            query = query.strip()
            if query:
                f.write("Executing query:\n" + query + "\n")
                execute_query(cursor, query, f)

    # 关闭数据库连接
    cursor.close()
    conn.close()

if __name__ == "__main__":
    query_mysql_status()
    print "Query results have been saved to:", OUTPUT_FILE