SQL equivalent in Python

Hello Community,

I’m trying to write a SQL function that is equivalent to following Python function. Can someone help me with the code to in SQL?

def rename_file(origin_path, dest_path, file_type, new_name):
  filelist = dbutils.fs.ls(origin_path)#list all files from origin path
  filtered_filelist = [x.name for x in filelist if x.name.endswith("."+file_type)]#keep names of the files that match the type requested
  if len(filtered_filelist) > 1:#check if we have more than 1 files of that type
    print("Too many "+file_type+" files. You will need a different implementation")
  elif len(filtered_filelist) == 0: #check if there are no files of that type
    print("No "+file_type+" files found")
  else:
    dbutils.fs.mv(origin_path+"/"+filtered_filelist[0], dest_path+"/"+new_name+"."+file_type)#move the file to a new path (can be the same) changing the name in the process

Thanks

Carlton

Why? SQL is not a tool you use to move and manage files on your filesystem. What part of Dataquest course is this part of ?

This sort of stuff you would just put into a very simple script, be that python, bash, batch or powershell for example.

Hi Rob,

Thanks for reaching out. The question is more curiosity than anything else.

Regards

You could enable SQL CLR ( https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration) to try and achieve the goal in SQL. If you are after a personal challenge try that route. I’ve actually never needed to use it before.

But honestly, in an enterprise environment you’d get frowned at for looking at such a possible solution because it’s just too time consuming and open to issues.
If you already use SSIS in your environment you could use this if memory serves.