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 ?
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
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.
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)?
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’.