使用Python和Elixir通过外键的数据库表发送邮件

Question

我有一个Python脚本，它从一个数据库表中提取信息，并每周给用户发送更新邮件。我想找个办法来修改我的代码，把所有的研究人员信息（前缀、名字、姓氏和邮箱）放到一个单独的表里。但是，我不太确定怎么在elixir模型和我的Python代码的结构中做到这一点。

这是我现在的elixir模型文件：

from elixir import *
import auth as auth
au = auth.UserAuth()

metadata.bind = 'mysql://' + au.user + ':' + au.password + '@localhost/' + au.database
metadata.bind.echo = True


class Protocol(Entity):
    id = Field(Integer, primary_key = True)
    irb_no = Field(Text)
    title = Field(Text)
    prefix = Field(Text)
    fname = Field(Text)
    lname = Field(Text)
    email = Field(Text)
    action_date = Field(Date)
    action = Field(Text)
    approved = Field(Integer)
    using_options(tablename = 'protocols')

    def __repr__ (self):
        return '%d' %(self.id)

这是我的Python脚本：

import smtplib
import auth as auth
import ProtocolModel as PM
from elixir import *
from datetime import datetime

au = auth.UserAuth()
setup_all()
create_all()
table = PM.Protocol
records = table.query.all()

if len(records) == 0:
    print 'No records in Table'
else:
    for record in records:
        setup_all()
        if record.approved == 1:
            print 'Completed'
        else:
            FROMADDR = au.google_user
            LOGIN    = FROMADDR
            PASSWORD = au.google_password
            TOADDRS  = record.email
            SUBJECT  = "Weekly Research Update for " + record.irb_no + " - " + record.title
            date1 = datetime.strptime(str(record.action_date), '%Y-%m-%d')
            date2 = date1.strftime('%B %d, %Y')
            msg = ("From: %s\r\nTo: %s\r\nSubject: %s\r\n\r\n" % (FROMADDR, ", ".join(TOADDRS), SUBJECT) )
            msg += 'Dear' + ' ' + record.prefix + ' ' + record.fname + ' ' + record.lname + ',' + '\n' + '\n'
            msg += 'Our records indicate that the most recent action on your research protocol titled ' + record.title + ' was taken on ' + str(date2) +'.' +'\n'
            msg += 'This action was: ' + record.action

            server = smtplib.SMTP('smtp.gmail.com', 587)
            server.set_debuglevel(1)
            server.ehlo()
            server.starttls()
            server.login(LOGIN, PASSWORD)
            server.sendmail(FROMADDR, TOADDRS, msg)
            server.quit()

这看起来应该是一个相对简单的修改，但我就是不知道该怎么做。任何帮助都非常感谢，谢谢！

Answer 1

我搞明白了。其实在模型文件的新实体类里用了ManyToOne，然后在Python代码里引用了它。重新回到这个问题后，发现其实很简单就能解决。

新的elixir模型：

from elixir import *
import auth as auth
au = auth.UserAuth()

metadata.bind = 'mysql://' + au.user + ':' + au.password + '@localhost/' + au.database
metadata.bind.echo = True

class Investigator(Entity):
    id = Field(Integer, primary_key = True)
    prefix = Field(Text)
    fname = Field(Text)
    lname = Field(Text)
    email = Field(Text)
    using_options(tablename = 'investigators')

    def __repr__ (self):
        return '%d' %(self.id)


class Protocol(Entity):
    id = Field(Integer, primary_key = True)
    irb_no = Field(Text)
    title = Field(Text)
    investigator = ManyToOne('Investigator', colname='investigator_id')
    action_date = Field(Date)
    action = Field(Text)
    approved = Field(Integer)
    using_options(tablename = 'protocols')

    def __repr__ (self):
        return '%d' %(self.id)

新的Python脚本：

import smtplib
import auth as auth
import ProtocolModel1 as PM
from elixir import *
from datetime import datetime

au = auth.UserAuth()
setup_all()
create_all()
table = PM.Protocol
records = table.query.all()

if len(records) == 0:
    print 'No records in Table'
else:
    for record in records:
        setup_all()
        if record.approved == 1:
            print 'Completed'
        else:
            FROMADDR = au.google_user
            LOGIN    = FROMADDR
            PASSWORD = au.google_password
            TOADDRS  = record.investigator.email
            SUBJECT  = "Weekly Research Update for " + record.irb_no + " - " + record.title
            date1 = datetime.strptime(str(record.action_date), '%Y-%m-%d')
            date2 = date1.strftime('%B %d, %Y')
            msg = ("From: %s\r\nTo: %s\r\nSubject: %s\r\n\r\n" % (FROMADDR, ", ".join(TOADDRS), SUBJECT) )
            msg += 'Dear' + ' ' + record.investigator.prefix + ' ' + record.investigator.fname + ' ' + record.investigator.lname + ',' + '\n' + '\n'
            msg += 'Our records indicate that the most recent action on your research protocol titled ' + record.title + ' was taken on ' + str(date2) +'.' +'\n'
            msg += 'This action was: ' + record.action

            server = smtplib.SMTP('smtp.gmail.com', 587)
            server.set_debuglevel(1)
            server.ehlo()
            server.starttls()
            server.login(LOGIN, PASSWORD)
            server.sendmail(FROMADDR, TOADDRS, msg)
            server.quit()