Python & Sqlite Primer - Exporting to CSV & Mailing a Report ~ coderPro.net Programming & Web Development
You are using an outdated browser. For a faster, safer browsing experience, upgrade for free today.
Download Subscribe

Last week, we were approached to create a Python daemon for one of our clients. Even though no one here has much experience with Python at the moment the folks here at coderPro.net never back down from a challenge. 💪 Being Chief Software Architect the fun rested on my shoulders to come up with a solution. Every time I work with Python I'm always incredibly surprised by both how fast it is in terms of execution speed, but more than that, I love what a simple language it is. After finishing it, I decided it would make a great primer for .Net developers interested in learning more about Python. So, without further adieu:

Monty Python - My brain hurts!

No, not that Python! C'mon... you know I couldn't resist the reference! 😂 I'm going to presume if you're reading this, you're coming from a different background. In the case you're coming from a .Net background, I'm going to first walk you through creating a Python project so that you can debug it. More often than not with Python, you're going to want to work with the native Python environment, so I'll walk you through that too. There is an implementation of Python called IronPython.net that is tightly integrated with .Net that you may want to play around with because it gives you the power of both .Net & Python libraries, but most organizations that use Python would rather use the original

Environment Setup

The first thing that you're going to want to do is make sure you have Python installed for Visual Studio. Open "Visual Studio Installer" and then click the modify button on the version of VS that you want to install, then click the checkbox next to Python development. This will install everything you need to both develop, debug, and run Python apps. 

Now open your preferred version of Visual Studio and create a new "Python Application" project. Name the project whatever you like and click Create. Make sure that you click Python app & not IronPython App, as the code I've written will not debug properly in IronPython. 

Getting Down to Brass Tacks

So, our task was to simply take the data in an SQLite database, export it to CSV, and mail it to HR. We'll start with the basic skeleton: 


from datetime import date
from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

import csv
import sqlite3
import threading 
import email, smtplib, ssl
import datetime
import os

class main:
    def __init__(self):
        super().__init__()

        self.email_server = "smtp.gmail.com"
        self.email_port = 465
        self.email_fromAddress = "coderpros81@gmail.com"
        self.email_toAddress = "coderpros81@gmail.com"
        self.email_password = "xXxXxXxXxXxXxXxX"
        self.email_subject = "Daily Time Report"
        self.attachment_filename = f"DailyTimeReport_{datetime.datetime.now().strftime('%Y%M%d')}.csv"

        self.execution_time = "00:00"
        self.temp_file = "D:\DeleteMe\out.csv"

        monitorThread = threading.Timer(1.0, lambda:self.monitor()).start()
start = main()

As you may have already inferred, the imports are much like using statements in C# & class main is the entry point of the application, much like in a C# app. Below that, I create some global variables and startup the thread that will do all of the work. Now, we need a function to generate the CSV, to send the email with the CSV, and a final function to tie them all together & handle the scheduling.


    # Subroutine responsible for converting data into CSV format.
    def generateCsv(self, fileName):
        connection = sqlite3.connect('employee.db')
        employees = connection.execute("SELECT * FROM employe;")
        
        with open(fileName, "w", newline='') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow([i[0] for i in employees.description]) # Write header
            csv_writer.writerows(employees)

        employees.close()
        connection.close()

Without much explaining, you more than likely can already see here that we open a connection to the database, open a temporary file for writing, and even writes every row from the database to the temporary CSV file with a single line of code. You would want to take a different approach with a large database though, this isn't the most efficient way of handling large amounts of data.


    # Subroutine responsible for sending the email.
    def send(self, fileName, attatchmentFileName):

        body = 'Attached, you will find the hours for ' + date.today().strftime('%m/%d/%Y')

        message = MIMEMultipart()

        message["From"] = self.email_fromAddress
        message["To"] = self.email_toAddress
        message["Subject"] = self.email_subject
        
        message.attach(MIMEText(body, "plain"))
               
        # Open file in binary mode.
        with open(fileName, "rb") as attachment:
            # Add file as application/octet-stream
            # Email client can usually download this automatically as attachment
            part = MIMEBase("application", "octet-stream")
            part.set_payload(attachment.read())

        encoders.encode_base64(part)

        # Add header as key/value pair to attachment part
        part.add_header(
            "Content-Disposition",
            f"attachment; filename= {attatchmentFileName}",
        )

        message.attach(part)
        text = message.as_string()

        with smtplib.SMTP_SSL(self.email_server, self.email_port) as server:
            server.login(self.email_fromAddress, self.email_password)
            server.sendmail(self.email_fromAddress, self.email_toAddress, text)

In this function, we create a message, attach the CSV file to the message, and use smtpLib to send the message via Gmail. Getting the SSL working correctly with this was a bit tricky, but that is mostly because of Gmail's security. 


    def monitor(self):
        if datetime.datetime.now().strftime("%H:%M") == self.execution_time:
            if os.path.exists(self.temp_file):
                self.generateCsv(self.temp_file)
                self.send(self.temp_file, self.attachment_filename)

                # All done, so delete the file.
                os.remove(self.temp_file)
            else:
                raise FileNotFoundError(f'Your temp file ({self.temp_file}) does not exist.')

        monitorThread = threading.Timer(60.0, lambda:self.monitor()).start()

This is the tie that binds. This function runs every 60 seconds, checks the time & if it is execution time, then it generates the CSV, mails the CSV, deletes the file, & lastly resets the timer to run again in another 60 seconds.

If you have any questions or need any help with your programming project, please drop me a line anytime, by using our contact form or even schedule a consultation with me here. That's it! Easy peasy, right? You can find the full source code for this and other projects on Github. As always, until next time: Happy Coding!

Happy coding!