업종 별 종가 합계 구하기

[테스트 환경] 

OS : CentOS 7 

DB : MariaDB 10.1.12 

Programming : Python 3.5

1. 함수 작성

  - 업종 마스터 테이블에서 업종 코드를 불러와서 해당 업종 코드에 속하는 종목의 종가 합계 구하기

import pp
from datetime import datetime
import sys, socket, pymysql

IpAddr = socket.gethostbyname(socket.gethostname())
 
conn = pymysql.connect(host=IpAddr, port=3306, user='root', passwd='root', db='STOCK',charset='utf8',autocommit=True)
cur = conn.cursor()

def sum_indtp(n, date):
  result = 0
  sql  = 'SELECT B.CLOSE FROM J_MASTER A, J_DAILY B WHERE  A.INDTP_CODE = '
  sql +=  n
  sql += ' AND B.ISSUE_CODE = A.ISSUE_CODE AND B.JS_DATE = '
  sql += date
  cur.execute(sql)
  issue_cd = cur.fetchall()
  for i in range(len(issue_cd)):
    result += issue_cd[i][0]
  print("%s result : %s "% (n,result))

cur.execute('SELECT INDTP_CODE FROM INDTP_INFO')

indtp_cd = cur.fetchall()

date = '20160415'

for i in range(len(indtp_cd)):
  sum_indtp(indtp_cd[i][0], date)

[결과]

...
032604 result : 7800000.00
032605 result : 3900000.00
...

2. 병렬 프로세서 이용

  - 1번에서 생성한 함수에 병렬 프로세서를 사용하도록 수정하며 특정 일자부터 특정 일자까지 일별 합계를 구할 수 있도록 수정

import pp
from datetime import datetime, timedelta
import sys, socket, pymysql

IpAddr = socket.gethostbyname(socket.gethostname())

conn = pymysql.connect(host=IpAddr, port=3306, user='root', passwd='root', db='STOCK',charset='utf8',autocommit=True)
cur = conn.cursor()

def sum_indtp(n, date):
  result = 0
  conn = pymysql.connect(host=IpAddr, port=3306, user='root', passwd='root', db='STOCK',charset='utf8',autocommit=True)
  cur = conn.cursor()
  sql  = 'SELECT B.CLOSE FROM J_MASTER A, J_DAILY B WHERE  A.INDTP_CODE = '
  sql +=  n
  sql += ' AND B.ISSUE_CODE = A.ISSUE_CODE AND B.JS_DATE = '
  sql += date
  cur.execute(sql)
  issue_cd = cur.fetchall()
  for i in range(len(issue_cd)):
    result += issue_cd[i][0]
  return result

ppservers = ()

if len(sys.argv) > 1:
    ncpus = int(sys.argv[1])
    # Creates jobserver with ncpus workers
    job_server = pp.Server(ncpus, ppservers=ppservers)
else:
    # Creates jobserver with automatically detected number of workers
    job_server = pp.Server(ppservers=ppservers)

print("Starting pp with %s workers" % job_server.get_ncpus())

cur.execute('SELECT INDTP_CODE FROM INDTP_INFO')

indtp_cd = cur.fetchall()


fromdate = datetime.strptime('20160310','%Y%m%d')
todate = datetime.strptime('20160415','%Y%m%d')

date = fromdate

jobs = []

while date <= todate:
  for i in range(len(indtp_cd)):
   input = (indtp_cd[i][0], "{:%Y%m%d}".format(date), IpAddr)
   jobs.append(job_server.submit(sum_indtp, input, (),("pymysql",)))
  date += timedelta(1)

#print(jobs)

job_server.wait()

job_server.print_stats()

cur.close()

[결과]

[python@localhost test]$ python test1.py 1
Starting pp with 1 workers
Job execution statistics:
 job count | % of all jobs | job time sum | time per job | job server
      4329 |        100.00 |      17.6993 |     0.004089 | local
Time elapsed since server creation 21.862860918045044
0 active tasks, 1 cores


[python@localhost test]$ python test1.py 2
Starting pp with 2 workers
Job execution statistics:
 job count | % of all jobs | job time sum | time per job | job server
      4329 |        100.00 |       8.5598 |     0.001977 | local
Time elapsed since server creation 5.454913377761841
0 active tasks, 2 cores