SQL Database

1.2.1 intersection

1.2.2 natual join

1.2.3 theta join

1.2.4 devision

• Example:
  

  Table 1: R

  Student	Task
  Peter	Database1
  Peter	Database2
  Peter	Compiler1
  Sara	Database1
  Sara	Database2
  Mary	Database1
  Mary	Comipler1

  Table 2: S

  Task
  Database1
  Database2

  Table 3: R÷ S

  Student
  Peter
  Sara

1.2.5 aggregation

2.1.1 CREATE & DROP & ALTER

CREATE DATABASE dbname;
USE DATABASE dbname;
CREATE TABLE table_name (column_name TYPE Constrains, ... );
DESC table_name;
DROP TABLE;

ALTER TABLE table_name
ADD COLUMN columnName ...
ADD PRIMARY KEY (columnName)
RENAME TO tableNewName
CHANGE COLUMN columnOldName columnNewName TYPE ...
MODIFY COLUMN columnName TYPE...
DROP COLUMN columnName

2.1.2 INSERT & UPDATE & DELETE

INSERT INTO tableName [(columnName1, columnName2, ...)] VALUES ('value1', 'value2', ...);
UPDATE tableName SET columnName1 = 'value1', columnName2 = 'value2' WHERE expr;
DELETE FROM tableName WHERE expr;

2.1.3 NOT

2.1.4 SHOW

SHOW CREATE TABLE tableName;
SHOW COLUMNS FROM tableName;
SHOW INDEX FROM tableName;
SHOW WARNINGS;

UPDATE tableName SET columnName =
CASE
  WHEN column_1 = somevalue1
    THEN newValue;

2.1.5 GROUP BY

SELECT columnName1, columnName2 FROM tableName GROUP BY columnName2

• SUM, AVG, MAX, MIN, COUNT
  

  match with GROUP_BY

  E.g:SUM(columnName) … GROUP BY columnName

• HAVING
  

  The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. E.g: HAVING count(columnName) > 5

2.1.6 WITH

WITH temp_view_name(columnName...) as
     select statement
SELECT ...
FROM temp_view_name
WHERE ...

2.1.7 RECURSIVE

WITH RECURSIVE empl(employee_name, manager_name) as (
SELECT employee_name, manager_name
FROM manager
UNION
SELECT manager.employee_name,empl.manager_name
FROM manager, empl
WHERE manager.manager_name = empl.employee_name
)
SELECT *
FROM empl

2.1.8 GRANT & REVOKE

2.1.9 Other Keywords

2.3.1 Overview

2.3.2 Inner Join

2.4.1 Noncorrelated Subquery

2.4.2 Correlated Subquery

3.2.1 One to One

• When to use
  
  1. Allow you to write fast queries.
  2. If you have a column containing values you don't yet know.Iso NULL value.
  3. Make data less accessible.
  4. To store a large piece of data, like BLOB.

3.2.2 One to Many

3.2.3 Many to Many

3.3.1 Definition

3.3.2 Functional Dependency

• Partial Functional Dependency
  

  When a non-key column is dependent on some, but not all, of the composite PK.

• Transitive Functional Dependency
  

  When any non-key column is dependent on any of the other non-key columns.

3.3.3 Rules For FD

3.3.4 Closure of Attributes

3.4.1 Definition

3.4.2 Rules For MVD

4.4.1 Validation Example

6.3.1 Facts

6.3.2 Creation

CREATE TABLE tableName
(
    ...
    columnName TYPE NOT NULL,
    [CONSTRAINT constraint_name,]
    FOREIGN KEY (foreign_key_name)
    REFERENCES parent_tableName (parent_columnName)
)

7.2.1 Event-Condition-Action Rules

• syntax
  

  CREATE TRIGGER trigger_name
  [BEFORE|AFTER|INSTEAD OF] events
  ,[referencing-variables]
  [For Each Row]
  [when (condition)]
  ,action
  

• events
  

  INSERT ON T
  DELETE ON T
  UPDATE [OF C1,...,Cn] ON T
  

• [For Each Row]
  

  Determines whether the trigger is row-level or statement-level

  • referencing-variables

    DEPENDS ON [For Each Row]
    OLD row AS var
    NEW row AS var
    OLD table AS var
    NEW table AS var
    

  • condition

    In when or where clause depends on the SQL Implementation.

9.3.1 read uncommitted: written by an uncommitted transaction

9.3.2 read committed(nonrepeatable reads): an item read multiple times cannot change values

T1:    Update Student Set GPA=(1.1)*GPA

T2.S1: Select AVG(GPA) From Student
T2.S2: Select MAX(GPA) From Student

