-1

Hi I want to concat outputFile variable instead of a filename in the below code in python can anyone help me out?

outputFile= 'test.csv'

connection.execute("TRUNCATE travel_staging.upsell_test;"
    "COPY travel_staging.upsell_test FROM 's3://folder/filename' WITH CREDENTIALS "
    "'aws_access_key_id=xxx;aws_secret_access_key=xxxx'"
    " FORMAT csv DELIMITER ',' IGNOREHEADER 1 DATEFORMAT 'auto' NULL AS 'null' MAXERROR 500 acceptinvchars;")

Expected output

 TRUNCATE travel_staging.upsell_test;
 COPY travel_staging.upsell_test FROM 's3://folder/test.csv' WITH CREDENTIALS 
 'aws_access_key_id=xxx;aws_secret_access_key=xxxx'
 FORMAT csv DELIMITER ',' IGNOREHEADER 1 DATEFORMAT 'auto' NULL AS 'null' MAXERROR 500
Dusky Dood
  • 197
  • 3
  • 13
  • 1
    Have you considered using a bind variable? They're generally the Right Way to pass data into SQL, without risking hostile values being parsed as additional query content. – Charles Duffy Mar 12 '20 at 18:04
  • Also, *which specific* dbapi driver are you using? They use slightly different mechanisms. – Charles Duffy Mar 12 '20 at 18:04
  • 1
    To give a contrived-and-not-at-all-tested example, you don't want what happens if `outputFilename=$'\'; DROP DATABASE travel_staging; -- '`. – Charles Duffy Mar 12 '20 at 18:06
  • ...if you *insist* on doing the wrong thing, I'd probably switch to an f-string, which we have plenty of Q&A entries already discussing, including the linked duplicate. – Charles Duffy Mar 12 '20 at 18:07

1 Answers1

0

To replace a variable in a string

Use f strings.

>>> fox = 'quick brown'
>>> dog = 'lazy'
>>> f'the {fox} fox jumps over the {dog} dog'

If you don't need the newlines

Use the \ to continue the statement on a new line.

>>> 'the quick brown fox jumps \
... over the lazy dog'
'the quick brown fox jumps over the lazy dog'

If you do need the new lines

Just have each line on a separate string.

>>> print('\n'.join([
...     'the quick brown fox jumps',
...     'over the lazy dog',
... ]))
the quick brown fox jumps
over the lazy dog
Pedro Rodrigues
  • 2,520
  • 2
  • 27
  • 26
  • This doesn't answer the OP's answer about how to use the value of `outputFile` instead of hardcoding `filename`. They already have perfectly good multi-line continuation; nothing in that aspect of the code needs to be changed at all. – Charles Duffy Mar 12 '20 at 18:08
  • (Try it yourself: `print("hello"` on one line, `" cruel "` on the second, `"world")` on the third, and you'll see that they all get concatenated to `print("hello cruel world")`, as you can see at https://ideone.com/P5jSca) – Charles Duffy Mar 12 '20 at 18:10
  • My point is that you were answering a question that wasn't asked. *Now* you're answering the actual question, but only in the first of the three sections in your answer; the other two are irrelevant. – Charles Duffy Mar 12 '20 at 18:16
  • ...the demonstration above is that *the OP's code already worked as-is*, except for the replacement. – Charles Duffy Mar 12 '20 at 18:17
  • (and as for the replacement itself, doing it as string substitution in the OP's context opens you up to SQL injection attacks, so teaching that practice is counterproductive to software security; good-practice alternatives are discussed in https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python). – Charles Duffy Mar 12 '20 at 18:18