Python doesn’t create dirs and files in AppData\Local directory of the user

I use sqlite with python program. To create and locate the db file I do this

home_directory = os.path.expanduser( '~' )
os.mkdir(home_directory+"\AppData\Local\\myprog")
db_url="sqlite:///"+home_directory+"\AppData\Local\\myprog\db1"

The db1 file is created but not where I expect it to be. Instead it is created in
C:\Users\33604\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.11_qbz5n2kfra8p0\LocalCache\Local

How can I prevent this behavior to have my dir and file where I want them to be ?

You could use os.environ(‘USERPROFILE’) as you home dir.
I think that is right env var, you check using set in a cmd terminal.

I am not sure I understand what you mean. I rewrote the code like this

home_directory = os.environ['USERPROFILE’]
os.mkdir(home_directory+"\AppData\Local\\myprog2")
db_url="sqlite:///"+home_directory+"\AppData\Local\\myprog2\db1"


The result is exacly the same.

I forgot to say that I am running the application from VSCode in a virtual env. What I notice now is that if I run the exe produced by pyinstaller, the folder and db are created at the right place i.e. AppData\Local.
Consequently, I consider this as solved. Thank you for your help.

Where did you expect it to be instead, and why? When you specify a path with that special sqlite:/// prefix, why do you suppose this is necessary, and what effect do you expect it to have?

My last post is timedated at 15h and yours is timedated à 15 h and I didn’t see it.
I probably read I need to use this prefix somewhere but I confess I don’t exactly understand why. I am mainly a linux user and very new to the windows system.
I expected it to be in AppData\Local under my user’s directory.
It will be kind of you to teach me.
I read here that in python i can use the linux style for a path but I am a bit confused with the way to create a sqlite db url

You don’t have to. sqlite3 — DB-API 2.0 interface for SQLite databases — Python 3.12.0 documentation The default in connect is uri=False.

SQLite is not a server you are contacting across the Internet. It is just some code in your program that accesses a file, so the scheme part of the URI, if you use one, is file.

1 Like

Most of the backslashes in this are wrong. For a file name, they should be double everywhere, or will be interpreted as escapes by the rules of string constants. However, in a URI, I’m fairly sure they would have to be forward-slashes.

pathlib.Path is useful for platform independent file path concatenation.

@Jeff Allen
I use sqlachemy and do not have a connect method.

to create the db I use these lines of code:

print ("The db_url is "+db_url)

engine = create_engine(db_url)#,pool_size=5,pool_recycle=3600)

Session =sessionmaker(bind=engine,expire_on_commit=False)

session =Session()

what is printed is

The db_url is sqlite:///C:\Users\33604\AppData\Local\myprog2\db1

and it seems to me that it is correct

I tried to use forward slashes but it didn’t work, may be because what is returned as home_directory contains already back slashes

I also tried at the beginning

home_directory = os.path.expanduser( '~' )
print("Home dir is "+home_directory)

wich returns

Home dir is C:\Users\33604

How to explain the behavior is different with the pyinstaller created application (creation at the intended place while elsewhere calling the script from VSCode)?

Hi Albéric,

Together with extracts from the sqlalchemy quickstart, I was able to get your code working correctly in a cmd terminal embedded in VS Code, on Windows 11, using an official Python 3.11 build from Python Releases for Windows | Python.org (see script below, and output). I imported Session instead of using sessionmaker, but otherwise haven’t even changed the strings of your file paths to raw strings (which you really ought to).

If my example doesn’t work for you, perhaps the trouble you’re having is due to one of the many pecularities of the particular Python build installed via the Windows app store (or the one VS Code sometimes installs for itself)? I know from my recent fresh install, Microsoft have tried really hard to make Windows 11 a walled garden (it’s almost as restrictive as Apple used to be). I had to adjust my settings to Developer Mode, (and even my registry?) in order to install software outside of the Windows app store, clicking past some warnings. I still strongly recommend doing so, uninstalling the Windows App store Python, and installing an official python.org Windows build. This gets you the py Windows launcher too, which is great for switching versions (and less to type).

(sqlalchemy) C:\Users\...\Coding\venvs\sqlalchemy>py Albéric_create_db.py
The db_url is sqlite:///C:\Users\...\AppData\Local\myprog\db1

(sqlalchemy) C:\Users\...\Coding\venvs\sqlalchemy>dir C:\Users\...\AppData\Local\myprog
 Volume in drive C is Windows-SSD
 Volume Serial Number is 128F-28BF

 Directory of C:\Users\...\AppData\Local\myprog

20/08/2023  16:16    <DIR>          .
20/08/2023  15:54    <DIR>          ..
20/08/2023  16:16             8,192 db1
import os

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

home_directory = os.path.expanduser( '~' )
os.makedirs(home_directory+"\AppData\Local\\myprog", exist_ok = True)
db_url="sqlite:///"+home_directory+"\AppData\Local\\myprog\db1"

print ("The db_url is "+db_url)

engine = create_engine(db_url)#,pool_size=5,pool_recycle=3600)



from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user_account"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[Optional[str]]


    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"
    
Base.metadata.create_all(engine)

with Session(engine) as session:
    spongebob = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
    )

    patrick = User(name="patrick", fullname="Patrick Star")

    session.add_all([spongebob, patrick])

    session.commit()

Thank you very much for your precious help. I was sure I installed python from where you point.
Anyway I uninstalled it, downloaded it from your link and reinstalled. I also made a new venv and my program too is working perfectly.
BTW how to see if a Python install comes from the Windows App Store instead of python.org?

You’re welcome. I don’t know the best way to tell that, as the official installer picks a different default installation dir depending on which version of Python it’s for, and if it’s a user only or system wide install. I’d check if I had py installed, and then look in sys.path, for any entries that look very long or ‘microsofty’.

Ok, that’s different. I used that recently myself. Abridged, my code (just following the tutorial) ran:

# Where the databases are stored (a directory)
DATA_DIR = "data"

class Thing:
 
   def __init__(self):
        # Create database (not :memory: as that is thread-local)
        dbpath = "sqlite+pysqlite:///" + self._db_path()
        self.engine = sqlalchemy.create_engine(dbpath, echo=False)

    def _db_path(self):
        """Path to database file"""
        os.makedirs(DATA_DIR, exist_ok=True)
        fname = self.__class__.__name__ + ".db"
        return os.path.join(DATA_DIR, fname)

In my case, I had lots of Thing sub-classes, each class having its own database and it was all busy with threads. No need to copy that.

os.path.join is better than hard-coding the path separator.

@everybody

I eventually switched to pathlib instead of os.path

I use this

from pathlib import Path
home_path=Path().home()
p=home_path/"AppData/Local/myprog"
p.mkdir(mode=0o777, parents=True, exist_ok=True) 
if sys.platform.startswith("linux"):
    db_url="sqlite:///"+str(home_path/".myprog/db5")
else:
    db_url="sqlite:///"+str(home_path/"AppData/Local/myprog/db5")