Untitled

import csv
import smtplib
import mimetypes
import datetime
import MySQLdb
from email.mime.multipart import MIMEMultipart
from email import encoders
from email.message import Message
from email.mime.audio import MIMEAudio
from email.mime.base import MIMEBase
from email.mime.image import MIMEImage
from email.mime.text import MIMEText
#l=raw_input("Dateiname(mit Endung): ")
f = open("foo.csv")
csv_f = csv.reader(f,delimiter=';')
kunden_emails = []
best_nr = []
vorname= []
nachname= []

for row in csv_f:
	best_nr.append(row[0])
	kunden_emails.append(row[1])
	vorname.append(row[2])
	nachname.append(row[3])


n = len(best_nr) - 1
i = 1
#connect to db
db = MySQLdb.connect(host="192.168.111.1",user="mails",passwd="sa83j2nNQXVWvTVL",db="mailversand")
#setup cursor
cur = db.cursor()
#emailfrom = raw_input("Absenderadresse eingeben: ")
#username = emailfrom
#password = raw_input("Passwort: ")
while i <= n:
	emailfrom = "jung@wunschreich.de"
	emailto = kunden_emails[i]
	fileToSend = best_nr[i] + ".pdf"
	username = "jung@wunschreich.de"
	password = "9Cdd9pTx"

	msg = MIMEMultipart()
	msg["From"] = emailfrom
	msg["To"] = emailto
	msg["Subject"] = "Ihre Rechnung mit der Rechnungsnummer: " + best_nr[i]
	msg.preamble = "Ihre Rechnung mit der Rechnungsnummer: " + best_nr[i]
	html = """\
	<html>
	<head></head>
	<body>
		<p>Sehr geehrter Kunde,<br>
	im Anhang finden sie die Rechnung ihrer Bestellung.<br>
		</p>
	</body>
	</html>
	"""
	part1 = MIMEText(html, 'html')

	msg.attach(part1)

	ctype, encoding = mimetypes.guess_type(fileToSend)
	if ctype is None or encoding is not None:
		ctype = "application/octet-stream"

	maintype, subtype = ctype.split("/", 1)

	if maintype == "text":
		fp = open(fileToSend)

		attachment = MIMEText(fp.read(), _subtype=subtype)
		fp.close()
	elif maintype == "image":
		fp = open(fileToSend, "rb")
		attachment = MIMEImage(fp.read(), _subtype=subtype)
		fp.close()
	elif maintype == "audio":
		fp = open(fileToSend, "rb")
		attachment = MIMEAudio(fp.read(), _subtype=subtype)
		fp.close()
	else:
		fp = open(fileToSend, "rb")
		attachment = MIMEBase(maintype, subtype)
		attachment.set_payload(fp.read())
		fp.close()
		encoders.encode_base64(attachment)
		attachment.add_header("Content-Disposition", "attachment", filename=fileToSend)
		msg.attach(attachment)

	server = smtplib.SMTP("smtp.1und1.de:587")
	server.starttls()
	server.login(username,password)
	server.sendmail(emailfrom, emailto, msg.as_string())
	server.quit()
	print kunden_emails [i]
	print best_nr [i]
	ts = datetime.datetime.now()
	ts.isoformat()
	#cur.execute("""insert into Mailversand values (%s,%s,%s)""", (kunden_emails[i],best_nr[i],ts))
	#INSERT INTO `mailversand` (`id`, `firstname`, `lastname`, `email`, `invoiceId`, `timestamp`, `processId`) VALUES (NULL, 'Max', 'Mustermann', 'max@mustermann.de', '', '2016-05-31', NULL);
	#INSERT INTO mailversand (firstname, lastname, email, invoiceId, timestamp) VALUES ("max", "Mustermann", "max@mustermann.de", "123123", "234324")
	try:
		cur.execute("""INSERT INTO mailversand (firstname, lastname, email, invoiceId, timestamp) VALUES (%s, %s, %s, %s, %s)""",(vorname[i], nachname[i],kunden_emails[i],best_nr[i], ts))
		#cur.commit()
	except:
		print "FEHLER!!!!!!!!!!!!!!!!"

	i = i+1

cur.execute(""""SELECT * FROM mailversand;""")
print cur.fetchall()

cur.close()
db.close()