在Jupyter笔记本中使用date_format格式的mysql '%'

f2uvfpb9 于 2023-01-04 发布在 Mysql

关注(0)|答案(1)|浏览(187)

我尝试在SQL查询中使用Jupyter notebook中的date_format来获取正确的数据，但Jupyter抛出了以下错误：

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;"
                                                    ^
SyntaxError: invalid syntax"

这是我的手机号码：

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)

SQL查询运行时没有错误，但在Jupyter中没有。转义%（我假设这是错误的原因）不会改变任何事情。
我能怎么办？

mysql

来源：https://stackoverflow.com/questions/74949463/using-mysql-in-date-format-in-jupyter-notebook

1条答案

按热度按时间

nukf8bse1#

用%转义%（并在双引号内使用单引号）：

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

或者，使用SQLAlchemy中提供的date_format函数。下面是一个简短的示例：

from sqlalchemy import func
from sqlalchemy import MetaData, Table, Column
from sqlalchemy import select
from sqlalchemy import Integer, String, DateTime

md = MetaData()

mytable = Table(
    "employees",
    md,
    Column("emp_no", Integer, primary_key=True),
    Column("first_name", String(30)),
    Column("last_name", String(30)),
    Column("hire_date", DateTime),
)

row = session.execute( select(mytable.c.first_name, func.date_format(mytable.c.hire_date, "%Y") ) ).first()
print(row)

赞(0）回复(0）举报 2023-01-04

我来回答

在Jupyter笔记本中使用date_format格式的mysql '%'

1条答案

相关问题

热门标签

最新问答