Connecting to SQL Database using SQLAlchemy in Python

SQL databases can be connected in Python by creating a database engine using SQLAlchemy. The connection is defined through a URL that includes the database type, credentials, host, port and database name. Using this method, connections can be established with databases such as MySQL and PostgreSQL.

Installation

Before creating a database connection, SQLAlchemy package must be installed in the Python environment. If not installed, install it using below command:

pip install sqlalchemy

Creating a Database Connection

SQLAlchemy creates database connections using create_engine() function. This function takes a connection URL and returns an engine object, which manages the connection between Python and the database.

Syntax:

sqlalchemy.create_engine(url, **kwargs)

URL Format: dialect+driver://username:password@host:port/database

Components:

dialect: database type (mysql, postgresql, sqlite)
driver: database connector library (pymysql, psycopg2)
username/password: login credentials
host/port: database server location
database: database name

Connecting to MySQL Database

The following example demonstrates how to create a connection to a MySQL database using SQLAlchemy. The code defines database credentials, creates a connection URL and generates an engine object that represents the connection.

Python

from sqlalchemy import create_engine

user = "root"
password = "password"
host = "127.0.0.1"
port = 3306
database = "GeeksForGeeks"

def get_connection():
    engine = create_engine(
        f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"
    )
    return engine

if __name__ == "__main__":

    try:
        engine = get_connection()
        print(f"Connection to the {host} for user {user} created successfully.")

    except Exception as ex:
        print("Connection could not be made due to the following error:\n", ex)

Output

Connection to the 127.0.0.1 for user root created successfully.

Explanation:

user, password, host, port, database: Stores the MySQL database credentials and server details required to create the connection.
def get_connection(): Defines a function that will create and return the database engine.
create_engine(f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}"): creates the SQLAlchemy engine using the MySQL dialect and pymysql driver.
engine = get_connection(): calls the function to generate the database connection engine.
except Exception as ex: handles errors and prints the reason if the connection fails.

Note: In these examples, database credentials are written directly in the code for simplicity. In production environments, credentials should not be hardcoded and are usually stored securely using environment variables, configuration files, or secret managers.

Connecting to PostgreSQL Database

The next example shows how to connect to a PostgreSQL database using SQLAlchemy. The process is similar to MySQL, but the connection URL uses the PostgreSQL dialect and default PostgreSQL port.