python_study_note

# excel_util.py
import xlwt


def set_style(name, height, bold=False):
    style = xlwt.XFStyle()
    font = xlwt.Font()
    font.name = name
    font.bold = bold
    font.colour_index = 4
    font.height = height
    style.font = font
    return style


def write(data, filename='1.xls'):
    # print(data)
    f = xlwt.Workbook()
    sheet1 = f.add_sheet('sheet1', cell_overwrite_ok=True)

    r = c = 0
    for it in data:
        for it2 in it:
            sheet1.write(r, c, it2)
            c += 1
        c = 0
        r += 1
    f.save(filename)
    print(f'\n{filename} is written to disk')


# def write():
#     f = xlwt.Workbook()
#     sheet1 = f.add_sheet('student', cell_overwrite_ok=True)
#     row0 = ['name', 'age', 'birth', 'hobby']
#     col0 =  ['zhangsan', 'lisi', 'python', 'xiaoming', 'xiaohong', 'na']
#     # write first row
#     for i in range(0, len(row0)):
#         sheet1.write(0, i+1, row0[i], set_style('Times New Roman', 220, True))
#     # write first column
#     for i in range(0, len(col0)):
#         sheet1.write(i+1, 0, col0[i], set_style('Times New Roman', 220, True))
#
#     sheet1.write(1, 2, '2000/12/12')
#     sheet1.write(5, 5, 1, 3, 'unknown') # merge cells of row
#     sheet1.write(1, 2, 3, 3, 'play game') # merge cells of column
#     sheet1.write(4, 5, 3, 3, 'play basketball')
#
#     f.save('.\\out\\text.xls')


if __name__ == '__main__':
    write()

query_db.py

#!/usr/bin/env python3

import sys
import cx_Oracle
import excelutil
import send_email_attach


def get_conn():
    db_url = 'xxxxx'
    return cx_Oracle.connect(db_url)


def query(cnt):
    conn = get_conn()
    cr = conn.cursor()
    sql = 'xxxxxxx'
    cr.execute(sql)
    rs = cr.fetchall()

    arr = []
    r_cnt = 0
    for r in rs:
        print(f'r_cnt={r_cnt}')
        arr.append([])
        for c in r:
            arr[r_cnt].append(str(c))
        r_cnt += 1
    cr.close()
    return arr


if __name__ == '__main__':
    print(f'len of sys.argv: {len(sys.argv)}\n')
    if len(sys.argv) > 2:
        result = query(sys.argv[1])
        excelutil.write(result, sys.argv[2])
        send_email_attach.send(sys.argv[2])
    else:
        print('Input count, filename please!!!')

send_email.py

#!/usr/bin/env python3

import smtplib
from email.mime.text import MIMEText
from email.header import Header

mail_host = '1.1.1.1'
sender = 'ray.sun@163.com'
receivers = ['ray.sun@163.comm']

message = MIMEText('Python 邮件发送测试...', 'plain', 'utf-8')
message['Subject'] = Header('Python 邮件发送测试...', 'utf-8')
message['From'] = sender
message['To'] = receivers[0]


def send(data, subject='test email', recipients=receivers):
    global message
    try:
        smtpObj = smtplib.SMTP(mail_host, 25)
        smtpObj.ehlo()
        smtpObj.starttls()
        # smtpObj.login(mail_user, mail_pass)
        if data:
            message = MIMEText(data, 'plain', 'utf-8')
            message['From'] = sender
            message['To'] = recipients[0]
            message['Subject'] = subject
        smtpObj.sendmail(sender, recipients, message.as_string())
        smtpObj.quit()
        print('success')
    except smtplib.SMTPException as e:
        print('error', e)


if __name__ == '__main__':
    send()
    # check server
    # s = smtplib.SMTP(mail_host, 25)
    # result = s.ehlo()
    # print(result)
    # result = s.starttls()
    # print(result)

send_email_attach.py

#!/usr/bin/env python3

import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
from email.header import Header

mail_host = '1.1.1.1'
sender = 'ray.sun@163.com'
receivers = ['ray.sun@163.com']


def send(filename):
    try:
        smtpObj = smtplib.SMTP(mail_host, 25)
        smtpObj.ehlo()
        smtpObj.starttls()
        if filename:
            excelFile = MIMEText(open(filename, 'rb').read(), 'base64', 'utf-8')
            excelFile['Content-Type'] = 'application/octet-stream'
            excelFile['Content-Disposition'] = 'attachment;filename=' + filename
            message = MIMEMultipart()
            message.attach(excelFile)
            message['From'] = sender
            message['To'] = receivers[0]
            message['Subject'] = 'excel file'
        smtpObj.sendmail(sender, receivers, message.as_string())
        smtpObj.quit()
        print('success')
    except smtplib.SMTPException as e:
        print('error', e)


if __name__ == '__main__':
    send()

words2.py

#!/usr/bin/env python3

"""Retrieve and print words from a URL

Usage:

    python3 words.py <URL>
"""

import sys
import re
from urllib.request import urlopen


def fetch_words(url):
    """Fetch a list of words from a URL.

    :param url: The URL of a UTF-8 text document.
    :return:
        A list of strings containing the words from
        the document.
    """
    story = urlopen(url)
    story_words = []
    for line in story:
        line_words = line.decode('utf-8').split()
        for word in line_words:
            print(word)
            found = re.findall('href="(.*?)"', word)
            # if len(found):
            #     print(found)
            story_words.append(found)
    story.close()
    return story_words


def print_words(items):
    """Print items one per line.

    :param items:
        An iterable series of printable items.
    """
    for item in items:
        print(item)


def main(url):
    """Print each word from a text document from a URL.

    :param url: The URL of a UTF-8 text document.
    """
    words = fetch_words(url)
    # print_words(words)


if __name__ == '__main__':
    main(sys.argv[1])