CODEWITHSUNDEEP
pythonpython-2.7
Chris
Chris

Reputation: 43

Comparing column in spreadsheet against column in SQL table

Python newbie here. I am trying to write a small Python script to automate some work I do regularly. I have an Excel spreadsheet with a worksheet called "new_it_users" with a list of usernames in column A, and I have a SQL database with an "ituserlist" table has a column called "username".

Basically I want to load the spreadsheet in Python and then check each username in column A against the results of a SQL query. If there is a result (i.e. username from spreadsheet exists in database table, the script will put "Existing User Found" in column 6 of the spreadsheet; if there's no result "No User Found" is entered.

Below is some of my code. The database connection is working and the spreadsheet is getting loaded but the "comparison" part of the script (which is the meat of this) isn't working :(. I will get "No User Found" in column 6 even if I add existing usernames into the spreadsheet during testing...

Can someone give me some guidance about what I'm doing wrong? Would appreciate any tips!

conn = psycopg2.connect(conn_string)

cursor = conn.cursor()

wb = openpyxl.load_workbook(input_filename)
sheet = wb.get_sheet_by_name('new_it_users')

query_statement = 'select username from ituserlist'
cursor.execute(query_statement)
sql_data = list(cursor.fetchall())

for rowNum, row in tqdm(enumerate(sheet.iter_rows(), start=1)):
    it_user = sheet.cell(row=rowNum, column=1).value

    if it_user in sql_data:
        sheet.cell(row=rowNum, column=6).value = "Existing User Found"
    else:
        sheet.cell(row=rowNum, column=6).value = "No User Found"

wb.save(output_filename)

cursor.close()
conn.close()

Upvotes: 0

Views: 1186

Answers (2)

mhawke
mhawke

Reputation: 87144

The problem is that cursor.fetchall() returns tuples containing the query result, not the single strings that your code assumes. A simple fix to your code is to massage the query result into a list of strings. You can use a list comprehension to pluck out the user name from first item of each row:

query_statement = 'select username from ituserlist'
names = [row[0] for row in cursor.execute(query_statement)]

names will contain a list of user name strings, and the in comparison should now match the user name from the spreadsheet as you require.

Upvotes: 1

theGtknerd
theGtknerd

Reputation: 3763

Personally, I would use SQL to do the comparison. It is a lot more powerful and flexible. A simple illustration might be:

conn = psycopg2.connect(conn_string)

cursor = conn.cursor()

wb = openpyxl.load_workbook(input_filename)
sheet = wb.get_sheet_by_name('new_it_users')

for rowNum, row in tqdm(enumerate(sheet.iter_rows(), start=1)):
    it_user = sheet.cell(row=rowNum, column=1).value
    cursor.execute('select username from ituserlist where username = %s', (it_user,))
    for row in cursor.fetchall():
        #this will only run if the user was found
        sheet.cell(row=rowNum, column=6).value = "Existing User Found"
        break #keep the else: from running
    else:
        #no user found, set not found error
        sheet.cell(row=rowNum, column=6).value = "No User Found"

wb.save(output_filename)

cursor.close()
conn.close()

Upvotes: 0

Related Questions