• phantoms
  

  T1:    Insert Into Student [100 new tuples]
  
  T2.S1: Select AVG(GPA) From Student
  T2.S2: Select MAX(GPA) From Student
  

  T2.S1 may excute before T1, T2.S2 may excute after T1. [100 new tuples] are known as the phantoms tuples.

9.3.3 Repeatable Read

9.3.4 Serializable

9.3.5 Summary

10.1.1 create_engine(…, echo=True)

10.1.2 Types

• Generic
  

  SQLAlchemy	Python	SQL
  BigInteger	int	BIGINT
  Boolean	bool	BOOLEAN or SMALLINT
  Date	datetime.date	DATE(SQLite: STRING)
  DateTime	datetime.datetime	DATETIME(SQLite: STRING)
  Enum	str	ENUM or VARCHAR
  Float	float or Decimal	FLOAT or REAL
  Integer	int	INTEGER
  Interval	datetime.timedelta	INTERVAL or DATE from epoch
  LargeBinary	byte	BLOB or BYTEA
  Numeric	decimal.Decimal	NUMERIC or DECIMAL
  Unicode	unicode	UNICODE or VARCHAR
  Text	str	CLOB or TEXT
  Time	datetime.time	DATETIME

• SQL standard
  

  See module sqlalchemy.types. The standard types are in all capital letters.

• Vendor specific
  

  See module sqlalchemy.dialects

• User defined
  

10.1.3 Metadata

10.1.4 Tables

• Using user-defined Table objects
  

  Table objects are initialized in SQLAlchemy Core in a supplied MetaData object by calling the Table constructor with the table name and metadata; any additional argu‐ ments are assumed to be column objects.

• Using declarative classes that represent your tables(ORM)
  
• Inferring them from the database
  

10.1.5 Columns

users = Table('users', metadata,
	      Column('user_id', Integer(), primary_key=True),
	      Column('username', String(15), nullable=False, unique=True),
	      Column('email_address', String(255), nullable=False),
	      Column('phone', String(20), nullable=False),
	      Column('password', String(25), nullable=False),
	      Column('created_on', DateTime(), default=datetime.now),
	      Column('updated_on', DateTime(), default=datetime.now,
		     onupdate=datetime.now))

10.1.6 Keys and Constraints

PrimaryKeyConstraint('user_id', name='user_pk')
UniqueConstraint('username', name='uix_username')
CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')
ForeignKeyConstraint(['order_id'], ['orders.order_id'])

10.1.7 Index

from sqlalchemy import Index
Index('ix_cookies_cookie_name', 'cookie_name')
Index('ix_test', mytable.c.cookie_sku, mytable.c.cookie_name)

10.1.8 SQL

• Insert
  

  • single insert

    ins = cookies.insert().values(
    # ins = insert(cookies).values( <= the same
        cookie_name="chocolate chip",
        cookie_recipe_url="http://some.aweso.me/cookie/recipe.html",
        cookie_sku="CC01",
        quantity="12",
        unit_cost="0.50")
    print(str(ins)) # print sql statement
    ins.compile().params # show arguments in sql statement
    result = connection.execute(ins) # executing the insert statement
    result.inserted_primary_key
    

  • multiple inserts

    result = connection.execute(ins, inventory_list)
    

• Query
  

  The select method expects a list of columns to select; however, for convenience, it also accepts Table objects and selects all the columns on the table.

  s = select([cookies])
  rp = connection.execute(s)
  results = rp.fetchall()
  

  • ResultProxy
    

    ResultProxy makes handling query results easier by allowing access using an index, name, or Column object.

    first_row = results[0] # get first row
    first_row[1] # access column by index
    first_row.cookie_name # access column by name
    first_row[cookies.c.cookie_name] # access column by Column object
    

    • iterating over a ResultProxy

      rp = connection.execute(s)
      for record in rp:
          print(record.cookie_name)
      

    • other methods: first, fetchone, scalar, keys
  • Order By
    

    from sqlalchemy import desc
    s = select([cookies.c.cookie_name,
    	    cookies.c.quantity]).order_by(desc(cookies.c.quantity)).limit(2)
    

  • Built-In SQL Functions
    

    s = select([func.sum(cookies.c.quantity)])
    

    • count

      s1 = select([func.count(cookies.c.cookie_name)])
      s2 = select([func.count(cookies.c.cookie_name).label('inventory_count')])# rename
      record1 = connection.execute(s).first()
      record2 = connection.execute(s).first()
      
      print(record1.count_1) # default: <func_name>_<position>
      print(record2.inventory_count)
      

  • Filtering
    

    select([cookies]).where(cookies.c.cookie_name == 'chocolate chip')
    

    • ClauseElements

      between(cleft, cright), concat(col_two), distinct(), in_([list]), notin_([list]), is_(None), isnot(None), contains(string), endswith(string), like(string), startswith(string), ilike(string)

    • use and_, or_, not_ inside where clauses
