bash - सीएसवी प्रारूप में MySQL क्वेरी परिणाम कैसे आउटपुट करें?




csv quotes (21)

क्या लिनक्स कमांड लाइन से MySQL क्वेरी चलाने और CSV प्रारूप में परिणाम आउटपुट करने का कोई आसान तरीका है?

मैं अब क्या कर रहा हूं:

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

यह बहुत गड़बड़ हो जाता है जब उद्धरणों से घिरे होने वाले बहुत सारे कॉलम होते हैं, या यदि परिणाम में उद्धरण होते हैं जिन्हें बचने की आवश्यकता होती है।


Answers

साथ ही, यदि आप बैश कमांड लाइन पर क्वेरी कर रहे हैं, तो मेरा मानना ​​है कि tr कमांड को डिफॉल्ट टैब को मनमाने ढंग से डिलीमीटर करने के लिए उपयोग किया जा सकता है।

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


कैसा रहेगा:

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

इसे करने का एक काफी गहरा तरीका है। इसे कहीं मिला, कोई क्रेडिट नहीं ले सकता है

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 केवल लिखने साबित होता है।

रेगेक्स स्पष्टीकरण:

  • s /// का मतलब है कि पहले // के बीच क्या है, दूसरे के बीच क्या है //
  • अंत में "जी" एक संशोधक है जिसका अर्थ है "सभी उदाहरण, केवल पहले नहीं"
  • ^ (इस संदर्भ में) का अर्थ लाइन की शुरुआत है
  • $ (इस संदर्भ में) का मतलब रेखा का अंत है

तो, इसे सब एक साथ रखो:

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

यह मुझे दो बार बचाया। तेज़ और यह काम करता है!

--batch

--raw

उदाहरण:

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

सीएसवी डंप को सरल क्वेरी करने के लिए छोटे बैश स्क्रिप्ट, 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"

यह आसान है, और यह बैच मोड या आउटपुट फ़ाइलों की आवश्यकता के बिना कुछ भी काम करता है:

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

from your_table where etc;

स्पष्टीकरण:

  1. प्रत्येक फ़ील्ड में "साथ" "बदलें -> प्रतिस्थापित करें (फ़ील्ड 1, '' ',' '' '' ')
  2. उद्धरण चिह्नों में प्रत्येक परिणाम के आसपास -> concat ('' ', परिणाम 1,' '')
  3. प्रत्येक उद्धृत परिणाम के बीच एक अल्पविराम रखें -> concat_ws (',', उद्धृत 1, उद्धृत 2, ...)

बस!


टिम द्वारा पोस्ट किए गए समाधान का उपयोग करके, मैंने प्रक्रिया को सुविधाजनक बनाने के लिए इस बैश स्क्रिप्ट को बनाया है (रूट पासवर्ड का अनुरोध किया गया है, लेकिन आप किसी भी अन्य उपयोगकर्ता से पूछने के लिए स्क्रिप्ट को आसानी से संशोधित कर सकते हैं):

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

यह नाम फ़ाइल बनायेगा: डेटाबेस.table.csv


MySQL वर्कबेंच सीएसवी को रिकॉर्डसेट निर्यात कर सकता है, और ऐसा लगता है कि खेतों में कॉमा को बहुत अच्छी तरह से संभाला जा सकता है। सीएसवी ओपनऑफिस जुर्माना में खुलता है।


पॉल टॉम्बलिन द्वारा दिए गए आउटफाइल समाधान को फ़ाइल को MySQL सर्वर पर ही लिखा जाना चाहिए, इसलिए यह केवल तभी काम करेगा जब आपके पास FILE पहुंच हो, साथ ही लॉगिन बॉक्स या उस बॉक्स से फ़ाइल को पुनर्प्राप्त करने के अन्य साधन भी हों।

यदि आपके पास ऐसी पहुंच नहीं है, और टैब-सीमांकित आउटपुट CSV के लिए एक उचित विकल्प है (उदाहरण के लिए, यदि आपका अंतिम लक्ष्य एक्सेल में आयात करना है), तो सर्बॉट का समाधान ( mysql --batch और वैकल्पिक रूप से --raw का उपयोग --raw ) है जाने का रास्ता।


वैकल्पिक रूप से ऊपर दिए गए उत्तर में, आपके पास एक MySQL तालिका हो सकती है जो CSV इंजन का उपयोग करती है।

फिर आपके पास अपनी हार्ड डिस्क पर एक फ़ाइल होगी जो हमेशा एक CSV प्रारूप में होगी जो आप इसे संसाधित किए बिना कॉपी कर सकते हैं।


यूनिक्स / Cygwin केवल, इसे 'tr' के माध्यम से पाइप करें:

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

एनबी: यह न तो एम्बेडेड कॉमा, न ही एम्बेडेड टैब को संभालता है।


यह उत्तर पायथन और एक लोकप्रिय तृतीय पक्ष लाइब्रेरी, PyMySQL का उपयोग करता है। मैं इसे जोड़ रहा हूं क्योंकि पायथन की csv लाइब्रेरी शक्तिशाली है .csv कई अलग-अलग स्वादों को सही तरीके से संभालने के लिए पर्याप्त है और डेटाबेस के साथ बातचीत करने के लिए कोई अन्य जवाब पायथन कोड का उपयोग नहीं कर रहा है।

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)

  1. तर्क :

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

