Absolute paths in subprocess - file not found

[kittyticker]

I've tried every permutation I can think of to get rid of this message - eg variables, literals, back slashes, forward slashes etc etc (thinking that was the issue) - But looking at the error on the internet, it maybe something more environment related, which I don't understand - If anyone has the answer, please could you post it in beginner speak!

NB - Database exists - C:/Users/gonks/Desktop/python_work/db/VE_match
table exists in it - db_val_policy
input CSV exits - C:/Users/gonks/Desktop/python_work/csv_val_pol.csv

from pathlib import Path
import sqlite3
import subprocess
import csv

#csv_file = Path("C:/Users/gonks/Desktop/python_work/csv_val_pol.csv")
#db_name = Path("C:/Users/gonks/Desktop/python_work/db/VE_match")
csv_file = Path("C:\\Users\\gonks\\Desktop\\python_work\\csv_val_pol.csv")
db_name = Path("C:\\Users\\gonks\\Desktop\\python_work\\db\\VE_match")

result = subprocess.run(['sqlite3',
                         "C:\\Users\\gonks\\Desktop\\python_work\\db\\VE_match".replace('\\','\\\\'),
                         '-cmd',
                         '.mode csv',
                         '.import --skip 1 C:\\Users\\gonks\\Desktop\\python_work\\db\\VE_match'.replace('\\','\\\\'), 'db_val_policy'],
                        capture_output=True)

Error:
File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.11_3.11.2032.0_x64__qbz5n2kfra8p0\Lib\subprocess.py", line 1538, in _execute_child
    hp, ht, pid, tid = _winapi.CreateProcess(executable, args,
                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
FileNotFoundError: [WinError 2] The system cannot find the file specified
PS C:\Users\gonks>

[deanhystad]

Why are you using a subprocess? If you really plan to do your query as a subprocess why are you importing sqlite3?

What is sqlite3? According to the subprocess.run() command, this is the name of some executable that you want to run. I don't have a program named sqlite3.exe on my computer. Do you? If you have such a file, where is it? It needs to be on the windows PATH, or you need to provide a complete path.

[kittyticker]

Well if I tell you what I want to do, maybe you will have a better solution? (I was attempting a bulk load into a database) What I'm trying to achieve is to load circa 15 large CSV files (approx 150k rows * 30 columns) into a database (SQLITE) so that I can match/manipulate the data before writing it out. (The database could be considered temporary, in so far as it will be rebuilt before each run). The main issues I want to manage are efficient use of memory and performance

[deanhystad]

Are you using this?

https://www.sqlite.org/cli.html

If so, you'll need to provide the absolute path to the sqlite3.exe file.

This part makes no sense:

"C:\\Users\\gonks\\Desktop\\python_work\\db\\VE_match".replace('\\','\\\\')

I think you are trying to create a string that looks like this:

"C:\\Users\\gonks\\Desktop\\python_work\\db\\VE_match"

But you don't want to do that. A backslash is only the start of an escape sequence when it appears in a "string literal". You only ever have to use "\\" in code. Backslashes in a string are just backslashes.

The "import sqlite3" is confusing. You do not have to import sqlite3 to use sqlite3.exe. They are unrelated. sqlite3.exe is a windows program. "import sqlite3" imports a python module.

Mabye you were thinking of doing multiprocessing instead of using subprocess. Subprocess is used when Python wants to run an executable. Multiprocessing is used when you want your python program to use multiple processors. You would import sqlite3 if you wanted to start multiple processes in your python program that would use the sqlite3 module to create/modify a database.

Are your concerns about memory and speed real or imaginary? 150 thousand row CSV files sound big, but they really aren't. I just created one and used it to create 15 pandas dataframes using pandas.from_csv(). It only took 6 seconds.

Can you provide any information about what kind of processing you need to do to the data contained in these files? Maybe you don't need a database at all.

[kittyticker]

Thanks for having a look. (ignore the import SQL - it was simply cloned from an earlier template - I also know the slashes are all over the place, but I literally spent hours on every permutation I could think of)

The basic problem is that I have circa 15-20 CSV files that all have a common key and maybe 30 columns in each. There will also be "1 to many' relationships in the data, or legitimate unmatched keys. The output will just be a single text file that is pulling in various bits of data from the multiple CSVs

It seems a good idea to me to host all the data in a database (could be temporary) to allow the data to be joined easily and to cater for unmatched rows etc.

This is just a proof of concept and I'm not set on a database, but I fear that if its done through other means it will be a behemoth