bash d'une - Comment afficher les résultats de la requête MySQL au format CSV?




exporter vers (21)

Existe-t-il un moyen facile d'exécuter une requête MySQL à partir de la ligne de commande Linux et de sortir les résultats au format CSV ?

Voici ce que je fais maintenant:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/        /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ

Cela devient compliqué quand il y a beaucoup de colonnes qui doivent être entourées de guillemets, ou s'il y a des guillemets dans les résultats qui doivent être échappés.


Answers

Voici une façon assez grossière de le faire. Trouvé quelque part, ne peut prendre aucun crédit

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

Fonctionne plutôt bien. Encore une fois si un regex prouve écrire seulement.

Explication Regex:

  • s /// signifie substituer ce qui est entre le premier // et ce qui est entre le second //
  • le "g" à la fin est un modificateur qui signifie "toutes les instances, pas seulement les premières"
  • ^ (dans ce contexte) signifie début de ligne
  • $ (dans ce contexte) signifie fin de ligne

Donc, tout mettre ensemble:

s/'/\'/          replace ' with \'
s/\t/\",\"/g     replace all \t (tab) with ","
s/^/\"/          at the beginning of the line place a "
s/$/\"/          at the end of the line place a "
s/\n//g          replace all \n (newline) with nothing

MySQL Workbench peut exporter des jeux d'enregistrements au format CSV et il semble très bien gérer les virgules dans les champs. Le CSV s'ouvre très bien dans OpenOffice.


Petit script bash pour faire une requête simple vers les dumps CSV, inspiré par https://.com/a/5395421/2841607 .

#!/bin/bash

# $1 = query to execute
# $2 = outfile
# $3 = mysql database name
# $4 = mysql username

if [ -z "$1" ]; then
    echo "Query not given"
    exit 1
fi

if [ -z "$2" ]; then
    echo "Outfile not given"
    exit 1
fi

MYSQL_DB=""
MYSQL_USER="root"

if [ ! -z "$3" ]; then
    MYSQL_DB=$3
fi

if [ ! -z "$4" ]; then
    MYSQL_USER=$4
fi

if [ -z "$MYSQL_DB" ]; then
    echo "Database name not given"
    exit 1
fi

if [ -z "$MYSQL_USER" ]; then
    echo "Database user not given"
    exit 1
fi

mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "$1" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $2
echo "Written to $2"

Construire sur user7610, voici la meilleure façon de le faire. Avec mysql outfile il y avait 60 minutes de problèmes de propriété et d'écrasement des fichiers.

Ce n'est pas cool, mais ça a marché en 5 minutes.

php csvdump.php localhost root password database tablename > whatever-you-like.csv

<?php

$server = $argv[1];
$user = $argv[2];
$password = $argv[3];
$db = $argv[4];
$table = $argv[5];

mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

// fetch the data
$rows = mysql_query('SELECT * FROM ' . $table);
$rows || die(mysql_error());


// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// output the column headings

$fields = [];
for($i = 0; $i < mysql_num_fields($rows); $i++) {
    $field_info = mysql_fetch_field($rows, $i);
    $fields[] = $field_info->name;
}
fputcsv($output, $fields);

// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);

?>

Si PHP est installé sur la machine que vous utilisez, vous pouvez écrire un script PHP pour le faire. Il nécessite l'installation de PHP a l'extension MySQL installée.

Vous pouvez appeler l'interpréteur PHP depuis la ligne de commande comme ceci:

php --php-ini path/to/php.ini your-script.php

--php-ini commutateur --php-ini , car vous devrez peut-être utiliser votre propre configuration PHP qui active l'extension MySQL. Sur PHP 5.3.0+, cette extension est activée par défaut, il n'est donc plus nécessaire d'utiliser la configuration pour l'activer.

Ensuite, vous pouvez écrire votre script d'exportation comme n'importe quel script PHP normal:

<?php
    #mysql_connect("localhost", "username", "password") or die(mysql_error());
    mysql_select_db("mydb") or die(mysql_error());

    $result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");

    $result || die(mysql_error());

    while($row = mysql_fetch_row($result)) {
      $comma = false;
      foreach ($row as $item) {

        # Make it comma separated
        if ($comma) {
          echo ',';
        } else {
          $comma = true;
        }

        # Quote the quotes
        $quoted = str_replace("\"", "\"\"", $item);

        # Quote the string
        echo "\"$quoted\"";
      }
        echo "\n";
    }
?>

L'avantage de cette méthode est qu'elle n'a aucun problème avec les champs varchar et text, qui contiennent du texte contenant des retours à la ligne. Ces champs sont correctement cités et ces nouvelles lignes seront interprétées par le lecteur CSV comme une partie du texte, pas comme des séparateurs d'enregistrements. C'est quelque chose qui est difficile à corriger par la suite avec sed ou alors.


