Weird problem with executing from APScheduler... wont load pymssql

[Bill Dailey]

Not getting much attention on stack overflow so I thought I would post here..... http://stackoverflow.com/questions/17024583/python-apscheduler-error-calling-script I dont want to regenerate it all here...but the basics are... I have a script that runs fine from the command line but when called from APScheduler it gives me ERROR:apscheduler.scheduler:Job "grab_pi_today (trigger: cron[month='*', day_of_week='mon-fri', hour='7', minute='42'], next run at: 2013-06-11 07:42:00)" raised an exception Traceback (most recent call last): File "/usr/local/lib/python2.7/dist-packages/apscheduler/scheduler.py", line 510, in _run_job retval = job.func(*job.args, **job.kwargs) File "./scheduler.py", line 19, in grab_pi_today execfile("./getandstore.py") File "./getandstore.py", line 110, in <module> getandstore(query[key],pcp,date) File "./getandstore.py", line 38, in getandstore conn = pymssql.connect(host='iccsql', user='wdailey', password='xxxxxxxx', \ NameError: global name 'pymssql' is not defined this is my scheduler #!/usr/bin/env python # script scheduler from apscheduler.scheduler import Scheduler import time import datetime import logging from subprocess import call logging.basicConfig() # start scheduler sched = Scheduler() sched.start() # define jobs def grab_pi_today(): execfile("./getandstore.py") # schedules sched.add_cron_job(grab_pi_today,month = '*',day_of_week='mon-fri', hour='07', minute='42') while True: pass What am i doing wrong here? Is there something that is blocking loading of the pymssql module? Bill

[Alex Grönholm]

It seems like your "getandstore.py" has an error -- you are attempting to invoke pymssql.connect() without having imported pymssql.

Bill

--
You received this message because you are subscribed to the Google Groups "APScheduler" group.
To unsubscribe from this group and stop receiving emails from it, send an email to apscheduler...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Alex Grönholm]

10.06.2013 19:54, Bill Dailey kirjoitti:

I'd also like to point out two other things:
1. Busy-waiting with a while: True loop is bad (it uses up lots of CPU cycles for no reason) -- use the standalone mode instead (assuming APScheduler 2.1.0)
2. It'd usually be preferable to either import your module (getandstore.py) instead, or use the subprocess module to execute it. The execfile() call executes the code in-place.


[Bill Dailey]

That's what it appears but pymssql is imported in the. 4th line...it is called in a function. I ended up putting the import inside the function and it threw an error that it couldn't find MySQLdb which was also imported at the beginning of the script. Like I said the script runs fine if called from the command line. If I import the modules within the function everything works fine.

I have seen. This behavior before. I think I am just going to do imports from within the function from here on out.

Thanks,

Doc

[Bill Dailey]

Thanks agin...

1. Will read up on that and implement.
2. Yes, I think it is odd to use exec. I am only less than a year into python so there are quite a few things I don't get. One is executing a complete script within a script. I can execute a function from another script (obviously) but haven't quite figured out how to execute the whole script. I suspect it is that "if __main__" stuff making the main part of the script into a callable function...will work on it. Thus far my elementary approach has gotten the job done....although I am sure their are downsides so will try to figure that out.

Appreciate your help.

Doc

[Alex Grönholm]

Simply import the module or a function from the module and schedule that
instead. No execfile() required.

[Bill Dailey]

ok on 2... i did that this way:

#testscript.py

def calledscript():
    print 'called part'


def main():
    print 'main part'
    calledscript()
   

if __name__ == "__main__":
    main()

then from scheduler:

from testscript import main as testscript

then put "testscript" in my scheduler line.

That works but I think I am missing something on the way I am importing it (seems clunky) but if i import it directly I have to call testscript.main() which sucks but might be the right way to do it.

I am not grasping how to do the APScheduler standalone.  I will have to play around.  It seems when I have tried this in the past and I run scheduler it drops straight through the program and terminates but I may have thought it wasnt running but it was.  that was a couple months ago.  It seems like I wrote a simple start script and a stop script. 

Insight is appreciated.

doc

[Alex Grönholm]

11.06.2013 14:11, Bill Dailey kirjoitti:

ok on 2... i did that this way:

#testscript.py

def calledscript():
    print 'called part'


def main():
    print 'main part'
    calledscript()
   

if __name__ == "__main__":
    main()

then from scheduler:

from testscript import main as testscript

then put "testscript" in my scheduler line.

That works but I think I am missing something on the way I am importing it (seems clunky) but if i import it directly I have to call testscript.main() which sucks but might be the right way to do it.

I am not grasping how to do the APScheduler standalone.  I will have to play around.  It seems when I have tried this in the past and I run scheduler it drops straight through the program and terminates but I may have thought it wasnt running but it was.  that was a couple months ago.  It seems like I wrote a simple start script and a stop script. 

Insight is appreciated.

It would help if you pasted the script you're calling with execfile().

[Bill Dailey]

I thought about that shoulda done it... here you go.. appreciate the insight.

