0

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:

imgfromme

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?

seizouki
  • 55
  • 6
  • You should famiiarise yourself with https://dev.mysql.com/doc/refman/8.0/en/identifiers.html Guess what sql sees arithmetic operators in identifiers as arithmetic operators.. – P.Salmon Dec 06 '20 at 10:51

2 Answers2

0

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

Try change this line:

sql = "SELECT * from " + table_name

to:

sql = "SELECT * from " + table_name + ";"
James Miguel Jaramillo Huaman
  • 43
  • 4
0

Do not use "-" in table names for MySQL. Apparently it can cause problems. So I dropped all my tables except "users", replaced "-" with "_" and this worked:

import pandas as pd
table_name = 'calcium_foods' 
sql = "SELECT * from " + table_name
#query = '''
#SELECT * from calcium-foods
#'''
print(sql)
df = pd.read_sql_query(sql, engine)
#conn = engine.connect()
#table_name = 'calcium-foods'
#df = pd.read_sql_query(query, engine )
print(df.head())

My tables now: myimgagain

seizouki
  • 55
  • 6