Exporting MySQL Query Results to CSV Format

Exporting MySQL query results to CSV (Comma Separated Values) format is a common requirement for data analysis, reporting, and import/export operations. In this tutorial, we will explore how to achieve this using various methods.

Introduction to MySQL CSV Export

MySQL provides several ways to export query results to CSV format. The most straightforward method is to use the INTO OUTFILE clause in your SQL query. This clause allows you to specify a file path and name where the query results will be written.

Method 1: Using INTO OUTFILE Clause

The INTO OUTFILE clause is used in conjunction with the FIELDS TERMINATED BY and LINES TERMINATED BY clauses to specify the CSV format. Here’s an example:

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

This query will export the results to a file named orders.csv in the /var/lib/mysql-files directory on the MySQL server. Note that the user running the MySQL process must have write permissions to this directory.

Method 2: Using MySQL Command-Line Tool

You can also use the MySQL command-line tool to export query results to CSV format. The --batch option allows you to specify a tab-separated output, which can be piped to a file.

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

To convert the tab-separated output to CSV, you can use the tr command:

mysql your_database --password=foo < my_requests.sql | tr '\t' ',' > data.csv

Alternatively, you can use the sed command to replace tabs with commas and add quotes around each field:

mysql your_database --password=foo < my_requests.sql | sed "s/\t/\",\"/g;s/^/\"/;s/$/\"/" > data.csv

Method 3: Using a Programming Language

You can also use a programming language like Python or PHP to connect to the MySQL database and export query results to CSV format. For example, in Python, you can use the mysql-connector-python library:

import mysql.connector
import csv

# Establish a connection to the MySQL database
cnx = mysql.connector.connect(
    user='your_username',
    password='your_password',
    host='your_host',
    database='your_database'
)

# Create a cursor object
cursor = cnx.cursor()

# Execute the query
query = "SELECT order_id, product_name, qty FROM orders WHERE foo = 'bar'"
cursor.execute(query)

# Fetch the results
results = cursor.fetchall()

# Open a CSV file for writing
with open('orders.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow([i[0] for i in cursor.description])  # Write the header row
    writer.writerows(results)  # Write the data rows

# Close the cursor and connection
cursor.close()
cnx.close()

This code establishes a connection to the MySQL database, executes the query, fetches the results, and writes them to a CSV file using the csv module.

Conclusion

In this tutorial, we explored three methods for exporting MySQL query results to CSV format: using the INTO OUTFILE clause, using the MySQL command-line tool, and using a programming language. Each method has its own advantages and disadvantages, and the choice of method depends on your specific requirements and preferences.