String for SQL where clause

From raju

string for SQL where clause

description

The idea here is to build strings that will be used in a sql where or in clauses.

lists

    >>> days = ['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat']
    >>> days
    ['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat']
    >>> day_string = "'" + "', '".join(days) + "'"
    >>> day_string
    "'sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'"
    

For a list of integers, map it to strings and then join

    >>> nodes = [1, 2, 3, 4, 5]
    >>> ", ".join(map(str, nodes))
    '1, 2, 3, 4, 5'
    

dataframes

using IPython 7.16.1, Python 3.8.3, pandas 1.0.5

    In [1]: import pandas as pd
       ...: df = pd.DataFrame({'days': ['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'num': [1, 2, 3, 4, 5, 6, 7]})
       ...: df
    Out[1]:
      days  num
    0  sun    1
    1  mon    2
    2  tue    3
    3  wed    4
    4  thu    5
    5  fri    6
    6  sat    7
    
    In [2]: df.dtypes
    Out[2]:
    days    object
    num      int64
    dtype: object
    

quote each element

useful for | varchar columns

    In [3]: "'" + "', '".join(df['days']) + "'"
    Out[3]: "'sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'"
    
    In [4]: "'" + "', '".join(df['num'].astype(str).tolist()) + "'"
    Out[4]: "'1', '2', '3', '4', '5', '6', '7'"
    

simple join

useful for | integer columns

For a simple join (without quoting individual elements)

    In [5]: ", ".join(df['days'])
    Out[5]: 'sun, mon, tue, wed, thu, fri, sat'
    
    In [6]: ", ".join(df['num'].astype(str).tolist())
    Out[6]: '1, 2, 3, 4, 5, 6, 7'
    

the latter can be done with map as well.

    In [7]: ", ".join(map(str, df['num']))
    Out[7]: '1, 2, 3, 4, 5, 6, 7'