Nasty Icky code that I’ve combined from a few things I found on the web.
# import MySQL module and the sendEmail class which can be found here
import MySQLdb
import sendEmail
# connection string to connect to the mysql database, the * are the password obviously
db = MySQLdb.connect(
host=”localhost”,
user=”db_user_name”,
passwd=”****”,
db=”db_name”)
# create a database cursor
cursor = db.cursor()
# pass SQL select statement to the cursor which will run it against the database and grab the result set
cursor.execute(“SELECT c.comment_author, c.comment_author_email, c.comment_author_ip FROM wp_comments c”)
# get and display one row at a time
rows = cursor.fetchall() #store everything in a variable named rows
cursor.close #close the cursor
db.close() #close the connection to the database so you don’t leave a perpetually hanging db thread
#Open a file to write to, in this case a file with an .xls extension
f = open(‘output.xls’,'w’)
towrite = ”” # variable to store what will eventually be written to the file
#iterate through the result set adding a tab after each piece to denote a column change, and then store each row in towrite
for row in rows:
data = ” ”
for dataset in row:
data = data + dataset + “ ”
towrite = towrite + data + “\n”
# at this point towrite should contain the entire result set with tabs to denote column change, or at least this is how excel will interpret it
print >>f, towrite
f.close() #If you don’t close the file before executing the email part below, it will send an empty file, as it hasn’t been written to yet
#create a new variable, ‘email’, pass to the sendMail class all of the information necessary for an email
message = sendEmail.sendEmail(msgTo=’someaddress@somedomain.com’, msgFrom=’someaddress@somedomain.com’, msgSubject=’TestingPythonScript’, msgBody=’Bodyofemail’, attachmentPaths=['/filepath/output.xls'])
message.send() #send email
Done. It is a very poor script but it gets the job done. Next up is to use XLWT, so that the Excel output is formatted correctly.
Then break all of this up into better code, and classes maybe. Not sure I’ll make it that far since this was just for fun.
