在Jupyter Notebook使用MySQL DATE_FORMAT遇语法错误的求助

错误原因分析 #

1. 字符串引号冲突：你用双引号包裹整个SQL语句，内部DATE_FORMAT(issues.created_on, "%Y")里的双引号和外层双引号边界冲突，Python无法正确识别字符串范围，直接抛出语法错误。
2. pandas占位符解析问题：即便修复引号问题，read_sql_query会默认把SQL中的%当作参数占位符（对应DBAPI的%s），%Y会被误判为需要替换的参数，导致后续执行出错。

解决方法 #

方法1：三引号定义SQL+转义% #

用三引号包裹SQL避免引号嵌套冲突，同时把%Y改为%%Y转义%，让pandas把它当作普通字符传递给MySQL：

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import sqlalchemy

session = sqlalchemy.create_engine("mysql+pymysql://user:pwj@ip/DB")
  
sql = """SELECT DATE_FORMAT(issues.created_on, "%%Y") as YEAR, issues.tracker_id as Ticketgrund, count(*) FROM issues where issues.project_id = '2' group by YEAR, Ticketgrund;"""
df = pd.read_sql_query(sql, session)
df.head(20)

方法2：用SQLAlchemy的text对象封装SQL #

通过sqlalchemy.text()包裹SQL语句，跳过pandas对%的占位符解析，同时用单引号包裹SQL解决引号冲突：

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import sqlalchemy
from sqlalchemy import text

session = sqlalchemy.create_engine("mysql+pymysql://user:pwj@ip/DB")
  
sql = text('SELECT DATE_FORMAT(issues.created_on, "%Y") as YEAR, issues.tracker_id as Ticketgrund, count(*) FROM issues where issues.project_id = "2" group by YEAR, Ticketgrund;')
df = pd.read_sql_query(sql, session)
df.head(20)

方法3：调换引号嵌套逻辑 #

用单引号包裹整个SQL语句，内部字符串用双引号，同时转义%：

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import sqlalchemy

session = sqlalchemy.create_engine("mysql+pymysql://user:pwj@ip/DB")
  
sql = 'SELECT DATE_FORMAT(issues.created_on, "%%Y") as YEAR, issues.tracker_id as Ticketgrund, count(*) FROM issues where issues.project_id = "2" group by YEAR, Ticketgrund;'
df = pd.read_sql_query(sql, session)
df.head(20)

内容的提问来源于stack exchange，提问作者GrobMo