Using SQL in Jupyter via DB ORMs

ORM( Object Relation Mappers) for various databases used in this notebook:

Sqlite
MySQL
Oracle
PostgreSQL

Sqlite

Version Check

Connecting to Sqlite DB engine

Declaring a Mapping

Version Check

A quick check to verify the version we are going to use.



In [7]:

    
# importing the sqlalechemy ORM( object realtional mapper )
import sqlalchemy

Connecting

We will use an in-memory-only SQLite database. To connect we use create_engine():

Some miniscules:

The return value of create_engine() is an instance of Engine,
- represents the core interface to the database,
- adapted through a dialect that handles the details of the database and DBAPI in use.
- In this case the SQLite dialect will interpret instructions to the Python built-in sqlite3 module.

The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the database, which is then used to emit the SQL

Note: When using the ORM, we typically don’t use the Engine directly once created; instead, it’s used behind the scenes by the ORM as we’ll see shortly.



In [5]:

    
# import create_engine function for connecting to the ORM engine
from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=False) # set echo to false for less output

Declaring a Mapping

When using the ORM, the configurational process starts by,

describing the database tables we’ll be dealing with.

then by defining our own classes which will be mapped to those tables.

In modern SQLAlchemy, these two tasks are usually performed together, using a system known as Declarative, which allows us to create classes that include directives to describe the actual database table they will be mapped to.

Classes mapped using the Declarative system, are defined in terms of a base class which,

Maintains a "catalog" of classes and tables relative to that base,
- known as Declarative base class.

Our demo application will usually have just one instance of this base in a commonly imported module. We create the base class using the declarative_base() function, as follows:



In [8]:

    
from sqlalchemy.ext.declarative import declarative_base

# Initialize Base
Base = declarative_base()

Now that we have a 'Base', we can define any number of mapped classes in terms of it.

Let's start with a single "Table" called - "User", which will store records for the end-users using our application.

A new class called User will be the class to which we map this table.

Within the class, we define details about the table to which we’ll be mapping,
- primarily the table name, and names and datatypes of columns:

GO to top: TOC



In [11]:

    
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)

Using SQL in Jupyter via DB ORMs

Sqlite

Table of Contents

Version Check

Connecting

Declaring a Mapping