"Execution failed on sql" for specific table name using pandas.read_sql and MSAccess

Question

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):

var = psql.read_sql("SELECT * FROM table_name;",conn)

except for a table named Currency. I could even call the table Currenc and the code works, no problem. As soon as I add the y at the end, I get:

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

Gord Thompson · Accepted Answer · 2015-10-24 22:05:59Z

6

Currency is a reserved word in Access SQL so you must enclose it in square brackets if you need to use it as a table (or column) name, i.e.,

SELECT ... FROM [Currency]

answered Oct 24, 2015 at 22:05

Gord Thompson

125k39 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

Thank you so much! That does it. I actually looked for something like that, but only found restricted characters, but not words. Thanks for the link, very useful.

Collectives™ on Stack Overflow

"Execution failed on sql" for specific table name using pandas.read_sql and MSAccess

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related