La plupart des réponses sur cette page sont faibles car elles ne gèrent pas le cas général de ce qui peut se produire au format CSV. par exemple des virgules et des guillemets intégrés dans des champs et d'autres conditions qui finissent toujours par arriver. Nous avons besoin d'une solution générale qui fonctionne pour toutes les données d'entrée CSV valides.

Voici une solution simple et solide en Python:

#!/usr/bin/env python

import csv
import sys

tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)

for row in tab_in:
    comma_out.writerow(row)

Nommez ce fichier tab2csv , placez-le sur votre chemin, donnez-lui les droits d'exécution, puis utilisez-le pour lister ceci:

mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv >outfile.csv

Les fonctions de gestion CSV de Python couvrent les cas d'angle pour le (s) format (s) d'entrée CSV.

Cela pourrait être amélioré pour gérer de très gros fichiers via une approche de streaming.


  1. logique :

CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;

Lorsque vous créez une table CSV, le serveur crée un fichier de format de table dans le répertoire de la base de données. Le fichier commence par le nom de la table et a une extension .frm. Le moteur de stockage crée également un fichier de données. Son nom commence par le nom de la table et a une extension .CSV. Le fichier de données est un fichier texte brut. Lorsque vous stockez des données dans la table, le moteur de stockage l'enregistre dans le fichier de données au format de valeurs séparées par des virgules.


Cette réponse utilise Python et une bibliothèque tierce populaire, PyMySQL . Je l'ajoute parce que la bibliothèque de csv de Python est assez puissante pour manipuler correctement beaucoup de différentes saveurs de .csv et aucune autre réponse n'utilise le code Python pour interagir avec la base de données.

import contextlib
import csv
import datetime
import os

# https://github.com/PyMySQL/PyMySQL
import pymysql

SQL_QUERY = """\
SELECT * FROM my_table WHERE my_attribute = 'my_attribute';
"""

# embedding passwords in code gets nasty when you use version control
# the environment isn't much better, but this is an example
# http://.com/questions/12461484/is-it-secure-to-store-passwords-as-environment-variables-rather-than-as-plain-t
SQL_USER = os.environ['SQL_USER']
SQL_PASS = os.environ['SQL_PASS']

connection = pymysql.connect(host='localhost',
                             user=SQL_USER,
                             password=SQL_PASS,
                             db='dbname')

with contextlib.closing(connection):
    with connection.cursor() as cursor:
        cursor.execute(SQL_QUERY)
        # Hope you have enough memory :)
        results = cursor.fetchall()

output_file = 'my_query-{}.csv'.format(datetime.datetime.today().strftime('%Y-%m-%d'))
with open(output_file, 'w', newline='') as csvfile:
    # http://.com/a/17725590/2958070 about lineterminator
    csv_writer = csv.writer(csvfile, lineterminator='\n')
    csv_writer.writerows(results)

Pas exactement comme un format CSV, mais la commande tee du client MySQL peut être utilisée pour enregistrer la sortie dans un fichier local :

tee foobar.txt
SELECT foo FROM bar;

Vous pouvez le désactiver en utilisant notee .

Le problème avec SELECT … INTO OUTFILE …; est qu'il nécessite l'autorisation d'écrire des fichiers sur le serveur.


Try this code:
SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

for more: http://dev.mysql.com/doc/refman/5.1/en/select-into.html

Toutes les solutions ici à ce jour, à l'exception du workbench Mysql, sont incorrectes et très probablement dangereuses (c'est-à-dire des problèmes de sécurité) pour au moins un contenu possible dans la base de données mysql.

Mysql Workbench (et de même PHPMyAdmin) fournissent une solution formellement correcte, mais sont conçus pour télécharger la sortie vers l'emplacement d'un utilisateur. Ils ne sont pas très utiles pour des choses comme l'automatisation de l'exportation de données.

Il n'est pas possible de générer de manière fiable csv à partir de la sortie de mysql -B -e 'SELECT ...' car cela ne peut pas encoder les retours chariot et les espaces dans les champs. L'indicateur '-s' de mysql fait un backslash s'échappant, et pourrait conduire à une solution correcte. Cependant, l'utilisation d'un langage de script (avec des structures de données internes correctes, et non pas bash), et des bibliothèques où les problèmes d'encodage ont déjà été soigneusement résolus est beaucoup plus sûr.

