0

Update:

Here is the code that got me what I needed after using Achampion's resolution:

# make a list generated from a previous operation (dynamic list), eg. listA - listB = dynLst

dynLst
['R10YW', 'R13YW', 'R32YWBY','NOSVCYW']

# construct item request for a SQL statement using dynLst, this is Achampion's code
reqSQL = '(' + ', '.join([r"'{}'".format(x) for x in dynLst]) + ')'

# specify query field from table:
f1 = '\"Product_Code\"'

WC = f1 + ' IN ' + reqSQL

WC
'\"Product_Code\" IN (\'R10YW\', \'R13YW\', \'R32YWBY\',\'NOSVCYW\')'

I have a list composed of derived values from a prior python operation. The items in this list will change every time the previous python command runs. Therefor I need a dynamic way to format the list. I'm using it with the 'IN' operand as part of a SQL statement/where clause being used in a subsequent python function.

In order to build the where clause the right way I need it formatted like this:

field = '\"Product_Code\"'  
qValues =  '(\'R10YW\', \'R13YW\', \'R32YWBY\',\'NOSVCYW\')'
whereClause = field + ' IN ' + qValues

I've tried this:

qValues = '(\\'
for val in dynLst:    # dynLst = a dynamic List
    val = val + '\\'
    qValues = qValues + val + "," + '\\'
qValues = qValues[:-1]
qValues = qValues + ')

But the format I get is this:

'(\\R10YW\\, \\R13YW\\, \\R32YWBY\\,\\NOSVCYW\\)'

I've also tried raw_string('(\') which I think is the python3 way of doing r"string\string", but no luck. I just get a syntax error.

geoJshaun
  • 637
  • 2
  • 11
  • 32
  • Can you use https://stackoverflow.com/a/283801/2958070 ? Your SQL library should be doing most of this work for you. What db are you using? What do you want the final query to look like? – Ben Aug 30 '19 at 00:37

2 Answers2

0

Are you sure you are not just confusing the escaped output. It looks like you just need a string with ' in them, e.g.: 

In []:
dynlist = ['R10YW', 'R13YW', 'R32YWBY', 'NOSVCYW']
'(' + ', '.join(["'{}'".format(x) for x in dynlist]) + ')'

Out[]:
"('R10YW', 'R13YW', 'R32YWBY', 'NOSVCYW')"

And if you really need a \ in the output then you will get a double \\ because that is how Python shows a single \:

In []:
'(' + ', '.join([r"\'{}\'".format(x) for x in dynlist]) + ')'

Out[]:
"(\\'R10YW\\', \\'R13YW\\', \\'R32YWBY\\', \\'NOSVCYW\\')"
AChampion
  • 29,683
  • 4
  • 59
  • 75
0

Your code works (sort of,) in that it actually produces the output you want. You should never make an SQL query this way however as it opens your code up to SQL injection attacks (I assumed SQLite other DBs have the same vulnerability and some method to avoid it.)

https://www.tutlane.com/tutorial/sqlite/sqlite-injection-attacks

https://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html#security-and-injection-attacks

That being said if you use the python3 interpreter as an interactive shell (or ipython or Jupyter,) when you have a string that is the result of an operation it prints it formatted for a programmer:

In [10]: s = "\\hello"

In [11]: s
Out[11]: '\\hello'

but if you print that same string:

In [12]: print(s)
\hello

It's just as you wanted it to be. I tried your code and other than missing a ' at the end of the last line it works as you seem to want it to.

edit: Redid the slashes as the site was also escaping things. The example now looks just like it will come out of ipython.

David Oldford
  • 1,127
  • 4
  • 11