- 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 >
On the Internet with sqlalchemy query sql, have provided a relatively simple method, but the use of error, please take guidance
from sqlalchemy import create_engine engine_ts = create_engine('mysql://user:firstname.lastname@example.org:3306/test?charset=utf8&use_unicode=1') sql = 'select * from stock_basic; df = pd.read_sql_query(sql, engine_ts)
File "D:\pythonProject\Trade\mysql.py", line 17, in read_data df = pd.read_sql_query(sql, engine_ts) File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 397, in read_sql_query return pandas_sql.read_query( File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 1560, in read_query result = self.execute(args) File "D:\Program Files\Python38\lib\site-packages\pandas\io\sql.py", line 1405, in execute return self.connectable.execution_options().execute(args, **kwargs) AttributeError: 'OptionEngine' object has no attribute 'execute'
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:
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)