#!/usr/bin/env python
# this script loads sql code (load sql) sequentially,takes stock sql parses it (parsesql), replacing  specified items
# with variables,l runs the query and stores the result.
import glob
import pymssql
import datetime
import MySQLdb
import sys




def loadsql(sqlname):
    #remove directory structure and file extension
    query={}
    for file in sqlname:
        qname = file[2:-4] 
        #print qname
        rlinestring = open(file, 'r').read()
        query[qname] = rlinestring
       
    return query
       
   
   
def parsesql(sql):
    sql = sql.replace('233', '{0}')
    sql = sql.replace('getdate()', """'{1}'""")
    sql = sql.replace('GETDATE()', """'{1}'""")
    sql = sql.replace('GetDate()', """'{1}'""")
    return sql


def getandstore(sql,pcp,date):
   
    conn = pymssql.connect(host='iccsql', user='wdailey', password='xxxxxxx', \
            database='icchartprod')   
    cur = conn.cursor()   
   
    sql = sql.format(pcp, date)
    #print sql
    cur.execute(sql) # execute the query
    results = cur.fetchone() # grab the result
    conn.close()
    # grab the field names so we can build a dictionary with the names and values
    field_names = [i[0] for i in cur.description]    
    count = len(field_names)
    dict_results = dict(zip(field_names, results))
    # open database and insert data
    db = MySQLdb.connect(host='localhost', port = 3306, user='willy', passwd='xxxxxxxx', db='performance')
    cursor = db.cursor()
   
    # very important... if there is no existing record then it creates one for this pcp and date
    sql = """Select * from performance where date = '{1}' and pcp = '{0}';"""
    sql = sql.format(pcp, date)
    cursor.execute(sql)
    isthere = cursor.fetchone()
    if not isthere:
        sql = """INSERT INTO performance( pcp, date) VALUEs ( '{0}','{1}' );"""
        sql = sql.format(pcp, date)
        cursor.execute(sql)
    if count > 1:
        for field in field_names:
            sql = """UPDATE performance SET {0} = '{1}' WHERE pcp  = '{2}' and date = '{3}';"""
            sql = sql.format(field, dict_results[field],pcp, date)
            print field,'-->', dict_results[field]
            #print sql
            cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()       
       
    else:
        sql = """UPDATE performance SET {0} = '{1}' WHERE pcp  = '{2}' and date = '{3}';"""
        sql = sql.format(field_names[0], dict_results[field_names[0]],pcp, date)
        print field_names[0],'-->', dict_results[field_names[0]]
        #print sql
        cursor.execute(sql)
        db.commit()
        cursor.close()
        db.close()       
       
    return

def main():

    now = datetime.datetime.now()
    date = now.date()

    #date = '2013-04-30'
    print date
    # get a list of sql files in working directory
    files = glob.glob('/home/bill/unity/*.sql')
    #print files

    # Load sql files and assign them to variable name from filename
    query = loadsql(files)

    # create dictionary for sql...parse it (233=%s) then update dictionary with code that is usable.
    for key in query.keys():
        newsql=parsesql(query[key])
        query[key]=newsql

    # run queries and store data in MySQLdb
    pcps  = [201,213,209,216,218,219,223,226,228,229,230,233,234,240,241,247]
    for pcp in pcps:
        print "PCP:", pcp
        for key in query.keys():
            getandstore(query[key],pcp,date)
            print key, 'complete.'
        print '\n'

if __name__ == "__main__":
    main()

 

[Alex Grönholm]

12.06.2013 13:12, Bill Dailey kirjoitti:

I thought about that shoulda done it... here you go.. appreciate the insight.

It seems to me that cron would be more appropriate for running this than APScheduler. You seem to be running a UNIX-like OS too.
Try importing your module instead of executing it. Then just call the appropriate function inside it.
I also strongly recommend that you look into using SQLAlchemy too instead of raw SQL.#!/usr/bin/env python

[Bill Dailey]

Will do that.  Have not looked at sql alchemy.

Can do cron.

Sent from mobile

You received this message because you are subscribed to a topic in the Google Groups "APScheduler" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/apscheduler/O9J6nU6r6F4/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to apscheduler...@googlegroups.com.

[Bill Dailey]

SQLALchemy looks a little rought connecting to MS SQL server.. ODBC drivers and on and on..what does this do?  Any reference on al l this?  I am not advanced enough to do a bunch of configuration...sorry for off topic

--
You received this message because you are subscribed to a topic in the Google Groups "APScheduler" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/apscheduler/O9J6nU6r6F4/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to apscheduler...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Doc

Bill Dailey
KXØO
                                


                  

[Alex Grönholm]

12.06.2013 19:15, Bill Dailey kirjoitti:

SQLALchemy looks a little rought connecting to MS SQL server.. ODBC drivers and on and on..what does this do?  Any reference on al l this?  I am not advanced enough to do a bunch of configuration...sorry for off topic

I haven't really used MSSQL on Python myself, but it supports many different drivers, including pymssql: http://docs.sqlalchemy.org/en/rel_0_8/dialects/mssql.html?highlight=mssql#module-sqlalchemy.dialects.mssql.pymssql
So ODBC is an option, but not a requirement.