mysqldump_to_csv.py

#!/usr/bin/env python
import gzip
import csv
import sys

# This prevents prematurely closed pipes from raising
# an exception in Python
from signal import signal, SIGPIPE, SIG_DFL
signal(SIGPIPE, SIG_DFL)

def is_insert(line):
    """
    Returns true if the line begins a SQL insert statement.
    """
    return line.startswith(b'INSERT INTO') or False

def is_create(line):
    """
    Returns true if the line begins a SQL create table statement.
    """
    return line.startswith(b'CREATE TABLE') or False

def get_values(line):
    """
    Returns the portion of an INSERT statement containing values
    """
    return line.partition(b'` VALUES ')[2]


def values_sanity_check(values):
    """
    Ensures that values from the INSERT statement meet basic checks.
    """
    #assert values
    #assert values[0] == b'('
    # Assertions have not been raised
    return True

def write_header(values, outfile):
    """
    Given a file handle and a list of column names,
    write the equivalent CSV to the file
    """
    writer = csv.writer(outfile, quoting=csv.QUOTE_MINIMAL)
    writer.writerow(values)

def parse_values(values, outfile):
    """
    Given a file handle and the raw values from a MySQL INSERT
    statement, write the equivalent CSV to the file
    """
    latest_row = []

    reader = csv.reader([values.decode()], delimiter=',',
                        doublequote=False,
                        escapechar='\\',
                        quotechar="'",
                        strict=True
    )

    writer = csv.writer(outfile, quoting=csv.QUOTE_MINIMAL)

    for reader_row in reader:
        for column in reader_row:
            # If our current string is empty...
            if len(column) == 0 or column == 'NULL':
                latest_row.append(chr(0))
                continue
            # If our string starts with an open paren
            if column[0] == "(":
                # Assume that this column does not begin
                # a new row.
                new_row = False
                # If we've been filling out a row
                if len(latest_row) > 0:
                    # Check if the previous entry ended in
                    # a close paren. If so, the row we've
                    # been filling out has been COMPLETED
                    # as:
                    #    1) the previous entry ended in a )
                    #    2) the current entry starts with a (
                    if latest_row[-1][-1] == ")":
                        # Remove the close paren.
                        latest_row[-1] = latest_row[-1][:-1]
                        new_row = True
                # If we've found a new row, write it out
                # and begin our new one
                if new_row:
                    writer.writerow(latest_row)
                    latest_row = []
                # If we're beginning a new row, eliminate the
                # opening parentheses.
                if len(latest_row) == 0:
                    column = column[1:]
            # Add our column to the row we're working on.
            latest_row.append(column)
        # At the end of an INSERT statement, we'll
        # have the semicolon.
        # Make sure to remove the semicolon and
        # the close paren.
        if latest_row[-1][-2:] == ");":
            latest_row[-1] = latest_row[-1][:-2]
            writer.writerow(latest_row)


def main():
    """
    Parse arguments and start the program
    """
    # Iterate over all lines in all files
    # listed in sys.argv[1:]
    # or stdin if no args given.

    create = False
    header = []

    filename = sys.argv[1]
    if filename.endswith(".gz"):
        my_open = gzip.open
    else:
        my_open = open

    try:
        for line in my_open(sys.argv[1]):

            if is_create(line):
                create = True

            if create:
                if len(line.strip()) == 0:
                    continue

                if b";" in line:
                    create = False
                    write_header(header, sys.stdout)
                    continue

                fields = line.strip().split()

                if b'`' in fields[0]:
                    header.append(fields[0][1:-1].decode())

            # Look for an INSERT statement and parse it.
            if is_insert(line):
                values = get_values(line)
                if values_sanity_check(values):
                    parse_values(values, sys.stdout)
    except KeyboardInterrupt:
        sys.exit(0)

if __name__ == "__main__":
    main()