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 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
# Sync_script.py