What's the difference between 'create_engine' and 'app.config['SQLALCHEMY_DATABASE_URI']'?

Question

EDIT:

After doing some digging, I found out that both create_engine and SQLALCHEMY_DATABASE_URI serve the same purpose of connecting to a database.

create_engine is a method of SQLAlchemy that creates a new SQLAlchemy Engine object, which represents a source of connectivity to a database. It accepts a string parameter that specifies the database dialect and connection arguments to be used for establishing a connection to the database.

SQLALCHEMY_DATABASE_URI, on the other hand, is a configuration key in Flask-SQLAlchemy that specifies the URI used to connect to the database. When Flask-SQLAlchemy is initialized, it sets up the underlying SQLAlchemy Engine object based on the value of this key.

In the example I provided in my original post, create_engine is used to create an Engine object that is used to execute a raw SQL query directly. In the Flask app, SQLALCHEMY_DATABASE_URI is used to specify the URI used to connect to the database via Flask-SQLAlchemy. Flask-SQLAlchemy is then used to perform higher-level operations on the database, such as ORM-based queries, and these operations use the underlying Engine object created by Flask-SQLAlchemy based on the URI specified in SQLALCHEMY_DATABASE_URI.

================================================================

ORIGINAL POST:

The create_engine function and app.config['SQLALCHEMY_DATABASE_URI'] configuration key look like they do the same thing. Are they just 2 different ways to connect to a database?

I found this in the SQLAlchemy docs for connecting using create_engine:

from sqlalchemy import create_engine
from sqlalchemy import text

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost:5432/mydatabase")

with engine.connect() as connection:
    result = connection.execute(text("select username from users"))
    for row in result:
        print("username:", row["username"])

But the web app that I just completed connects this way:

app.py

from project import create_app

app = create_app()

if __name__ == '__main__':
    app.run()

init.py

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def create_app():
    app.config['SQLALCHEMY_DATABASE_URI'] = 
    'mysql+pymysql://username:password@server/db'

    db.init_app(app)
    
    with app.app_context():
        from .views import views
        from .auth import auth       
        from .models import users

        app.register_blueprint(views, url_prefix="/")
        app.register_blueprint(auth, url_prefix="/")

        db.create_all()

        return app

Your app uses `flask_sqlalchemy`, which includes `sqlalchemy`, but it's not the same. `flask_sqlalchemy` probably just takes care of initializing for you. If you were just using pure `sqlalchemy` (which is discussed in detail in the linked duplicate question), you would have to use `create_engine` because the `app.config` approach would not work. — palsch, Dec 12 '22 at 01:50

topsail · Answer 1 · 2022-12-09T13:57:56.003

your app config setting is just storing a string variable in an environment variable. This is useful for cases where the db connection checks environment variables to find connection strings (production deployments do this, for instance, in many cases). create_engine just happens to also take a connection string, which is why you see a similarity. But that is an in memory object to use with sqlalchemy in the application, not merely a string variable! Per the sqlalchemy docs, the most basic function of the Engine is to provide access to a Connection, which can then invoke SQL statements - see working with engines and connections

Indeed it looks like your web app handles the db connection by using the environment variable. To be honest, you must have two different ways of connecting to your database, one for "local development work" and one for "deployed web app".

What's the difference between 'create_engine' and 'app.config['SQLALCHEMY_DATABASE_URI']'?

1 Answers1