在不先删除所有条目的情况下从外部数据源更新SQL表(但删除条目不再存在)

Question

我有一堆MySQL表，其中的最终数据源来自一个非常慢的SQL server，由其他人管理。我的前辈们处理这个问题的解决方案是进行或多或少如下的查询：

results = python_wrapper('SELECT primary_key, col2, col3 FROM foreign_table;')
other_python_wrapper('DELETE FROM local_table;')
other_python_wrapper('INSERT INTO local_table VALUES() %s;' % results)

问题是，这意味着您永远不能将local_table中的值用作其他表的外键约束，因为每当您从外部源对其进行更新时，这些值都会不断地被删除并重新添加到表中。但是，如果一条记录确实将结果中的内容分散到了外部服务器上的查询中，那么这通常意味着您希望触发级联效应，从而删除其他本地表中的记录，这些记录是通过foreign key约束链接到从外部表传播的数据的。

我想出的唯一半合理的解决方案是这样做：

results = python_wrapper('SELECT primary_key, col2, col3 FROM foreign_table;')
other_python_wrapper('DELETE FROM local_table_temp;')
other_python_wrapper('INSERT INTO local_table_temp VALUES() %s;' % results)
other_python_wrapper('DELETE FROM local_table WHERE primary_key NOT IN local_table_temp;')
other_python_wrapper('INSERT INTO local_table SELECT * FROM local_table_temp ON DUPLICATE KEY UPDATE local_table.col2 = local_table_temp.col2, local_table.col3 = local_table_temp.col3

问题是这样的表有相当多，而且许多表都有大量的列需要更新，所以重复编写相同的模板是很乏味的。如果表模式发生更改，则需要在多个位置更新所有列的列表。

有没有更简洁的方法来处理SQL代码呢？

谢谢!

Answer 1

对于我自己的问题，我有一个不太令人满意的答案。由于我使用python查询外部Oracle数据库并将其放入SQL中，而且我相信表名和列名的格式会表现得很好，所以我可以将整个过程包装在python代码中，并让python基于检查表生成更新SQL更新查询。

出于许多原因，我仍然希望看到一种更好的方法来做这件事，但它对我来说是有效的，因为：

1. 我使用的是一种外部脚本语言，它无论如何都可以检查数据库架构。
2. 我相信我正在使用的数据库、列和表名行为良好，因为这些都是我可以直接控制的东西。
3. 我的解决方案依赖于本地SQL表结构；具体地说，哪些键是主键。如果没有结构合理的表，代码将无法工作。但这没问题，因为我可以重新构造MySQL表，以使我的python代码正常工作。

虽然我确实希望其他人能想出一个更优雅和/或通用的解决方案，但我会将我自己的python代码提供给任何正在处理类似问题的人，他们可以安全地做出与我上面所做的相同的假设。

下面是我用python进行简单SQL查询的python包装器：

import config, MySQLdb
class SimpleSQLConn(SimpleConn):                                                                         
    '''simplified wrapper around a MySQLdb.connection'''                                                 

    def __init__(self, **kwargs):                                                                        
        self._connection = MySQLdb.connect(host=config.mysql_host,                                       
                                           user=config.mysql_user,                                       
                                           passwd=config.mysql_pass,                                     
                                           **kwargs)                                                     
        self._cursor = self._connection.cursor()                                                         

    def query(self, query_str):                                                                          
        self._cursor.execute(query_str)                                                                  
        self._connection.commit()                                                                        
        return self._cursor.fetchall()   

    def columns(self, database, table):                                                                  
        return [x[0] for x in self.query('DESCRIBE `%s`.`%s`' % (database, table))g]                      

    def primary_keys(self, database, table):                                                             
        return [x[0] for x in self.query('DESCRIBE `%s`.`%s`' % (database, table)) if 'PRI' in x]

下面是实际的更新函数，使用上面的SQL包装器类：

def update_table(database, 
                 table,
                 mysql_insert_with_dbtable_placeholder):                                                                            
    '''update a mysql table without first deleting all the old records                                                                 

    mysql_insert_with_dbtable_placeholder should be set to a string with                                                               
    placeholders for database and table, something like:                                                                               

    mysql_insert_with_dbtable_placeholder = "                                                                                          
    INSERT INTO `%(database)s`.`%(table)s` VALUES (a, b, c);                                                           

    note: code as is will update all the non-primary keys, structure                                                                   
    your tables accordingly                                                                                                            
    '''                                                                                                                                
    sql = SimpleSQLConn()                                                                                                              
    query ='DROP TABLE IF EXISTS `%(database)s`.`%(table)s_temp_for_update`' %\                                                        
        {'database': database, 'table': table}                                                                                         
    sql.query(query)                                                                                                                   
    query ='CREATE TABLE `%(database)s`.`%(table)s_temp_for_update` LIKE `%(database)s`.`%(table)s`'%\                                 
        {'database': database, 'table': table}                                                                                         
    sql.query(query)                                                                                                                   
    query = mysql_insert_with_dbtable_placeholder %\                                                                                   
        {'database': database, 'table': '%s_temp_for_update' % table}                                                                  
    sql.query(query)                                                                                                                   
    query = '''DELETE FROM `%(database)s`.`%(table)s` WHERE                                                                            
       (%(primary_keys)s) NOT IN                                                                                                       
       (SELECT %(primary_keys)s FROM `%(database)s`.`%(table)s_temp_for_update`);                                                      
       ''' % {'database': database,                                                                                                    
              'table': table,                                                                                                          
              'primary_keys': ', '.join(['`%s`' % key for key in sql.primary_keys(database, table)])}                                  

    sql.query(query)                                                                                                                   
    update_columns = [col for col in sql.columns(database, table)                                                                      
                      if col not in sql.primary_keys(database, table)]                                                                 
    query = '''INSERT into `%(database)s`.`%(table)s`                                                                                  
    SELECT * FROM `%(database)s`.`%(table)s_temp_for_update`                                                                           
    ON DUPLICATE KEY UPDATE                                                                                                            
    %(update_cols)s                                                                                                                    
    ''' %  {'database': database,                                                                                                      
            'table': table,                                                                                                            
            'update_cols' : ',\n'.join(['`%(table)s`.`%(col)s` = `%(table)s_temp_for_update`.`%(col)s`' \                              
                                      % {'table': table, 'col': col} for col in update_columns])}                                      
    sql.query(query)