UPDATE function in sqldf in Python

Question

Building SQL code in Python platform using 'from pandasql import sqldf'. Executed code below and it turned out to be "no such table df" as output. Anyone able to rectify it?

query="""
UPDATE df
SET C = C*10
WHERE C <50
"""

Answer 1

pandasql does not support update operations, you need to use something like this:

from pandasql import sqldf
import pandas as pd

df = pd.DataFrame({
   'A': ['foo', 'bar', 'baz', 'foo', 'bar', 'baz'],
   'B': ['one', 'one', 'two', 'three', 'two', 'two'],
   'C': [10, 20, 30, 40, 50, 60],
   'D': [7, 8, 9, 10, 11, 12]
})

query = """
SELECT *
FROM df
WHERE column1 > 10;
"""

result = sqldf(query, globals())

result.loc[result['C'] < 50, 'C'] *= 10