How to pass in string as f-string into another method?

Question

I'm planning on passing in multiple f-string based templates into another general method that iterates over databases and tables.

My code:

@staticmethod
def query1_template():
 query = '''
 SELECT
'{db}' as db_name,
'{tbl}' as tbl_name,
'{config[db][tbl]['definition']}' as definition
'''
return query

def build(self, query, config):
 etl = ''
 for i, db in enumerate(self.datasets):
  for j, tbl in enumerate(self.datasets.get(db)):
   if i > 0 or j > 0: 
    etl += 'UNION ALL'
   etl += query

Current output:

SELECT
'{db}' as db_name,
'{tbl}' as tbl_name
UNION ALL
SELECT ...

My expected output should be having the {db} and {tbl} values actually filled out.

I've tried etl += query.format(db, tbl) as well but keep getting a key error for db.

Is there a way for this logic to work?

``format`` doesn't know how the parameters are named. Use ``query.format(db=db, tbl=tbl)`` instead. Note that f-strings (literals akin to ``f"..."``) and format strings (strings used with ``.format``) are subtly but decidedly different things. — MisterMiyagi, Jul 16 '21 at 15:56
You don't have any f-strings (which are *literals*, not special values), only ordinary string literals. — chepner, Jul 16 '21 at 15:59
But this is the wrong wrong *wrong* way to define a parameterized SQL query. — chepner, Jul 16 '21 at 15:59
That makes sense. What would be a better way to define a parameterized SQL query? — Ricardo Francois, Jul 16 '21 at 16:00
Use a library that will build it for you. Most libraries provide a way to execute a query given a string with placeholders and the arguments for those placeholders. Table names are trickier; you might need to use something like SQLAlchemy to handle them. — chepner, Jul 16 '21 at 16:01
I'm planning on executing these queries with the Athena API using the `start_query_execution` method. Would SQLAlchemy still assist me with this? — Ricardo Francois, Jul 16 '21 at 16:08
Another follow-up: I've edited my code to add another parameter in addition to `db` and `table`. In a scenario like this, how would `format` work? — Ricardo Francois, Jul 16 '21 at 16:13

score 0 · Accepted Answer · answered Jul 16 '21 at 16:56

0

In the query1_template() method:

query = '''
 SELECT
'{db}' as db_name,
'{tbl}' as tbl_name,
'{definition}' as definition
'''

In the build() method:

etl += query.format(db=db, tbl=tbl, definition=config[db][tbl]['definition']

answered Jul 16 '21 at 16:56

Ricardo Francois

752
7
24

How to pass in string as f-string into another method?

1 Answers1