• Update
  

  from sqlalchemy import update
  u = update(cookies).where(cookies.c.cookie_name == "chocolate chip")
  u = u.values(quantity=(cookies.c.quantity + 120))
  # u = u.values({"quantity": 1, "cookie_name": "bear"}) # multi column update
  result = connection.execute(u)
  print(result.rowcount)
  

• Delete
  

  from sqlalchemy import delete
  u = delete(cookies).where(cookies.c.cookie_name == "dark chocolate chip")
  result = connection.execute(u)
  print(result.rowcount)
  

• Join
  

  columns = [
      orders.c.order_id,
      users.c.username,
      users.c.phone,
      cookies.c.cookie_name,
      line_items.c.quantity,
      line_items.c.extended_cost,
  ]
  cookiemon_orders = select(columns)
  cookiemon_orders = cookiemon_orders.select_from(
      orders.join(users).join(line_items).join(cookies)).where(
  	users.c.username == 'cookiemon')
  

• Alias
  

  manager = employee_table.alias('mgr')
  # SELECT employee.name
  # FROM employee, employee AS mgr
  # WHERE employee.manager_id = mgr.id AND mgr.name = ?
  

• GroupBy
  

  columns = [users.c.username, func.count(orders.c.order_id)]
  all_orders = select(columns)
  all_orders = all_orders.select_from(users.outerjoin(orders))
  all_orders = all_orders.group_by(users.c.username)
  

• Raw Queries
  

  from sqlalchemy import text
  result = connection.execute("select * from orders").fetchall()
  stmt = select([users]).where(text("username='cookiemon'"))
  print(connection.execute(stmt).fetchall())
  

10.1.9 Exceptions

10.1.10 Transactions

transaction = connection.begin()
connection.execute(...)
try:
    transaction.commit()
except IntegrityError as error:
    transaction.rollback()
    print(error)

10.1.11 Reflection

metadata.reflect(bind=engine)
metadata.tables.keys() # show tables
table_obj = metadata.tables["table_name"]

10.2.1 User-defined tables

Base = declarative_base()
class Cookie(Base):
    __tablename__ = 'cookies'

    cookie_id = Column(Integer(), primay_key=True)
    ...
Cookie.__table__ # same as the table object using Table(...)

• Constraints
  

  use __table_args__ to specify constraints like Table constructor in core section.

  class SomeDataClass(Base):
      __tablename__ = 'somedatatable'
      __table_args__ = (ForeignKeyConstraint(['id'], ['other_table.id']),
  		      CheckConstraint(unit_cost >= 0.00,
  				      name='unit_cost_positive'))
  

• FK
  

  from sqlalchemy import ForeignKey, Boolean
  from sqlalchemy.orm import relationship, backref
  class Order(Base):
      __tablename__ = 'orders'
      order_id = Column(Integer(), primary_key=True)
      user_id = Column(Integer(), ForeignKey('users.user_id'))
      shipped = Column(Boolean(), default=False)
      user = relationship("User", backref=backref('orders', order_by=order_id)) # one(user) to many(orders) relationship
  

  This relationship also establishes an orders property on the User class via the backref keyword argument, which is ordered by order_id.

  The relationship directive needs a target class for the relationship, and can optionally include a back reference to be added to target class.

• Persistance
  

  Base.metadata.create_all(engine)
  

10.2.2 Relationship

• one-to-one
  

  class Key(Model):
      #...
      lock_id = Column(Integer, ForeignKey('Locks.id'))
  
  class Lock(Model):
      #...
      key = db.relationship('Key', backref='lock', uselist=False) # backref add a lock obj to key
  

• one-to-many
  

  Just change uselist to True in one-to-one example.

• many-to-many
  

10.2.3 Session

• States
  
  • Transient: The instance is not in session, and is not in the database
  • Pending: The instance has been added to the session with add(), but hasn’t been flushed or committed.
  • Persistent: The object in session has a corresponding record in the database.
  • Detached: The instance is no longer connected to the session, but has a record in the database.
• Init
  

  from sqlalchemy import create_engine
  from sqlalchemy.orm import sessionmaker
  engine = create_engine('sqlite:///:memory:')
  Session = sessionmaker(bind=engine) # generate factory function
  session = Session() # establish the connection
  # or
  with engine.connect() as conn:
      session = Session(bind=conn)
  

10.2.4 SQL

