I have a folder structure like

C:Userschale
├── sql-files
│   ├── source1.sql
│   └── source2.sql
├── python-files
│   ├── source1.py
│   └── source2.py
├── extract-files
│   ├── source1.csv
│   └── source2.csv

I am trying to write a generic way to open the corresponding sql file based on the python file that I am running, and then I perform some pandas data transformations on my sql extracted data and write it to extract-files, again with the same name as the python file running.

I cannot seem to target the correct directory though to open the sql file.

import os
from pathlib import Path

# get the current path of the python file
fullPath = Path(os.path.dirname(__file__))
# create a list of the parts of the current absolute path
fullPathSplit= os.path.dirname(__file__).split('\')
# get name of the current file, and isolate the name from the file type
fileName = os.path.basename(__file__).split('.')[0]
print(os.path.join(*fullPathSplit[:len(fullPathSplit)-1], 'sql-files', fileName+'.sql'))
# open the corresponding sql file to read
file = open(os.path.join(*fullPathSplit[:len(fullPathSplit)-1], 'sql-files', fileName+'.sql'), 'r')

The output of my print statement above is
c:Userschalesql-filessource1.sql

but I get an error from the open statement

Traceback (most recent call last):
  File "c:Userschale\python-filessource1.py", line 14, in <module>
    file = open(os.path.join(*destinationTable[:len(destinationTable)-1], 'sql-files', fileName+'.sql'), 'r')
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
FileNotFoundError: [Errno 2] No such file or directory: 'c:Users\chale\sql-files\source1.sql'

The file I am trying to open, opens fine when I hard code the printed value with a slash between c: and Users as a raw string to open

file = open(r'C:Userschalesql-filessource1.sql', 'r')

I have it working using the below code which strips out the c: from the fullPathSplit and then I manually add it in with a slash to join, but I do not understand why it does not join normally.

pathToOpen = os.path.join('C:\', *fullPathSplit[1:len(fullPathSplit)-1], 'sql-files', fileName+'.sql')
file = open(pathToOpen, 'r')

I don’t understand what discussed in the documentation here which I think is the issue. Why do I get a relative path (like c:foo) from my os.path.join? Or maybe my question is, can I configure open to take a relative path like c:foo rather than needing the absolute path? https://docs.python.org/3/library/os.path.html#os.path.join
"On Windows, the drive is not reset when a rooted path segment (e.g., r'foo') is encountered. If a segment is on a different drive or is an absolute path, all previous segments are ignored and the drive is reset. Note that since there is a current directory for each drive, os.path.join("c:", "foo") represents a path relative to the current directory on drive C: (c:foo), not c:foo."