Overview

Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm.

Brings an abstraction level in communication with different databases, by wrapping DB actions in an idiomatic APIs.

Provides automation and DRY

Allow expression of DB/SQL tasks using declarative patterns

Analogy between Tables and Classes

			CREATE TABLE users (
				id serial primary key,
				name character varying,
				address character varying
			)
		

			class Users:
				def __init__(self, name, address):
					self.name = name
					self.address = address
		

SimpleCompanyDB - the Model

In next slides, in order to illustrate SQLAlchemy, I'll use a very simple model: SimpleCompanyDB

You can download the MySQL dump: SimpleCompanyDB_dump

SimpleCompanyDB - the data

DB engine used is MariaDB

Next sample data are pre-populated into the database:

			 root@localhost:SimpleCompanyDB> SELECT * from company;
			+--------------+----------------+
			|   company_id | company_name   |
			|--------------+----------------|
			|            1 | Google         |
			|            2 | Facebook       |
			|            4 | Microsoft      |
			|            5 | Microsoft      |
			+--------------+----------------+
			4 rows in set
			Time: 0.002s
			mariadb root@localhost:SimpleCompanyDB> SELECT * from employee;
			+---------------+-----------------+
			|   employee_id | employee_name   |
			|---------------+-----------------|
			|             1 | Ivan Petrov     |
			|             2 | Maria Popova    |
			|             3 | Georgi Ivanov   |
			+---------------+-----------------+
			3 rows in set
			Time: 0.005s
			mariadb root@localhost:SimpleCompanyDB> SELECT * from company_employee
			+---------------+--------------+
			|   employee_id |   company_id |
			|---------------+--------------|
			|             1 |            2 |
			|             2 |            1 |
			|             3 |            1 |
			|             3 |            2 |
			+---------------+--------------+
			4 rows in set
		

Overview

SQLAlchemy is the Python SQL Toolkit and ORM

Official site www.sqlalchemy.org

Overview

The SQLAlchemy flow:

SQLAlchemy Engines

An Engine allows SQLAlchemy to interact with a database

The typical usage of create_engine() is once per particular database URL, held globally for the lifetime of a single application process

			engine = create_engine(
				dialect+driver://username:password@host:port/database)
		

			from sqlalchemy import create_engine

			# connect to Mysql/MariaDB:
			mysql_engine = create_engine(
			  "mysql+pymysql://user:pass@localhost/test")

			# connect to PostgreSQL:
			pg_engine = create_engine('postgresql://usr:pass@localhost/sqlalchemy')
		

Supported Databases/Dialects

SQLAlchemy includes many Dialect implementations for various backends. Many other are available as external projects

List and more information about dialects can be found on: Dialects

Create an engine to SimpleCompanyDB

			from sqlalchemy import create_engine

			#Create engine with MySQL dialect:
			engine = create_engine(
			    'mysql+pymysql://pythontest:123@localhost/SimpleCompanyDB?charset=utf8',
			    connect_args = {
			        # 'port': 3306
			    },
			    # echo='debug',
			    echo_pool=True
			)

			# test the connection - get tables names:
			print("Tables in SimpleCompanyDB: ",engine.table_names())
			# Tables in SimpleCompanyDB:  ['company', 'company_employee', 'employee']
		

The Declarative system

SQLAlchemy uses 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 - this is known as the declarative base class.

			from sqlalchemy.ext.declarative import declarative_base

			Base = declarative_base()
		

SQLAlchemy Data Types

SQLAlchemy provides abstraction for various data types used in most RBMS.

Column and Data Types

			class Address(Base):
			    __tablename__ = 'address'
			    # define columns for the table address.
			    id = Column(Integer, primary_key=True)
			    street_name = Column(String(250))
			    street_number = Column(String(250))
			    post_code = Column(String(250), nullable=False)
			    person_id = Column(Integer, ForeignKey('person.id'))
			    person = relationship(Person)
		

The Table object

A table in a database is represented by an object of class Table

Create a table into SimpleCompanyDB

SQLAlchemy Relationships

SQLAlchemy supports four types of relationships: One To Many, Many To One, One To One, and Many To Many

Example of One To Many relationship:

			class Person(Base):
			    __tablename__ = 'person'
			    id = Column(Integer, primary_key=True)
			    pets = relationship("Pet")


			class Pet(Base):
			    __tablename__ = 'pet'
			    id = Column(Integer, primary_key=True)
			    person_id = Column(Integer, ForeignKey('person.id'))
		

Example of Many To Many relationship

			company_employee = Table('company_employee', Base.metadata,
			    Column('employee_id', Integer, ForeignKey('employee.employee_id')),
			    Column('company_id', Integer, ForeignKey('company.company_id'))
			)

			class Employee(Base):
			    __tablename__ = 'employee'
			    employee_id = Column(Integer, primary_key=True)
			    companies = relationship("Company", secondary=company_employee)

			class Company(Base):
			    __tablename__ = 'company'
			    id = Column(Integer, primary_key=True)
		

SQLAlchemy Sessions

Table

Sessions are used to guarantee the database consistency.

All modifications tracked by Sessions (Units of Works) will be applied to the underlying database together, or none of them will.

More on Sessions: Session Basics @docs.sqlalchemy.org

Overview

If a table already exists, SQLAlchemy can read its column definitions directly from the database.

This processes is called reflecting the table.

DataFrame from MySQL table

      import MySQLdb
      mysql_cn= MySQLdb.connect(host='myhost',
                      port=3306,user='myusername', passwd='mypassword',
                      db='information_schema')
      df_mysql = pd.read_sql('select * from VIEWS;', con=mysql_cn)
      print 'loaded dataframe from MySQL. records:', len(df_mysql)
      mysql_cn.close()
    

Readings

PEP 249 -- Python Database API Specification v2.0

Object Relational Tutoria @docs.sqlalchemy.org

Reflecting Database Objects

Python SQLAlchemy Cheatsheet