使用 pandas.read_sql 和 MSAccess 的特定表名的“sql 执行失败”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33323736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Execution failed on sql" for specific table name using pandas.read_sql and MSAccess
提问by David
The weirdest thing is happening. I am connecting to an Access DB using pypyodbc and pandas.io.sql in Python 2.7 32 bit. The following code works perfectly with pretty ANY table name (table_name):
最奇怪的事情正在发生。我正在使用 Python 2.7 32 位中的 pypyodbc 和 pandas.io.sql 连接到 Access DB。以下代码与漂亮的任何表名 (table_name) 完美配合：
var = psql.read_sql("SELECT * FROM table_name;",conn)
except for a table named Currency. I could even call the table Currencand the code works, no problem. As soon as I add the yat the end, I get:
除了名为Currency的表。我什至可以调用表Currenc并且代码有效，没问题。一旦我在最后添加y，我就会得到：
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 421, in read_sql
coerce_float=coerce_float, parse_dates=parse_dates)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1046, in read_sql
cursor = self.execute(*args)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1041, in execute
raise_with_traceback(ex)
File "C:\Python27\lib\site-packages\pandas\io\sql.py", line 1030, in execute
cur.execute(*args)
File "C:\Python27\lib\site-packages\pypyodbc.py", line 1605, in execute
self.execdirect(query_string)
File "C:\Python27\lib\site-packages\pypyodbc.py", line 1631, in execdirect
check_success(self, ret)
File "C:\Python27\lib\site-packages\pypyodbc.py", line 986, in check_success
ctrl_err(SQL_HANDLE_STMT, ODBC_obj.stmt_h, ret, ODBC_obj.ansi)
File "C:\Python27\lib\site-packages\pypyodbc.py", line 954, in ctrl_err
raise ProgrammingError(state,err_text)
pandas.io.sql.DatabaseError: Execution failed on sql: SELECT * FROM Currency;
any ideas?
有任何想法吗？
Thanks
谢谢
回答by Gord Thompson
Currencyis a reserved wordin Access SQL so you must enclose it in square brackets if you need to use it as a table (or column) name, i.e.,
Currency是Access SQL 中的保留字，因此如果需要将其用作表（或列）名称，则必须将其括在方括号中，即，
SELECT ... FROM [Currency]
