Python MySQL query to CSV in memory

Question

I have a Python script which queries a DB and then emails the result. At the moment the script looks like this (please excuse my inexperience with Python)

db = mdb.connect(db_host, db_user, db_pass, db_name)
cur = db.cursor()
dbQuery =  ("SELECT A, B, C from table")
cur.execute (dbQuery)

rows = cur.fetchall()
fp = open(filename,'w')
myFile = csv.writer(fp, quotechar='"', quoting=csv.QUOTE_ALL)
myFile.writerows(rows)
fp.close()

msg = MIMEMultipart()
msg['Subject'] = subject 
msg['From'] = email_from
msg['To'] = email_to
body = MIMEMultipart('alternative')
body.attach(MIMEText(content, 'plain' ))
part = MIMEBase('application', "octet-stream")
part.set_payload(open(filename, "rb").read())
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment', filename=filename) 
msg.attach(body)
msg.attach(part)

server = smtplib.SMTP(smtp_server)
server.sendmail(email_from, email_to, msg.as_string())

I realise that it is inefficient to store the CSV file on disk, then close it and re-open to read. How can I do this all in memory? There is no requirement for the CSV to be saved as the data is all in the DB.

Answer 1

Use a SpooledTemporaryFile. This acts like a file, but is held in memory (unless it gets very large at which point it is converted transparently to a temporary file).

fp = tempfile.SpooledTemporaryFile()
myFile = csv.writer(fp, quotechar='"', quoting=csv.QUOTE_ALL)
myFile.writerows(rows)
fp.seek(0)   # rewind the file handle

...
part.set_payload(fp.read())

Answer 2

You can use cStringIO.

# create an in-memory "file":
f = cStringIO.StringIO()

# attach it to a writer:
w = csv.writer(f)

# write something:
w.writerows([[1,2,3],[4,5,6],[7,8,9]])

# read the content:
content = f.getvalue()

'1,2,3\r\n4,5,6\r\n7,8,9\r\n'