sqlalchemy,creating an sqlite database if it doesn't exist

Question

I am trying out sqlalchemy and i am using this connection string to connect to my databases

engine = create_engine('sqlite:///C:\\sqlitedbs\\database.db')

Does sqlalchemy create an sqlite database for you if one is not already present in a directory it was supposed to fetch the database file?.

score 43 · Accepted Answer · answered Apr 29 '13 at 18:06

43

Yes,sqlalchemy does create a database for you.I confirmed it on windows using this code

from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///C:\\sqlitedbs\\school.db', echo=True)
Base = declarative_base()


class School(Base):

    __tablename__ = "woot"

    id = Column(Integer, primary_key=True)
    name = Column(String)  


    def __init__(self, name):

        self.name = name    


Base.metadata.create_all(engine)

answered Apr 29 '13 at 18:06

Gandalf

12,622
27
90
148

2

so if the sqlite database is an existing file in that directory that you are pointing at, it will call it up instead? Ie: if it exist, connect to it, else, create it. Am I right to say that? – jake wong Mar 29 '16 at 15:17
2

Absolutely,i found that to be the case. – Gandalf May 04 '16 at 18:16
8

In case others are confused: the file is created during the `create_all(engine)` statement, not during the `create_engine(...)` statement. – dthor Aug 21 '18 at 21:45

score 10 · Answer 2 · answered Jun 05 '20 at 21:03

10

As others have posted, SQLAlchemy will do this automatically. I encountered this error, however, when I didn't use enough slashes!

I used SQLALCHEMY_DATABASE_URI="sqlite:///path/to/file.db" when I should have used four slashes: SQLALCHEMY_DATABASE_URI="sqlite:////path/to/file.db"

answered Jun 05 '20 at 21:03

dsummersl

6,228
47
64

1

This was my issue! – Eric Frechette Jun 16 '20 at 23:09

score 9 · Answer 3 · answered Jul 04 '20 at 18:39

9

Linux stored SQLite3 database

database will be create in the same folder as the .py file:

engine = create_engine('sqlite:///school.db', echo=True)

will instantiate the school.db file in the same folder as the .py file.

answered Jul 04 '20 at 18:39

Arthur Zennig

1,780
21
17

I need to call engine.connect() to get the db file actually created – Alan Feb 11 '22 at 17:07

score 7 · Answer 4 · answered Jun 06 '16 at 16:19

7

I found (using sqlite+pysqlite) that if the directory exists, it will create it, but if the directory does not exist it throws an exception:

OperationalError: (sqlite3.OperationalError) unable to open database file

My workaround is to do this, although it feels nasty:

    if connection_string.startswith('sqlite'):
        db_file = re.sub("sqlite.*:///", "", connection_string)
        os.makedirs(os.path.dirname(db_file), exist_ok=True)
    self.engine = sqlalchemy.create_engine(connection_string)

answered Jun 06 '16 at 16:19

danio

8,251
6
43
55

Right, in this case I would probably just print a message to the user that says, "Sorry, I can't create database FOO because directory BAR does not exist." Then, if the user wants to create it and proceed, they can. – Tom Barron Jul 02 '16 at 18:46
For an interactive app that's fine, but I also want this to work in a continuous integration context – danio Sep 23 '16 at 09:23
1

Once the directories exist, it will work in continuous integration. You have to decide whether the missing directory is an error and requires user intervention (in which case you generate a error message and give up) or whether a missing directory is just a warning or minor exception in which case you create (possibly a whole chain of) directories and carry on, which, as you say, is 'nasty'. Or you make it configurable... – Tom Barron Sep 26 '16 at 18:18
by "make it configurable", I mean provide a config setting where the user can say 'create any missing directories without bothering me about it' or 'let me know if directories are missing and I'll create them for you.' – Tom Barron Sep 26 '16 at 18:19

score 0 · Answer 5 · answered Mar 31 '22 at 00:00

@Gandolf's answer was good.

The database is created it when you make any connection with your engine.

Here's an example of doing nothing with a database besides connecting to it, and this will create the database.

from sqlalchemy import create_engine

engine = create_engine('sqlite:///database.db')

with engine.connect() as conn:
    pass

Without the engine.connect() or some form of metadata.create_all() the database will not be ceated.

sqlalchemy,creating an sqlite database if it doesn't exist

5 Answers5

Linked