By coincidence, I'm working on a similar problem right now, so here's a chance for me to write up an experiment I ran today, in the hope that it might prove useful. However, the solution presented below is far from ideal (see section 3).
2. The idea
Processing your query results through Django's ORM and then through
csv.writer is time-consuming. You can speed things up by getting the database to write the query output directly to a file and then serving that file.
First, your database user needs to have
FILE privilege in order to be able to write files:
mysql> GRANT FILE ON *.* TO 'user'@'host';
Then you need to run your query with MySQL's
INTO OUTFILE clause. You can get the query you need from Django by calling
from django.db.models import Q
s101 = Q(fwy='101', dir='S', abs_pm__gte=420.80, abs_pm__lte=431.63)
s280 = Q(fwy='280', dir='S', abs_pm__gte=41.16, abs_pm__lte=56.49)
sql, params = Station.objects.filter(s101|s280).query.sql_with_params()
INTO OUTFILE clause:
sql += ''' INTO OUTFILE %s
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' '''
Pick a place to put the CSV:
csv_path = os.path.join(csv_directory, csv_filename)
Run the query:
from django.db import connection
cursor = connection.cursor()
cursor.execute(sql, params + (csv_path,))
Send the file (using
StreamingHttpResponse which is new in Django 1.5):
from django.http import StreamingHttpResponse
response = StreamingHttpResponse(open(csv_path), content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename=' + csv_filename
In my test cases (about a million records) this is around ten times as fast as processing the data through
csv.writer in Python.
But there are several problems, mostly related to security:
This depends on the MySQL server being on the same machine as the Django web server. You can imagine working around this by some kind of reverse proxying but it all gets rather complicated.
FILE access to your MySQL user is dangerous because it means that a SQL injection attacker can read and write files on your disk. You might want to create a separate MySQL user just to generate these CSV files, and you'll definitely want to set MySQL's
Even if the attacker can't inject SQL, they may be able to cause your disk to fill up with these temporary files. You need a plan for how they are going to be deleted. (Maybe using the
You need to find somewhere safe to put them (a directory to which the MySQL database user has write access).
I did some more timing experiments today, trying out five different ways of generating and downloading the CSV, summarized in the table below:
| | through Django |Direct |
| |Streaming|Not streaming|from DB|
|ORM | 630| 370| N/A|
|No ORM| 190| 125| 58|
Notes on the table:
- Times are in seconds.
- This is for a query with a million rows that generates 150 MiB of CSV.
- "ORM" means that I passed the query results through Django's object-relational mapping system before generating the CSV.
- "No ORM" means that I avoided this by running a custom SQL query.
- "Streaming" means that I used a
StreamingHttpResponse and generated the CSV one line at a time using a technique I'll describe below.
- "Not streaming" means that I used the technique described in the Django documentation to generate the CSV. (This involves reading the entire CSV output into memory, which is a bit painful for other processes running on the server.)
- "Direct" is the technique described in section 2 above, using MySQL's
INTO OUTFILE clause.
Clearly Django's ORM and Python's
csv.writer are both significant bottlenecks. If only the
INTO OUTFILE approach weren't so painful!
So I would still be interested to see other suggestions for speeding up this operation.
5. Appendix: streaming CSV download
csv module doesn't provide an easy way to generate one record at a time, but you can coerce it into doing so like this, using
io.BytesIO to capture the output:
from io import BytesIO
"""Format row in CSV format and return it as a byte string."""
bio = BytesIO()
csv.writer(bio).writerow([unicode(r).encode('utf-8') for r in row])