MySQL query or identifier is too long in Jupyter? [duplicate]

Question

I have a Jupyter notebook in which I want to execute a query. I'm using sqlalchemy and the following code is working just fine if I run it as script in PyCharm:

from sqlalchemy import create_engine
import pandas as pd

engine = create_engine('mysql+mysqlconnector://user:password@server_address:3306/DBase', echo=False)
connection = engine.connect()

query = "SELECT week(date) as week, count(*) FROM table GROUP BY week"
df = pd.read_sql(query, con=engine)
print(df)

However, when I try to run this in Jupyter notebook I'll get the error:

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1059 (42000): Identifier name 'SELECT week(date) as week, count(*) FROM table GROUP BY week' is too long

When I try something like

query = "SELECT count(*) as number FROM table"

It does work correctly. As soon as I add some statement to the query it will throw the error. I'm not sure why it is complaining about an identifier specifically. It doesn't seem to be due to the 'AS' since it will also throw the error if I leave that out and add a WHERE statement. Any suggestions?

Answer 1

Instead of using this:

df = pd.read_sql(query, con=engine)

You should use this:

df = pd.read_sql_query(query, con=engine)

Your query is somehow getting treated as a SQL Table name when you're calling read_sql, the reason for that is unknown for me and I also couldn't find a reason for it from the documentation.