def main(): now= datetime.datetime.now() timestamp= now.strftime("%Y%m%d%H%M%S")#getting the timestamp #creating log file logging.basicConfig(filename='db_pass_script.log') logging.info('begin at'+timestamp) try: lite_con,lite_cur=connection_script.connect_to_sqlite()#connect to sqlite3 ms_con, ms_cur=connection_script.connect_to_mssql()#connect to ms sql ssh=connection_script.connect_to_webserver()# connect to unix box total_tries=0 # picking up the newly updated and inserted data from the sqlite3 DB lite_cur.execute("select prod_cd,status from inv_data_bk where status<>'Y' and status<>'V' and status<>'E';") lite_rows=lite_cur.fetchall() for lite_row in lite_rows: pass_prod_cd=lite_row[0] if pass_prod_cd.find('('): pass_prod_cd=pass_prod_cd.replace('(','_-') if pass_prod_cd.find(')'): pass_prod_cd=pass_prod_cd.replace(')','-_') pass_prod_cd=str(pass_prod_cd) # get the data from MS SQL ms_qry="SELECT i.prod_cd prod_cd,\ i.descrip,\ i.image_nm pics,\ i.class_cd class_cd,\ i.sales_cost sales_cost, \ d.in_stock in_stock,\ d.order_qty order_qty\ FROM omsdata.dbo.inv i,\ omsdata.dbo.inv_data d\ WHERE d.prod_cd=i.prod_cd\ AND i.prod_cd='%s';"%(lite_row[0]) ms_cur.execute(ms_qry) ms_row=ms_cur.fetchone()#fetch the row row_status=lite_row[1]#variable to identify if the row is newly inserted or updated cmd='python2.7 '+ settings_script.remote_script i=0# a temporary counter #print row_status, 'row status' if row_status=='D': cmd,i=add_to_cmd(cmd,i,'program_mode','delete') cmd,i=add_to_cmd(cmd,i,'prod_cd',pass_prod_cd) else: if row_status=='N': cmd,i=add_to_cmd(cmd,i,'program_mode','insert') elif row_status=='U': cmd,i=add_to_cmd(cmd,i,'program_mode','update') #generating command line arguments cmd,i=add_to_cmd(cmd,i,'prod_cd',pass_prod_cd) cmd,i=add_to_cmd(cmd,i,'in_stock',ms_row.in_stock) cmd,i=add_to_cmd(cmd,i,'sales_cost',ms_row.sales_cost) cmd,i=add_to_cmd(cmd,i,'order_qty',ms_row.order_qty) cmd,i=add_to_cmd(cmd,i,'class_cd',ms_row.class_cd) cmd,i=add_to_cmd(cmd,i,'descrip','"'+ms_row.descrip+'"') cmd=re.sub(' +',' ',cmd) #executing the linux script stdin, stdout, stderr = ssh.exec_command(cmd) if stdout: stdoutput=stdout.read() print stdoutput prod_cd=re.sub(r'\s', '', ms_row.prod_cd) #print stdoutput.find(prod_cd) , stdoutput.find('created') #check if the output has the string inserted in it just to be sure. #The script there should not be modified without unless it gives update in the same way if (stdoutput.find(prod_cd)!=-1 and stdoutput.find('created')!=-1): lite_cur.execute("update inv_data_bk set status='Y' where prod_cd =%s"%('"' +lite_row[0]+'"')) rc=lite_cur.rowcount if rc==1: lite_con.commit() total_tries+=1 logging.info('created/updated table:'+lite_row[0]) print 'created/updated table:'+lite_row[0] else: print 'unexpected rownum',rc elif (stdoutput.find(prod_cd)!=1) and (stdoutput.find('removed from products and products categories')!=-1): lite_cur.execute("update inv_data_bk set status='V' where prod_cd =%s"%('"' +lite_row[0]+'"')) rc=lite_cur.rowcount if rc==1: lite_con.commit() total_tries+=1 logging.info('created/updated table:'+lite_row[0]) print 'created/updated table:'+lite_row[0] else: print 'unexpected rownum',rc else: print stdoutput logging.info('unable to update status:'+lite_row[0]+stdoutput) print 'unable ',stdoutput if stderr: print stderr.read() #close connections lite_con.close() ms_con.close() ssh.close() print 'total number of updates, inserts tried:',total_tries return total_tries except KeyboardInterrupt: sys.exit() raise except: print 'Unexpected error:',sys.exc_info() raise
def create_database(): connection, cursor= connection_script.connect_to_sqlite() cursor.execute(create_query) connection.commit() cursor.close() connection.close()
import pyodbc import sqlite3 import connection_script ms_con, ms_cur = connection_script.connect_to_mssql() lite_con, lite_cur = connection_script.connect_to_sqlite() lite_cur.execute("select prod_cd from inv_data_bk order by prod_cd") rows = lite_cur.fetchall() errors = 0 done = 0 for row in rows: prod_cd = row[0] ms_cur.execute("select sales_cost from inv where prod_cd='%s';" % (prod_cd)) ms_row = ms_cur.fetchone() lite_cur.execute("update inv_data_bk set sales_cost=%s where prod_cd='%s';" % (ms_row.sales_cost, prod_cd)) if lite_cur.rowcount: done += 1 print "affected rows:", lite_cur.rowcount print str("item=%s, cost=%s, rows done=%s" % (prod_cd, ms_row.sales_cost, float(done * 100) / 7106)) else: errors += 1 print str("error item=%s, cost=%s" % (prod_cd, ms_row.sales_cost)) lite_con.commit() lite_con.close() ms_con.close()
def delete_database(): connection, cursor= connection_script.connect_to_sqlite() cursor.execute("""DROP TABLE inv_data_bk;""") cursor.close() connection.close()
# Sync_script.py