How to add quotes around items in a List.

[deleted-user-yC5VkbyXzrQR]

How do I put quotes around the items in the variable ? 

b = ['23','25']
a = ['003','030']


sql = "{} IN ({}) AND {} IN ({})".format("STATE_CODE",", ".join([str(m) for m in b]), "CNTY_CODE", ", ".join([str(n) for n in a]))
‍‍‍‍‍

It will spit out something like this

 'STATE_CODE IN (23, 25) AND CNTY_CODE IN (003, 030)'

I need the quotes around the numbers. mapping.DefintionQuery does not like the zeros for some reason. I believe its because there's no quotes around the number.

So I need it to look like below. 

SQL = "STATE_CODE IN ('23', '25') AND CNTY_CODE IN ('003', '030')"

Thanks

[DanPatterson_Retired]

like this? 

sql = "{} IN ({}) AND {!s:} IN ({!s:})".format("STATE_CODE", b, "CNTY_CODE",  a)

sql
"STATE_CODE IN (['23', '25']) AND CNTY_CODE IN (['003', '030'])"‍‍‍‍

or like this?

sql = "{} IN ('{}', '{}') AND {} IN ('{}', '{}')".format("STATE_CODE", *b, "CNTY_CODE",  *a)

sql
"STATE_CODE IN ('23', '25') AND CNTY_CODE IN ('003', '030')"‍‍‍‍‍‍‍‍

or perhaps specify what the string should look like when complete

[deleted-user-yC5VkbyXzrQR]

Hey Dan thanks for the reply. 

The problem with the first one is that the SQL expression doesn't like the [ ] brackets around the results. Could i do a remove "["  "]" from the string or something like that. ? 

The problem with the second one is that, the list can have more than 2 variables, it can have say from 1 - 15. So the list length will change each time. 

"or perhaps specify what the string should look like when complete "I'm not sure how to do this.  

[DanPatterson_Retired]

tuple your list

b = ['23','25', '27', '29']
a = ['003','030', '300']

sql = "{} IN {} AND {!s:} IN {!s:}".format("STATE_CODE", tuple(b), "CNTY_CODE",  tuple(a))

sql

 "STATE_CODE IN ('23', '25', '27', '29') AND CNTY_CODE IN ('003', '030', '300')"‍‍‍‍‍‍‍‍

[deleted-user-yC5VkbyXzrQR]

Hey Dan thanks for the help. We ended up with a script like below. I realized that the SQL query was not right doing it the above way. 

li2 = [['40', '153'],
 ['40', '043'],
 ['40', '003'],
 ['40', '093'],
 ['40', '151'],
 ['40', '047'],
 ['40', '011'],
 ['50', '073'],
 ['50', '099']]
lst_fips = li2

length = len(lst_fips)-1
output = ""
#iterate through list and format output
index = 0 
for i,j in lst_fips:   
    state_code = '(\''+ str(i) +'\')'
    county_code = '(\'' + str(j) + '\')'
    sql = "(STATE_CODE IN {} AND CNTY_CODE IN {})"
    if index < length:
        output = output + sql.format(state_code, county_code) + " OR "
        index += 1
    else:
        output = output + sql.format(state_code, county_code)


print (output) 
(STATE_CODE IN ('40') AND CNTY_CODE IN ('153')) OR (STATE_CODE IN ('40') AND CNTY_CODE IN ('043')) OR (STATE_CODE IN ('40') AND CNTY_CODE IN ('003')) OR (STATE_CODE IN ('40') AND CNTY_CODE IN ('093')) OR (STATE_CODE IN ('40') AND CNTY_CODE IN ('151')) OR (STATE_CODE IN ('40') AND CNTY_CODE IN ('047')) OR (STATE_CODE IN ('40') AND CNTY_CODE IN ('011')) OR (STATE_CODE IN ('50') AND CNTY_CODE IN ('073')) OR (STATE_CODE IN ('50') AND CNTY_CODE IN ('099'))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[DanPatterson_Retired]

sounds like you should add a query field (eg. STATE_CNTY) and concatenate the state code and county code (eg. '40_153'), then use that field for your queries if you have to do it often.

[deleted-user-yC5VkbyXzrQR]

Yeah I've been trying to get that fips field in there for a while now. 

[JoshuaBixby]

Unless you restructure your data, as Dan suggests, the performance of your query will be poor.  Since performance is already out the window, I suggest a different approach than building a series of massive AND OR statements:

>>> fips_fields = [
...     'STATE_CODE',
...     'CNTY_CODE'
... ]
>>>
>>> fips_values = [
...     ['40', '153'],
...     ['40', '043'],
...     ['40', '003'],
...     ['40', '093'],
...     ['40', '151'],
...     ['40', '047'],
...     ['40', '011'],
...     ['50', '073'],
...     ['50', '099']
... ]
>>>
>>> sql_fields = "CONCAT({})".format(", ".join(field for field in fips_fields))
>>> sql_values = "('{})'".format("', '".join("".join(i) for i in fips_values))
>>> sql = "{} IN {}".format(sql_fields, sql_values)
>>>
>>> print(sql)
CONCAT(STATE_CODE, CNTY_CODE) IN ('40153', '40043', '40003', '40093', '40151', '40047', '40011', '50073', '50099)'
>>> ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The above assumes STATE_CODE and CNTY_CODE are already TEXT fields; otherwise, you will have to add in CAST operators to cast numbers to text.