Database URIs are a pain

Note to self: When using SQLite with SQLAlchemy, the URI has three slashes after the colon, and that's not counting a leading slash.

I only post this because I've forgotten this at least three times and had to spend way too much time figuring it out.  A have a project that has a test SQLite database in the local project directory with a URI of sqlite:///../file.db.  And that's fine.  But then I forget and try to change it to an absolute path and can't figure out why sqlite:///path/to/file.db doesn't work.  But of course that's wrong: it's sqlite:////path/to/file.db with four slashes at the beginning - the last one being part of the actual path.  Hopefully this time I won't forget.

You can reply to this entry by leaving a comment below. You can send TrackBack pings to this URL. This entry accepts Pingbacks from other blogs. You can follow comments on this entry by subscribing to the RSS feed.

Comments #

    I wonder why I have the same issue but my solution is the exact opposite !

    Hi Peter Geer

    I had the same exact problem and it took me days to figure out as well. However, my problem with the URI is that I used four //// instead of three after the colon. As soon as I changed to 3 /// with an absolute path and it worked ! I wonder if you happen to know why ? So I wrote something like this

    app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///C:/Users/Giang/PyCharmProjects/FlaskWebBlog/FlaskWebBlog/site.db

    so that is the absolute path and I used three /// :(

    Also, can you explain why it is supposed to be sqlite:///../file.db with relative path ?
    Thank you. I would really appreciate your help ?

    Probably Linux vs. Windows

    Hello Giang!

    It looks to me like the difference between Windows and Linux. As I understand it, the scheme part of the URI is "sqlite:///" and the rest (at least for SQLite) is the path to the database file. I was deploying on a Linux server, so the absolute path starts with a "/" character. That gives me four slashes - three as part of the scheme and the fourth as the first character in the path. You're on Windows, so you only need the three slashes for the scheme because absolute paths start with a drive letter instead of a slash.

    As for the relative path question, that's just due to how I structured by project. The path of the database is relative the to application. I had a top-level project directory that contained the test database and the Python module was a subdirectory of that. So the database was actually one directory up the hierarchy from where the application actually lived, hence the need for the "../" in the path. If I'd put the database file inside the Python module, I wouldn't have needed that.

Add your comments #

A comment body is required. No HTML code allowed. URLs starting with http:// or ftp:// will be automatically converted to hyperlinks.