I am new to python, but am trying to use python to query a Msft SQL Server database and the data gets returned in this format:
(('orange,apple,coconut',), ('lettuce,carrot,celery',), ('orange,lemon,strawberry',))
What I am trying to do is then check to find a match, to see if some data from another data table exists in that data from MS SQL.
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.
Thanks for any help or suggestions!
# 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