python pymysql executes the SQL statement that compares time. It can be executed smoothly in mysql, but why does it report an error when executed in python?

Question

Python version: 3.5 mysql version: 5.6
python pymysql executes the sql statement comparing time. It can be executed smoothly in mysql, but an error is reported when executed in python;
The database table is exported from the attendance printer Attendance data, filter out those who are late and leave early based on their work number;
Filter out those who clock in after 7:30:00 and before 17:30:00 in the database;

---

Database table structure:

---

The following is the sql statement

select * from kaoqinjilu WHERE gonghao = 6063 and date_format(datatime,'%Y-%m-%d %H:%i:%s')>DATE_ADD(date_format(datatime,'%Y-%m-%d'),INTERVAL "7:30:00" HOUR_SECOND)
and date_format(datatime,'%Y-%m-%d %H:%i:%s')<DATE_ADD(date_format(datatime,'%Y-%m-%d'),INTERVAL "17:30:00" HOUR_SECOND);

Execution result, get the result successfully:

---

python code:

import pymysql.cursors
#连接数据库
connect = pymysql.connect(host='127.0.0.1',port=3306, user='root', passwd='111111', db='test',charset='utf8',)
#获取游标
cursor = connect.cursor()

sql = "select * from kaoqinjilu WHERE gonghao = 6063 AND date_format(datatime,'%%Y-%%m-%%d %%H:%%i:%%s')>DATE_ADD(date_format(datatime,'%%Y-%%m-%%d'),INTERVAL '7:30:00' HOUR_SECOND)
and date_format(datatime,'%%Y-%%m-%%d %%H:%%i:%%s')<DATE_ADD(date_format(datatime,'%%Y-%%m-%%d'),INTERVAL '17:30:00' HOUR_SECOND)"
cursor.execute(sql)

#提交
connect.commit()

for row in cursor.fetchall():
    print(row)
print('迟到早退人数',cursor.rowcount)

Error message:

C:\Users\gsd\AppData\Local\Programs\Python\Python35\python.exe F:/100lainxiti/考勤查询.py
  File "F:/100lainxiti/考勤查询.py", line 7
    sql = "select * from kaoqinjilu WHERE gonghao = 6063 AND date_format(datatime,'%%Y-%%m-%%d %%H:%%i:%%s')>DATE_ADD(date_format(datatime,'%%Y-%%m-%%d'),INTERVAL '7:30:00' HOUR_SECOND)
                                                                                                                                                                                        ^
SyntaxError: EOL while scanning string literal

Process finished with exit code 1

Answer 1

Try using multi-line statements

""" ...... 
    ...... 
"""

Package

Answer 2

After reading the comments above, see this: https://stackoverflow.com/que...

When using MySQL statements in Python, there is no need to add % to escape %. Python’s MySQL module will add escape characters by default.