1

When I run the following:

table = cfg_pre + '_' + cfg_tbl
check_data = 'SELECT COUNT(*) FROM X WHERE TABLE_NM = {}'.format(table)

I get the following:

SELECT COUNT(*) FROM X WHERE TABLE_NM = ABC_123

What I need is:

SELECT COUNT(*) FROM X WHERE TABLE_NM = 'ABC_123'

Otherwise the SQL won't execute. Any ideas?

Improve this question
6
  • 2
    You shouldn't use string formatting to create SQL queries. Leaves you vulnerable to SQL injection. Commented Jun 7, 2018 at 21:24
  • thanks...do you have a better suggestion for this? Commented Jun 7, 2018 at 21:26
  • 1
    Let the DB driver of your choice prepare your statement, but in general: "whatever '{}' else".format("foo bar") will result in whatever 'foo bar' else. Commented Jun 7, 2018 at 21:26
  • 1
    stackoverflow.com/questions/10950362/… Commented Jun 7, 2018 at 21:27
  • 1
    I'm assuming you want to execute those SQL statements. If you really only want to generate a string with single quotes around that value, let me know and I'll reopen. Or find a more suitable dupe. Commented Jun 7, 2018 at 21:30

3 Answers 3

Reset to default
3

Use double speech marks instead of single speech marks:

"SELECT COUNT(*) FROM X WHERE TABLE_NM = '{}'".format(table)

This isn't entirely relevant (more of a nicety than necessity), but you could also use f-strings if you're using Python3.6 (but you still need the double speech marks).

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

Comments

1

If you're using Python 3.6, f-strings are a f-un solution. Ultimately, you need to use double quotes to enclose the single quotes.

table = f'{cfg_pre}_{cfg_tbl}'
check_data = f"SELECT COUNT(*) FROM X WHERE TABLE_NM = '{table}'"
Improve this answer

5 Comments

While yes, this achieves the desired output, this is not a safe way to generate queries.
f-strings are cool, but also irrelevant to this problem. What actually fixes things in your answer is using double quotes on the string literal, so you can use single quotes inside the string—exactly the same as Adi219's answer, except that he explains it, while you explain something irrelevant.
OP didn't ask how to create safe dynamic queries, he asked how to add single quotes.
Giving bad advice just because someone asks for bad advice is still bad advice.
But it's not just about what the OP wants ... it's about achieving what the OP wants with the best security/efficiency/optimacy/etc.
1

While SQL injection is a problem you could run into if you create your query with variables, the solution would be to escape the characters or change quotes.

table = cfg_pre + '_' + cfg_tbl
check_data = "SELECT COUNT(*) FROM X WHERE TABLE_NM = '{}'".format(table)

Or

table = cfg_pre + '_' + cfg_tbl
check_data = 'SELECT COUNT(*) FROM X WHERE TABLE_NM = \'{}\''.format(table)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.