SQL query f string formatting in Python script

Question

I have been trying to apply an f string formatting for the colname parameter inside the SQL query for a script I am building, but I keep getting a parse exception error.

def expect_primary_key_have_relevant_foreign_key(spark_df1, spark_df2, colname):
  '''
  Check that all the primary keys have a relevant foreign key
  '''
  # Create Temporary View
  spark_df1.createOrReplaceTempView("spark_df1")
  spark_df2.createOrReplaceTempView("spark_df2")
  
  # Wrap Query in spark.sql
  result = spark.sql("""
                        select df1.*
                        from spark_df1 df1
                        left join
                        spark_df2 df2
                        f"on trim(upper(df1.{colname})) = trim(upper(df2.{colname}))"
                        f"where df2.{colname} is null"
                        """)
  
  if result == 0:
    print("Validation Passed!")
  else:
    print("Validation Failed!")
  
  return result

Luigi · Accepted Answer · 2021-03-24 16:23:32Z

2

I found the solution, the f goes before the triple quotes """ as:

 # Wrap Query in spark.sql
  result = spark.sql(f"""
                        select df1.*
                        from spark_df1 df1
                        left join
                        spark_df2 df2
                        on trim(upper(df1.{colname})) = trim(upper(df2.{colname}))
                        where df2.{colname} is null
                        """)

answered Mar 24, 2021 at 16:23

Luigi

591 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query f string formatting in Python script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related