连接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
标签:Python,数据库,sql,cursor,--,file,MySQL,query From: https://www.cnblogs.com/dll102/p/18369438