How to check if rows in postgresql table and list in python are matching or does contain same files?-CodePudding


import psycopg2

conn = psycopg2.connect("dbname=mydatabase user=myuser password=mypassword")

cur = conn.cursor()

files_name = ["file_789", "file_456", "file_555", "file_111"]

sql = "SELECT filename FROM xml_job WHERE filename IN %s"

cur.execute(sql, (tuple(files_name),))

matching_files = cur.fetchall()


matching_files = [f[0] for f in matching_files]


print(matching_files)


cur.close()
conn.close()

This is my current code. It gives me this output:

[("file_555",), ("file_111",)] # output

My postgresql table has two rows: one named 'filename' and other one 'remarks' I want my output to be like:

[("file_555","remark1"), ("file_111","remark2")] # output

CodePudding user response：

I dont have your db, so just gonna pencil in what you probably ought to do.

But for the rest, you can use this to find matches and pull out remarks. Note that the order gets changed, but you could easily sort it again.


files_name = ["file_789", "file_456", "file_555", "file_111"]

# adjust your db stuff probably as follows:

#sql = "SELECT filename, remark FROM xml_job WHERE filename IN %s"
#matching_files = [(f[0],f[1]) for f in matching_files]

rows = [("file_555","R1"), ("file_111",'R2')]

#build a dict associating filename and remark.
di = {tu[0]:tu[1] for tu in rows}

#use set operators to find matches
matches = set(files_name) & di.keys()

#now pull the remark from the dictionary you built.
res = [(name,di[name]) for name in matches]

print(res)

output:

[('file_111', 'R2'), ('file_555', 'R1')]

Also, rather than addressing fields with the obscure f[0], f[1] index-based notation, there is something called cursor.description nthat is an array, that has among other things, a field giving the name of the column, and another its index. Sorry, use sqlalchemy myself, but I have used this in the past, much easier to read code when you can see the column names.