[PYTHON] Schreiben Sie ein Skript, um einen MySQL-Dump in TSV zu konvertieren

Versuche, das von MySQL ausgegebene SQL für andere Zwecke in tabulatorgetrennten Text (sogenannte TSV) zu konvertieren.

Überblick

MySQL-Tabellen können mit mysqldump als SQL ausgegeben werden. Die Hauptteile sind die Anweisungen CREATE und INSERT.

• Dump durch Angabe einer Tabelle | mysqldump-Lehrbuch

(Weggelassen) CREATE TABLE t_price ( (Weggelassen) INSERT INTO t_price VALUES (1,'feste Form',25,82),(Weggelassen)(10,'feste Form外',4000,1180); (Weggelassen)

Sie können direkt an TSV sichern, wenn es sich um einen von Ihnen verwalteten Speicherauszug von MySQL handelt.

• MySQL-Daten im CSV-, Tsv-Format - Qiita sichern

Wenn die einzigen an anderer Stelle veröffentlichten Daten ein SQL-Dump sind, können Sie sie entweder abrufen und sichern oder konvertieren. Dieses Mal werde ich ein Skript schreiben und die Konvertierung versuchen.

• Wie später beschrieben wird, wird davon ausgegangen, dass der Wiktionary-Dump konvertiert wird. Da die Daten sehr groß sind, möchte ich sie nicht einmal importieren.

Spezifikation

Extrahiert nur die VALUES der INSERT-Anweisung aus SQL und konvertiert sie in TSV. Insbesondere streben wir das folgende Verhalten an.

Vor der Konvertierung

INSERT INTO `table` VALUES (1,2,3),(4,5,6);
INSERT INTO `table` VALUES (1,'a,b','c\'d');

Nach der Konvertierung

1	2	3
4	5	6
1	a,b	c'd

Python

Schreiben Sie ein Konvertierungsskript in Python.

read_string

Analysiert die vom einfachen Anführungszeichen eingeschlossene Zeichenfolge. Dies ist das geschäftigste.

Überprüft, ob das erste Zeichen "" ist. Die Escape-Sequenzverarbeitung wird vereinfacht, indem "" für "", "", "\" entfernt wird, aber "" für andere Steuerzeichen übrig bleibt.

test ist ein Testflag.

sql2tsv.py

test = True

def read_string(src, pos):
    length = len(src)
    if pos >= length or src[pos] != "'": return None
    pos += 1
    ret = ""
    while pos < length and src[pos] != "'":
        if src[pos] == "\\":
            pos += 1
            end = pos >= length
            if end or not src[pos] in "\"'\\":
                ret += "\\"
            if end: break
        ret += src[pos]
        pos += 1
    if pos < length and src[pos] == "'": pos += 1
    return (ret, pos)

if test:
    src = r"'a,b','c\'d'"
    pos = 0
    while pos < len(src):
        s, p = read_string(src, pos)
        print("read_string", (src, pos), "->", (s, p))
        pos = p + 1

Ausführungsergebnis

read_string ("'a,b','c\\'d'", 0) -> ('a,b', 5)
read_string ("'a,b','c\\'d'", 6) -> ("c'd", 12)

read_value

Lesen Sie ein Datenelement. Wenn es sich um eine Zeichenfolge handelt, lesen Sie "read_string", andernfalls "," oder "(".

sql2tsv.py (Fortsetzung)

def read_value(src, pos):
    length = len(src)
    if pos >= length: return None
    sp = read_string(src, pos)
    if sp: return sp
    p = pos
    while p < length and src[p] != "," and src[p] != ")":
        p += 1
    return (src[pos:p], p)

if test:
    for src in ["1,2,3", r"1,'a,b','c\'d'"]:
        pos = 0
        while (value := read_value(src, pos)):
            s, p = value
            print("read_value", (src, pos), "->", (s, p))
            pos = p + 1

Ausführungsergebnis

read_value ('1,2,3', 0) -> ('1', 1)
read_value ('1,2,3', 2) -> ('2', 3)
read_value ('1,2,3', 4) -> ('3', 5)
read_value ("1,'a,b','c\\'d'", 0) -> ('1', 1)
read_value ("1,'a,b','c\\'d'", 2) -> ('a,b', 7)
read_value ("1,'a,b','c\\'d'", 8) -> ("c'd", 14)

read_values

Liest alle durch Kommas getrennten Daten in Klammern.

sql2tsv.py (Fortsetzung)

def read_values(src, pos):
    length = len(src)
    if pos >= length or src[pos] != "(": return None
    pos += 1
    ret = []
    if pos < length and src[pos] != ")":
        while (value := read_value(src, pos)):
            s, pos = value
            ret.append(s)
            if pos >= length or src[pos] != ",": break
            pos += 1
    if pos < length and src[pos] == ")": pos += 1
    return (ret, pos)

if test:
    for src in [r"(1,2,3)", r"(1,'a,b','c\'d')"]:
        print("read_values", (src, 0), "->", read_values(src, 0))

Ausführungsergebnis

read_values ('(1,2,3)', 0) -> (['1', '2', '3'], 7)
read_values ("(1,'a,b','c\\'d')", 0) -> (['1', 'a,b', "c'd"], 16)

read_all_values

Liest alle in Klammern eingeschlossenen Daten. Machen Sie es zu einem Generator, der die Handhabung in einer Schleife übernimmt.

sql2tsv.py (Fortsetzung)

def read_all_values(src, pos):
    length = len(src)
    while (sp := read_values(src, pos)):
        s, pos = sp
        yield s
        if pos >= length or src[pos] != ",": break
        pos += 1

if test:
    src = r"(1,2,3),(1,'a,b','c\'d')"
    print("read_all_values", (src, 0), "->", list(read_all_values(src, 0)))

Ausführungsergebnis

read_all_values ("(1,2,3),(1,'a,b','c\\'d')", 0) -> [['1', '2', '3'], ['1', 'a,b', "c'd"]]

read_sql

Suchen Sie die Zeile, die mit "INSERT INTO" beginnt, aus jeder Zeile in SQL und verarbeiten Sie sie mit "read_all_values".

sql2tsv.py (Fortsetzung)

def read_sql(stream):
    while (line := stream.readline()):
        if line.startswith("INSERT INTO "):
            p = line.find("VALUES (")
            if p >= 0: yield from read_all_values(line, p + 7)

if test:
    import io
    src = r"""
INSERT INTO `table` VALUES (1,2,3),(4,5,6);
INSERT INTO `table` VALUES (1,'a,b','c\'d');
""".strip()
    print("read_sql", (src,))
    print("->", list(read_sql(io.StringIO(src))))

Ausführungsergebnis

read_sql ("INSERT INTO `table` VALUES (1,2,3),(4,5,6);\nINSERT INTO `table` VALUES (1,'a,b','c\\'d');",)
-> [['1', '2', '3'], ['4', '5', '6'], ['1', 'a,b', "c'd"]]

Befehl

Ich habe alle notwendigen Funktionen implementiert. Schalten Sie den Test aus.

sql2tsv.py (ändern)

test = False

Liest die angegebene Datei und schreibt sie in die angegebene Datei.

Geben Sie für "Öffnen" "error =" replace "an, vorausgesetzt, die UTF-8-Byte-Zeichenfolge wird in der Mitte eines Zeichens abgeschnitten. Dies ersetzt den anomalen Charakter durch. Wenn Sie keine "Fehler" angeben, tritt ein Fehler auf.

[Referenz] (Windows) Ursachen und Problemumgehungen für UnicodeEncodeError in Python 3 --Qiita

sql2tsv.py (Fortsetzung)

if __name__ == "__main__":
    import sys
    try:
        sql, tsv = sys.argv[-2], sys.argv[-1]
        if not (sql.endswith(".sql") and tsv.endswith(".tsv")):
            raise Exception
    except:
        print("usage: %s sql tsv" % sys.argv[0])
        exit(1)
    with open(sql, "r", encoding="utf-8", errors="replace") as fr:
        with open(tsv, "w", encoding="utf-8") as fw:
            for values in read_sql(fr):
                fw.write("\t".join(values))
                fw.write("\n")

Verwenden Sie es wie folgt.

python sql2tsv.py input.sql output.tsv

Ich habe das ganze Skript unten gestellt.

• [py] mysqldump (SQL) to TSV converter

Messung

Es misst, wie schnell es mit einer riesigen Datei verarbeitet werden kann.

Wiktionary japanische Version

Wie bei Wikipedia sind Dump-Daten für die Öffentlichkeit zugänglich.

• https://dumps.wikimedia.org/jawiktionary/

Die folgenden Dateien werden ab der Ausgabe vom 1. Mai 2020 verwendet, die zum Zeitpunkt des Schreibens des Artikels verfügbar ist.

• jawiktionary-20200501-categorylinks.sql.gz 10.7 MB

Das Extrahieren der Datei beträgt ca. 90 MB.

gunzip -k jawiktionary-20200501-categorylinks.sql.gz

• -K ist eine Option, um die Datei vor dem Extrahieren zu behalten.

Beim Konvertieren des erweiterten SQL mit diesem Skript wird eine TSV von ca. 79 MB ausgegeben.

$ time python sql2tsv.py jawiktionary-20200501-categorylinks.sql jawiktionary-20200501-categorylinks.tsv

real    0m19.921s
user    0m19.375s
sys     0m0.516s

Die Anzahl der Zeilen beträgt ca. 950.000.

$ wc -l jawiktionary-20200501-categorylinks.tsv
951077 jawiktionary-20200501-categorylinks.tsv

Wiktionary englische Version

Dann probieren Sie die englische Version.

• https://dumps.wikimedia.org/enwiktionary/

Verwenden Sie die folgenden Dateien. Im aufgeklappten Zustand schwillt es auf ca. 3 GB an.

• enwiktionary-20200501-categorylinks.sql.gz 344.6 MB

Konvertieren Sie dies. Die Ausgabedatei ist ungefähr 2,6 GB groß.

$ time python sql2tsv.py enwiktionary-20200501-categorylinks.sql enwiktionary-20200501-categorylinks.tsv

real    15m58.965s
user    15m39.063s
sys     0m12.578s

Die Anzahl der Leitungen beträgt 28 Millionen.

$ wc -l enwiktionary-20200501-categorylinks.tsv
28021874 enwiktionary-20200501-categorylinks.tsv

F#

Ich habe es als Test auf F # portiert.

• [F#] mysqldump (SQL) to TSV converter

Die Verarbeitung derselben Datei war viel schneller.

Komprimierte Datei lesen

Das .NET Framework verfügt über einen GZipStream, mit dem Sie eine komprimierte gz-Datei direkt lesen können.

sql2tsv.fsx (ändern)

open System.IO.Compression
let args = Environment.GetCommandLineArgs()
let sqlgz, tsv =
    if args.Length < 2 then ("", "") else
    let len = args.Length
    (args.[len - 2], args.[len - 1])
if not (sqlgz.EndsWith ".sql.gz") || Path.GetExtension tsv <> ".tsv" then
    printfn "usage: sql2tsv sql.gz tsv"
    exit 1
do
    use fs = new FileStream(sqlgz, FileMode.Open)
    use gs = new GZipStream(fs, CompressionMode.Decompress)
    use sr = new StreamReader(gs)
    use sw = new StreamWriter(tsv)
    sw.NewLine <- "\n"
    for values in readSql sr do
        sw.WriteLine(String.concat "\t" values)

Die Messergebnisse sind nachstehend zusammengefasst.

Zusammenfassung

Ordnen Sie die Messergebnisse.

Die direkte Verarbeitung komprimierter Dateien ist etwas langsamer, spart jedoch Speicherplatz, was von Vorteil ist.