How to use Python to connect with a database?

Python is a popular programming language that is widely used in web development, scientific computing, and data analysis. One of the most significant updates to Python was the release of Python 3, which brought several improvements over its predecessor, Python 2. In this blog post, we will explore some of the nuances of Python3 and how it is used for object relational mapping. We will also introduce SQLAlchemy and Alembic modules and explain how to use Python3 to communicate with a database.

Object Relational Mapping (ORM) is a technique used to map database tables to objects in a programming language. The main goal of ORM is to provide a higher level of abstraction than raw SQL, making it easier to work with databases from within a programming language. Python3 has several ORM libraries, but the most popular one is SQLAlchemy.

SQLAlchemy is an open-source ORM library for Python3. It provides a set of high-level API for working with relational databases. SQLAlchemy supports a wide range of databases, including MySQL, PostgreSQL, SQLite, and Oracle. One of the unique features of SQLAlchemy is its support for Object-Relational Mapping (ORM) and SQL Expression Language (SQLAlchemy Core).

ORM allows developers to work with the database using Python objects instead of SQL. SQLAlchemy ORM provides a set of classes and methods that allow developers to interact with the database in a high-level way. For example, to create a new record in a table using SQLAlchemy ORM, you can create a new Python object, set its attributes, and call the add() method on a session object.

SQLAlchemy Core, on the other hand, provides a lower-level API for working with SQL. It allows developers to write SQL queries using Python code, which can be a more convenient way of working with SQL.

Alembic is another Python library that works alongside SQLAlchemy to provide database migrations. Database migrations are used to modify the structure of a database over time while preserving its data. Alembic provides a set of tools for generating and managing database migrations. It also integrates well with SQLAlchemy, allowing developers to easily create and execute database migrations using Python code.

To use Python3 with a database using SQLAlchemy and Alembic, you need to follow a few steps:

1. Install SQLAlchemy and Alembic modules using pip.

pip install sqlalchemy
pip install alembic

2. Create a Python script that connects to the database using SQLAlchemy.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# create an engine to connect to the database
engine = create_engine('postgresql://username:password@localhost/mydatabase')

# create a session factory
Session = sessionmaker(bind=engine)

# create a session object
session = Session()

3. Define your database tables using SQLAlchemy ORM.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

# create a base class for all your ORM classes
Base = declarative_base()

# define a user class
class User(Base):
    __tablename__ = 'users'

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

4. Create your database schema.

Base.metadata.create_all(engine)

5. Use SQLAlchemy ORM to interact with the database.

# create a new user
user = User(name='Medium User', age=30)
session.add(user)
session.commit()

# retrieve all users
users = session.query(User).all()

Here’s an intro on how to use Alembic to manage database migrations

Alembic is a Python library that provides a set of tools for generating and managing database migrations. Database migrations are used to modify the structure of a database over time while preserving its data. Alembic integrates well with SQLAlchemy, allowing developers to easily create and execute database migrations using Python code.

To use Alembic, you need to follow a few steps:

1. Initialize Alembic in your project directory.

alembic init alembic

This will create an alembic directory in your project directory containing several files and directories. The most important file is alembic.ini, which contains configuration options for Alembic.

2. Configure Alembic to connect to your database.

Open alembic.ini and modify the sqlalchemy.url option to connect to your database.

sqlalchemy.url = postgresql://username:password@localhost/mydatabase

3. Create a new migration

alembic revision -m "Add age column to users table"

This will create a new migration script in the alembic/versions directory. The migration script is a Python file that contains two functions: upgrade() and downgrade(). The upgrade() function is used to apply the migration, while the downgrade() function is used to undo the migration.

def upgrade():
    op.add_column('users', sa.Column('age', sa.Integer))

def downgrade():
    op.drop_column('users', 'age')

4. Apply the migration

alembic upgrade head

This will apply the migration to your database. The head parameter tells Alembic to apply all pending migrations.

5. Roll back the migration.

alembic downgrade -1

This will roll back the last migration. The -1 parameter tells Alembic to roll back one migration.

6. Create a new migration based on changes made to your models.

alembic revision --autogenerate -m "Add email column to users table"

This will create a new migration script based on changes made to your SQLAlchemy models. Alembic will compare the current state of your models to the previous state and generate the necessary migration code.

7. Apply the new migration.

alembic upgrade head

Using Alembic with SQLAlchemy allows you to manage database schema changes in a structured and consistent way. By following these steps, you can easily create and apply database migrations using Python code, making it easier to work with databases from within a programming language.