11

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?

Steven Matthews
  • 9,705
  • 45
  • 126
  • 232
  • Wouldn't it be easier to use rows rather than columns? – Álvaro González Jul 13 '12 at 08:30
  • Well, the problem with that, and it may be my database design (it has been such a headache on this project), is that then I'd need a row for EACH word, and an associated value, and I don't want that as these words are all related. They will be part of the same query to the API. Others, with a different identifier will be a different query. – Steven Matthews Jul 13 '12 at 08:33
  • 2
    If you have a new question, use a new post. Answerers are not notified of you editing this post, let alone that you edited it to ask another question. – Martijn Pieters Jul 13 '12 at 08:41

4 Answers4

12

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
1

No, you cannot dynamically produce the list of columns to be selected. It will have to be hardcoded in your final query.

Your current query would produce a result set with one column and the value of that column would be the string "word%" in all rows that satisfy the condition.

Jon
  • 428,835
  • 81
  • 738
  • 806
1

You can generate the list of column names first by using

SHOW COLUMNS IN tblname LIKE "word%"

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

"SELECT {0} FROM searchterms WHERE onstate = 1".format(', '.join(columns))
Maria Zverina
  • 10,863
  • 3
  • 44
  • 61
0

This could be helpful: MySQL wildcard in select

In conclusion it is not possible in MySQL directly.

What you could do as a dirty workaround is get all the column names from the table with an initial query (http://dev.mysql.com/doc/refman/5.0/en/show-columns.html) and then compare in python if the name matches your pattern. Afterwards you could do the MySQL select statement with the found column names like this:

SELECT word1, word2, word3 from searchterms where onstate = 1;
Community
  • 1
  • 1
Horen
  • 11,184
  • 11
  • 71
  • 113