Ecrire un script pour convertir un vidage MySQL en TSV

Tente de convertir le SQL sauvegardé de MySQL en texte délimité par des tabulations (appelé TSV) à d'autres fins.

Aperçu

Les tables MySQL peuvent être vidées en SQL avec mysqldump. Les parties principales sont les instructions CREATE et INSERT.

(Omis) CREATE TABLE t_price ( (Omis) INSERT INTO t_price VALUES (1,'forme fixe',25,82),(Omis)(10,'forme fixe外',4000,1180); (Omis)

Vous pouvez effectuer un vidage directement sur TSV s'il s'agit d'un vidage de MySQL que vous gérez.

Si les seules données publiées ailleurs sont un vidage SQL, vous pouvez soit le récupérer et le vider, soit le convertir. Cette fois, je vais écrire un script et essayer la conversion.

spécification

Extrait uniquement les valeurs de l'instruction INSERT de SQL et les convertit en TSV. Plus précisément, nous visons le comportement suivant.

Avant la conversion


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

Après la conversion


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

Python

Écrivez un script de conversion en Python.

read_string

Analyse la chaîne entourée du guillemet simple `` ''. C'est le plus fréquenté.

Vérifie si le premier caractère est `` ''. Le traitement des séquences d'échappement est simplifié en supprimant \ pour \" , \ ',\\, mais en laissant\pour les autres caractères de contrôle.

test est un indicateur de test.

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

Résultat d'exécution


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

read_value

Lisez une donnée. S'il s'agit d'une chaîne, lisez read_string, sinon lisez vers, ʻou (`.

sql2tsv.py (suite)


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

Résultat d'exécution


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

Lit toutes les données séparées par des virgules entre parenthèses.

sql2tsv.py (suite)


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

Résultat d'exécution


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

Lit toutes les données entre parenthèses. Je vais en faire un générateur en supposant une manipulation en boucle.

sql2tsv.py (suite)


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

Résultat d'exécution


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

read_sql

Trouvez la ligne commençant par ʻINSERT INTO de chaque ligne dans SQL et traitez-la avec read_all_values`.

sql2tsv.py (suite)


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

Résultat d'exécution


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"]]

Commander

J'ai implémenté toutes les fonctions nécessaires. Désactivez le test.

sql2tsv.py (changer)


test = False

Lit le fichier spécifié et l'écrit dans le fichier spécifié.

ʻOpenspécifie ʻerrors = "replace", en supposant que la chaîne d'octets UTF-8 est coupée au milieu d'un caractère. Cela remplacera le caractère anormal par. Si ʻerrors` n'est pas spécifié, une erreur se produira.

[Référence] (Windows) Causes et solutions de contournement pour UnicodeEncodeError dans Python 3 --Qiita

sql2tsv.py (suite)


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")

Utilisez-le comme suit.

python sql2tsv.py input.sql output.tsv

J'ai mis tout le script ci-dessous.

la mesure

Il mesure la vitesse à laquelle il peut être traité avec un gros fichier.

Version japonaise du Wiktionnaire

Comme avec Wikipédia, les données de vidage sont ouvertes au public.

Les fichiers suivants seront utilisés à partir de la version du 1er mai 2020 disponible au moment de la rédaction de l'article.

L'extraction du fichier sera d'environ 90 Mo.

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

La conversion du SQL étendu avec ce script produira un TSV d'environ 79 Mo.

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

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

Le nombre de lignes est d'environ 950 000.

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

Version anglaise du Wiktionnaire

Essayez ensuite la version anglaise.

Utilisez les fichiers suivants. Une fois déplié, il gonfle à environ 3 Go.

Convertissez ceci. Le fichier de sortie fait environ 2,6 Go.

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

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

Le nombre de lignes peut atteindre 28 millions.

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

F#

Je l'ai porté sur F # à titre d'essai.

Le traitement du même fichier était beaucoup plus rapide.

Fichier Heure
jawiktionary-20200501-categorylinks.sql 0m03.168s
enwiktionary-20200501-categorylinks.sql 1m52.396s

Lire le fichier compressé

Le .NET Framework dispose d'un GZipStream qui vous permet de lire directement un fichier gz compressé.

sql2tsv.fsx (modifier)


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)

