Column Name Wildcards for MySQL

Question

I am trying to select multiple columns, but not all columns, from a database. All columns that I want to select will start with a word.

So, in pseudo code, I would like to do this:

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

More or less. I do not find documentation on how to do this - is this possible in MySQL? Basically, I am trying to keep a list of words on one line with an identifier, and I want to associate all the words with this identifier when I pull out the entries. All words will be connected as a string and transferred to another function in the array / dictionary with their identifier.

I am trying to make as many queries to the FEW database as possible in order to save fast code.

Ok, here is another question for you guys:

They will have a variable number of columns named "word". Will it be faster to make a separate database call for each row, the generated Python query for each row, or will it be faster just SELECT * and use only the columns you need? Can I say SELECT * NOT XYZ?

Answer 1

No, SQL does not provide you with any syntax for this choice.

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

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

allows you to select column names. Then you can do it 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 create SQL.

However, if everything you do limits the number of columns, you don’t need to do a dynamic query at all. Hard work for the database - row selection; it doesn’t make much difference to send you 5 columns out of 10 or all 10.

In this case, just use "SELECT * FROM ..." and use Python to extract the columns from the result set.

Answer 2

No, you cannot dynamically create a list of selected columns. It must be hard-coded in your final request.

In the current query, a result set with one column will be created, and the value of this column will be the string "word%" in all rows that satisfy the condition.

Answer 3

First you can generate a list of column names using

 SHOW COLUMNS IN tblname LIKE "word%" 

Then the loop through the cursor and the generated SQL statement uses all the columns from the above query.

 "SELECT {0} FROM searchterms WHERE onstate = 1".format(', '.join(columns)) 

Answer 4

This can be useful: MySQL wildcard in the choice

In conclusion, this is not possible in MySQL directly.

What you can do as a dirty workaround is to get all column names from the table with the original query ( http://dev.mysql.com/doc/refman/5.0/en/show-columns.html ) and then compare in python if The name matches your pattern. After that, you can make the MySQL select statement with the found column names as follows:

 SELECT word1, word2, word3 from searchterms where onstate = 1;