Python Access：删除数据库中指定的表和查询

函数说明

delete_access_objects 函数具有以下特点：

1. 参数验证：

   • 检查数据库文件是否存在
   • 处理空的表名和查询名列表

2. 安全删除：

   • 在删除前检查对象是否存在
   • 使用异常处理确保程序不会因单个对象删除失败而中断

3. 资源管理：

   • 使用 try/finally 块确保正确关闭数据库和 Access 应用程序
   • 避免资源泄漏

4. 反馈信息：

   • 提供详细的操作反馈，包括成功删除的对象和跳过的对象

使用注意事项

1. 备份重要数据：删除操作不可逆，建议在执行前备份数据库

import win32com.client as win32
import os

def delete_access_objects(mdb_path, table_names=None, query_names=None):
    """
    删除指定 Access 数据库中的表和查询
    
    参数:
        mdb_path: Access 数据库文件路径 (.mdb 或 .accdb)
        table_names: 要删除的表名称列表
        query_names: 要删除的查询名称列表
    """
    # 初始化参数为空列表（如果未提供）
    if table_names is None:
        table_names = []
    if query_names is None:
        query_names = []
    
    # 检查文件是否存在
    if not os.path.exists(mdb_path):
        print(f"错误：数据库文件不存在 - {mdb_path}")
        return False
    
    access = None
    try:
        # 创建 Access 应用程序对象
        access = win32.Dispatch("Access.Application")
        access.Visible = False  # 不显示 Access 界面
        
        # 打开数据库
        access.OpenCurrentDatabase(mdb_path)
        db = access.CurrentDb()
        
        # 删除指定的表
        for table_name in table_names:
            try:
                # 检查表是否存在
                table_exists = False
                for table in db.TableDefs:
                    if table.Name == table_name:
                        table_exists = True
                        break
                
                if table_exists:
                    db.TableDefs.Delete(table_name)
                    print(f"已删除表: {table_name}")
                else:
                    print(f"表不存在，跳过: {table_name}")
            except Exception as e:
                print(f"删除表 {table_name} 时出错: {str(e)}")
        
        # 删除指定的查询
        for query_name in query_names:
            try:
                # 检查查询是否存在
                query_exists = False
                for query in db.QueryDefs:
                    if query.Name == query_name:
                        query_exists = True
                        break
                
                if query_exists:
                    db.QueryDefs.Delete(query_name)
                    print(f"已删除查询: {query_name}")
                else:
                    print(f"查询不存在，跳过: {query_name}")
            except Exception as e:
                print(f"删除查询 {query_name} 时出错: {str(e)}")
        
        print("操作完成!")
        return True
        
    except Exception as e:
        print(f"操作过程中发生错误: {str(e)}")
        return False
    finally:
        # 确保关闭 Access 应用程序
        if access:
            access.CloseCurrentDatabase()
            access.Quit()
            access = None

# 使用示例
if __name__ == "__main__":
    # 指定数据库路径
    database_path = r"C:\Path\To\Your\Database.mdb"
    
    # 指定要删除的表和查询名称列表
    tables_to_delete = ["OldTable1", "TempTable", "ObsoleteData"]
    queries_to_delete = ["OldQuery", "TempQuery", "UnusedReport"]
    
    # 执行删除操作
    delete_access_objects(database_path, tables_to_delete, queries_to_delete)

V 1.5

import win32com.client as win32
import os

