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'