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



Answers

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

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

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

क्या लिनक्स कमांड लाइन से 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

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




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

?>



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

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

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

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

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




  1. तर्क :

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

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




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

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




यदि आपके द्वारा उपयोग की जा रही मशीन पर PHP स्थापित है, तो आप ऐसा करने के लिए एक PHP स्क्रिप्ट लिख सकते हैं। इसके लिए PHP स्थापना में MySQL एक्सटेंशन स्थापित होना आवश्यक है।

आप कमांड लाइन से PHP दुभाषिया को इस प्रकार कॉल कर सकते हैं:

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

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

इस विधि का लाभ यह है कि इसमें वर्कर और टेक्स्ट फ़ील्ड्स के साथ कोई समस्या नहीं है, जिसमें टेक्स्टलाइन न्यूलाइन हैं। उन क्षेत्रों को सही ढंग से उद्धृत किया गया है और उनमें उन नईलाइनों को सीएसवी रीडर द्वारा पाठ के एक हिस्से के रूप में व्याख्या किया जाएगा, रिकॉर्ड विभाजक नहीं। ऐसा कुछ ऐसा है जो बाद में या तो सही के साथ सही करना मुश्किल है।




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

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



यह उत्तर पायथन और एक लोकप्रिय तृतीय पक्ष लाइब्रेरी, 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)



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

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



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

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




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

फिर आपके पास अपनी हार्ड डिस्क पर एक फ़ाइल होगी जो हमेशा एक 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    



Related