Using SQL in Jupyter via SqlAlchemy ORM

Using the ORM( Object Relational Mappers) for various databases used in this notebook, some of them are:

Sqlite

MySQL

Oracle

PostgreSQL

MongoDB

Neo4j

Sqlite

Version Check

Connecting to Sqlite DB engine

Declaring a Mapping

Creating a Schema

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):
    
"""
+ Class needs min of one attribute, here it's __tablename__.

+ And a min of one "Coloumn" which is a part of a "primary key".

"""    
     __tablename__ = 'users'   

     id = Column(Integer, primary_key=True)  # and 
     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)

Note:

SQLAlchemy never makes any assumptions by itself about the table to which a class refers,

Including that it has no built-in conventions for,
- names,
- datatypes,
- or constraints

When our class is constructed, two things happen:
1. Declarative replaces all the Column objects with special Python accessors called descriptors.
  - This process is called -- "Instrumentation".
2. The “instrumented” mapped class will provide us,
  - means to refer to our table in a SQL context,
  - As well as to persist and load the values of columns from the database.

Outside of what the mapping process does to our class, the class remains otherwise mostly a normal Python class, to which we can define any number of ordinary attributes and methods needed by our application.

Creating a Schema

With our User class constructed, via the Declarative system, let's define information about our table, known as Table metadata. The object used by SQLAlchemy to represent this information for a specific table is called the Table object, and here Declarative has made one for us.

Let's inspect what our object User using the __table__ attribute.



In [12]:

    
User.__table__