Querying mysql multiple columns using list in python-CodePudding

q = f"""
SELECT * FROM table;
"""
df =  pd.read_sql(q, con=conn)

I have multiple columns that I store in python list [col1, col2, ... , coln]

Whenever I want to query all columns except a few, query all columns then dropping is method currently being used.

I am wondering if passing in list of columns in q

q = f""" SELECT {column_list} FROM table"""

is possible.

Reason for this is to save memory in jupyter notebook by bringing in only necessary columns to the notebook.

CodePudding user response：

You could create a CSV list of columns from your list of column names:

col_list = ['col1', 'col2', 'col3']
cols = ', '.join(col_list)
q = f"""
SELECT {cols} FROM table;
"""
df =  pd.read_sql(q, con=conn)
print(q)  # SELECT col1, col2, col3 FROM table;

CodePudding user response：

EDIT: Don't do this. See comments below.

Sure, you can do that. However, using f-strings (or some custom way to format) is not recommended, and one of the reasons is to prevent an SQL injection attack. The preferred way is to use whatever method your database connector uses.

I've used SQLite3 before, and in it we do something like this:

from sqlite3 import connect
connection = connect(path_to_db)
data = connection.execute("SELECT ?, ?, ? FROM table;", ("col1", "col2", "col3"))

The question marks will be substituted for column names in the query. The library has protections built-in that will act if you use this approach. You can also use this for other stuff (like ...WHERE name = ?;).