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()