Using SQLAlchemy in Deepnote

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access. Here, we'll cover the basics of SQLAlchemy, including setting up a connection, creating database-backed classes, adding and querying data, and establishing relationships between tables.

Set Up SQLAlchemy

To start using SQLAlchemy, you need to install it and import the necessary modules. First, establish a connection to your database. For simplicity, we'll use an in-memory SQLite database in this example.

import sqlalchemy
from sqlalchemy import create_engine

# Connect to the database
engine = create_engine('sqlite:///:memory:', echo=False)

Create Database-Backed Classes

SQLAlchemy's ORM uses Python classes to represent database tables. These classes inherit from a base class provided by SQLAlchemy.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Define Table Attributes

In SQLAlchemy, table columns are defined with specific data types and constraints such as primary keys or foreign keys.

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
                self.name, self.fullname, self.password)

After defining the classes, create the tables in the database.

Base.metadata.create_all(engine)

Creat Session

Sessions are used to interact with the database. They handle the context for conversations with the database.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

Adding, Updating, and Committing Objects

Once the tables are set up, you can start adding objects to the database.

ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
session.add(ed_user)
session.commit()

You can query the database to retrieve objects, which will include any uncommitted changes.

our_user = session.query(User).filter_by(name='ed').first()
print(our_user)

Multiple objects can be added, modified, and then committed together.

session.add_all([
    User(name='wendy', fullname='Wendy Williams', password='foobar'),
    User(name='mary', fullname='Mary Contrary', password='xxg527'),
    User(name='fred', fullname='Fred Flinstone', password='blah')
])

ed_user.password = 'newpassword'
session.commit()

Roll Back Transactions

SQLAlchemy supports rolling back transactions to undo changes.

ed_user.name = 'Edwardo'
session.rollback()

Querying

You can query the database with various filtering options.

for user in session.query(User).filter(User.name == 'ed'):
    print(user)

Common filtering operators include equals (==), not equals (!=), LIKE, IN, NOT IN, AND, and OR.

Establishing Relationships

Relationships between tables can be established using foreign keys and relationship properties.

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="addresses")

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

User.addresses = relationship("Address", order_by=Address.id, back_populates="user")
Base.metadata.create_all(engine)

Adding related objects is straightforward.

jack = User(name='jack', fullname='Jack Bean', password='gjffdd')
jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]
session.add(jack)
session.commit()

Introduction to Deepnote Notebooks

Deepnote is a collaborative data science notebook that integrates seamlessly with SQLAlchemy, making it easier to manage, query, and visualize your data directly within an interactive notebook environment. Here’s how you can use SQLAlchemy within a Deepnote notebook:

Setup: Start by installing SQLAlchemy in your Deepnote notebook.

!pip install sqlalchemy

Database Connection: Use the same SQLAlchemy connection setup as described earlier.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:', echo=False)

Defining Models: Define your ORM models directly in the notebook cells.

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

Base.metadata.create_all(engine)

Creating Sessions: Create and use sessions to add and query data.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

Data Manipulation: Add, update, and query your data using standard SQLAlchemy commands.

new_user = User(name='deepnote_user', fullname='Deepnote User', password='password123')
session.add(new_user)
session.commit()

By leveraging Deepnote’s interactive environment and SQLAlchemy’s robust ORM capabilities, you can efficiently manage your data workflows, collaborate with team members, and visualize results in real time.

Conclusion

SQLAlchemy provides a comprehensive suite for managing database interactions in Python, from defining schema through ORM models to performing complex queries and handling transactions. When combined with Deepnote notebooks, it creates a powerful tool for collaborative data science and database management, making data manipulation and visualization seamless and interactive. If you encounter any issues, please get in touch with our support. Happy coding in Deepnote!