query - read csv in mysql




MySQL 쿼리 결과를 CSV 형식으로 출력하는 방법은 무엇입니까? (20)

mysql --batch, -B

탭을 열 구분 기호로 사용하여 결과를 인쇄하고 각 행을 새 행에 인쇄하십시오. 이 옵션을 사용하면 mysql이 히스토리 파일을 사용하지 않는다. 일괄 처리 모드는 표 형식이 아닌 출력 형식과 특수 문자 이스케이프를 초래합니다. 이스케이프 처리는 원시 모드를 사용하여 비활성화 할 수 있습니다. --raw 옵션에 대한 설명을 참조하십시오.

이렇게하면 탭으로 구분 된 파일이 제공됩니다. 쉼표 (또는 쉼표가 포함 된 문자열)는 이스케이프되지 않기 때문에 구분 기호를 쉼표로 변경하는 것은 쉽지 않습니다.

Linux 명령 행에서 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

따옴표로 묶일 필요가있는 열이 많이 있거나 이스케이프해야하는 결과에 따옴표가있는 경우에는 지저분 해집니다.


  1. 논리 :

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

CSV 테이블을 만들면 서버는 데이터베이스 디렉토리에 테이블 형식 파일을 만듭니다. 파일은 테이블 이름으로 시작하고 확장자가 .frm입니다. 스토리지 엔진은 또한 데이터 파일을 생성합니다. 그 이름은 테이블 이름으로 시작하고 확장자가 .CSV입니다. 데이터 파일은 일반 텍스트 파일입니다. 데이터를 테이블에 저장하면 스토리지 엔진은 쉼표로 구분 된 값 형식으로 데이터 파일에 저장합니다.


PHP를 서버에 설치했다면, mysql2csv 를 사용하여 mysql2csv MySQL 쿼리에 대한 (실제로 유효한) CSV 파일을 내보낼 수 있습니다. MySQL에서 내 대답 보기 - SELECT * 로컬로보기? 좀 더 많은 컨텍스트 / 정보.

mysql 에서 옵션 이름을 유지하려고 시도 했으므로 --file--query 옵션을 제공하기에 충분해야합니다.

./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"

를 통해 "설치" mysql2csv

wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65  mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv

(요지의 내용을 다운로드하고 체크섬을 체크하고 실행 가능하게 만든다).


Paul Tomblin이 제공하는 OUTFILE 솔루션은 MySQL 서버 자체에 파일을 작성하게하므로 FILE 액세스 권한이있는 경우에만 작동하고 로그인 액세스 또는 해당 상자에서 파일을 검색하는 다른 방법도 사용할 수 있습니다.

그러한 액세스 권한이없고 탭으로 구분 된 출력이 CSV에 대한 합리적인 대체품 인 경우 (예 : 최종 목표가 Excel로 가져 오는 것이면), Serbaut의 솔루션 ( mysql --batch 및 선택적으로 --raw )은 다음과 같습니다. 갈 길.


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

꽤 잘 작동합니다. 다시 한번 정규식은 쓰기 만 증명합니다.

정규식 설명 :

  • 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

다음 bash 스크립트가 저에게 효과적입니다. 또한 선택적으로 요청 된 테이블에 대한 스키마를 가져옵니다.

#!/bin/bash
#
# export mysql data to CSV
#https://.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
#

#ansi colors
#http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
blue='\033[0;34m'
red='\033[0;31m'
green='\033[0;32m' # '\e[1;32m' is too bright for white bg.
endColor='\033[0m'

#
# a colored message 
#   params:
#     1: l_color - the color of the message
#     2: l_msg - the message to display
#
color_msg() {
  local l_color="$1"
  local l_msg="$2"
  echo -e "${l_color}$l_msg${endColor}"
}


#
# error
#
# show the given error message on stderr and exit
#
#   params:
#     1: l_msg - the error message to display
#
error() {
  local l_msg="$1"
  # use ansi red for error
  color_msg $red "Error:" 1>&2
  color_msg $red "\t$l_msg" 1>&2
  usage
}

#
# display usage 
#
usage() {
  echo "usage: $0 [-h|--help]" 1>&2
  echo "               -o  | --output      csvdirectory"    1>&2
  echo "               -d  | --database    database"   1>&2
  echo "               -t  | --tables      tables"     1>&2
  echo "               -p  | --password    password"   1>&2
  echo "               -u  | --user        user"       1>&2
  echo "               -hs | --host        host"       1>&2
  echo "               -gs | --get-schema"             1>&2
  echo "" 1>&2
  echo "     output: output csv directory to export mysql data into" 1>&2
  echo "" 1>&2
  echo "         user: mysql user" 1>&2
  echo "     password: mysql password" 1>&2
  echo "" 1>&2
  echo "     database: target database" 1>&2
  echo "       tables: tables to export" 1>&2
  echo "         host: host of target database" 1>&2
  echo "" 1>&2
  echo "  -h|--help: show help" 1>&2
  exit 1
}

#
# show help 
#
help() {
  echo "$0 Help" 1>&2
  echo "===========" 1>&2
  echo "$0 exports a csv file from a mysql database optionally limiting to a list of tables" 1>&2
  echo "   example: $0 --database=cms --user=scott --password=tiger  --tables=person --output person.csv" 1>&2
  echo "" 1>&2
  usage
}

