كيفية إخراج نتائج استعلام MySQL بتنسيق CSV؟




bash quotes (17)

هل هناك طريقة سهلة لتشغيل استعلام MySQL من سطر أوامر Linux وإخراج النتائج بتنسيق CSV ؟

إليك ما أفعله الآن:

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

يصبح الفوضى عند وجود العديد من الأعمدة التي يجب أن تكون محاطة بعلامات اقتباس أو إذا كانت هناك علامات اقتباس في النتائج التي يجب تجنبها.


mysql --batch، -B

اطبع النتائج باستخدام علامة التبويب كفاصل للعمود ، مع كل صف في سطر جديد. باستخدام هذا الخيار ، لا يستخدم mysql ملف السجل. ينتج عن وضع الدُفعة تنسيق إخراج غير جدولي وهروب من الأحرف الخاصة. قد يتم تعطيل escaping باستخدام وضع raw؛ راجع وصف الخيار --raw.

سيعطيك هذا ملفًا مفصولًا بعلامة التبويب. بما أن الفواصل (أو السلاسل التي تحتوي على فاصلة) لا يتم إهمالها ، فليس من السهل تغيير المُحدد إلى فاصلة.


  1. منطق :

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

عندما تقوم بإنشاء جدول CSV ، يقوم الخادم بإنشاء ملف تنسيق جدول في دليل قاعدة البيانات. يبدأ الملف باسم الجدول ولديه ملحق .frm. مشغل التخزين أيضا بإنشاء ملف بيانات. يبدأ اسمها باسم الجدول ولديه ملحق .CSV. ملف البيانات هو ملف نصي عادي. عند تخزين البيانات في الجدول ، يقوم محرك التخزين بحفظها في ملف البيانات بتنسيق قيم مفصولة بفواصل.


إذا كان هناك PHP مثبتًا على الجهاز الذي تستخدمه ، فيمكنك كتابة برنامج PHP النصي للقيام بذلك. يتطلب تثبيت PHP تثبيت ملحق MySQL.

يمكنك الاتصال بمترجم PHP من سطر الأوامر كما يلي:

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

أقوم --php-ini مفتاح --php-ini ، لأنك قد تحتاج إلى استخدام تكوين PHP الخاص بك الذي يمكّن ملحق MySQL. على PHP 5.3.0+ يتم تمكين هذه الإضافة بشكل افتراضي ، بحيث لم يعد ذلك ضروريًا لاستخدام التكوين لتمكينها.

ثم يمكنك كتابة البرنامج النصي للتصدير الخاص بك مثل أي برنامج نصي PHP العادي:

<?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";
    }
?>

تكمن ميزة هذه الطريقة في عدم وجود مشكلات في حقول varchar والنصوص التي تحتوي على نص يحتوي على خطوط جديدة. يتم اقتباس هذه الحقول بشكل صحيح وسيتم تفسير تلك الخطوط الجديدة في قارىء CSV كجزء من النص ، وليس فواصل السجلات. هذا شيء يصعب تصحيحه بعد ذلك مع sed أو ما شابه.


إليك ما أقوم به:

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

يعمل البرنامج النصي perl (المقتطف من مكان آخر) بشكل جيد في تحويل حقول تباعد علامة التبويب إلى CSV.


باستخدام الحل الذي نشره تيم ، قمت بإنشاء هذا البرنامج النصي bash لتسهيل العملية (يتم طلب كلمة مرور الجذر ، ولكن يمكنك تعديل البرنامج النصي بسهولة لطلب أي مستخدم آخر):

#!/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

سيقوم بإنشاء ملف يسمى: database.table.csv


بدلاً من ذلك ، يمكنك الحصول على جدول MySQL يستخدم محرك CSV بدلاً من الإجابة المذكورة أعلاه.

ثم سيكون لديك ملف على القرص الثابت الخاص بك والذي سيكون دائمًا بتنسيق CSV الذي يمكنك نسخه فقط دون معالجته.


بناءً على user7610 ، إليك أفضل طريقة للقيام بذلك. مع ملف mysql outfile كان هناك 60 دقيقة من ملكية الملفات ومشاكل الكتابة.

انها ليست باردة ، لكنها عملت في 5 دقائق.

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

?>

تستخدم هذه الإجابة Python ومكتبة عامة لجهة خارجية ، PyMySQL . أقوم بإضافته لأن مكتبة csv في Python قوية بما يكفي للتعامل بشكل صحيح مع العديد من النكهات المختلفة من .csv ولا توجد إجابات أخرى تستخدم شفرة Python للتفاعل مع قاعدة البيانات.

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)

كيف حول:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv

للتوسع في الإجابات السابقة ، تصدر إحدى الخطوط التالية جدولاً منفصلاً كملف مفصول بعلامات جدولة. انها مناسبة للأتمتة ، وتصدير قاعدة البيانات كل يوم أو نحو ذلك.

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

بشكل ملائم ، يمكننا استخدام نفس الأسلوب لإدراج جداول MySQL ، ووصف الحقول على جدول واحد:

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    

من 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';

استخدام أسماء أعمدة الأوامر هذه لن يتم تصديرها.

لاحظ أيضًا أن /var/lib/mysql-files/orders.csv سيكون على خادم يقوم بتشغيل MySQL. يجب أن يكون لدى المستخدم الذي تعمل عليه عملية MySQL أذونات الكتابة إلى الدليل المختار ، وإلا فسوف يفشل الأمر.

إذا كنت ترغب في كتابة الإخراج إلى جهازك المحلي من خادم بعيد (لا سيما جهاز مستضاف أو ظاهري مثل Heroku أو Amazon RDS) ، فهذا الحل غير مناسب.


من خلال سطر الأوامر ، يمكنك القيام بذلك:

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*

الاعتمادات: تصدير الجدول من Amazon RDS إلى ملف CSV


هذا بسيط ، ويعمل على أي شيء دون الحاجة إلى وضع الدفعات أو ملفات الإخراج:

select concat_ws(',',
    concat('"', replace(field1, '"', '""'), '"'),
    concat('"', replace(field2, '"', '""'), '"'),
    concat('"', replace(field3, '"', '""'), '"'))

from your_table where etc;

تفسير:

  1. استبدل "with" "في كل حقل -> replace (field1، '"'، '""')
  2. تحيط كل نتيجة بعلامات اقتباس -> concat ('"'، result1، '"')
  3. ضع فاصلة بين كل نتيجة مقتبسة -> concat_ws ('،' ، ونقلت ، 1 ، ونقلت 2 ، ...)

هذا هو!


هنا طريقة جنائية للقيام بذلك. وجدت في مكان ما ، لا يمكن اتخاذ أي الائتمان

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

يعمل بشكل جيد. مرة أخرى على الرغم من regex يثبت الكتابة فقط.

شرح regex:

  • s /// تعني استبدال ما بين أول // مع ما بين الثاني //
  • يكون "g" في النهاية معدلاً يعني "على سبيل المثال وليس فقط أولاً"
  • ^ (في هذا السياق) تعني بداية السطر
  • $ (في هذا السياق) تعني نهاية السطر

لذا ، ضع كل ذلك معًا:

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

يونيكس / Cygwin فقط ، من خلال الأنابيب 'tr':

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

ملاحظة: هذا لا يعالج الفواصل المضمنة أو علامات التبويب المضمنة.


MySQL Workbench يمكنه تصدير مجموعات السجلات إلى CSV ، ويبدو أنه يتعامل مع الفواصل في الحقول بشكل جيد للغاية. CSV يفتح في OpenOffice بخير.


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






quotes