Python Extract Database (Postgresql) and send mail

just getting started with Python, it's really a good language to discover.

The business unit requests that a table in a database be sent to the relevant department personnel mailbox regularly.

In fact, the entire business needs are simple, it is not difficult to achieve.

However, as a result of the introduction of Python, so still borrowed from the Internet content, but also got a lot of friends to remind.

The business unit uses the PostgreSQL database, so the PSYCONPG2 module is used.

The entire script is divided into three parts:

1. Database connection and data written to Excel table (the whole for beginners, it should be difficult)

2. Sending a message

3. Generating deletion of Excel files

# Coding:utf-8

Import Sys

Import XLWT

Import PSYCOPG2

Import datetime

Import time

Import Smtplib

From Email.mime.multipart import Mimemultipart

From Email.mime.text import Mimetext

From email.mime.application import mimeapplication

Import Os.path

# parameters for connecting to the database: host, user, password, port, and database name

Host = ' 192.168.1.77 '

user = ' Postgres '

PWD = ' Postgres '

Port = 5432

db = ' Pytest '

Sheet_name = ' report ' + time.strftime ("%y-%m-%d")

filename = ' Report_ ' + time.strftime ("%y-%m-%d" + "-" + "%h%m%s") + '. xls '

Out_path = "D:/test/report_" + time.strftime ("%y-%m-%d" + "-" + "%h%m%s") + ". xls" # The path file name is named with datetime, but the file name does not support colons: So remove the colon

Cur_path = ' D:/test '

Print (Out_path)

sql = ' select * from website; '

def export ():

    # Database Connection

conn = Psycopg2.connect (dbname=db, User=user, Password=pwd, Host=host, Port=port)

cursor = Conn.cursor ()

Cursor.execute (SQL)

result = Cursor.fetchall ()

Count = Cursor.rowcount

Print ("select" + str (count) + "Records")

# cursor.scroll (0, mode= ' relative ')

Fields = cursor.description # the title of the data table

workbook = xlwt. Workbook (encoding= ' Utf-8 ') # Create an Excel document

sheet = workbook.add_sheet (Sheet_name, cell_overwrite_ok=true) # according to Sheet_name Create Excel of the document sheet

For field in range (1, len): # file header to write to data table

Sheet.write (0, field, Fields[field][0])

    # row-by-column add data

For row in range (1, len (result) + 1):

For Col in range (0, len):

Sheet.write (Row, col, U '%s '%result[row-1][col])

Workbook.save (out_path) # Follow Out_path the format and path to save Excel Table

_user = "[Email protected]"

_pwd = "123456."

Areceiver = "[Email protected]"

ACC = "[Email protected]"

# as the name suggests Multipart is more than one part

msg = Mimemultipart ()

msg["Subject"] = U ' [Data select_ ' + time.strftime ("%y-%m-%d") + U '] '

Msg["from"] = _user

Msg["to"] = Areceiver

msg["Cc"] = ACC

Def send_email ():

conn = Psycopg2.connect (dbname=db, User=user, Password=pwd, Host=host, Port=port)

cursor = Conn.cursor ()

Cursor.execute (SQL)

Cursor.fetchall ()

Count = cursor.rowcount # summary rows number

    # ---- This is the text part -----

content = "' Dear all, \ n accessories are daily statistics, please check!

Total result digits:"' + str (count)

Part = Mimetext (content, ' plain ', ' utf-8 ')

Msg.attach (part)

If Count > 0:

        # This is the attachment section .

# xls Type Attachment

file_name = ' d:/test/' + filename

Part = Mimetext (open (file_name, ' RB '). Read (), ' base64 ', ' gb2312 ')

part["Content-type"] = ' application/octet-stream '

basename = Os.path.basename (file_name)

# part["content-disposition"] = ' attachment; filename=%s '% basename.encode (' utf-8 ')

part[' content-disposition '] = ' attachment; filename=%s '% basename

# part.add_header (' content-disposition ', ' attachment ', filename= (' Utf-8 ', basename))

Msg.attach (part)

s = smtplib. SMTP (' mail.ucinbox.com ', timeout=120) # connecting to an SMTP mail server

S.login (_user, _pwd)

S.sendmail (_user, Areceiver.split (', ') + acc.split (', '), Msg.as_string ()) # Send mail

Print ("Email send Successfully")

S.close ()

Else

Print ("Nothing to send!")

# Delete the generated Excel file

# previously used to put different Excel into different folders, so write a traverse to delete all Excel

def delete (path):

ls = os.listdir (cur_path)

For L in LS:

path_file = Os.path.join (path,l) # fetch file path

If Os.path.isfile (path_file):

Os.remove (Path_file)

Else

For f in Os.listdir (Path_file):

Path_file2 = Os.path.join (path_file,f)

If Os.path.isfile (path_file2):

Os.remove (Path_file2)

    # calling Functions

if __name__ = = "__main__":

Export ()

Send_email ()

Delete (Cur_path)

Python Fetch Database (Postgresql) and send mail