Edit:
See answer but "-" in MySQL table names causes problems. I tried this: https://stackoverflow.com/a/37730334/14767913
My code:
import pandas as pd
table_name = 'calcium-foods'
df = pd.read_sql('SELECT * FROM calcium-foods', con=engine )
The table is there:
I connected properly and can get a list of tables using engine.table_names()
This did not work either;
import pandas as pd
table_name = 'calcium-foods'
sql = "SELECT * from " + table_name
print(sql)
df = pd.read_sql_query(sql, engine)
I am working in Jupyter Notebooks, Python 3.7 or 3.8, and here is the 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)
675 self._result.unbuffered_active = False
--> 676 packet.raise_for_error()
677 return packet
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/protocol.py in raise_for_error(self)
222 if DEBUG: print("errno =", errno)
--> 223 err.raise_mysql_exception(self._data)
224
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/err.py in raise_mysql_exception(data)
106 errorclass = InternalError if errno < 1000 else OperationalError
--> 107 raise errorclass(errno, errval)
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")
The above exception was the direct cause of the following exception:
ProgrammingError Traceback (most recent call last)
<ipython-input-16-7e7208d94a8a> in <module>
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._last_executed = q
320 self._clear_result()
--> 321 conn.query(q)
322 self._do_get_result()
323 return self.rowcount
~/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)
674 if self._result is not None and self._result.unbuffered_active is True:
675 self._result.unbuffered_active = False
--> 676 packet.raise_for_error()
677 return packet
678
~/anaconda3/envs/gamechangers/lib/python3.7/site-packages/pymysql/protocol.py in raise_for_error(self)
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)
As an aside, when I click on the SQL Alchemy error URL, it seems like they all take me to the same page rather than error-specific. Is this normal?