I have a working Pyramid web app, based on the SQLAlchemy scaffold. Within my application, I have a function that sends emails and inserts/updates tables in my database via SQLAlchemy. On the web, I call this function via a view, i.e., a button on the web submits to a view and within the view it calls the function.
I would like to create a console script that calls this same function, but on a scheduled basis. I'm working through the sample documentation for setting up a Pyramid console script. In a perfect world I want to be able to access all of my models and functions that I'm using in my web app, but be able to use them from the console. Through trial and error I've managed to include some basics to get something working, in that I'm able to query one of my model objects and print it to the console. I'm even able to call the function that I want.
However, inside the function it writes a row to the database and sends out an email. When I call the function from the console, it does all the work (at least printed to the console) and sends the emails. It prints 'INSERT' statements where it should. But it isn't actually either executing the INSERTs or committing them, I'm not sure which. I'm importing the DBSession from my models.py package that the rest of the pyramid app uses, but is there a trick or something I need to know? I tried declaring a new DBSession and creating it custom to the console script but that threw some sort of "can't find a mapper" error.
In the example below, SendEmail gets called for each record; the function itself basically looks up the corresponding record, inserts a row back into the database for another model object, then sends the email. It works great as part of the web app. On the console side here, it prints out that it's doing everything it should be doing and sends the emails, but the database record isn't actually inserted.
Here's my console script:
# describe the script here
import datetime
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import logging
import optparse
import smtplib
from smtplib import SMTPException
import sys
import textwrap
import pyramid.paster
from pyramid.paster import bootstrap
from pyramid.request import Request
from sqlalchemy.exc import DBAPIError
from sqlalchemy import (
or_,
and_,
not_,
asc,
desc,
func
)
from functions import SendEmail
from models import DBSession, LogSession, groupfinder
from models import MyObject
from pyramid.session import UnencryptedCookieSessionFactoryConfig
my_session_factory = UnencryptedCookieSessionFactoryConfig('itsaseekreet')
from pyramid.config import Configurator
from sqlalchemy import engine_from_config
from pyramid.authentication import AuthTktAuthenticationPolicy
from pyramid.authorization import ACLAuthorizationPolicy
from zope.sqlalchemy import ZopeTransactionExtension
def main():
description = """\
Print the deployment settings for a Pyramid application. Example:
'show_settings deployment.ini'
"""
usage = "usage: %prog config_uri"
parser = optparse.OptionParser(
usage=usage,
description=textwrap.dedent(description)
)
parser.add_option(
'-o', '--omit',
dest='omit',
metavar='PREFIX',
type='string',
action='append',
help=("Omit settings which start with PREFIX (you can use this "
"option multiple times)")
)
options, args = parser.parse_args(sys.argv[1:])
if not len(args) >= 1:
print('You must provide at least one argument')
return 2
config_uri = args[0]
omit = options.omit
if omit is None:
omit = []
request = Request.blank('/', base_url='http://localhost:13715/')
env = bootstrap(config_uri, request=request)
settings = env['registry'].settings
pyramid.paster.setup_logging(config_uri)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
LogSession.configure(bind=engine)
authn_policy = AuthTktAuthenticationPolicy(
'itsaseekreet', callback=groupfinder)
authz_policy = ACLAuthorizationPolicy()
config = Configurator(settings=settings,
root_factory='myapp.models.RootFactory',
session_factory=my_session_factory)
config.set_authentication_policy(authn_policy)
config.set_authorization_policy(authz_policy)
config.add_static_view('static', 'static', cache_max_age=3600)
log = logging.getLogger(__name__)
log.info('Starting EmailSender...')
init_time = datetime.datetime.utcnow()
log.info('Current datetime (UTC): {0}'.format(str(init_time)))
items_to_process = DBSession.query(MyObject). \
filter(and_(MyObject.startdate <= init_time,
MyObject.enddate >= init_time,
MyObject.manual_send_only == False)).all()
for item in items_to_process:
log.info('{0}: runtime: {1}'.format(item.description, item.send_time))
item_url = request.route_url('itemresponse', responseid='XXXXXX')
rtn = SendEmail(item.id, item_url)
env['closer']()
if __name__ == '__main__':
main()
Also, another thing I'm running into but not so important right now: I have logging handlers where my log.blah goes to the database (using the LogSession I've created). This also works fine from the web app, but is not writing to the database when I run it. I don't know if it's the same issue, or if in my config the handlers aren't set up right for console or something. I dunno, but the main issue above is what I'm looking for. Thanks!
EDIT:
I poked at it some more and found the tutorial talking about SQLAlchemy setup and was looking at the initializedb.py script, since that modifies the database like I want and hooks up to the models, too. I did import transaction and wrapping the above with
with transaction.manager:
items_to_process = DBSession.query(MyObject). \
filter(and_(MyObject.startdate <= init_time,
MyObject.enddate >= init_time,
MyObject.manual_send_only == False)).all()
for item in items_to_process:
log.info('{0}: runtime: {1}'.format(item.description, item.send_time))
item_url = request.route_url('itemresponse', responseid='XXXXXX')
rtn = SendEmail(item.id, item_url)
This seems to do exactly what I want, or at least actual does commit and write to the database. I'm going to have to work with it some more since I'm not exactly sure what happens if there's a DB error within the called function, if it rolls the whole thing back, if it commits part of it, or what. Within the function itself there is exception handling and and cleanup but I think it normally depends on the pyramid_tm and Zope to handle the behind the scenes stuff.
transactionpackage documentaion. - zaquest