domysql() {
  mysql --host $host -u$user --password=$password $database
}

getcolumns() {
  local l_table="$1"
  echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null
}

host="localhost"
mysqlfiles="/var/lib/mysql-files/"

# parse command line options
while true; do
  #echo "option $1"
  case "$1" in
    # options without arguments
    -h|--help) usage;;
    -d|--database)     database="$2" ; shift ;;
    -t|--tables)       tables="$2" ; shift ;;
    -o|--output)       csvoutput="$2" ; shift ;;
    -u|--user)         user="$2" ; shift ;;
    -hs|--host)        host="$2" ; shift ;;
    -p|--password)     password="$2" ; shift ;;
    -gs|--get-schema)  option="getschema";; 
    (--) shift; break;;
    (-*) echo "$0: error - unrecognized option $1" 1>&2; usage;;
    (*) break;;
  esac
  shift
done

# checks
if [ "$csvoutput" == "" ]
then
  error "ouput csv directory not set"
fi
if [ "$database" == "" ]
then
  error "mysql database not set"
fi
if [ "$user" == "" ]
then
  error "mysql user not set"
fi
if [ "$password" == "" ]
then
  error "mysql password not set"
fi

color_msg $blue "exporting tables of database $database"
if [ "$tables" = "" ]
then
tables=$(echo "show tables" | domysql)
fi

case $option in
  getschema) 
   rm $csvoutput$database.schema
   for table in $tables
   do
     color_msg $blue "getting schema for $table"
     echo -n "$table:" >> $csvoutput$database.schema
     getcolumns $table >> $csvoutput$database.schema
   done  
   ;;
  *)
for table in $tables
do
  color_msg $blue "exporting table $table"
  cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:)
  if [  "$cols" = "" ]
  then
    cols=$(getcolumns $table)
  fi
  ssh $host rm $mysqlfiles/$table.csv
cat <<EOF | mysql --host $host -u$user --password=$password $database 
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
  scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
  (echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
  rm $csvoutput$table.csv.raw
done
  ;;
esac

또한 Bash 명령 줄에서 쿼리를 수행하는 경우 tr 명령을 사용하여 기본 탭을 임의의 구분 기호로 대체 할 수 있다고 생각합니다.

$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,


어때?

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

여기 내가하는 일이있다.

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로 변환하는 훌륭한 작업을 수행합니다.


유닉스 / Cygwin 전용, 'tr'을 통해 파이프 :

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

주의 : 이것은 쉼표 나 내장 탭을 처리하지 않습니다.


이 대답은 Python과 널리 사용되는 타사 라이브러리 인 PyMySQL 합니다. Python의 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 is not 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)

이 페이지의 많은 답변은 CSV 형식으로 발생할 수있는 일반적인 경우를 처리하지 못하기 때문에 약합니다. 예를 들어 필드에 포함 된 쉼표 및 따옴표 및 항상 발생하는 기타 조건. 모든 유효한 CSV 입력 데이터에 대해 작동하는 일반적인 솔루션이 필요합니다.

다음은 파이썬에서 간단하고 강력한 해결책입니다 :

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

파일 이름을 tab2csv 지정하고 경로에 입력 한 다음 실행 권한을 부여한 다음 목록에 사용하십시오.

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

파이썬 CSV 처리 함수는 CSV 입력 형식의 대소 문자를 처리합니다.

이는 스트리밍 방식을 통해 매우 큰 파일을 처리하도록 향상 될 수 있습니다.


이것은 간단하며 일괄 처리 모드 또는 출력 파일을 필요로하지 않고 모든 작업을 수행합니다.

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

from your_table where etc;

설명:

  1. 각 필드에서 ""로 바꾸기 -> 바꾸기 (field1, ' "'," "" ')
  2. 각 결과를 따옴표로 묶으십시오 -> concat ( ' "', result1, '"')
  3. 인용 된 각 결과 사이에 쉼표를 넣으십시오 -> concat_ws ( ',', quoted1, quoted2, ...)

그게 다야!


이전 답변을 확장하려면 다음 한 줄자가 단일 표를 탭으로 구분 된 파일로 내 보냅니다. 매일 데이터베이스를 내보내는 자동화에 적합합니다.

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    

정확하게 CSV 형식은 아니지만 MySQL 클라이언트의 tee 명령 을 사용하여 출력을 로컬 파일에 저장합니다.

tee foobar.txt
SELECT foo FROM bar;

당신은 notee 사용하여 비활성화 할 수 있습니다.

SELECT … INTO OUTFILE …; 의 문제점 SELECT … INTO OUTFILE …; 서버에 파일을 쓰는 권한이 필요하다는 것입니다.


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 Workbench 는 레코드 세트를 CSV로 내보낼 수 있으며 필드의 쉼표를 매우 잘 처리합니다. CSV는 OpenOffice에서 잘 열립니다.


https://.com/a/5395421/2841607 에서 영감을 얻어 CSV 덤프에 대한 간단한 쿼리를 수행하기위한 초소형 bash 스크립트.

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

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

탭으로 구분됩니다. 진정한 CSV를 얻으려면 그것을 파이프하십시오 (감사합니다 @ therefromhere).

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




quotes