Specific results in SQL query

Question

I have read similar articles on SO and found none where this is applicable, to this extent.

I have a SQL db where I am storing specific information.

Different scripts populate selective columns.

I want to know how I can view only the populated columns upon query and not return the NULL values, nor respective columns in the row queried.

with sqlite3.connect('Test.sql3') as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM Table WHERE Item=?', (car,))
        results = cursor.fetchall()

        item_0_in_result = [_[0] for _ in results]
        item_0_in_result1 = [_[1] for _ in results]
        item_0_in_result2 = [_[2] for _ in results]

In this query the results can be more, or less items based on the populated columns.

This script should then only return the columns with respective values upon query.

I am sure that this can be achieved with a type of array based query, but with the variation of columns I seem to be a bit stuck.

Serge Ballesta · Accepted Answer · 2021-04-12 12:30:19Z

1

You could use cursor.description to extract the column names and apply zip on the result of fetchall to get the values in columns:

cursor.execute('SELECT * FROM Tab WHERE Item=?', (car,))
data = {cursor.description[i][0]: _ for i, _ in enumerate(zip(*cursor.fetchall()))
        if any(val is not None for val in _)}

will return a dictionary of all the columns containing at least one not null value indexed by the column name, and

cursor.execute('SELECT * FROM Tab WHERE Item=?', (car,))
data = {cursor.description[i][0]: _ for i, _ in enumerate(zip(*cursor.fetchall()))
        if all(val is not None for val in _)}

will return the columns containing only not null values.

answered Apr 12, 2021 at 12:30

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ReflexTechR Over a year ago

Thank you very much for the answer. It seems to have worked much better in the collective data structure.

Ptit Xav · Accepted Answer · 2021-04-12 11:47:44Z

0

Check for not NULL values in the query :

SELECT * FROM Table WHERE Item=? AND Columnxx IS NOT NULL;

Columnxx is the name of the column where value should not be NULL in the results

answered Apr 12, 2021 at 11:47

Ptit Xav

3,2272 gold badges10 silver badges18 bronze badges

1 Comment

ReflexTechR Over a year ago

I did try this option, the only issue was that i had almost 10 columns that is left out each time and it gave them same NULL value result. Although it does work with one or two values, for me anyway. I am currently trying to and a filter to give results based on the input.

Collectives™ on Stack Overflow

Specific results in SQL query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related