I have a folder structure like
C:\Users\chale\
├── 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:Users\chale\sql-files\source1.sql
but I get an error from the open statement
Traceback (most recent call last):
File "c:\Users\chale\\python-files\source1.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:\Users\chale\sql-files\source1.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."
C:xxx and C:\xxx have two different meanings: "path relative to current directory of drive C:" vs. "absolute path from root of drive C:". That's why you explicitly have to add the backslash.
Stick to Path methods and things are more simple:
import os
from pathlib import Path
script_path = Path(__file__)
root = script_path.parent.parent
sql_path = root / 'sql-files' / (script_path.stem + '.sql')
extract_path = root / 'extract-files' / (script_path.stem + '.csv')
print(f'{script_path=}')
print(f'{sql_path=}')
print(f'{extract_path=}')
Output for source1.py:
script_path=WindowsPath('C:/Users/chale/python-files/source1.py')
sql_path=WindowsPath('C:/Users/chale/sql-files/source1.sql')
extract_path=WindowsPath('C:/Users/chale/extract-files/source1.csv')