def delete_access_objects(mdb_path, table_names=None, query_names=None):
    """
    删除指定 Access 数据库中的表和查询
    
    参数:
        mdb_path: Access 数据库文件路径 (.mdb 或 .accdb)
        table_names: 要删除的表名称列表
        query_names: 要删除的查询名称列表
    """
    # 初始化参数为空列表（如果未提供）
    table_names = table_names or []
    query_names = query_names or []
    
    # 检查文件是否存在
    if not os.path.exists(mdb_path):
        print(f"错误：数据库文件不存在 - {mdb_path}")
        return False
    
    access = None
    try:
        # 创建 Access 应用程序对象
        access = win32.Dispatch("Access.Application")
        access.Visible = False  # 不显示 Access 界面
        
        # 打开数据库
        access.OpenCurrentDatabase(mdb_path)
        db = access.CurrentDb()
        
        # 预先获取所有表和查询的名称集合（一次性读取，避免重复循环）
        all_tables = {table.Name for table in db.TableDefs}
        all_queries = {query.Name for query in db.QueryDefs}
        
        # 删除指定的表（使用集合交集提高效率）
        tables_to_delete = set(table_names) & all_tables
        tables_not_found = set(table_names) - all_tables
        
        for table_name in tables_not_found:
            print(f"表不存在，跳过: {table_name}")
            
        for table_name in tables_to_delete:
            try:
                db.TableDefs.Delete(table_name)
                print(f"已删除表: {table_name}")
            except Exception as e:
                print(f"删除表 {table_name} 时出错: {str(e)}")
        
        # 删除指定的查询（使用集合交集提高效率）
        queries_to_delete = set(query_names) & all_queries
        queries_not_found = set(query_names) - all_queries
        
        for query_name in queries_not_found:
            print(f"查询不存在，跳过: {query_name}")
            
        for query_name in queries_to_delete:
            try:
                db.QueryDefs.Delete(query_name)
                print(f"已删除查询: {query_name}")
            except Exception as e:
                print(f"删除查询 {query_name} 时出错: {str(e)}")
        
        print("操作完成!")
        return True
        
    except Exception as e:
        print(f"操作过程中发生错误: {str(e)}")
        return False
    finally:
        # 确保关闭 Access 应用程序
        if access:
            try:
                access.CloseCurrentDatabase()
                access.Quit()
            except:
                pass  # 忽略关闭时的错误
            finally:
                access = None

# 更快的版本 - 如果需要极致性能，可以禁用所有输出
def delete_access_objects_fast(mdb_path, table_names=None, query_names=None, verbose=False):
    """
    快速删除指定 Access 数据库中的表和查询（禁用输出以提高速度）
    """
    table_names = table_names or []
    query_names = query_names or []
    
    if not os.path.exists(mdb_path):
        if verbose:
            print(f"错误：数据库文件不存在 - {mdb_path}")
        return False
    
    access = None
    try:
        access = win32.Dispatch("Access.Application")
        access.Visible = False
        
        access.OpenCurrentDatabase(mdb_path)
        db = access.CurrentDb()
        
        # 一次性获取所有对象名称
        all_tables = {table.Name for table in db.TableDefs}
        all_queries = {query.Name for query in db.QueryDefs}
        
        # 批量删除表
        for table_name in set(table_names) & all_tables:
            try:
                db.TableDefs.Delete(table_name)
                if verbose:
                    print(f"已删除表: {table_name}")
            except Exception:
                if verbose:
                    print(f"删除表 {table_name} 时出错")
        
        # 批量删除查询
        for query_name in set(query_names) & all_queries:
            try:
                db.QueryDefs.Delete(query_name)
                if verbose:
                    print(f"已删除查询: {query_name}")
            except Exception:
                if verbose:
                    print(f"删除查询 {query_name} 时出错")
        
        if verbose:
            print("操作完成!")
        return True
        
    except Exception as e:
        if verbose:
            print(f"操作过程中发生错误: {str(e)}")
        return False
    finally:
        if access:
            try:
                access.CloseCurrentDatabase()
                access.Quit()
            except:
                pass

# 使用示例
if __name__ == "__main__":
    # 指定数据库路径
    database_path = r"C:\Path\To\Your\Database.mdb"
    
    # 指定要删除的表和查询名称列表
    tables_to_delete = ["OldTable1", "TempTable", "ObsoleteData"]
    queries_to_delete = ["OldQuery", "TempQuery", "UnusedReport"]
    
    # 执行删除操作（标准版本）
    delete_access_objects(database_path, tables_to_delete, queries_to_delete)
    
    # 执行删除操作（快速版本）
    # delete_access_objects_fast(database_path, tables_to_delete, queries_to_delete, verbose=True)

