From 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';
Note: That syntax may need to be reordered to
SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';
in more recent versions of MySQL.
Using this command, columns names will not be exported.
Also note that /var/lib/mysql-files/orders.csv
will be on the server that is running MySQL. The user that the MySQL process is running under must have permissions to write to the directory chosen, or the command will fail.
If you want to write output to your local machine from a remote server (especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.
Connecting to MYSQL with Python 2 in three steps
1 - Setting
You must install a MySQL driver before doing anything. Unlike PHP, Only the SQLite driver is installed by default with Python. The most used package to do so is MySQLdb but it's hard to install it using easy_install. Please note MySQLdb only supports Python 2.
For Windows user, you can get an exe of MySQLdb.
For Linux, this is a casual package (python-mysqldb). (You can use sudo apt-get install python-mysqldb
(for debian based distros), yum install MySQL-python
(for rpm-based), or dnf install python-mysql
(for modern fedora distro) in command line to download.)
For Mac, you can install MySQLdb using Macport.
2 - Usage
After installing, Reboot. This is not mandatory, But it will prevent me from answering 3 or 4 other questions in this post if something goes wrong. So please reboot.
Then it is just like using any other package :
#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect(host="localhost", # your host, usually localhost
user="john", # your username
passwd="megajonhy", # your password
db="jonhydb") # name of the data base
# you must create a Cursor object. It will let
# you execute all the queries you need
cur = db.cursor()
# Use all the SQL you like
cur.execute("SELECT * FROM YOUR_TABLE_NAME")
# print all the first cell of all the rows
for row in cur.fetchall():
print row[0]
db.close()
Of course, there are thousand of possibilities and options; this is a very basic example. You will have to look at the documentation. A good starting point.
3 - More advanced usage
Once you know how it works, You may want to use an ORM to avoid writing SQL manually and manipulate your tables as they were Python objects. The most famous ORM in the Python community is SQLAlchemy.
I strongly advise you to use it: your life is going to be much easier.
I recently discovered another jewel in the Python world: peewee. It's a very lite ORM, really easy and fast to setup then use. It makes my day for small projects or stand alone apps, Where using big tools like SQLAlchemy or Django is overkill :
import peewee
from peewee import *
db = MySQLDatabase('jonhydb', user='john', passwd='megajonhy')
class Book(peewee.Model):
author = peewee.CharField()
title = peewee.TextField()
class Meta:
database = db
Book.create_table()
book = Book(author="me", title='Peewee is cool')
book.save()
for book in Book.filter(author="me"):
print book.title
This example works out of the box. Nothing other than having peewee (pip install peewee
) is required.
Best Answer
I've seen this being caused by a huge temporary table that was created during a complex query with many joins. Think where in your application could such a query exist and try triggering it while monitoring disk activity/available space. This is how we discovered what was going on. As a solution, you can give more space for use for temporary tables (/tmp by default), or try refactoring the query. The fact that /tmp is empty and has plenty of space when you look at it, doesn't mean it's not all getting consumed during query execution. In our case, /tmp could take all available space on disk, and it actually did during the execution of this particularly complex query, breaking with an error similar to yours because even that wasn't enough.