Les résultats des mesures sont résumés ci-dessous.

Résumé

Organisez les résultats de mesure.

Langue Fichier temps
Python jawiktionary-20200501-categorylinks.sql 0m19.921s
F# jawiktionary-20200501-categorylinks.sql 0m03.168s
F# jawiktionary-20200501-categorylinks.sql.gz 0m03.509s
Python enwiktionary-20200501-categorylinks.sql 15m58.965s
F# enwiktionary-20200501-categorylinks.sql 1m52.396s
F# enwiktionary-20200501-categorylinks.sql.gz 2m15.380s

Le traitement direct des fichiers compressés est un peu plus lent, mais cela économise de l'espace disque, ce qui est un avantage.

Recommended Posts

Ecrire un script pour convertir un vidage MySQL en TSV
Créer un script shell pour écrire un journal
Écrivons un script de confirmation ping de manière appropriée
Je n'oublierai jamais comment écrire un script shell, n'oubliez pas! !!
Script pour mysqldump vers toutes les bases de données MySQL
Écrire la sortie standard dans un fichier
Convertir une chaîne en image
Outil pour convertir la configuration Juniper
Ecrire un script batch avec Python3.5 ~
Écrivez un script pour calculer la distance avec le système Elasticsearch 5 sans douleur
Qiita (1) Comment écrire un nom de code
Exécuter un script depuis Jupyter pour traiter
[Python] Comment convertir une liste bidimensionnelle en liste unidimensionnelle
Comment convertir csv en tsv dans la CLI
[Ubuntu] Comment exécuter un script shell
Un simple script IDAPython pour nommer une fonction
[Aux messieurs sur Twitter] J'ai écrit un script pour convertir immédiatement .jpg-large en .jpg.
Vider les données SQLite3 et migrer vers MySQL
J'ai créé un script en python pour convertir des fichiers .md au format Scrapbox
J'ai fait un script pour afficher des pictogrammes
Script pour créer un fichier de dictionnaire Mac
Comment exécuter des scripts Maya Python
Convertir l'API cURL en script Python (à l'aide du stockage d'objets IBM Cloud)
Script Python pour convertir la latitude et la longitude en code de maillage
Convertir une liste multidimensionnelle (tableau) en une dimension
Pourquoi Python doit-il écrire un deux-points?
Comment écrire une instruction ShellScript Bash for
J'ai écrit un script pour télécharger un plug-in WordPress
Comment écrire un document tuple nommé en 2020
[Go] Comment écrire ou appeler une fonction
Je veux écrire dans un fichier avec Python
Comment écrire une instruction de cas bash ShellScript
Connectez-vous à mysql
Comment convertir / restaurer une chaîne avec [] en python
Comment écrire une interface graphique à l'aide de la commande maya
Convertir un objet de tranche en liste de numéros d'index
Convertir un fichier texte avec des valeurs hexadécimales en fichier binaire
Je veux écrire en Python! (2) Écrivons un test
Comment convertir un objet de classe en dictionnaire avec SQLAlchemy
J'ai fait un script pour mettre un extrait dans README.md
Comment écrire un type liste / dictionnaire de Python3
Un mémorandum pour exécuter un script python dans un fichier bat
J'ai créé un code pour convertir illustration2vec en modèle Keras
Comment convertir un spectrogramme Mel en un fichier wav
[Python] Un mémo pour écrire du CSV verticalement avec Pandas
[Python] Création d'une méthode pour convertir la base en 1 seconde
Comment créer un simple script serveur / client TCP
Écrire du code dans UnitTest une application Web Python
Script Python qui crée un fichier JSON à partir d'un fichier CSV
Sortie d'un vidage binaire en binaire et retour dans un fichier binaire
Générer un script bash pour ajouter les paramètres du moniteur Datadog
J'ai créé un script en Python pour convertir un fichier texte pour JSON (pour l'extrait d'utilisateur vscode)