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.

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.

Output

Connection to the 127.0.0.1 for user root created successfully.

Explanation:

• def get_connection(): defines a function that will create the PostgreSQL connection engine.
• create_engine(f"postgresql://{user}:{password}@{host}:{port}/{database}"): creates the SQLAlchemy engine using the PostgreSQL dialect.
• engine = get_connection(): calls the function to create the database connection.
• except Exception as ex: catches errors and prints the error message if the connection fails.