जब आप एक CSV तालिका बनाते हैं, तो सर्वर डेटाबेस निर्देशिका में एक तालिका प्रारूप फ़ाइल बनाता है। फ़ाइल तालिका के नाम से शुरू होती है और इसमें एक .frm एक्सटेंशन होता है। स्टोरेज इंजन भी एक डेटा फ़ाइल बनाता है। इसका नाम तालिका के नाम से शुरू होता है और इसमें एक सीएसवी एक्सटेंशन होता है। डेटा फ़ाइल एक सादा पाठ फ़ाइल है। जब आप तालिका में डेटा संग्रहीत करते हैं, तो स्टोरेज इंजन इसे अल्पविराम से अलग मूल्य प्रारूप में डेटा फ़ाइल में सहेजता है।


$ mysql your_database --password=foo < my_requests.sql > out.csv

टैब अलग कौन सा है। इसे सही सीएसवी प्राप्त करने के लिए पाइप करें (धन्यवाद @ कहीं भी नहीं):

... .sql | sed 's/\t/,/g' > out.csv

MySQL वर्कबेंच को छोड़कर, यहां तक ​​कि सभी समाधान, MySQL डीबी में कम से कम कुछ संभावित सामग्री के लिए गलत और संभवतः असुरक्षित (यानी सुरक्षा समस्याएं) हैं।

MySQL वर्कबेंच (और इसी तरह PHPMyAdmin) औपचारिक रूप से सही समाधान प्रदान करते हैं, लेकिन उपयोगकर्ता के स्थान पर आउटपुट डाउनलोड करने के लिए डिज़ाइन किए गए हैं। वे डेटा निर्यात स्वचालित करने जैसी चीजों के लिए बहुत उपयोगी नहीं हैं।

mysql -B -e 'SELECT ...' के आउटपुट से भरोसेमंद सही सीएसवी उत्पन्न करना संभव नहीं है क्योंकि यह फ़ील्ड में कैरिज रिटर्न और सफेद स्थान को एन्कोड नहीं कर सकता है। MySQL में '-s' ध्वज बैकस्लैश से बच निकलता है, और यह एक सही समाधान का कारण बन सकता है। हालांकि, एक स्क्रिप्टिंग भाषा का उपयोग करना (सभ्य आंतरिक डेटा संरचनाओं के साथ एक, जो बाश नहीं है), और पुस्तकालय जहां एन्कोडिंग समस्याएं पहले ही सावधानी से तैयार की गई हैं, कहीं अधिक सुरक्षित है।

मैंने इसके लिए एक स्क्रिप्ट लिखने के बारे में सोचा, लेकिन जैसे ही मैंने सोचा कि मैं इसे क्या कहूंगा, यह मेरे नाम से पूर्व-मौजूदा काम को उसी नाम से खोजने के लिए हुआ। जबकि मैं इसे पूरी तरह से नहीं चला गया है, https://github.com/robmiller/mysql2csv पर समाधान आशाजनक लग रहा है। आपके आवेदन के आधार पर, SQL आदेश निर्दिष्ट करने के लिए yaml दृष्टिकोण हालांकि अपील कर सकता है या नहीं। मैं अपने उबंटू 12.04 लैपटॉप या डेबियन निचोड़ सर्वर के साथ मानक के रूप में आता है की तुलना में रूबी के एक नवीनतम संस्करण के लिए आवश्यकता से भी रोमांचित नहीं हूं। हां, मुझे पता है कि मैं आरवीएम का उपयोग कर सकता हूं, लेकिन मैं इसे इतना आसान उद्देश्य के लिए बनाए रखना नहीं चाहता हूं।

उम्मीद है कि कोई उपयुक्त टूल इंगित करेगा, जिसमें थोड़ा परीक्षण हो रहा है। अन्यथा जब मैं एक ढूंढता हूं या लिखता हूं तो शायद मैं इसे अपडेट कर दूंगा।



एक सीएसवी प्रारूप के रूप में बिल्कुल नहीं, लेकिन MySQL क्लाइंट से tee कमांड का उपयोग स्थानीय फ़ाइल में आउटपुट को सहेजने के लिए किया जा सकता है:

tee foobar.txt
SELECT foo FROM bar;

आप notee का उपयोग कर इसे अक्षम कर सकते हैं।

SELECT … INTO OUTFILE …; साथ समस्या SELECT … INTO OUTFILE …; यह है कि सर्वर पर फ़ाइलों को लिखने की अनुमति की आवश्यकता है।


यहां मैं क्या करता हूं:

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

पर्ल स्क्रिप्ट (कहीं और से छीन ली गई) टैब स्पेस फ़ील्ड को CSV में परिवर्तित करने का अच्छा काम करती है।


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 प्रक्रिया के अंतर्गत चल रहा है उसे चयनित निर्देशिका में लिखने की अनुमति होनी चाहिए, या आदेश विफल हो जाएगा।

यदि आप रिमोट सर्वर से विशेष रूप से होस्टेड या वर्चुअलाइज मशीन जैसे स्थानीय मशीन पर आउटपुट लिखना चाहते हैं, तो यह समाधान उपयुक्त नहीं है।


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

रूट के रूप में mysql पर लॉग इन करें और निम्न क्वेरी टाइप करें

select User from mysql.user;

+------+
| User |
+------+
| amon |
| root |
| root |
+------+




mysql bash csv quotes