python mssql将下列功能封装成简单使用的函数：链接、备份、还原、查询、更新、删除、查询并直接导出excel文件

python

import pymssql
import pandas as pd
from openpyxl import Workbook

# 数据库连接函数
def connect_to_mssql(server, user, password, database):
    try:
        conn = pymssql.connect(server=server, user=user, password=password, database=database)
        return conn
    except pymssql.Error as e:
        print("连接数据库时出错:", e)
        return None

# 数据库备份函数
def backup_database(conn, backup_file):
    try:
        with conn.cursor() as cursor:
            cursor.execute(f'BACKUP DATABASE {conn.database} TO DISK = \'{backup_file}\'')
        conn.commit()
    except pymssql.Error as e:
        print("备份数据库时出错:", e)

# 数据库还原函数
def restore_database(conn, restore_file):
    try:
        with conn.cursor() as cursor:
            cursor.execute(f'RESTORE DATABASE {conn.database} FROM DISK = \'{restore_file}\'')
        conn.commit()
    except pymssql.Error as e:
        print("还原数据库时出错:", e)

# 数据库查询函数
def query_database(conn, query):
    try:
        df = pd.read_sql_query(query, conn)
        return df
    except pymssql.Error as e:
        print("查询数据库时出错:", e)
        return None

# 数据库更新函数
def update_database(conn, update_query):
    try:
        with conn.cursor() as cursor:
            cursor.execute(update_query)
        conn.commit()
    except pymssql.Error as e:
        print("更新数据库时出错:", e)

# 数据库删除函数
def delete_from_database(conn, delete_query):
    try:
        with conn.cursor() as cursor:
            cursor.execute(delete_query)
        conn.commit()
    except pymssql.Error as e:
        print("删除数据时出错:", e)

# 数据库查询并导出 Excel 函数
def query_and_export_to_excel(conn, query, excel_file):
    try:
        df = query_database(conn, query)
        if df is not None:
            wb = Workbook()
            ws = wb.active
            for row in df.values:
                ws.append(row)
            wb.save(excel_file)
    except Exception as e:
        print("查询并导出到 Excel 时出错:", e)

使用示例：

python

server = 'your_server_name'
user = 'your_username'
password = 'your_password'
database = 'your_database_name'

conn = connect_to_mssql(server, user, password, database)

backup_file = 'backup.bak'
backup_database(conn, backup_file)

restore_file = 'estore.bak'
restore_database(conn, restore_file)

query = 'SELECT * FROM your_table'
df = query_database(conn, query)

update_query = 'UPDATE your_table SET column1 = value1 WHERE condition'
update_database(conn, update_query)

delete_query = 'DELETE FROM your_table WHERE condition'
delete_from_database(conn, delete_query)

excel_file = 'data.xlsx'
query_and_export_to_excel(conn, query, excel_file)

conn.close()