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.

"I realise that it is inefficient to store the CSV file on disk", I doubt that this will ever be a bottleneck. I doubt it very much. — Kijewski
– Kijewski, Commented Aug 1, 2014 at 23:14
True, and the output of the query is pretty straightforward, but it just seems wrong if it's unnecessary. Would far rather it all happen in memory — btongeorge
– btongeorge, Commented Aug 1, 2014 at 23:15

Kijewski · Accepted Answer · 2014-08-02 00:00:23Z

2

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())

edited Aug 2, 2014 at 0:00

Kijewski

26.1k14 gold badges108 silver badges149 bronze badges

answered Aug 1, 2014 at 23:39

Colin Phipps

9185 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kijewski · Accepted Answer · 2014-08-01 23:21:48Z

1

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'

answered Aug 1, 2014 at 23:21

Kijewski

26.1k14 gold badges108 silver badges149 bronze badges

2 Comments

btongeorge Over a year ago

Great thanks, that looks like the kind of thing! So if I want to refer to the file when I attach it to the MIME email, how would I do so?

Kijewski Over a year ago

You just need to use part.set_payload(f.getvalue()).

Collectives™ on Stack Overflow

Python MySQL query to CSV in memory

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related