How to add a Python string variable to part of a SQL string variable in Python?

Question

I am working in Python with SQLAlchemy to execute Redshift SQL queries. I am defining the SQL I want to execute as python strings I want to create some additional python string variables that will become a section of the SQL strings. For instance, in the below example, the table name in the SQL query can be built from the python variable "table_string" plus the rest of the table name itself. In this example the resulting table name would be "test_table_name".

table_string = 'test_'

create_test_sql_string_python='''
begin;
create table 'table_string'+'table_name'
(id int not null, name varchar(40), date timestamp);
end;
'''
execute(sa.text(create_test_sql_string_python).execution_options(autocommit=True))

I have done something similar in R and am wondering what the python equivalent of this may be. Here is the R sample:

table_string <- 'test_'
dbSendQuery(redshift,paste0("begin;
                         create table ",table_string,"table_name
                         (id int not null, name varchar(40), date timestamp);
                          end;"))

OriolAbril · Accepted Answer · 2018-03-27 22:20:21Z

3

The .format method can be used (see PyFormat Documentation). In you case, it would look like that:

table_string = 'test_'

create_test_sql_string_python='''
begin;
create table {0}table_name
(id int not null, name varchar(40), date timestamp);
end;
'''.format(table_string)

Placing {0} inside the string will substitute there the first argument inside .format(), which could have more than one argument.

answered Mar 27, 2018 at 22:20

OriolAbril

9,0334 gold badges33 silver badges41 bronze badges

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

4 Comments

roganjosh Over a year ago

I think "table_name" is a typo tbh. Upvote though; you beat me to it (though we have different documentation) :)

OriolAbril Over a year ago

From the R code example and the question saying that the resulting table name should be "test_table_name" I understood that the variable table_string was just a prefix

roganjosh Over a year ago

Sorry, I meant a typo on the OP's behalf. But for redshift, specifically, I can't be sure. I'd be surprised if you had to have "table_name" to qualify what you were specifying but I may be wrong.

nation161r Over a year ago

Thanks both of you. This works. I have a situation where I need to end up using several different variable stings inside SQL variable strings. Trying to avoid having to modify any of the actual SQL and just have a list of variables to change once at the top of the python file before running the entire thing. Haven't got that far yet but this will definitely help out.

roganjosh · Accepted Answer · 2018-03-27 22:22:07Z

2

I can't comment on the SQL statement itself, but you can use format to pass a variable here.

The string would look like:

"""
begin;
create table {} (id int not null, 
                 name varchar(40), 
                 date timestamp);
end;
""".format(table_string)

answered Mar 27, 2018 at 22:22

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to add a Python string variable to part of a SQL string variable in Python?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related