Python連線MySQL資料庫

一只竹节虫發表於2024-08-20
PythonMySql資料庫

連線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

相關文章