CODEWITHSUNDEEP
pythonsql-server
mtnbiker1185
mtnbiker1185

Reputation: 25

Performing an SQL query for each item in a tuple

I am new to Python and am hoping someone can help me figure out how to perform an SQL query on each item in a tuple using Python.

I have a SQL Express server that contains a number of databases for a badge reader system. What I am trying to do is pull the user id's that have scanned into a particular reader, then use those id's to get the actual user names.

Currently, I am able run the query that pulls the user id's and run a query on the other table using just one id. What want to be able to do, and seem to be having an issue figuring out, is running that second query on every user id in the tuple that is created from the first query. Below is the code for the two functions I am currently using.

def get_id():
global cardholder
global cur   

cur.execute("SELECT user_id FROM db.table WHERE badgereaderid = 'badgereader1'")

cardholder = []  
rows = cur.fetchall()
for row in rows:
    if row == None:
        break
    cardholder.append(row[0])

print(cardholder)

def get_name():
global cardholder
global user
global cur

cur.execute("SELECT FirstName, LastName FROM db.table WHERE user_id= '%s'" % cardholder)

while 1:
    row = cur.fetchone()
    if row == None:
        break
    user = row[0] + row[1]

Upvotes: 1

Views: 2816

Answers (2)

Jakub Dering
Jakub Dering

Reputation: 76

A good practice in Python is to define the data collections outside the function if you intend to use them later on in your code Try this code:

cardholder_names = []
#pass the cardholder as a param to the function
def get_name(cardholder):
#cur is already defined as a global param, no need to do it twice     
    cur.execute("SELECT FirstName, LastName FROM db.table WHERE user_id='{0}'".format(cardholder))
    return cur.fetchone()


#now use the for loop to iterate over all the cardholders
for holder in cardholders:
    cardholder_name = get_name(holder)
    cardholder_names.append( {"name" : cardholder_name[0], "surname" : cardholder_name[1]})

Upvotes: 1

CJC
CJC

Reputation: 400

Two possible options

Repeated queries in Python

for user_id in cardholder:
    cur.execute("SELECT FirstName, LastName FROM db.table WHERE user_id= '%s'" % user_id)

But why not just pull all the data in the first query?

cur.execute("SELECT a.user_id, b.FirstName, b.LastName FROM db.table1 a left join bd.table2 b on a.user_id = b.user_id WHERE a.badgereaderid = 'badgereader1'")

or, use triple quotes to allow multi-line strings and make the SQL command easier to understand

cur.execute("""SELECT
a.user_id,
b.FirstName,
b.LastName
FROM db.table1 a
left join db.table2 b
on a.user_id = b.user_id
WHERE a.badgereaderid = 'badgereader1'""")

Upvotes: 1

Related Questions