How to check if a list in python and row in PostgreSQL table contain same files?-CodePudding

I have a row named filename in postgresql table named xml_joblist which contain many files, and a list named files_name in python which contain several files that are sorted after some process. I want to compare filename & files_name and check whether there are any files matching.

|filename |
|---------|
|file_111 |
|file_555 |
|file_888 |
|file_333 | 
|file_445 |
|   .     |
|   .     |
| goes-on |

the above given is the filename row in postgresql table

files_name = [file_789, file_456, file_555, file_111]

the above given is the files_name list i python

How can i write a sql statement to do this process in python?

Expected result:

matchin_files = [file_555, file_111]

CodePudding user response：

To compare filesnames in PostgreSQL with the files_name list in Python and find the matching files, you can use the IN operator in a SELECT statement.

Here is an example of how you can write a SQL statement to do this in Python using the psycopg2 library:

import psycopg2

# Connect to the database
conn = psycopg2.connect("dbname=mydatabase user=myuser password=mypassword")

# Create a cursor
cur = conn.cursor()

# Define the list of files
files_name = ["file_789", "file_456", "file_555", "file_111"]

# Build the SELECT statement
sql = "SELECT filename FROM xml_joblist WHERE filename IN %s"

# Execute the SELECT statement
cur.execute(sql, (tuple(files_name),))

# Fetch the matching files
matching_files = cur.fetchall()

# Print the matching files
print(matching_files)

# Close the cursor and connection
cur.close()
conn.close()

This will execute a SELECT statement that retrieves the filename column where the filename is in the files_name list. The resulting rows will be stored in the matching_files variable, which will contain the list of matching files.

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

Note that this returns list of tuples. You can use list comprehension to covert it to proper list

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

# Returns: ["file_555", "file_111"]

CodePudding user response：

To check if a list in Python and a row in a PostgreSQL table contain the same files, you can do the following:

Connect to the PostgreSQL database using the psycopg2 library. Execute a SELECT query to retrieve the row from the table. Iterate through the list of files in Python and check if each file is present in the retrieved row. Here is an example of how this can be done in Python:

import psycopg2

#Connect to the database
conn = psycopg2.connect("host=localhost dbname=mydatabase user=myuser password=mypassword")

#Create a cursor
cur = conn.cursor()

#Select the row from the table
cur.execute("SELECT * FROM mytable WHERE id = 1")
row = cur.fetchone()

#Iterate through the list of files
for file in my_files:

    #Check if the file is present in the row
    if file in row:
        print(f"{file} is present in the row")
    else:
        print(f"{file} is not present in the row")

#Close the cursor and connection
cur.close()
conn.close()

In this example, we are connecting to the database, executing a SELECT query to retrieve the row with the ID 1 from the table mytable, and iterating through the list of files in my_files. For each file, we check if it is present in the retrieved row.

You can modify the SELECT query and the conditions in the if statement to match your specific requirements.

I hope this helps! Let me know if you have any questions.