python - 问题 "removing".sql 文件中的行 block

Question

我正在编写一个脚本，该脚本将从 SQL 转储中删除一行行(基本上，清理我们不想恢复的任何表)。我以为我已经可以正常工作了，但是当尝试将生成的文件恢复到我的数据库时，我意识到当脚本重写该文件时，它丢失的行数超出了应有的行数，并且恢复失败。这是我对此的微不足道的尝试:

#!/usr/bin/python

to_keep = []
to_remove = []

f = open("backuptest.sql","r")
lines = f.readlines()

f.close()

### Function to remove lines associated with a table block
def remove_lines(table_name):
    for i in range(0, len(lines)):
        line = lines[i]
        if "structure" in line and table_name in line:
            for j in range(i, len(lines)):
                to_remove.append(lines[j])
                if "UNLOCK TABLES;" in lines[j]:
                    break
        if line not in to_remove:
            to_keep.append(line)
            f.write(line)

print "Finding lines"

f = open("backuptest.sql", "w")
remove_lines("advanced_searches")
f.close()

f = open("backuptest.sql", "w")
remove_lines("test_table2")
f.close()

f = open("backuptest.sql", "w")
remove_lines("test_table3")
f.close()

这是 sql 文件的其中一个 block 的样子，供引用:

-- Table structure for table `advanced_searches`
--

DROP TABLE IF EXISTS `advanced_searches`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `advanced_searches` (
  `batch_size` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `advanced_searches`
--

LOCK TABLES `advanced_searches` WRITE;
/*!40000 ALTER TABLE `advanced_searches` DISABLE KEYS */;
/*!40000 ALTER TABLE `advanced_searches` ENABLE KEYS */;
UNLOCK TABLES;

我通过匹配此类 block 中的第一行(包括单词“结构”和表名称)来启动脚本，然后尝试从该行迭代“UNLOCK TABLES;”相反，它正在删除“UNLOCK TABLES;”的所有实例。在脚本中，即使是我不想删除的 block ，它也会从每个 block 中删除以下行 ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; "(通过该行其余部分的右括号) .

感谢您的帮助。我会继续努力弥补我错过的事情。

Answer 1

尝试sqlparse

$ pip install sqlparse

教程中的快速测试:

>>> import sqlparse
>>> sql = "select * from foo; select * from bar;"
>>> res = sqlparse.split(sql)
>>> res
[u'select * from foo;', u'select * from bar;']

应用于您的 SQL 脚本:

>>> sql = """-- Table structure for table `advanced_searches`
... --
... 
... DROP TABLE IF EXISTS `advanced_searches`;
... /*!40101 SET @saved_cs_client     = @@character_set_client */;
... /*!40101 SET character_set_client = utf8 */;
... CREATE TABLE `advanced_searches` (
...   `batch_size` int(11) NOT NULL DEFAULT '0'
... ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
... /*!40101 SET character_set_client = @saved_cs_client */;
... 
... --
... -- Dumping data for table `advanced_searches`
... --
... 
... LOCK TABLES `advanced_searches` WRITE;
... /*!40000 ALTER TABLE `advanced_searches` DISABLE KEYS */;
... /*!40000 ALTER TABLE `advanced_searches` ENABLE KEYS */;
... UNLOCK TABLES;"""
...
>>> res = sqlparse.split(sql)
>>> res
[u'-- Table structure for table `advanced_searches`\n--\n\nDROP TABLE IF EXISTS `advanced_searches`;',
 u'/*!40101 SET @saved_cs_client     = @@character_set_client */;',
 u'/*!40101 SET character_set_client = utf8 */;',
 u"CREATE TABLE `advanced_searches` (\n  `batch_size` int(11) NOT NULL DEFAULT '0'\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;",
 u'/*!40101 SET character_set_client = @saved_cs_client */;',
 u'--\n-- Dumping data for table `advanced_searches`\n--\n\nLOCK TABLES `advanced_searches` WRITE;',
 u'/*!40000 ALTER TABLE `advanced_searches` DISABLE KEYS */;',
 u'/*!40000 ALTER TABLE `advanced_searches` ENABLE KEYS */;',
 u'UNLOCK TABLES;']

这提供了正确解析的脚本，最后要做的事情是逐个过滤它，仅选择那些似乎需要的脚本。这个我留给你了。