0

I'm trying to create a database engine from a connection string using sqlite3 and sqlalchemy but am running into issues.

My particular details are that I want to use an absolute filepath from the os module in the connection string (this makes it easier when I deploy it only python anywhere) and am currently working on windows.

The most viewed answer to this question can be found here: OperationalError: (sqlite3.OperationalError) unable to open database file but its answers did not work for me.

Here is my current directory structure:

 /db
   -- assets.db
 /app
   -- load_engine.py
   -- models.py

The idea is that I want to initialize my sqlalchemy connection from a function in the load_engine file, but reference the file path to /db/assets.db into it.

This is the function inside load_engine:

def load_engine():
    
    db_file = os.environ.get('database_file')
    
    script_path = os.path.realpath('__file__')
    parent_path = os.path.dirname(script_path)
    db_path = os.path.join(os.path.sep,parent_path, "db", db_file)
    
    engine = create_engine(fr"sqlite:///{db_path}")
    return engine

I'm trying to invoke this function in the models.py file in the following way:

Base = declarative_base()

class Assets(Base):
    """Main table that will hold the list of ticker names"""
    __tablename__ = 'assets'
    
    id     = Column(Integer, primary_key = True)
    ticker = Column(String(250), nullable = False, unique = True)
    type   = Column(String(100), nullable = False)

class BarData(Base):
    """Will hold daily bar data from yahoo finance"""
    __tablename__ = 'bardata'
    
    id        = Column(Integer, primary_key = True)
    ticker    = Column(String(250), ForeignKey("assets.ticker"), nullable = False)
    open      = Column(Float, nullable = False)
    high      = Column(Float, nullable = False)
    low       = Column(Float, nullable = False)
    close     = Column(Float, nullable = False)
    volume    = Column(Float, nullable = False)
    dividends = Column(Float, nullable = False)
    splits    = Column(Float, nullable = True)
    
    
if __name__ == '__main__':
    engine = load_engine()
    Base.metadata.create_all(engine)

And when I run this I get the error, presumably because my filepath is wrong. I've tried deleting the assets.db file to see if that changed, but it didn't work. I also tried using 4 forward slashes vs. 3, but also got the same message.

If I reference a file that's in the same directory it works fine, so I would assume the issue is the filepath.

EDIT:

Per comments, output of engine is the following:

Engine(sqlite:////C:\Users\Jonat\trading\app\db\assets.db)
Jonathan Bechtel
  • 3,497
  • 4
  • 43
  • 73
  • So what does `db_path` actually contain after you've constructed it? – Gord Thompson Nov 14 '21 at 00:28
  • better check what you really have in path. Char \ has special meaning in string (even in path) and \t` in \trading can be treated as tab, The same with \Uxxx in \Users which is for unicode. You could also check if it should have four / in sqlite://// - maybe it should be only three / - and then you shouldn't use os.path.sep in os.path.join – furas Nov 14 '21 at 20:03
  • 1
    after checking some source I think all problem is that you have four / in `sqlite://// `. It should be `three / + absolute_path` but in Windows absolute path starts with `C:\ `, not with / , but you use `/C:\ `. On Linux absolute path starts with / so finally it looks like four / - Windows `"sqlite:///" + "C:\Users\Jonat\..."`, Linux/Unix/MacOS `"sqlite:///" + "/home/Jonat/..."` – furas Nov 14 '21 at 20:05

0 Answers0