import pandas as pd
import openpyxl
import pymysql
NP0 = pd.read_excel('c:/Temp/ChangeNP.xlsx', sheet_name = str(2011)+str(1)+'Q')
Code=NP0.iloc[5:,0]
Name=NP0.iloc[5:,1]
for index in range(1,34):
year1=2011+(index-1)//4
quarter1=(index-1)%4+1
year2=2011+index//4
quarter2=index%4+1
OP1 = pd.read_excel('c:/Temp/ChangeOP.xlsx', sheet_name = str(year1)+str(quarter1)+'Q')
NP1 = pd.read_excel('c:/Temp/ChangeNP.xlsx', sheet_name = str(year1)+str(quarter1)+'Q')
ROE1 = pd.read_excel('c:/Temp/ChangeROE.xlsx', sheet_name = str(year1)+str(quarter1)+'Q')
OPM1 = pd.read_excel('c:/Temp/ChangeOPM.xlsx', sheet_name = str(year1)+str(quarter1)+'Q')
Sales1 = pd.read_excel('c:/Temp/ChangeSales.xlsx', sheet_name = str(year1)+str(quarter1)+'Q')
OP1 = OP1.astype(object).where(pd.notnull(OP1), None)
NP1 = NP1.astype(object).where(pd.notnull(NP1), None)
ROE1 = ROE1.astype(object).where(pd.notnull(ROE1), None)
OPM1 = OPM1.astype(object).where(pd.notnull(OPM1), None)
Sales1 = Sales1.astype(object).where(pd.notnull(Sales1), None)
OP2 = pd.read_excel('c:/Temp/ChangeOP.xlsx', sheet_name = str(year2)+str(quarter2)+'Q')
NP2 = pd.read_excel('c:/Temp/ChangeNP.xlsx', sheet_name = str(year2)+str(quarter2)+'Q')
ROE2 = pd.read_excel('c:/Temp/ChangeROE.xlsx', sheet_name = str(year2)+str(quarter2)+'Q')
OPM2 = pd.read_excel('c:/Temp/ChangeOPM.xlsx', sheet_name = str(year2)+str(quarter2)+'Q')
Sales2 = pd.read_excel('c:/Temp/ChangeSales.xlsx', sheet_name = str(year2)+str(quarter2)+'Q')
OP2 = OP2.astype(object).where(pd.notnull(OP2), None)
NP2 = NP2.astype(object).where(pd.notnull(NP2), None)
ROE2 = ROE2.astype(object).where(pd.notnull(ROE2), None)
OPM2 = OPM2.astype(object).where(pd.notnull(OPM2), None)
Sales2 = Sales2.astype(object).where(pd.notnull(Sales2), None)
CHOP = [(y-x)/x if (None not in (x, y)) & (type(x) is not str) & (type(y) is not str) else None for x, y in zip(OP1.iloc[5:,2], OP2.iloc[5:,2]) ]
CHNP = [(y-x)/x if (None not in (x, y)) & (type(x) is not str) & (type(y) is not str) else None for x, y in zip(NP1.iloc[5:,2], NP2.iloc[5:,2]) ]
CHROE = [(y-x)/x if (None not in (x, y)) & (type(x) is not str) & (type(y) is not str) else None for x, y in zip(ROE1.iloc[5:,2], ROE2.iloc[5:,2]) ]
CHOPM = [(y-x)/x if (None not in (x, y)) & (type(x) is not str) & (type(y) is not str) else None for x, y in zip(OPM1.iloc[5:,2], OPM2.iloc[5:,2]) ]
CHSales = [(y-x)/x if (None not in (x, y)) & (type(x) is not str) & (type(y) is not str) else None for x, y in zip(Sales1.iloc[5:,2], Sales2.iloc[5:,2])]
for k in range (5,2276):
conn = pymysql.connect(host = "xxxx", port=3306, user = "root", password = "xxxx", db = "FactorScoringModel")
try:
with conn.cursor() as cursor:
sql = """INSERT INTO Change (code, name, ChangeOP, ChangeNP, ChangeOPM, ChangeROE, ChangeSales, year, quarter) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
values = (Code.iloc[k-5],Name.iloc[k-5], CHOP[k-5], CHNP[k-5],CHOPM[k-5], CHROE[k-5], CHSales[k-5], year1, quarter1)
cursor.execute(sql, values)
conn.commit()
finally:
conn.close()
ProgrammingError Traceback (most recent call last)
<ipython-input-13-cefead1f52a9> in <module>
48 sql = """INSERT INTO Change (code, name, ChangeOP, ChangeNP, ChangeOPM, ChangeROE, ChangeSales, year, quarter) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
49 values = (Code.iloc[k-5],Name.iloc[k-5], CHOP[k-5], CHNP[k-5],CHOPM[k-5], CHROE[k-5], CHSales[k-5], year1, quarter1)
---> 50 cursor.execute(sql, values)
51 conn.commit()
52
~\Anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
168 query = self.mogrify(query, args)
169
--> 170 result = self._query(query)
171 self._executed = query
172 return result
~\Anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
326 self._last_executed = q
327 self._clear_result()
--> 328 conn.query(q)
329 self._do_get_result()
330 return self.rowcount
~\Anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
515 sql = sql.encode(self.encoding, 'surrogateescape')
516 self._execute_command(COMMAND.COM_QUERY, sql)
--> 517 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
518 return self._affected_rows
519
~\Anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
730 else:
731 result = MySQLResult(self)
--> 732 result.read()
733 self._result = result
734 if result.server_status is not None:
~\Anaconda3\lib\site-packages\pymysql\connections.py in read(self)
1073 def read(self):
1074 try:
-> 1075 first_packet = self.connection._read_packet()
1076
1077 if first_packet.is_ok_packet():
~\Anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
682
683 packet = packet_type(buff, self.encoding)
--> 684 packet.check_error()
685 return packet
686
~\Anaconda3\lib\site-packages\pymysql\protocol.py in check_error(self)
218 errno = self.read_uint16()
219 if DEBUG: print("errno =", errno)
--> 220 err.raise_mysql_exception(self._data)
221
222 def dump(self):
~\Anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
107 errval = data[3:].decode('utf-8', 'replace')
108 errorclass = error_map.get(errno, InternalError)
--> 109 raise errorclass(errno, errval)
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Change (code, name, ChangeOP, ChangeNP, ChangeOPM, ChangeROE, ChangeSales, year,' at line 1")
sql문법이 잘못되었다고 나오는데 어디가 잘못됬는지 알려주시면 감사하겠습니다.