J'ai pensé écrire un script pour cela, mais dès que j'ai pensé à ce que j'appellerais, il m'est venu à l'esprit de chercher un travail préexistant du même nom. Bien que je ne l'aie pas étudié à fond, la solution sur https://github.com/robmiller/mysql2csv semble prometteuse. En fonction de votre application, l'approche yaml pour spécifier les commandes SQL pourrait ou non ne pas plaire. Je ne suis pas non plus ravi de l'exigence d'une version plus récente de ruby ​​que celle fournie en standard avec mon ordinateur portable Ubuntu 12.04 ou mes serveurs Debian Squeeze. Oui, je sais que je pourrais utiliser le RVM, mais je préférerais ne pas le maintenir pour un objectif aussi simple.

Espérons que quelqu'un nous indiquera un outil approprié, qui a eu un peu de test. Sinon, je vais probablement mettre à jour quand je trouve ou écris un.


Pour étendre sur les réponses précédentes, le one-liner suivant exporte une seule table en tant que fichier séparé par des tabulations. Il est adapté à l'automatisation, l'exportation de la base de données tous les jours ou plus.

mysql -B -D mydatabase -e 'select * from mytable'

Commodément, nous pouvons utiliser la même technique pour lister les tables de MySQL et décrire les champs sur une seule table:

mysql -B -D mydatabase -e 'show tables'

mysql -B -D mydatabase -e 'desc users'

Field   Type    Null    Key Default Extra
id  int(11) NO  PRI NULL    auto_increment
email   varchar(128)    NO  UNI NULL    
lastName    varchar(100)    YES     NULL    
title   varchar(128)    YES UNI NULL    
userName    varchar(128)    YES UNI NULL    
firstName   varchar(100)    YES     NULL    

Cela m'a sauvé une ou deux fois. Rapide et ça marche!

--batch

--raw

Exemple:

sudo mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8 --database=demo_database \
   --batch --raw < /var/demo_sql_query.sql > /var/demo_csv_export.csv

La solution OUTFILE fournie par Paul Tomblin provoque l'écriture d'un fichier sur le serveur MySQL lui-même, donc cela ne fonctionnera que si vous avez un accès FILE , ainsi qu'un accès de connexion ou d'autres moyens pour récupérer le fichier de cette boîte.

Si vous ne disposez pas d'un tel accès et que la sortie délimitée par des tabulations est un substitut raisonnable de CSV (par exemple, si votre objectif final est d'importer vers Excel), alors la solution de Serbaut (using mysql --batch et éventuellement --raw ) est le chemin à parcourir.


mysql --batch, -B

Imprimez les résultats en utilisant l'onglet comme séparateur de colonne, chaque ligne sur une nouvelle ligne. Avec cette option, mysql n'utilise pas le fichier historique. Le mode batch entraîne un format de sortie non tabulaire et l'échappement des caractères spéciaux. L'échappement peut être désactivé en utilisant le mode brut; Voir la description de l'option --raw.

Cela vous donnera un fichier séparé par des tabulations. Puisque les virgules (ou les chaînes contenant une virgule) ne sont pas échappées, il n'est pas simple de changer le délimiteur en virgule.


Voici ce que je fais:

echo $QUERY | \
  mysql -B  $MYSQL_OPTS | \
  perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
  mail -s 'report' [email protected]

Le script perl (snipé d'ailleurs) fait un bon travail de conversion des champs espacés par tabulation en CSV.


Alternativement à la réponse ci-dessus, vous pouvez avoir une table MySQL qui utilise le moteur CSV.

Ensuite, vous aurez un fichier sur votre disque dur qui sera toujours dans un format CSV que vous pourriez simplement copier sans le traiter.


À partir de votre ligne de commande, vous pouvez faire ceci:

mysql -h *hostname* -P *port number* --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv*

Crédits: Exportation de la table d'Amazon RDS dans un fichier csv


En utilisant la solution postée par Tim, j'ai créé ce script bash pour faciliter le processus (le mot de passe root est demandé, mais vous pouvez facilement modifier le script pour demander un autre utilisateur):

#!/bin/bash

if [ "$1" == "" ];then
    echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
    exit
fi

DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3

echo "MySQL password:"
stty -echo
read PASS
stty echo

mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME

Il va créer un fichier nommé: database.table.csv


En outre, si vous exécutez la requête sur la ligne de commande Bash, je crois que la commande tr peut être utilisée pour remplacer les onglets par défaut par des délimiteurs arbitraires.

$ echo "SELECT * FROM Employee" | mysql Database | tr '\t' ,


Essayez ceci, ça a marché pour moi

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

IGNORE 1 ROWS ici ignore la première ligne qui contient les noms de champs. Notez que pour le nom de fichier, vous devez taper le chemin absolu du fichier.





mysql bash csv quotes