Find entries in a SQL Database with a partial match-CodePudding

I want to automatically move files to their target folder. The path to the target folder should be found in a SQL table. With the following code I can find the entry as long as the entry exactly matches the entry in the table.

direc = "Directory"
searchfor = "200000"
sql = text(f"SELECT * FROM table_name WHERE {direc} LIKE '%{searchfor}%'")

This gives me as result

C:\Users\Public\Music\200000

as the "searchfor" meets the Entry in the Directory.

How can i find the same result if i search for "sometext_200000_sometext"? I already tried it with % {searchfor} %, with *, with . and anything else what cme into my mind but without result.

CodePudding user response：

I'm assuming you are building an SQLAlchemy text statement.

So pass in parameters for a text statement with a like clause you can do the following:

params = { 'searchfor': f'%{searchfor}%' } # How to define params
stmt = text(
        """
        select 
            *
        from
            table_name 
        where
            Directory like :searchfor
        """
    )
with get_engine().begin() as con:
    result = con.execute(stmt, params) # How to execute

Let me know if I have misunderstood your question.

CodePudding user response：

I solved that with a Regular Expresion, which gives me exact the reuslt what i was looking for.

import re

text = "PO 200000 PO_2000001 PO_200002_Delivery Delivery_200003"
 
Six_digits_numbers = re.findall("(?=(\d{6}))",text)

print(Six_digits_numbers)

Result:

['200000', '200000', '000001', '200002', '200003']

CodePudding user response：

Here's the final result for everyone who's interested. The script is working but the following problem needs to fixed for productive use (maybe ;-)): a) the Reg-Expression returns the Filename as well as the Search result which ends up in an Error while moving the files to the path (temp. Solution: ignore the Errors)

import os
import re
import shutil
import sqlalchemy
from sqlalchemy import text

# Get a list of all files in the source folder
engine = sqlalchemy.create_engine('mssql pyodbc://'':''@BERNHARD\SQLEXPRESS/my_Database?driver=SQL Server Native Client 11.0')
source_folder = "C://Users//Public//Music"
files = os.listdir(source_folder)

def move(src, dest):
    shutil.move(src, dest)

# Iterate over the files and extract the identifier from the file name
for filename in files:
    search_po = re.findall("(?=(\d{6}))", filename)

    for po in search_po: #-->from here do something with each single file
        sql = text(f"SELECT Directory FROM table_name WHERE Directory LIKE '%{po}%'")
        engine.execute (sql)
        result = str(engine.execute(sql).fetchall()[0][0])
        try:
   
 
            for record in result:
                    filetomove = (source_folder   "//"   filename)
                     # Check if the file already exists in the destination folder
                    if os.path.exists(result   "//"   filename):
                        # Generate a new file name by appending a number to the end of the original file name
                        i = 1
                        while True:
                            new_filename = f"{filename} ({i})"
                            if not os.path.exists(result   "//"   new_filename):
                                break
                            i  = 1
                        # Rename the file
                        os.rename(filetomove, source_folder   "//"   new_filename)
                        filetomove = (source_folder   "//"   new_filename)
                    # Move the file to the destination folder
                    shutil.move(filetomove, result)
                    break  # exit the loop

    #                print ("File to move with path:     "   filetomove)
    #                print("Path to the final Directory:"   result)
    #                print ("Found PO in the Filename:   "   po)
    #                print("The Filename:               "   filename)
    #                print ("------------------------------------------------------------------------------------------------")
        except: 
            pass