5

I have a dataset with some null values I wanted to update to read missing instead of null

I've looked through all the forums and documentation and can't seem to find a response 

from pandasql import sqldf

pysqldf = lambda q: sqldf(q, globals())

Updated_table= pysqldf("update practice_names set Region = 'test'       where Region is null;")

I sadly then get this error log 

--------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510 

OperationalError: no such table: practice_names

The above exception was the direct cause of the following exception:

OperationalError                          Traceback (most recent call last)
~/anaconda3/lib/python3.7/site-packages/pandasql/sqldf.py in __call__(self, query, env)
     60             try:
---> 61                 result = read_sql(query, conn)
     62             except DatabaseError as ex:

~/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in read_sql(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)
    396             coerce_float=coerce_float, parse_dates=parse_dates,
--> 397             chunksize=chunksize)
    398 

~/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize)
   1062 
-> 1063         result = self.execute(*args)
   1064         columns = result.keys()

~/anaconda3/lib/python3.7/site-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
    953         """Simple passthrough to SQLAlchemy connectable"""
--> 954         return self.connectable.execute(*args, **kwargs)
    955 

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    941         if isinstance(object, util.string_types[0]):
--> 942             return self._execute_text(object, multiparams, params)
    943         try:

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_text(self, statement, multiparams, params)
   1103             parameters,
-> 1104             statement, parameters
   1105         )

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1199                 cursor,
-> 1200                 context)
   1201 

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    264     cause = exc_value if exc_value is not exception else None
--> 265     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    266 

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    247         if value.__traceback__ is not tb:
--> 248             raise value.with_traceback(tb)
    249         raise value

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:

~/anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    508     def do_execute(self, cursor, statement, parameters, context=None):
--> 509         cursor.execute(statement, parameters)
    510 

OperationalError: (sqlite3.OperationalError) no such table: practice_names [SQL: "update practice_names set Region = 'test' where Region is null;"] (Background on this error at: http://sqlalche.me/e/e3q8)

During handling of the above exception, another exception occurred:

PandaSQLException                         Traceback (most recent call last)
<ipython-input-158-f1734320a61f> in <module>
      6 print(missing_region)
      7 
----> 8 A= pysqldf("update practice_names set Region = 'test' where Region is null;")
      9 
     10 

<ipython-input-158-f1734320a61f> in <lambda>(q)
      1 from pandasql import sqldf
----> 2 pysqldf = lambda q: sqldf(q, globals())
      3 
      4 ##After inspecting the practice names dataset we notice missing values for region
      5 missing_region= pysqldf("SELECT COUNT(DISTINCT Practice_ID) as counter FROM practice_names where Region is null ;")

~/anaconda3/lib/python3.7/site-packages/pandasql/sqldf.py in sqldf(query, env, db_uri)
    154     >>> sqldf("select avg(x) from df;", locals())
    155     """
--> 156     return PandaSQL(db_uri)(query, env)

~/anaconda3/lib/python3.7/site-packages/pandasql/sqldf.py in __call__(self, query, env)
     61                 result = read_sql(query, conn)
     62             except DatabaseError as ex:
---> 63                 raise PandaSQLException(ex)
     64             except ResourceClosedError:
     65                 # query returns nothing

PandaSQLException: (sqlite3.OperationalError) no such table: practice_names [SQL: "update practice_names set Region = 'test' where Region is null;"] (Background on this error at: http://sqlalche.me/e/e3q8)

I was expecting to have all the null values change to "missing" however the code will not execute

what occurs is an error message stating the table I'm trying to update does not exist - this is despite that the table is indeed spelled correctly.

willepi
  • 127
  • 1
  • 11
  • 1
    Which part of this: "_no such table: practice_names_" - makes you confused? The table does not exist. Have you created it? – DYZ Jan 19 '19 at 19:03
  • Thanks so much for your response - I have indeed created the table and when I do a simple count on this table it works fine – willepi Jan 19 '19 at 19:04
  • Also, what is`pysqldf = lambda q: sqldf(q, globals())`? A named `lambda` and `globals()` in one line. This is not good practice at all. – roganjosh Jan 19 '19 at 19:10
  • Make sure that you spell the table name consistently (the underscore, upper/lower case letters). What is the result of the quesry `"show tables;"`? – DYZ Jan 19 '19 at 19:10
  • The table is certainly spelled correctly - I'll keep having a dig about - thanks for your suggestions thus far – willepi Jan 19 '19 at 19:30

1 Answers1

2

If this is still an issue: the update statement does not work because it is simply not implemented, see https://github.com/yhat/pandasql/issues/83 - There is a PR pending but has not been merged so far; for the time being and my own purposes I have patched sqldf.py locally, not a big deal, but "dirty"

WLA
  • 21
  • 3