postgresql使用sql封装邮箱发送功能

“ postgresql数据库支持python语言，同样可以封装一下python的邮件发送功能。

        本文假设数据库中存有职员的花名册，对花名册进行遍历发送邮件。”

01

—

数据库安装

安装依赖

yum install -y bison flex readline-devel zlib-devel zlib zlib-devel gcc  gcc-c++ openssl-devel python3  python3-devel libicu-devel ncurses-devel sqlite-devel tk-devel gcc make
yum install -y bison flex readline-devel zlib-devel zlib zlib-devel gcc  gcc-c++ openssl-devel python3  python3-devel libicu-devel ncurses-devel sqlite-devel tk-devel gcc make

为postgres授权

useradd postgres 
vim /etc/sudoers

在101行加入以下内容

postgres ALL=(ALL)     NOPASSWD: ALL
postgres ALL=(ALL)     NOPASSWD: ALL

下载postgresql源码包

wget https://ftp.postgresql.org/pub/source/v16.1/postgresql-16.1.tar.gz
wget https://ftp.postgresql.org/pub/source/v16.1/postgresql-16.1.tar.gz

解压压缩包

mv postgresql-16.1.tar.gz /home/postgres
su - postgres 
tar -zxf postgresql-16.1.tar.gz
cd postgresql-16.1

编译python支持还是很重要。--with-python 自行构建plpython3u插件

./configure --prefix=/home/postgres/pg --with-openssl  --with-python
make && make install

编辑环境变量

cd 
vim .bash_profile

加入以下环境变量值

source /etc/profile
export PATH=/home/postgres/pg/bin:$PATH 
export PGDATA=/home/postgres/pg/data

加载环境变量

source ~/.bash_profile
source ~/.bash_profile

初始化数据库

initdb -D $PGDATA -U postgres -W 
(输入超级用户密码两次)
pg_ctl start 
pg_ctl status

启动数据库

pg_ctl start
pg_ctl start

创建拓展

postgres=# create extension plpython3u ;
CREATE EXTENSION

02

—

获取授权码

    编写邮箱代码前，需要知道自己发送邮箱的服务器地址等相关信息。

以QQ邮箱为发件人为例，登录QQ邮箱

进入设置页面，然后开启pop3服务

此时记住你所获得的授权码

作为QQ邮箱相关服务器地址和端口信息

我们使用smtp，所以服务器地址是smtp.qq.com   端口使用465

02

—

编写python进行封装

创建函数，封装具有邮件发送功能的python代码段落

DROP FUNCTION IF EXISTS send_email(m_server varchar(100),port integer , username varchar(100), authcode varchar(100), received varchar(100),content varchar(100));

CREATE OR REPLACE FUNCTION send_email(m_server varchar(100),port integer , username varchar(100), authcode varchar(100), received varchar(100),content varchar(100))
RETURNS varchar(2000)
AS $$

import smtplib
from email.mime.text import MIMEText

#发件箱服务地址
mailserver = m_server

#发件箱服务地址端口
email_port = port

# 发件人
userName_SendMail = username

# 发件人邮箱授权码
userName_AuthCode = authcode

# 收件人
received_mail = received


#邮件正文
emali_content = content

# 纯文本形式的邮件内容的定义，通过MIMEText进行操作，plain为默认的文本的展示形式
email = MIMEText(emali_content, 'plain', 'utf-8')
# 定义邮件主题
email['Subject'] = '你好' 
# 发件人
email['From'] = userName_SendMail
# 收件人
email['To'] = received_mail 


# 链接到右键服务器地址
smtp = smtplib.SMTP_SSL(mailserver, email_port)
smtp.login(userName_SendMail, userName_AuthCode)

# 发送邮件
try:
    smtp.sendmail(userName_SendMail, received_mail, email.as_string())
    smtp.quit()
    return '发送成功'
except Exception as e:
    return str(e)
$$ LANGUAGE plpython3u;

创建一个职员花名册

create table email_text(id serial,name varchar(10),email varchar(100),remark varchar(100));
insert into email_text(name ,email,remark)
values('CM','*******@qq.com','入职成功'),('XHL','*******@qq.com','入职成功'),('WCY','*******@qq.com','入职成功'),('YDH','*******@qq.com','入职成功');

创建一个函数，进行遍历该表花名册信息并执行邮件发送任务

CREATE OR REPLACE FUNCTION for_send_email(m_server varchar(100),port integer , username varchar(100), authcode varchar(100))
RETURNS void
AS $$
DECLARE 
I RECORD;  
return_result VARCHAR(20);
begin
for i in select name,email,remark from email_text loop
select send_email(m_server,port,username,authcode,i.email,i.remark) into return_result;
if return_result='发送成功' 
then
raise notice '%员工已经发送成功',i.name ;
else
raise NOTICE '%员工已经发送失败',i.name ;
end if;
end loop ;
end ;
$$ LANGUAGE plpgsql;

    这里在创建函数的过程中全部使用变量，只在调用函数的过程才会使用实际值，避免数据库明文存储的风险。表中邮件信息的明文存储可以使用权限控制进行规避风险泄露，

select for_send_email('smtp.qq.com','465','填写发件人邮箱','填写发件人的授权码');
select for_send_email('smtp.qq.com','465','填写发件人邮箱','填写发件人的授权码');

此时可以看到邮箱已经发送成功。

在邮件发送中也可以附件功能，将表中相关信息附加到邮件中去。