Welcome to SQLAlchemy

- Yash Mehrotra

About Me

  • 3rd Year Engineering Student
  • Mozilla Contributor
  • Open Source Evangelist
  • Data Science Wannabe

RoadMap

  • What is SQLAlchemy
  • Intro to ORMs
  • How does SQLAlchemy work
  • Creating and migrating a database with SQLAlchemy
  • CRUD Operations with SQLAlchemy.
  • Why SQLAlchemy
  • Questions by Audience

What is SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL.

 

It provides a full suite of well known enterprise-level persistence patterns, designed for efficient and high-performing database access, adapted into a simple and Pythonic domain language.

What it means

If you use a Relational Database and you are writing raw SQL Queries, it's time for a change.

What is an ORM?

ORM stands for Object Relational Mapper.

 

It is basically a wrapper for dealing with database models and provides an interface that can be used in your code for easy manipulation/processing of data. 

 

How does SQLAlchemy Work?

SQLAlchemy extracts data from the core level and we would then interact with it's API to manipulate data.

GETTING STARTED

Installation

pip install SQLAlchemy

No Extra Libraries, just one simple command

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import sessionmaker

# Initializing SQLAlchemy
engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
Base = declarative_base()

# Declaring a DB Class (model)
class User(Base):
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)
 
    def __repr__(self):
        return "<User(name='%s', email='%s')>" % (self.name, self.email)
 
 
# Creating Everthing
Base.metadata.create_all(engine)

CRUD OPERATIONS

# Creating Multiple Users Here for our User Column
user = [ User(name='Yash', email='yashmehrotra95@gmail.com'),
         User(name='Oliver', email='oliver@queenindustries.com'),
         User(name='Bruce', email='bruce@wayneenterprises.com') ]

# Staring a database session
session = Session()

# Bulk Insertion
# add_all is used for adding a list
session.add_all(user)
session.commit()

# Getting all users
query = session.query(User)
print 'All users so far:'
for row in query:
    print row.name, row.email

# Selecting through fields
# Updating a user through filtering
query = session.query(User).filter(User.email == 'yashmehrotra95@gmail.com')[0]
query.name = 'Yash Mehrotra'
session.add(query)
session.commit()

CRUD OPERATIONS

# Getting all users after previous update
query = session.query(User)

print '\nAll users after update:'
for row in query:
    print row.name, row.email

# Deleting a user through filtering
query = session.query(User).filter(User.email == 'yashmehrotra95@gmail.com')[0]

session.delete(query)

# Lets check our deletion
query = session.query(User)
print '\nAll users after delete:'
for row in query:
    print row.name, row.email
session.close()

Why SQLAlchemy

  • More Layers to interact with
  • Pythonic
  • Customizable
  • Consistent Syntax

Any Questions for me ?

twitter: @yashm95

github: /yashmehrotra

gmail: yashmehrotra95@gmail.com

LinkedInhttps://in.linkedin.com/in/yashmehrotra

Thank You

{% THE END %}

SQLAlchemy (PyCon 2015)

By Yash Mehrotra

SQLAlchemy (PyCon 2015)

  • 1,177