sqlalchemy failed to query mysql

• I found a very good blog on this issue, you can see if it helps, here is a link: sqlalchemy connection using MySQL < / font > < / a > < / li >

Part of this answer uses GPT, GPT_Pro to solve the problem better.
When you connect to the sql database using sqlalchemy, some necessary parameters are missing or incorrectly filled.

First, sqlalchemy provides a create_engine() function to connect to the database, which takes a number of arguments. For example, the user, password, host, port, database, etc. When you write engine=create_engine('mysql:7]000C') in the code, you simply fill in a database type, not complete parameters. So naturally there are mistakes.

Next, look at the sqlalchemy official documentation and see that the correct usage should be as follows:

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://用户名:密码@主机IP:端口号/数据库?charset=utf8')

Here you need to notice:

1. engine = create_engine('mysql+pymysql:// Username: password @Host IP: port number/database? charset=utf8')
   Here we add mysql+pymysql, which is required for sqlalchemy to connect to mysql;
2. User name, password, host IP address, port number, and database are all mandatory;
3. charset=utf8 This option is used to specify the character set and is usually included.

So the modified code should look like this:

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://用户名:密码@主机IP:端口号/数据库?charset=utf8')  # 修改连接mysql的部分
sql = 'select * from stock_basic;'  # 定义sql语句
df = pd.query(sql, engine)  # 运行sql语句

At this point, querying mysql with sqlalchemy is safe.
If the answer is helpful, please accept it.

This answer quotes ChatGPT

This error may be caused by an incompatible version of SQLAlchemy or an incorrect argument passed to the create_engine function. Here are a few possible solutions:

1. Verify that the execute method is supported by the SQLAlchemy version: Verify that the correct version of SQLAlchemy is used and that there are no known compatibility issues.

2. Verify that the database connection is normal: Ensure that the database server is running and that your user name and password are correct. You can also try testing your connection using other database clients to confirm that it is valid.

3. Check whether the SQL statement is correct: Ensure that your SQL query statement is correct, and that the table name and column name are correct.

4. Try using different SQLAlchemy parameters: You can try using other create_engine function arguments, such as create_engine('sql+pymysql://user:password @host:port/database'), Or connect to the database using a different database client, such as MySQL Workbench, to see if the query can be successfully executed.

5. Use another pandas function to read data: If the preceding steps fail to solve the problem, you may use another function to read data, such as pd.read_sql_table or pd.read_sql.

Found the answer from somewhere else, just downgrade to below 2.0, or change the script

The latest version of SQLAlchemy(2.0) removes Engine.execute. At this point you may need to downgrade SQLAlchemy

  python -m pip install --upgrade 'sqlalchemy<2.0'

(or the equivalent conda command if conda is used).
Or, as Gord Thompson points out in his review, query with sqlalchemy.text Package.

from sqlalchemy import text
# ...
with engine.begin() as conn:
    query = text("""SELECT * FROM tbl""")
    df = pd.read_sql_query(query, conn)