Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
190 views
in Technique[技术] by (71.8m points)

python - Wildcards in column name for MySQL

I am trying to select multiple columns, but not all of the columns, from the database. All of the columns I want to select are going to start with "word".

So in pseudocode I'd like to do this:

SELECT "word%" from searchterms where onstate = 1;

More or less. I am not finding any documentation on how to do this - is it possible in MySQL? Basically, I am trying to store a list of words in a single row, with an identifier, and I want to associate all of the words with that identifier when I pull the records. All of the words are going to be joined as a string and passed to another function in an array/dictionary with their identifier.

I am trying to make as FEW database calls as possible to keep speedy code.

Ok, here's another question for you guys:

There are going to be a variable number of columns with the name "word" in them. Would it be faster to do a separate database call for each row, with a generated Python query per row, or would it be faster to simply SELECT *, and only use the columns I needed? Is it possible to say SELECT * NOT XYZ?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

No, SQL doesn't provide you with any syntax to do such a select.

What you can do is ask MySQL for a list of column names first, then generate the SQL query from that information.

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'your_table'
    AND column_name LIKE 'word%'

let's you select the column names. Then you can do, in Python:

"SELECT * FROM your_table WHERE " + ' '.join(['%s = 1' % name for name in columns])

Instead of using string concatenation, I would recommend using SQLAlchemy instead to do the SQL generating for you.

However, if all you are doing is limit the number of columns there is no need to do a dynamic query like this at all. The hard work for the database is selecting the rows; it makes little difference to send you 5 columns out of 10, or all 10.

In that case just use a "SELECT * FROM ..." and use Python to pick out the columns from the result set.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...