Issue "removing" a block of lines from a .sql file
Question: I'm working on a script that will remove a block of lines from a SQL dump
(basically, cleanse any table we do not want to restore). I thought I had it
working, but when trying to restore the resulting file to my database I
realized that when the script rewrites to the file, it's missing more lines
than it should, and the restore fails. Here's my paltry attempt at this:

    
    
    #!/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()
    

And here's what one of the blocks of the sql file looks like, for reference:

    
    
    -- 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;
    

I start the script by matching against the first line in such a block
(includes the word "structure" and the table name), then I'm attempting to
iterate from that line through "UNLOCK TABLES;" What's happening instead is
it's removing ALL instances of "UNLOCK TABLES;" in the script, even from
blocks I do not want removed, and it's also removing the following line from
each block as well " ) ENGINE=MyISAM DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci; " (The closing parentheses through the rest of that
line).

Thanks for any help. I'll keep plugging away at what I've missed.


Answer: # Try `sqlparse`

    
    
    $ pip install sqlparse
    

Quick test from tutorial:

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

Applying to your SQL script:

    
    
    >>> 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;']
    

This provides properly parsed script and last thing to do, is to filter it one
by one, picking only those, which seem to be needed. This I leave to you.

