I am new to Python, and I am trying to use Python to query a Microsoft SQL Server database. The data is returned in this format:

(('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))

What I am trying to do is check to find a match, to see if some data from another data table exists in that data from SQL Server.

When I try to use the "in" to check, it does not work for me. I thought if I could convert the data (a tuple of tuples) into a list, then I could more easily search and match. But that doesn't work because there are some brackets around each list element. At least, that is the what I think because, if I manually recreate the list without the extra brackets, then I can search successfully.

I am wondering if there is a way to remove that extra bracket. Or, maybe there is a better approach. I have read several posts here and other articles, and so far, I have not found an approach.

Here is what I have tried. As you can see, the final one works, but that is what i have created manually.

#  here is the data that gets returned from Msft Sql Server
sql_data = (('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))
print(sql_data)
print('type of rds_qubole_data is {}'.format(type(sql_data)))
# the type shows as <class 'tuple'>

# set some variables for checking in the data
Orange1 = 'orange,apple,coconut' in sql_data
print('orange1 is {}'.format(Orange1))
# shows orange1 is False
Vegan1 = 'lettuce,carrot,celery' in sql_data
print('Vegan1 is {}'.format(Vegan1))
# Vegan1 is False

#  then, i try to convert to a list using a method i saw in another post
conv_to_list = [list(tup) for tup in sql_data]
print(conv_to_list)
#  data looks like:
# ['orange,apple,coconut', 'lettuce,carrot,celery', 'orange,lemon,strawberry']
print('type of conv_to_list is {} '.format(type(conv_to_list)))
# prints type of conv_to_list is <class 'list'>
Orange1 = 'orange,apple,coconut' in conv_to_list
print('orange1 is {}'.format(Orange1))
#  orange1 is False 
Vegan1 = 'lettuce,carrot,celery' in conv_to_list
print('Vegan1 is {}'.format(Vegan1))
# Vegan1 is False

# finally, i just manually type the list and remove the brackets around each element
manual_list = ['orange,apple,coconut', 'lettuce,carrot,celery', 'orange,lemon,strawberry']
print(manual_list)
#  ['orange,apple,coconut', 'lettuce,carrot,celery', 'orange,lemon,strawberry']
print('Type of manual_list is {}'.format(type(manual_list)))
#  Type of manual_list is <class 'list'>
Orange1 = 'orange,apple,coconut' in manual_list
print('orange1 is {}'.format(Orange1))
# orange1 is True
Vegan1 = 'lettuce,carrot,celery' in manual_list
print('Vegan1 is {}'.format(Vegan1))
# Vegan1 is True

CodePudding user response：

Check below code if it helps:

t1 = (('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',)) # DataBase
q1 = 'lettuce,apple,celery' # query

for i in t1:
for j in i:
    if q2 in j:
        print("Items present")

If you have a list of items to query:

q1 = 'lettuce,apple,celery' # no
q2 = 'orange,apple,coconut' # yes
q3 = 'orange,lemon,strawberry' #yes
q4 = 'lettuce,apple,lemon' # no
q = [q1,q2,q3,q4]

for i,j in enumerate(t1):
    for x,y in enumerate(q):
        if y in j:
            print(f'Items in q{x 1} are present in t1 at index: {i}')

The answer would be:

Items in q2 are present in t1 at index: 0

Items in q3 are present in t1 at index: 2

enter code here

CodePudding user response：

use for loop to match data:

sql_data = (('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))

target = 'orange,apple,coconut'

found = False

for item in sql_data:
    if target  in item:
        found = True

print(found)

Output:

True

Use list compression to find weather target is found or not:

sql_data = (('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))

target = 'orange,apple,coconut'


out = [True if target in item else False for item in sql_data]

print(True in out)

Output:

True

Use lambda to find weather target value exits or not:

from functools import reduce
sql_data = (('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))

target = 'orange,apple,coconut'

found = reduce(lambda bool_value1, bool_value2:bool_value1 or bool_value2,[True if target in item else False for item in sql_data])


print(found)

Output:

True

CodePudding user response：

Bug

conv_to_list = [list(tup) for tup in sql_data]

Means list of string list instead of list of strings. That's conv_to_list is list[list[str]] instead of list[str]

Fix

conv_to_list = [list(tup).pop() for tup in sql_data]

Full Code

# Data from SQL Server
sql_data = (('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))

# Data to find
Orange1 = 'orange,apple,coconut'

# Converting to list and removing brackets
conv_to_list = [list(tup).pop() for tup in sql_data]

# Check for a match
check_match = Orange1 in conv_to_list

print(f'Orange1 found: {check_match}')   # [1] Output: True