Python mysql error

Asked 2 years ago, Updated 2 years ago, 39 views

    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")

It says that the sql grammar is wrong, so I would appreciate it if you could tell me where it is wrong.

python mysql

2022-09-22 18:31

1 Answers

Table name change seems to be mysql reserved word.

https://doc.ispirer.com/sqlways/Output/SQLWays-1-205.html


2022-09-22 18:31

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.