How to use pandas list as variable in SQL query using python?-CodePudding

I am using the Pandas library on Python and was wondering if there is a way to use a list variable (or perhaps series is better?), let's say uID_list, in an SQL query that is also executed within the same Python code. For example:

dict = {'a': 1, 'b': 2, 'c':3}
uID_series = pd.Series(data=dict, index=['a','b','c'])
uID_list = uID_series.toList()

and let's assume this uID_list can be changed down the road, so it does not always consist of 1, 2, 3.

How can I "plug in" this uID_list variable in the following SQL query?

sql = 
f'''
CREATE PROCEDURE SearchForUsers(@uIDinput AS INT(11))
AS
BEGIN

    SELECT username
    FROM users_table
    WHERE uID in @uIDinput

END

EXECUTE SearchForUsers {uID_list}
'''

Note that creating a new table in the database is not an option for me, as it is work related and my supervisor is strict on keeping the database clean.

What I'm essentially trying to do is see a selection of usernames in users_table table, where the uIDs in users_table match a varying collection of uIDs stored, which is given by uID_list. And uID_list depends on the rest of the python code where it can get changed and manipulated.

CodePudding user response：

Are you just trying to pull the data? Will be easier outside of a stored procedure

l1 = ['ad', 'dfgdf', 'htbgf', 'dtghyt']

l1_str = "('"   "', '".join([str(item) for item in l1])   "')"


sql = 
f'''
    SELECT username
    FROM users_table
    WHERE uID in {l1_str}
'''

CodePudding user response：

You can start by converting your list to a tuple then pass it as a parameter of pandas.read_sql_query.

Assuming cnx is the name of your connection, try this :

uID_tuple = tuple(uID_list)

sql = 
'''
CREATE PROCEDURE SearchForUsers(@uIDinput AS INT(11))
AS
BEGIN

    SELECT username
    FROM users_table
    WHERE uID in @uIDinput

END

EXECUTE SearchForUsers ?
'''

pd.read_sql_query(sql, cnx, params=[uID_tuple])