FANCY_QUERY="""
SELECT B.PYISSUE, B. PYGROUP, B. PYNAME,
C.PYAPPLICATION,
D.PYCHANNEL, D. PYDIRECTION,
E. PYOPERATOR, F. PYOUTCOME,
A .*
FROM PR_DATA_IH_FACT A JOIN PR_DATA_IH_DIM_ACTION B ON
A.PZACTIONID = B.PZID
JOIN PR_DATA_IH_DIM_APPLICATION C ON A. PZAPPLICATIONID =
C.PZID
JOIN PR_DATA_IH_DIM_CHANNEL D ON A.PZCHANNELID = D.PZID JOIN
PR_DATA_IH_DIM_OPERATOR E ON A, PZOPERATORID = E.PZID
JOIN PR_DATA_IH_DIM_OUTCOME F ON A.PZOUTCOMEID = F.ZID WHERE
PYSUBJECTID in ({0}) """
This select query only i am using in that
Below is my function for about query: in
constants.FANCY_QUERY I USED IT
def assert_results(db_manager, df):
customers = df[ 'PYSUBJECTID'].astype(str).values. tolist()
cust = ", ". join( ["" " + x + "" " for
x in customers])
select_query = constants.FANCY_QUERY. format(cust)
results=db_manager.execute_select_statement_as_table(constants.APP_SCHEMA,
select_query)
results = results.drop([ 'PXFACTID', 'PXINTERACTIONID' ],
axis=1)
df = df.drop([ 'PXFACTID', 'PXINTERACTIONID' ], axis=1)
actual_df = results[df. columns]
df = df.sort_values(by=df. columns.
tolist()).reset_index(drop=True)
actual_df=actual_df.sort_values(by=actual_df.columns.tolist()).reset_index(drop=True)
print(df.equals(actual_df))
assert_frame_equal(df, actual_df, check_dtype=False,
check_index_type=False)
My final expectation is i should not get missing expression
error and my fancy query variable i used that select query it should validate
the give I'd is not exist in that database result needs to pass
SOLUTION
;
you are trying to
use the FANCY_QUERY string in your assert_results function to retrieve data
from a database and compare it to a Pandas dataframe df. It seems that you are
using the format method to substitute a list of customer IDs, represented as a
string, into the FANCY_QUERY string.
The error you are
encountering, "missing expression", could be due to a number of
issues. One possibility is that there is a problem with the syntax of the FANCY_QUERY
string. You may want to check the syntax of the query to make sure all the
required elements are present and properly formatted.
Another possibility
is that there is a problem with the way you are substituting the customer IDs
into the FANCY_QUERY string. You could try printing out the resulting string to
see if it looks correct.
Finally, it's
possible that there is a problem with the database itself, such as a table or
column that is not present or is misspelled. You may want to check the schema
of the database to make sure all the required tables and columns are present
and spelled correctly.
The FANCY_QUERY
string appears to be a SELECT statement that joins several tables in the
database. The format method is used to insert a list of PYSUBJECTID
values into the WHERE clause of the SELECT statement.
The assert_results
function appears to be comparing the results of the SELECT statement with a
given dataframe, df. The function first converts the PYSUBJECTID
values in df to a list of strings and formats them as a comma-separated
string to be used in the WHERE clause of the SELECT statement.
It then executes the
SELECT statement using the execute_select_statement_as_table method,
which is expected to return a table of results. The returned table is filtered
to include only the columns that are present in df, and both tables are
sorted by their columns and the index is reset.
Finally, the
function checks if the two tables are equal using the pandas.DataFrame.equals
method and the pandas.testing.assert_frame_equal method. If the tables
are not equal, an AssertionError will be raised.
It appears that you
are encountering a "missing expression" error when executing the
SELECT statement. This error usually indicates that there is a problem with the
syntax of the SELECT statement. Some possible causes for this error include:
- A comma is missing between two table
names or table aliases in the FROM clause.
- A join condition is missing between two
tables in the FROM clause.
- A column name or alias is specified in
the SELECT clause, but the column does not exist in any of the tables
listed in the FROM clause.
To troubleshoot this
issue, you may want to try the following steps:
- Check that all table names and column
names are spelled correctly and are properly referenced in the SELECT
statement.
- Check that all commas and join
conditions are present in the FROM clause.
- If you are using table aliases, make
sure that they are defined for each table in the FROM clause and are used
consistently throughout the SELECT statement.
- If you are using any functions or
expressions in the SELECT clause, make sure that they are written
correctly and that all required arguments are provided.
The assert_results
function appears to take in a database manager object and a Pandas DataFrame
df. It then creates a list of PYSUBJECTID values from the DataFrame and formats
them into a string to be used in the FANCY_QUERY string. The resulting string
is then passed to the execute_select_statement_as_table method of the
db_manager object, which returns a table of results.
The resulting table
is then modified to remove some columns, and the original DataFrame df is also
modified to remove some columns. The modified DataFrame and results table are
then sorted and reset the index, and their equality is checked using the Pandas
equals method and the assert_frame_equal function from the pandas.testing
module.
If the
assert_frame_equal function does not raise an AssertionError, it means that the
modified DataFrame and results table are equal, and the function will return
without any issues. However, if the assert_frame_equal function does raise an
AssertionError, it means that the modified DataFrame and results table are not
equal, and the function will raise an AssertionError.
It is not clear from
the provided code what the "missing expression error" is that you are
trying to avoid, or how the FANCY_QUERY variable is being used to validate the
existence of a given ID in the database. It would be helpful to provide more
context and clarify what you are trying to achieve.
