SQLAlchemy

glithan

Unlike ODBC and DSN, SQLAlchemy is a Python library for working with databases. It uses a database URL, a single string containing all the valuable information needed to connect to a database. Variables usually needed to connect to a database include server name, username, password, and database name (all in string format).

First, create a .env file with all of our sensitive information.

MAKE SURE TO INCLUDE ‘.env’ FILE IN .GITIGNORE

Example .env file:

login = "username"
password "1235"
dbname = "postgres"
host = "hostnameURL"
port = "5432"

Let’s call in the given database credentials with SQLAlchemy:

Import Libraries:

from sqlalchemy import create_engine
import pandas as pd
import os as os
from dotenv import load_dotenv
load_dotenv(".env")

Load Credentials:

user = os.environ['login']
password = os.environ['password']
dbname = os.environ['dbname']
host = os.environ['host']
port = os.environ['port']

Create SQL Engine:

engine = create_engine(
    (
        f"postgresql://{user}:"
        f"{password}"
        f"@{host}:{port}/{dbname}"
    )
)
connection = engine.connect()

pd.read_sql_query(SELECT * FROM krabby_patty_formula, connection)

We are now able to connect and access our remote database. So, we can access any schemas or table from our database such as the krabby_patty_formula above by using SQL Alchemy and Python.

Related Posts