Simeon Simeonov
free software - free as in "freedom" (MIT licensed)
portability - the programming interface is independent of the type of RDBMS and connector used
security - no more SQL injections
abstraction - no need to bother with complex JOINs
object-orientation - you work with objects and NOT tables and rows
performance - exploits the likehood of reusing a particular query
flexibility - you can override almost anything
0.0125 karma points for each MB transferred (2021)
SQLAlchemy consists of several components, including the the ORM.
+SQLAlchemy consists of several components, including the ORM.
- from sqlalchemy.orm import mapper
- from sqlalchemy.orm import relationship, backref
+ from sqlalchemy.orm import backref, mapper, relation
class Author:
def __init__(self, name):
@@ -198,7 +198,7 @@
class Author(Base):
__tablename__ = "authors"
- id = Column(Integer, primary_key=True)
+ author_id = Column(Integer, primary_key=True)
name = Column(String)
def __init__(self, name):
@@ -211,10 +211,10 @@
class Book(Base):
__tablename__ = "books" # self.__table__ will be available for our objects
- id = Column(Integer, primary_key=True)
+ book_id = Column(Integer, primary_key=True)
title = Column(String)
description = Column(String)
- author_id = Column(Integer, ForeignKey("authors.id"))
+ author_id = Column(Integer, ForeignKey("authors.author_id"))
author = relationship(Author, backref=backref("books", order_by=title))
def __init__(self, title, description, author):
@@ -225,7 +225,7 @@
def __str__(self):
return self.title
- Base.metadata.create_all(engine)# create tables
+ Base.metadata.create_all(engine) # create tables
- session.query(Book).order_by(Book.id) # returns a Query instance with a .statement attribute
- session.query(Book).order_by(Book.id).all() # returns an object-list
+ session.query(Book).order_by(Book.book_id) # returns a Query instance with a .statement attribute
+ session.query(Book).order_by(Book.book_id).all() # returns an object-list
# return all book objects where title == "The Selfish Gene"
- session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.id).all()
+ session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.book_id).all()
# using LIKE
- session.query(Book).filter(Book.title.like("The%")).order_by(Book.id).all()
+ session.query(Book).filter(Book.title.like("The%")).order_by(Book.book_id).all()
- query = session.query(Book).filter(Book.id == 9).order_by(Book.id)
- query.count() # returns 0L
+ query = session.query(Book).filter(Book.book_id == 9).order_by(Book.book_id)
+ query.count() # returns 0
query.all() # returns an empty list
query.first() # returns None
query.one() # raises NoResultFound exception
- query = session.query(Book).filter(Book.id == 1).order_by(Book.id)
+ query = session.query(Book).filter(Book.book_id == 1).order_by(Book.book_id)
book_1 = query.one()
book_1.description # returns "A popular science book"
book_1.author.books # returns a list of Book-objects representing all the books from the same author.
# get a list of all Book-instances where the author"s name is "Richard Dawkins"
- session.query(Book).filter(Book.author_id == Author.id).filter(Author.name == "Richard Dawkins").all()
+ session.query(Book).filter(Book.author_id == Author.author_id).filter(Author.name == "Richard Dawkins").all()
session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
session.query(Book).\
- from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.id AND a.name=:name").\
+ from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.author_id AND a.name=:name").\
params(name="Richard Dawkins").all()
https://gitlab.fifty.eu/odin/data-science/odin-data-access
-Provides the odin_data_access module with some of the following entities:
-The DBTableAccessor:
-
+
+ import pandas as pd
-
- Demo
- Now watch this well-rehearsed demo...
+ from sqlalchemy import func
+
+ class Book(Base):
+ # ...
+ author = relationship(
+ Author, backref=backref("books", lazy="dynamic", order_by=title)
+ )
+ # ...
+
+ @hybrid_property
+ def newly_arrived(self):
+ return self.book_id > 2
+
+ @newly_arrived.expression
+ def newly_arrived(cls):
+ return cls.book_id > 2
+ # return func.abs(cls.book_id) > 2
+
+ # .books is now a Query object
+ query = author_obj.books.filter(Book.title.ilike("%red%"))
+
+ session.query(Book).filter(Book.newly_arrived.is_(True)).all()
+ # Out: [<__main__.Book at 0x7f132bdf0130>]
+ # WHERE (abs(books.book_id) > ?) IS 1 ... in the case of func.abs
+
+ df = pd.read_sql_table("my_table", con=session.get_bind()) # or con=engine
+
+ df = pd.read_sql_query(query.statement, engine)
+
+
+