Databricks pass Python parameters into a looped SQL script multiple times

Question

I am trying to create a python list with multiple variables that will then be used as values in a for loop that loops through a sql query that creates multiple tables but I can't seem to find an example that shows a parameter that is used multiple times.

list = ["a","b","c"]
for x in list:
sql = ''' 
create volatile table test_{} as (
select * from database.tablename_{}
) with data on commit preserve rows;

create volatile table test2_{} as (
select * from database.tablename2_{}
) with data on commit preserve rows;
'''
display(sql)

Alex Ott · Accepted Answer · 2020-08-20 13:59:40Z

2

You can either use so-called f-strings (Since Python 3.6), to interpolate the same variable into multiple places:

list = ["a","b","c"]
for x in list:
    sql = f'''
create volatile table test_{x} as (
select * from database.tablename_{x}
) with data on commit preserve rows;

create volatile table test2_{x} as (
select * from database.tablename2_{x}
) with data on commit preserve rows;
'''
    print(sql)

or you can use format with numbered placeholders

list = ["a","b","c"]
for x in list:
    sql = '''
create volatile table test_{0} as (
select * from database.tablename_{0}
) with data on commit preserve rows;

create volatile table test2_{0} as (
select * from database.tablename2_{0}
) with data on commit preserve rows;
'''.format(x)
    print(sql)

answered Aug 20, 2020 at 13:59

Alex Ott

88.1k10 gold badges110 silver badges157 bronze badges

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

Collectives™ on Stack Overflow

Databricks pass Python parameters into a looped SQL script multiple times

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