Automating the Boring Stuff

PyEugene

Thanks to IDX Broker for Hosting

Files are located here: https://github.com/sdudenhofer/

PyEugene-automate

What Modules will we be using

Pandas

openpyxl

docx

readDocx

os

glob

So to quickly create a word Document

>>> import docx
>>> doc = docx.Document()
>>> doc.add_paragraph('Hello world!')
>>> doc.save('helloworld.docx')

To create multiple paragraphs

>>> import docx
>>> doc = docx.Document()
>>> doc.add_paragraph('Hello world!')
>>> paraObj1 = doc.add_paragraph('This is a second paragraph.')
>>> paraObj2 = doc.add_paragraph('This is a yet another paragraph.')
>>> paraObj1.add_run(' This text is being added to the second paragraph.')
>>> doc.save('multipleParagraphs.docx')

For More about Creating/Editing/Reading Word docs

https://automatetheboringstuff.com/chapter13/

So for the hospital, everyone loves to get reports in Excel (xlsx) format

Which is great except a lot of reports come from CSV files or databases. If you don't want to have to open Excel and then import the data we can write a simple script to accept input and then parse the data.

The next couple examples are downloaded from Kaggle.com - but are already in the repo.

import pandas as pd

file = input("Enter File Name/Location:")

data = pd.read_csv(file)
output = input("Enter Excel Filename:")
data.to_excel(output, index=False)

If you run this your output should look like this:

Enter File Name/Location:avocado.csv
Enter Excel Filename:avo1.xlsx

Next let's query a sqlite database and return the results in an excel file

import sqlite3
import pandas as pd

conn = sqlite3.connect('database.sqlite')
c = conn.cursor()
print(c)
query = '''select t01.artist, t02.content, t03.genre, t04.label, t05.score, t06.year
from artists t01 left outer join content t02 on t01.reviewid = t02.reviewid
left OUTER JOIN genres t03 on t02.reviewid = t03.reviewid
LEFT OUTER JOIN labels t04 on t03.reviewid = t04.reviewid
left OUTER join reviews t05 on t04.reviewid = t05.reviewid
left outer JOIN years t06 on t05.reviewid = t06.reviewid
order by t06.year ASC;'''

cursor = conn.execute(query)
array = []

for row in cursor:
    array.append(row)

df = pd.DataFrame(array)
save_file = input("Where should we save this file?")
df.to_excel(save_file, index=False)

So if you want to watch a folder to see the newest modified file in the folder

import os
import glob

folder = input("Please enter directory to search:")
path = os.path.join(folder, '*')
files = sorted(glob.iglob(path), key=os.path.getctime, reverse=True)
print(files[0])

To search a file for a phrase and replace it we can do that with a quick little script - you could use this script to search a file for passwords and replace them - then upload to GitHub

file = input("Enter the filename:")
output = input("Enter new filename:")
f = open(file,'r')
filedata = f.read()
f.close()

find = input("Enter phrase to search for:")
replace = input("Enter phrase to replace it with: ")
newdata = filedata.replace(find, replace)

f = open(output,'w')
f.write(newdata)
f.close()

file = open("/private/var/log/wifi.log")
phrase = input("Enter a Search phrase:")

for line in file:
    if phrase in line:
        print(line)
    else:
        continue

so this script looks through the wifi log on this Mac and returns lines that contain the phrase entered.

import os

command = os.popen('ls -alh')

print(command.read())

import smtplib

from email.message import EmailMessage

with open(textfile) as fp:
    msg = EmailMessage()
    msg.set_content(fp.read)


msg['Subject'] = 'Test Email'
msg['From'] = 'test@test.com'
msg['To'] = 'test1@test.com'

s = smtplib.SMTP('localhost')
s.send_message(msg)
s.quit()

More Resources

https://medium.com/@goranaviani/how-to-create-read-update-and-search-through-excel-files-using-python-c70680d811d4

http://automatetheboringstuff.com/chapter12/

https://realpython.com/python-send-email/