• Insert
  
  • add->commit
    

    cc_cookie = Cookie(...)
    session.add(cc_cookie)
    print(cc_cookie.cookie_id) # None
    session.commit()
    print(cc_cookie.cookie_id) # auto generated no
    

  • add->flush
    

    Use flush if you are going to do additional work with the objects after inserting them.

    A flush is like a commit; however, it doesn’t perform a database commit and end the transaction. The data objects are still associated with the session.

    cookie1 = Cookie(...)
    cookie2 = Cookie(...)
    session.add(cookie1)
    session.add(cookie2)
    session.flush()
    print(cookie1.cookie_id) # some number
    

    after flush, the records in session are changed.

  • bulk_save_objects
    

    performance benefits:

    • Relationship settings and actions are not respected or triggered.
    • The objects are not connected to the session.
    • Fetching primary keys is not done by default.
    • No events will be triggered.

    session.bulk_save_objects([cookie1,cookie2])
    session.commit()
    

    • after bulk_save_objects, the state is Transient. use inspect to see the object state

    from sqlalchemy import inspect
    inspect(cookie1)
    inspect(cookie2)
    for state in ['transient', 'pending', 'persistent', 'detached']:
        print('{:>10}: {}'.format(state, getattr(insp, state)))
    

• Query
  

  cookies = session.query(Cookie).all()
  

  fetch method: all, first, one, scalar

  for cookie in session.query(Cookie):
      print(cookie)
  

  The iterable version is more memory efficient.

  • Choose columns
    

    print(session.query(Cookie.cookie_name, Cookie.quantity).first())
    

  • Ordering
    

    for cookie in session.query(Cookie).order_by(desc(Cookie.quantity)):
        print('{:3} - {}'.format(cookie.quantity, cookie.cookie_name))
    

  • Limiting
    

    query = session.query(Cookie).order_by(
        Cookie.quantity)[:2]  # slices the return list. ineffecient.
    query = session.query(Cookie).order_by(Cookie.quantity).limit(2)
    print([result.cookie_name for result in query])
    

    This runs the query and slices the returned list. This can be very ineffecient with a large result set.

  • Built-in functions
    

    from sqlalchemy import func
    inv_count = session.query(func.sum(Cookie.quantity)).scalar()
    rec_count = session.query(
        func.count(Cookie.cookie_name).label('inventory_count')).first()  # rename
    

  • Filtering
    

    record = session.query(Cookie).filter(
        Cookie.cookie_name == 'chocolate chip').first()
    record = session.query(Cookie).filter_by(cookie_name='chocolate chip').first()
    query = session.query(Cookie).filter(Cookie.cookie_name.like('%chocolate%'))
    

  • Conjunctions
    

    query = session.query(Cookie).filter(
        Cookie.quantity > 23, Cookie.unit_cost < 0.40)  # default and conjunctions
    from sqlalchemy import and_, or_, not_
    query = session.query(Cookie).filter(
        or_(Cookie.quantity.between(10, 50), Cookie.cookie_name.contains('chip')))
    

• Update
  

  query = session.query(Cookie)
  cc_cookie = query.filter(Cookie.cookie_name == "chocolate chip").first()
  
  cc_cookie.quantity = cc_cookie.quantity + 120
  # or
  query.update({Cookie.quantity: Cookie.quantity - 20})
  
  session.commit()
  print(cc_cookie.quantity)
  

• Delete
  
  • session.delete
    

    query = session.query(Cookie)
    query = query.filter(Cookie.cookie_name == "dark chocolate chip")
    dcc_cookie = query.one()
    session.delete(dcc_cookie)
    session.commit()
    

  • query.delete
    

    query = session.query(Cookie)
    query = query.filter(Cookie.cookie_name == "molasses")
    query.delete()
    

• Join
  

  query = session.query(Order.order_id, User.username, User.phone,
  Cookie.cookie_name, LineItem.quantity,
  LineItem.extended_cost)
  query = query.join(User).join(LineItem).join(Cookie)
  results = query.filter(User.username == 'cookiemon').all()
  

• GroupBy
  

  query = session.query(User.username, func.count(Order.order_id))
  query = query.outerjoin(Order).group_by(User.username)
  

• Transaction
  

  Transaction begins after session.commit()

10.2.5 Exceptions

• MultipleResultsFound
  

  This exception occurs when we use the .one() query method, but get more than one result back.

• DetachedInstanceError
  

  This exception occurs when we attempt to access an attribute on an instance that needs to be loaded from the database, but the instance we are using is not currently attached to the database.

10.2.6 Reflection

from sqlalchemy.ext.automap import automap_base
Base = automap_base() # use automap_base instead of declarative_base
Base.prepare(engine, reflect=True)

Base.classes.keys() # show ORM classes
User = Base.classes.User
Order = Base.classes.User

10.3.1 Configuring the Environment

├── migrations
│   ├── env.py
│   ├── README
│   ├── script.py.mako
│   └── versions
└── alembic.ini

10.3.2 Building Migrations Manually

10.3.3 Autogenerating a Migration

10.3.4 Version Control

10.3.5 Generating SQL