尝试将名称中带有"-"的MySQL表格转换为Pandas数据框时出现编程错误（SQL语法）。
尝试将名称中带有"-"的MySQL表格转换为Pandas数据框时出现编程错误（SQL语法）。
编辑：
参见答案，但在MySQL表名中使用“-”会导致问题。
我尝试了这个：https://stackoverflow.com/a/37730334/14767913
我的代码：
import pandas as pd
table_name = 'calcium-foods'
df = pd.read_sql('SELECT * FROM calcium-foods', con=engine )
表是存在的：
我已经正确连接，并可以使用engine.table_names()获取表列表。
这个也不起作用：
import pandas as pd table_name = 'calcium-foods' sql = "SELECT * from " + table_name print(sql) df = pd.read_sql_query(sql, engine)
我在Jupyter Notebooks中工作，使用的是Python 3.7或3.8，
以下是Traceback：
--------------------------------------------------
ProgrammingError Traceback (most recent call last)
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1266 self.dialect.do_execute_no_params(
-> 1267 cursor, statement, context
1268 )
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute_no_params(self, cursor, statement, context)
595 def do_execute_no_params(self, cursor, statement, context=None):
--> 596 cursor.execute(statement)
597
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
162
--> 163 result = self._query(query)
164 self._executed = query
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/cursors.py in _query(self, q)
320 self._clear_result()
--> 321 conn.query(q)
322 self._do_get_result()
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
504 self._execute_command(COMMAND.COM_QUERY, sql)
--> 505 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
506 return self._affected_rows
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
723 result = MySQLResult(self)
--> 724 result.read()
725 self._result = result
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in read(self)
1068 try:
-> 1069 first_packet = self.connection._read_packet()
1070
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
676 packet.raise_for_error()
677 return packet
--> 678
679 def _read_query_result(self, unbuffered=False):
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in raise_for_error(self)
106 errorclass = InternalError if errno < 1000 else OperationalError
107 raise errorclass(errno, errval)
--> 108
109 def escape_string(s):
110 return _escape_args(s, encoders.escape_string)
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-foods' at line 1")
上述异常是以下异常的直接原因：
ProgrammingError Traceback (most recent call last)
in
3 sql = "SELECT * from " + table_name
4 print(sql)
----> 5 df = pd.read_sql_query(sql, engine)
6 #conn = engine.connect()
7 #table_name = 'calcium-foods'
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pandas/io/sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
381 coerce_float=coerce_float,
382 parse_dates=parse_dates,
--> 383 chunksize=chunksize,
384 )
385
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize)
1293 args = _convert_params(sql, params)
1294
-> 1295 result = self.execute(*args)
1296 columns = result.keys()
1297
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
1160 """Simple passthrough to SQLAlchemy connectable"""
1161 return self.connectable.execution_options(no_parameters=True).execute(
-> 1162 *args, **kwargs
1163 )
1164
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
2233
2234 connection = self._contextual_connect(close_with_result=True)
-> 2235 return connection.execute(statement, *multiparams, **params)
2236
2237 def scalar(self, statement, *multiparams, **params):
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, object_, *multiparams, **params)
1001 """
1002 if isinstance(object_, util.string_types[0]):
-> 1003 return self._execute_text(object_, multiparams, params)
1004 try:
1005 meth = object_._execute_on_connection
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_text(self, statement, multiparams, params)
1176 parameters,
1177 statement,
-> 1178 parameters,
1179 )
1180 if self._has_events or self.engine._has_events:
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1315 except BaseException as e:
1316 self._handle_dbapi_exception(
-> 1317 e, statement, parameters, cursor, context
1318 )
1319
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
1509 elif should_wrap:
1510 util.raise_(
-> 1511 sqlalchemy_exception, with_traceback=exc_info[2], from_=e
1512 )
1513 else:
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
180
181 try:
--> 182 raise exception
183 finally:
184 # credit to
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
1265 if not evt_handled:
1266 self.dialect.do_execute_no_params(
-> 1267 cursor, statement, context
1268 )
1269 else:
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute_no_params(self, cursor, statement, context)
594
595 def do_execute_no_params(self, cursor, statement, context=None):
--> 596 cursor.execute(statement)
597
598 def is_disconnect(self, e, connection, cursor):
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
161 query = self.mogrify(query, args)
162
--> 163 result = self._query(query)
164 self._executed = query
165 return result
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/cursors.py in _query(self, q)
319 self._clear_result()
320 conn.query(q)
--> 321 self._do_get_result()
322 return self.rowcount
323
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
503 sql = sql.encode(self.encoding, 'surrogateescape')
504 self._execute_command(COMMAND.COM_QUERY, sql)
--> 505 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
506 return self._affected_rows
507
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
722 else:
723 result = MySQLResult(self)
--> 724 result.read()
725 self._result = result
726 if result.server_status is not None:
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in read(self)
1067 def read(self):
1068 try:
-> 1069 first_packet = self.connection._read_packet()
1070
1071 if first_packet.is_ok_packet():
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
675 self._result.unbuffered_active = False
676 packet.raise_for_error()
--> 677 return packet
678
679 def _read_query_result(self, unbuffered=False):
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/protocol.py in raise_for_error(self)
220 errval = self.read_uint8()
221 errno = self.read_uint16()
--> 222 if DEBUG: print("errno =", errno)
223 err.raise_mysql_exception(self._data)
224
225 def dump(self):
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/err.py in raise_mysql_exception(data)
105 if errorclass is None:
106 errorclass = InternalError if errno < 1000 else OperationalError
--> 107 raise errorclass(errno, errval)
ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-foods' at line 1")
[SQL: SELECT * from calcium-foods]
(Background on this error at: http://sqlalche.me/e/13/f405)
另外，当我点击SQL Alchemy错误的URL时，它似乎总是将我带到同一个页面，而不是特定的错误页面。这正常吗？
出现这个问题的原因是在构建SQL语句时，没有正确地将表名与语句连接起来。解决方法是在构建SQL语句时，在表名后面添加分号。
具体的代码如下所示：
import pandas as pd import MySQLdb # 创建与MySQL数据库的连接 conn = MySQLdb.connect(host='localhost', user='root', passwd='password', db='mydatabase') # 定义表名 table_name = "my-foods" # 构建SQL查询语句 sql = "SELECT * from " + table_name + ";" # 从MySQL数据库中获取数据并存入Pandas DataFrame中 df = pd.read_sql(sql, conn) # 打印DataFrame print(df)
通过在构建SQL语句时添加分号，可以解决这个问题。这样，MySQL数据库就能正确解析SQL语句，从而将表中的数据读取到Pandas DataFrame中。
在使用MySQL数据库时，应避免在表名中使用"-"，因为这可能会引发问题。为了解决这个问题，可以将表名中的"-"替换为"_"。以下是一个示例代码，展示了如何将一个包含"-"的表名的MySQL表格导入Pandas数据框中：
import pandas as pd table_name = 'calcium_foods' sql = "SELECT * from " + table_name print(sql) df = pd.read_sql_query(sql, engine) print(df.head())
以上代码中，首先定义了一个表名变量"table_name"，其值为"calcium_foods"。然后，通过将表名拼接到SQL查询语句中，形成一个完整的查询语句。接下来，使用Pandas的"read_sql_query"函数执行该查询，并将结果存储在名为"df"的数据框中。最后，通过打印数据框的前几行来验证查询是否成功。
在解决了表名中的"-"问题后，代码可以正常执行，将MySQL表格导入到Pandas数据框中。现在，表格的结构如下图所示：
[myimgagain](https://i.stack.imgur.com/6SeFy.png)