主要优化点

1. 使用集合操作：一次性获取所有对象名称，使用集合交集来快速确定哪些对象需要删除
2. 减少循环次数：从 O(n*m) 降低到 O(n+m)，特别是在处理大量对象时效果明显
3. 简化参数初始化：使用 or 操作符替代 if None 检查
4. 可选静默模式：快速版本不需要详细输出设置 verbose=False，减少 I/O 开销
5. 改进错误处理：在 finally 块中添加额外保护

性能对比：

• 原版本：每个要删除的对象都需要遍历所有现有对象
• 优化版本：只需要两次遍历（获取所有表名和查询名），后续操作都是高效的集合操作

import win32com.client as win32
import os
import logging

# Configure logging
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.StreamHandler(),  # Output to console
        logging.FileHandler('access_cleanup.log')  # Optional: Output to file
    ]
)

logger = logging.getLogger(__name__)

def delete_access_objects(mdb_path, table_names=None, query_names=None):
    """
    Delete specified tables and queries from an Access database.
    
    Args:
        mdb_path (str): Path to the Access database file (.mdb or .accdb)
        table_names (list, optional): List of table names to delete
        query_names (list, optional): List of query names to delete
    
    Returns:
        bool: True if operation completed successfully, False otherwise
    """
    # Initialize parameters as empty lists if not provided
    table_names = table_names or []
    query_names = query_names or []
    
    # Check if file exists
    if not os.path.exists(mdb_path):
        logger.error(f"Database file does not exist: {mdb_path}")
        return False
    
    access = None
    try:
        # Create Access application object
        access = win32.Dispatch("Access.Application")
        access.Visible = False  # Hide Access interface
        
        # Open database
        access.OpenCurrentDatabase(mdb_path)
        db = access.CurrentDb()
        
        # Pre-fetch all table and query names (single read operation to avoid repeated loops)
        all_tables = {table.Name for table in db.TableDefs}
        all_queries = {query.Name for query in db.QueryDefs}
        
        # Delete specified tables (using set intersection for efficiency)
        tables_to_delete = set(table_names) & all_tables
        tables_not_found = set(table_names) - all_tables
        
        # Log tables not found
        for table_name in tables_not_found:
            logger.info(f"Table not found, skipping: {table_name}")
            
        # Delete existing tables
        for table_name in tables_to_delete:
            try:
                db.TableDefs.Delete(table_name)
                logger.info(f"Successfully deleted table: {table_name}")
            except Exception as e:
                logger.error(f"Error deleting table {table_name}: {str(e)}")
        
        # Delete specified queries (using set intersection for efficiency)
        queries_to_delete = set(query_names) & all_queries
        queries_not_found = set(query_names) - all_queries
        
        # Log queries not found
        for query_name in queries_not_found:
            logger.info(f"Query not found, skipping: {query_name}")
            
        # Delete existing queries
        for query_name in queries_to_delete:
            try:
                db.QueryDefs.Delete(query_name)
                logger.info(f"Successfully deleted query: {query_name}")
            except Exception as e:
                logger.error(f"Error deleting query {query_name}: {str(e)}")
        
        logger.info("Operation completed successfully!")
        return True
        
    except Exception as e:
        logger.error(f"Error during operation: {str(e)}")
        return False
    finally:
        # Ensure Access application is properly closed
        if access:
            try:
                access.CloseCurrentDatabase()
                access.Quit()
            except Exception as e:
                logger.warning(f"Warning during Access cleanup: {str(e)}")
            finally:
                access = None

# Usage example
if __name__ == "__main__":
    # Specify database path
    database_path = r"C:\Path\To\Your\Database.mdb"
    
    # Specify lists of tables and queries to delete
    tables_to_delete = ["OldTable1", "TempTable", "ObsoleteData"]
    queries_to_delete = ["OldQuery", "TempQuery", "UnusedReport"]
    
    # Execute deletion operation
    delete_access_objects(database_path, tables_to_delete, queries_to_delete)