Apakah ada cara mudah untuk menjalankan query MySQL dari Linux command line dan output hasil di CSV format?
Berikut ini's apa yang saya'm lakukan sekarang:
mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ
Itu akan berantakan ketika ada banyak kolom yang harus dikelilingi oleh tanda kutip, atau jika ada tanda kutip dalam hasil yang harus melarikan diri.
Dari http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Menggunakan perintah ini kolom nama tidak akan diekspor.
Juga perhatikan bahwa /var/lib/mysql-files/order.csv
akan di server yang menjalankan MySQL. Pengguna bahwa MySQL adalah proses yang berjalan di bawah harus memiliki izin untuk menulis ke direktori yang dipilih, atau perintah akan gagal.
Jika anda ingin menulis output untuk mesin lokal anda dari remote server (terutama host atau virtualisasi mesin seperti Heroku atau Amazon RDS), solusi ini tidak cocok.
mysql --batch, -B
hasil Cetak menggunakan tab sebagai kolom pemisah, dengan masing-masing baris pada baris baru. Dengan opsi ini, mysql tidak menggunakan file history. modus Batch hasil non-tabel output format dan melarikan diri dari karakter khusus. Melarikan diri dapat dinonaktifkan dengan menggunakan mode raw; lihat deskripsi untuk --baku pilihan.
Ini akan memberikan anda sebuah tab file dipisahkan. Sejak koma (atau string yang mengandung koma) yang tidak lolos itu tidak mudah untuk mengubah delimiter koma.
Berikut ini's cukup degil cara untuk melakukannya. Ternyata di suatu tempat, dapat't mengambil kredit apapun
mysql --user=wibble --password goyangan -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
Bekerja dengan cukup baik. Sekali lagi meskipun regex membuktikan menulis saja.
Regex Penjelasan:
Jadi, menempatkan itu semua bersama-sama:
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
Ini menyelamatkan saya beberapa kali. Cepat dan bekerja!
Contoh:
mysql -udemo_user -p -h127.0.0.1 --port=3306 \
--default-character-set=utf8mb4 --database=demo_database \
--batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv
Untuk kelengkapan anda bisa mengkonversi ke csv (tapi hati-hati karena tab bisa dalam bidang nilai - nilai misalnya bidang teks)
tr '\t' ',' < file.tsv > file.csv
OUTFILE solusi yang diberikan oleh Paulus Tomblin menyebabkan file yang akan ditulis pada MySQL server itu sendiri, sehingga ini akan bekerja hanya jika anda memiliki FILE akses, serta akses login atau cara lain untuk mengambil file dari kotak itu.
Jika anda don't memiliki akses tersebut, dan tab-delimited output adalah pengganti yang wajar untuk CSV (misalnya, jika tujuan akhir anda adalah untuk impor ke Excel), maka Serbaut's solusi (menggunakan mysql --batch
dan opsional --mentah
) adalah cara untuk pergi.
MySQL Workbench dapat mengekspor recordsets ke CSV, dan tampaknya untuk menangani koma di bidang-bidang yang sangat baik. CSV membuka di OpenOffice baik-baik saja.
Semua solusi di sini untuk saat ini, kecuali MySQL workbench satu, adalah tidak benar dan sangat mungkin tidak aman (yaitu masalah keamanan) untuk setidaknya beberapa konten mungkin di db mysql.
MYSQL Workbench (dan demikian pula PHPMyAdmin) memberikan secara resmi solusi yang tepat, tetapi dirancang untuk men-download keluaran untuk pengguna's lokasi. Mereka're tidak begitu berguna untuk hal-hal seperti mengotomatisasi data ekspor.
Hal ini tidak mungkin untuk menghasilkan andal benar csv dari output mysql -B -e 'PILIH ...'
karena itu tidak dapat menyandikan kereta kembali dan putih ruang di bidang. The ' s' bendera ke mysql tidak melakukan backslash melarikan diri, dan mungkin menyebabkan solusi yang tepat. Namun, dengan menggunakan bahasa scripting (satu dengan layak struktur data internal yang tidak bash), dan perpustakaan di mana masalah pengkodean telah hati-hati bekerja keluar jauh lebih aman.
Saya berpikir tentang menulis script untuk ini, tetapi segera setelah saya berpikir tentang apa yang saya'd sebut itu, terpikir olehku untuk mencari karya yang sudah ada dengan nama yang sama. Sementara aku belum't pergi lebih dari itu secara menyeluruh, solusi di https://github.com/robmiller/mysql2csv tampak menjanjikan. Tergantung pada aplikasi anda, yaml pendekatan untuk menentukan perintah-perintah SQL yang mungkin atau mungkin tidak menarik meskipun. I'm juga tidak senang dengan persyaratan untuk versi yang lebih baru dari ruby dari datang sebagai standar dengan saya Ubuntu 12.04 laptop atau server Debian Squeeze. Ya aku tahu aku bisa menggunakan RVM, tapi aku'd agak tidak mempertahankan bahwa untuk suatu tujuan sederhana.
Mudah-mudahan seseorang akan menunjukkan sebuah alat yang cocok, yang's memiliki sedikit pengujian. Kalau tidak, aku'll mungkin update ini ketika aku menemukan atau menulis satu.
Dari baris perintah anda, anda dapat melakukan ini:
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*
Kredit: https://stackoverflow.com/questions/9536224/exporting-table-from-amazon-rds-into-a-csv-file
Banyak jawaban pada halaman ini adalah lemah karena mereka don't menangani kasus umum dari apa yang dapat terjadi dalam format CSV. misalnya koma dan kutipan tertanam di ladang dan kondisi lain yang selalu datang pada akhirnya. Kita perlu solusi umum yang bekerja untuk semua berlaku CSV input data.
Berikut ini's sederhana dan kuat solusi dalam 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)
Nama file tab2csv
, meletakkannya di jalan anda, memberikan izin mengeksekusi, kemudian menggunakannya seperti ini:
mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv > outfile.csv
Python CSV-fungsi penanganan menutupi kasus sudut untuk CSV format input(s).
Ini bisa ditingkatkan untuk menangani file yang sangat besar melalui streaming pendekatan.
MEMBUAT TABEL () (PILIH data DARI other_table ) ENGINE=CSV ;
Ketika anda membuat tabel CSV, server menciptakan format tabel dalam file direktori database. File yang dimulai dengan nama tabel dan memiliki .frm ekstensi. Mesin penyimpanan juga menciptakan sebuah file data. Namanya dimulai dengan nama tabel dan memiliki .Ekstensi CSV. Data file file teks biasa. Ketika anda menyimpan data ke dalam tabel, penyimpanan mesin menyimpannya ke dalam data file comma-separated values format.
Jawaban ini menggunakan Python dan tempat perpustakaan pihak ketiga, PyMySQL. I'm menambahkan ini karena Python's csv perpustakaan adalah cukup kuat untuk benar menangani banyak rasa yang berbeda dari .csv
dan tidak ada jawaban lain yang menggunakan kode Python untuk berinteraksi dengan database.
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 is not much better, but this is an example
# https://stackoverflow.com/questions/12461484
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://stackoverflow.com/a/17725590/2958070 about lineterminator
csv_writer = csv.writer(csvfile, lineterminator='\n')
csv_writer.writerows(results)
Ini adalah sederhana, dan bekerja pada apa pun tanpa perlu modus batch atau file output:
select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))
from your_table where etc;
Penjelasan:
"
dengan ","
di bidang masing-masing --> menggantikan(field1, ',"', '"",')
concat('",', result1, '",')
concat_ws(',', quoted1, quoted2, ...)
Yang's itu!
Untuk memperluas pada jawaban sebelumnya, berikut satu-kapal ekspor satu tabel yang dipisahkan tab file. It's cocok untuk otomatisasi, mengekspor database setiap hari atau lebih.
mysql -B -D mydatabase -e 'select * from mytable'
Mudah, kita dapat menggunakan teknik yang sama untuk daftar keluar MySQL's tabel, dan untuk menggambarkan kolom pada satu tabel:
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
Bangunan di user7610, berikut adalah cara terbaik untuk melakukannya. Dengan mysql outfile
ada 60 menit dari kepemilikan file dan timpa masalah.
It's tidak keren, tapi itu bekerja dalam 5 menit.
php csvdump.php localhost password root database tablename > apa pun yang anda suka.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);
?>
Tidak persis seperti format CSV, tapi tee
command dari MySQL klien dapat digunakan untuk menyimpan output ke a lokal file:
tee foobar.txt
SELECT foo FROM bar;
Anda dapat menonaktifkannya dengan menggunakan notee
.
Masalah dengan PILIH ... KE OUTFILE ...;
adalah bahwa hal itu membutuhkan izin untuk menulis file-file di server.