Lecture 7: PostgreSQL part 4

Lecture 7: PostgreSQL part 4#

Introduction#

SQL (Structured Query Language) is a powerful language used for managing and manipulating relational databases. It consists of five main components:

DDL (Data Definition Language):
- Used for defining and modifying database structures.
- Examples: CREATE, ALTER, DROP.
DQL (Data Query Language):
- Used for querying data from the database.
- Example: SELECT.
DML (Data Manipulation Language):
- Used for manipulating data within the database.
- Examples: INSERT, UPDATE, DELETE.
DCL (Data Control Language):
- Used for controlling access to data within the database.
- Examples: GRANT, REVOKE.
TCL (Transaction Control Language):
- Used for managing transactions within the database.
- Examples: COMMIT, ROLLBACK.

Today, we will focus on DDL (Data Definition Language), specifically on how to create, modify, and drop tables in SQL, including the use of integrity constraints.

Learning Objectives#

Understand how to create tables with various integrity constraints, including PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, DEFAULT, and CHECK.
Learn how to modify existing tables using the ALTER statement.
Gain knowledge on how to drop tables using the DROP statement.

Connecting to the Database#

# load the ipython-sql extension
%load_ext sql

import json
import urllib.parse

with open('data/credentials.json') as f:
    login = json.load(f)
    
username = login['user']
password = urllib.parse.quote(login['password'])
host = login['host']
port = login['port']

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Let’s connect to your dvdrental database

%sql postgresql://{username}:{password}@{host}:{port}/dvdrental

'Connected: postgres@dvdrental'

%%sql
DROP TABLE IF EXISTS employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

Creating Tables#

PRIMARY KEY#

General Syntax

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    ...
);

Example#

To create a table with a primary key:

%%sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

Explanation:
- id SERIAL PRIMARY KEY: Creates an auto-incrementing primary key.
- first_name VARCHAR(50): Creates a column for the first name with a maximum length of 50 characters.
- last_name VARCHAR(50): Creates a column for the last name with a maximum length of 50 characters.
- age INT: Creates a column for the age.
- department VARCHAR(50): Creates a column for the department with a maximum length of 50 characters.

A primary key should be unique. Let’s try to insert two records of the same id, this should fail

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
0 rows affected.

id	first_name	last_name	age	department

%%sql
INSERT INTO employees (first_name, last_name, age, department)
VALUES ('John', 'Doe', 25, 'Sales');

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

[]

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	first_name	last_name	age	department
1	John	Doe	25	Sales

%%sql
INSERT INTO employees
VALUES (2, 'Jane', 'Doe', 30, 'Marketing');

SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.
2 rows affected.

id	first_name	last_name	age	department
1	John	Doe	25	Sales
2	Jane	Doe	30	Marketing

FOREIGN KEY#

General Syntax

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    FOREIGN KEY (column_name) REFERENCES other_table (column_name)
);

Example#

To create a table with a foreign key:

%%sql
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (id)
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.
Done.
Done.

[]

Explanation:
- FOREIGN KEY (department_id) REFERENCES departments (id): Ensures that the department_id in the employees table must match an id in the departments table.

%%sql
INSERT INTO departments (name) VALUES
    ('Engineering'),
    ('Marketing'),
    ('Sales'),
    ('HR'),
    ('Finance'),
    ('IT');

SELECT * FROM departments;

 * postgresql://postgres:***@localhost:5432/dvdrental
6 rows affected.
6 rows affected.

id	name
1	Engineering
2	Marketing
3	Sales
4	HR
5	Finance
6	IT

%%sql
INSERT INTO employees (first_name, last_name, age, department_id) VALUES
    ('John', 'Doe', 30, 1),
    ('Jane', 'Smith', 25, 2),
    ('Alice', 'Johnson', 28, 3),
    ('Bob', 'Brown', 35, 4),
    ('Charlie', 'Davis', 40, 5),
    ('David', 'Miller', 32, 6);

SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
6 rows affected.
6 rows affected.

id	first_name	last_name	age	department_id
1	John	Doe	30	1
2	Jane	Smith	25	2
3	Alice	Johnson	28	3
4	Bob	Brown	35	4
5	Charlie	Davis	40	5
6	David	Miller	32	6

What if I insert a row in the employees table where the department_id doesn’t exist in the department table?

%%sql
INSERT INTO employees (first_name, last_name, age, department_id) VALUES
    ('Quan', 'Nguyen', 30, 12345);

 * postgresql://postgres:***@localhost:5432/dvdrental

---------------------------------------------------------------------------
ForeignKeyViolation                       Traceback (most recent call last)
File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

ForeignKeyViolation: insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL:  Key (department_id)=(12345) is not present in table "departments".


The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
Cell In[175], line 1
----> 1 get_ipython().run_cell_magic('sql', '', "INSERT INTO employees (first_name, last_name, age, department_id) VALUES\n    ('Quan', 'Nguyen', 30, 12345);\n")

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/magic.py:95, in SqlMagic.execute(self, line, cell, local_ns)
     92     return self._persist_dataframe(parsed['sql'], conn, user_ns)
     94 try:
---> 95     result = sql.run.run(conn, parsed['sql'], self, user_ns)
     97     if result is not None and not isinstance(result, str) and self.column_local_vars:
     98         #Instead of returning values, set variables directly in the
     99         #users namespace. Variable names given by column names
    101         if self.autopandas:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/run.py:340, in run(conn, sql, config, user_namespace)
    338 else:
    339     txt = sqlalchemy.sql.text(statement)
--> 340     result = conn.session.execute(txt, user_namespace)
    341 _commit(conn=conn, config=config)
    342 if result and config.feedback:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
   1376     util.raise_(
   1377         exc.ObjectNotExecutableError(statement), replace_context=err
   1378     )
   1379 else:
-> 1380     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    330 def _execute_on_connection(
    331     self, connection, multiparams, params, execution_options, _force=False
    332 ):
    333     if _force or self.supports_execution:
--> 334         return connection._execute_clauseelement(
    335             self, multiparams, params, execution_options
    336         )
    337     else:
    338         raise exc.ObjectNotExecutableError(self)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1572, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1560 compiled_cache = execution_options.get(
   1561     "compiled_cache", self.engine._compiled_cache
   1562 )
   1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1565     dialect=dialect,
   1566     compiled_cache=compiled_cache,
   (...)
   1570     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571 )
-> 1572 ret = self._execute_context(
   1573     dialect,
   1574     dialect.execution_ctx_cls._init_compiled,
   1575     compiled_sql,
   1576     distilled_params,
   1577     execution_options,
   1578     compiled_sql,
   1579     distilled_params,
   1580     elem,
   1581     extracted_params,
   1582     cache_hit=cache_hit,
   1583 )
   1584 if has_events:
   1585     self.dispatch.after_execute(
   1586         self,
   1587         elem,
   (...)
   1591         ret,
   1592     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1943, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1940             branched.close()
   1942 except BaseException as e:
-> 1943     self._handle_dbapi_exception(
   1944         e, statement, parameters, cursor, context
   1945     )
   1947 return result

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2124, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123 elif should_wrap:
-> 2124     util.raise_(
   2125         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126     )
   2127 else:
   2128     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    208     exception.__cause__ = replace_context
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle
    216     del exception, replace_context, from_, with_traceback

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                 break
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:
   1905     self.dispatch.after_cursor_execute(
   1906         self,
   1907         cursor,
   (...)
   1911         context.executemany,
   1912     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.ForeignKeyViolation) insert or update on table "employees" violates foreign key constraint "employees_department_id_fkey"
DETAIL:  Key (department_id)=(12345) is not present in table "departments".

[SQL: INSERT INTO employees (first_name, last_name, age, department_id) VALUES
    ('Quan', 'Nguyen', 30, 12345);]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

It returns an error message saying

ForeignKeyViolation: insert or update on table “employees” violates foreign key constraint >”employees_department_id_fkey” DETAIL: Key (department_id)=(12345) is not present in table “departments”.

UNIQUE#

General Syntax

CREATE TABLE table_name (
    column1 datatype UNIQUE,
    ...
);

Example#

To create a table with a unique constraint:

%%sql
DROP TABLE employees;
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.

[]

Explanation:
- email VARCHAR(100) UNIQUE: Ensures that all values in the email column are unique.

Insert a row with a unique email

%%sql
INSERT INTO employees (first_name, last_name, age, department, email) VALUES
    ('John', 'Doe', 30, 'Engineering', 'john.doe@example.com');
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.
1 rows affected.

id	first_name	last_name	age	department	email
1	John	Doe	30	Engineering	john.doe@example.com

Attempt to insert another row with the same email. This should throw an error

%%sql
INSERT INTO employees (first_name, last_name, age, department, email) VALUES
    ('Jane', 'Smith', 25, 'Marketing', 'john.doe@example.com');

 * postgresql://postgres:***@localhost:5432/dvdrental

---------------------------------------------------------------------------
UniqueViolation                           Traceback (most recent call last)
File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

UniqueViolation: duplicate key value violates unique constraint "employees_email_key"
DETAIL:  Key (email)=(john.doe@example.com) already exists.


The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
Cell In[178], line 1
----> 1 get_ipython().run_cell_magic('sql', '', "INSERT INTO employees (first_name, last_name, age, department, email) VALUES\n    ('Jane', 'Smith', 25, 'Marketing', 'john.doe@example.com');\n")

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/magic.py:95, in SqlMagic.execute(self, line, cell, local_ns)
     92     return self._persist_dataframe(parsed['sql'], conn, user_ns)
     94 try:
---> 95     result = sql.run.run(conn, parsed['sql'], self, user_ns)
     97     if result is not None and not isinstance(result, str) and self.column_local_vars:
     98         #Instead of returning values, set variables directly in the
     99         #users namespace. Variable names given by column names
    101         if self.autopandas:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/run.py:340, in run(conn, sql, config, user_namespace)
    338 else:
    339     txt = sqlalchemy.sql.text(statement)
--> 340     result = conn.session.execute(txt, user_namespace)
    341 _commit(conn=conn, config=config)
    342 if result and config.feedback:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
   1376     util.raise_(
   1377         exc.ObjectNotExecutableError(statement), replace_context=err
   1378     )
   1379 else:
-> 1380     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    330 def _execute_on_connection(
    331     self, connection, multiparams, params, execution_options, _force=False
    332 ):
    333     if _force or self.supports_execution:
--> 334         return connection._execute_clauseelement(
    335             self, multiparams, params, execution_options
    336         )
    337     else:
    338         raise exc.ObjectNotExecutableError(self)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1572, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1560 compiled_cache = execution_options.get(
   1561     "compiled_cache", self.engine._compiled_cache
   1562 )
   1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1565     dialect=dialect,
   1566     compiled_cache=compiled_cache,
   (...)
   1570     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571 )
-> 1572 ret = self._execute_context(
   1573     dialect,
   1574     dialect.execution_ctx_cls._init_compiled,
   1575     compiled_sql,
   1576     distilled_params,
   1577     execution_options,
   1578     compiled_sql,
   1579     distilled_params,
   1580     elem,
   1581     extracted_params,
   1582     cache_hit=cache_hit,
   1583 )
   1584 if has_events:
   1585     self.dispatch.after_execute(
   1586         self,
   1587         elem,
   (...)
   1591         ret,
   1592     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1943, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1940             branched.close()
   1942 except BaseException as e:
-> 1943     self._handle_dbapi_exception(
   1944         e, statement, parameters, cursor, context
   1945     )
   1947 return result

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2124, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123 elif should_wrap:
-> 2124     util.raise_(
   2125         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126     )
   2127 else:
   2128     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    208     exception.__cause__ = replace_context
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle
    216     del exception, replace_context, from_, with_traceback

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                 break
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:
   1905     self.dispatch.after_cursor_execute(
   1906         self,
   1907         cursor,
   (...)
   1911         context.executemany,
   1912     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "employees_email_key"
DETAIL:  Key (email)=(john.doe@example.com) already exists.

[SQL: INSERT INTO employees (first_name, last_name, age, department, email) VALUES
    ('Jane', 'Smith', 25, 'Marketing', 'john.doe@example.com');]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

NOT NULL#

General Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    ...
);

Example#

To create a table with a not null constraint:

%%sql
DROP TABLE employees;
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT,
    department VARCHAR(50)
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

%%sql
INSERT INTO employees (first_name, last_name, age, department) VALUES
    ('John', 'Doe', 30, 'Engineering')
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	first_name	last_name	age	department
1	John	Doe	30	Engineering

%%sql
INSERT INTO employees ( last_name, age, department) VALUES
    ('John', 30, 'Engineering')
;

 * postgresql://postgres:***@localhost:5432/dvdrental

---------------------------------------------------------------------------
NotNullViolation                          Traceback (most recent call last)
File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

NotNullViolation: null value in column "first_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (3, null, John, 30, Engineering).


The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
Cell In[242], line 1
----> 1 get_ipython().run_cell_magic('sql', '', "INSERT INTO employees ( last_name, age, department) VALUES\n    ('John', 30, 'Engineering')\n;\n")

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/magic.py:95, in SqlMagic.execute(self, line, cell, local_ns)
     92     return self._persist_dataframe(parsed['sql'], conn, user_ns)
     94 try:
---> 95     result = sql.run.run(conn, parsed['sql'], self, user_ns)
     97     if result is not None and not isinstance(result, str) and self.column_local_vars:
     98         #Instead of returning values, set variables directly in the
     99         #users namespace. Variable names given by column names
    101         if self.autopandas:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/run.py:340, in run(conn, sql, config, user_namespace)
    338 else:
    339     txt = sqlalchemy.sql.text(statement)
--> 340     result = conn.session.execute(txt, user_namespace)
    341 _commit(conn=conn, config=config)
    342 if result and config.feedback:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
   1376     util.raise_(
   1377         exc.ObjectNotExecutableError(statement), replace_context=err
   1378     )
   1379 else:
-> 1380     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    330 def _execute_on_connection(
    331     self, connection, multiparams, params, execution_options, _force=False
    332 ):
    333     if _force or self.supports_execution:
--> 334         return connection._execute_clauseelement(
    335             self, multiparams, params, execution_options
    336         )
    337     else:
    338         raise exc.ObjectNotExecutableError(self)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1572, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1560 compiled_cache = execution_options.get(
   1561     "compiled_cache", self.engine._compiled_cache
   1562 )
   1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1565     dialect=dialect,
   1566     compiled_cache=compiled_cache,
   (...)
   1570     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571 )
-> 1572 ret = self._execute_context(
   1573     dialect,
   1574     dialect.execution_ctx_cls._init_compiled,
   1575     compiled_sql,
   1576     distilled_params,
   1577     execution_options,
   1578     compiled_sql,
   1579     distilled_params,
   1580     elem,
   1581     extracted_params,
   1582     cache_hit=cache_hit,
   1583 )
   1584 if has_events:
   1585     self.dispatch.after_execute(
   1586         self,
   1587         elem,
   (...)
   1591         ret,
   1592     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1943, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1940             branched.close()
   1942 except BaseException as e:
-> 1943     self._handle_dbapi_exception(
   1944         e, statement, parameters, cursor, context
   1945     )
   1947 return result

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2124, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123 elif should_wrap:
-> 2124     util.raise_(
   2125         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126     )
   2127 else:
   2128     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    208     exception.__cause__ = replace_context
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle
    216     del exception, replace_context, from_, with_traceback

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                 break
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:
   1905     self.dispatch.after_cursor_execute(
   1906         self,
   1907         cursor,
   (...)
   1911         context.executemany,
   1912     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "first_name" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (3, null, John, 30, Engineering).

[SQL: INSERT INTO employees ( last_name, age, department) VALUES
    ('John', 30, 'Engineering')
;]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

%%sql
INSERT INTO employees (first_name, last_name, age, department) VALUES
    ('John', 'Doe', 30, 'Engineering');

SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.
4 rows affected.

id	first_name	last_name	age	department
1	John	Doe	30	Engineering
2	John	30	30	Engineering
4	John	Doe	30	Engineering
5	John	Doe	30	Engineering

Explanation:
- first_name VARCHAR(50) NOT NULL: Ensures that the first_name column cannot have a NULL value.
- last_name VARCHAR(50) NOT NULL: Ensures that the last_name column cannot have a NULL value.

Attempt to insert a row with a NULL first_name

%%sql
INSERT INTO employees (first_name, last_name, age, department) 
VALUES
    ('John', 'Doe', 30, NULL);

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

[]

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	first_name	last_name	age	department
2	John	Doe	30	None

DEFAULT#

General Syntax

CREATE TABLE table_name (
    column1 datatype DEFAULT default_value,
    ...
);

Example#

To create a table with a default constraint:

%%sql
DROP TABLE employees;
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department VARCHAR(50) DEFAULT 'General'
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.

[]

Explanation:
- department VARCHAR(50) DEFAULT 'General': Sets a default value of ‘General’ for the department column if no value is provided.

Insert a Row Without Specifying the department Column

%%sql
INSERT INTO employees (first_name, last_name, age) 
VALUES
    ('John', 'Doe', 30),
    ('Jane', 'Smith', 25)
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	first_name	last_name	age	department
1	John	Doe	30	General
2	Jane	Smith	25	General

CHECK#

General Syntax

CREATE TABLE table_name (
    column1 datatype CHECK (condition),
    ...
);

Example#

To create a table with a check constraint:

%%sql
DROP TABLE employees;
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT CHECK (age > 0 AND age < 100),
    department VARCHAR(50),
    date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.

[]

Explanation:
- age INT CHECK (age > 0): Ensures that the age must be greater than 0.

To test if the CHECK constraint on the age column holds up, we can attempt to insert rows with invalid values for the age column. If the constraint is enforced correctly, the insertion should fail.

Insert a Row with a Valid age:

%%sql
INSERT INTO employees (first_name, last_name,  department) VALUES
    ('John', 'Doe', 'Engineering')
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental

---------------------------------------------------------------------------
CheckViolation                            Traceback (most recent call last)
File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

CheckViolation: new row for relation "employees" violates check constraint "employees_age_check"
DETAIL:  Failing row contains (2, John, Doe, -1, Engineering, 2024-09-25 12:56:38.338107).


The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
Cell In[197], line 1
----> 1 get_ipython().run_cell_magic('sql', '', "INSERT INTO employees (first_name, last_name,  department) VALUES\n    ('John', 'Doe', 'Engineering')\nRETURNING *;\n")

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/magic.py:95, in SqlMagic.execute(self, line, cell, local_ns)
     92     return self._persist_dataframe(parsed['sql'], conn, user_ns)
     94 try:
---> 95     result = sql.run.run(conn, parsed['sql'], self, user_ns)
     97     if result is not None and not isinstance(result, str) and self.column_local_vars:
     98         #Instead of returning values, set variables directly in the
     99         #users namespace. Variable names given by column names
    101         if self.autopandas:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/run.py:340, in run(conn, sql, config, user_namespace)
    338 else:
    339     txt = sqlalchemy.sql.text(statement)
--> 340     result = conn.session.execute(txt, user_namespace)
    341 _commit(conn=conn, config=config)
    342 if result and config.feedback:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
   1376     util.raise_(
   1377         exc.ObjectNotExecutableError(statement), replace_context=err
   1378     )
   1379 else:
-> 1380     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    330 def _execute_on_connection(
    331     self, connection, multiparams, params, execution_options, _force=False
    332 ):
    333     if _force or self.supports_execution:
--> 334         return connection._execute_clauseelement(
    335             self, multiparams, params, execution_options
    336         )
    337     else:
    338         raise exc.ObjectNotExecutableError(self)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1572, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1560 compiled_cache = execution_options.get(
   1561     "compiled_cache", self.engine._compiled_cache
   1562 )
   1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1565     dialect=dialect,
   1566     compiled_cache=compiled_cache,
   (...)
   1570     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571 )
-> 1572 ret = self._execute_context(
   1573     dialect,
   1574     dialect.execution_ctx_cls._init_compiled,
   1575     compiled_sql,
   1576     distilled_params,
   1577     execution_options,
   1578     compiled_sql,
   1579     distilled_params,
   1580     elem,
   1581     extracted_params,
   1582     cache_hit=cache_hit,
   1583 )
   1584 if has_events:
   1585     self.dispatch.after_execute(
   1586         self,
   1587         elem,
   (...)
   1591         ret,
   1592     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1943, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1940             branched.close()
   1942 except BaseException as e:
-> 1943     self._handle_dbapi_exception(
   1944         e, statement, parameters, cursor, context
   1945     )
   1947 return result

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2124, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123 elif should_wrap:
-> 2124     util.raise_(
   2125         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126     )
   2127 else:
   2128     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    208     exception.__cause__ = replace_context
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle
    216     del exception, replace_context, from_, with_traceback

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                 break
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:
   1905     self.dispatch.after_cursor_execute(
   1906         self,
   1907         cursor,
   (...)
   1911         context.executemany,
   1912     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.CheckViolation) new row for relation "employees" violates check constraint "employees_age_check"
DETAIL:  Failing row contains (2, John, Doe, -1, Engineering, 2024-09-25 12:56:38.338107).

[SQL: INSERT INTO employees (first_name, last_name,  department) VALUES
    ('John', 'Doe', 'Engineering')
RETURNING *;]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

Attempt to Insert a Row with an Invalid age:

%%sql
INSERT INTO employees (first_name, last_name, age, department) VALUES
    ('Jane', 'Smith', 50, 'Marketing')
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	first_name	last_name	age	department	date_time
3	Jane	Smith	50	Marketing	2024-09-25 12:54:55.125256

Referential Actions#

Referential actions define what happens to the foreign key when the referenced key is updated or deleted. These actions help maintain referential integrity between tables.

Purpose#

ON UPDATE: Specifies the action to be taken when the referenced key is updated.
ON DELETE: Specifies the action to be taken when the referenced key is deleted.

Actions#

CASCADE: Automatically updates or deletes the foreign key to match the referenced key.
SET NULL: Sets the foreign key to NULL if the referenced key is updated or deleted.
SET DEFAULT: Sets the foreign key to its default value if the referenced key is updated or deleted.
RESTRICT: Prevents the update or deletion of the referenced key if there are matching foreign keys.
NO ACTION: Same as RESTRICT in PostgreSQL.

ON UPDATE CASCADE#

%%sql
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (id)
    ON UPDATE CASCADE
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.
Done.
Done.

[]

Explanation:
- If the id in the departments table is updated, the department_id in the employees table will also be updated to match the new value.

Insert Sample Data into departments Table:

%%sql
INSERT INTO departments (name) VALUES
    ('Engineering'),
    ('Marketing')
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	name
1	Engineering
2	Marketing

Insert Sample Data into employees Table:

%%sql
INSERT INTO employees (first_name, last_name, age, department_id) VALUES
    ('John', 'Doe', 30, 1),
    ('Jane', 'Smith', 25, 2)
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	first_name	last_name	age	department_id
1	John	Doe	30	1
2	Jane	Smith	25	2

Update the id of a Department:

%%sql
UPDATE departments SET id = 3 WHERE id = 1
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	name
3	Engineering

Query the employees Table to Verify the Cascade Update:

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	first_name	last_name	age	department_id
2	Jane	Smith	25	2
1	John	Doe	30	3

ON DELETE SET NULL#

%%sql
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (id)
    ON DELETE SET NULL
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.
Done.
Done.

[]

Explanation:
- If the id in the departments table is deleted, the department_id in the employees table will be set to NULL.

Insert Sample Data into departments Table:

%%sql
INSERT INTO departments (name) VALUES
    ('Engineering'),
    ('Marketing')
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	name
1	Engineering
2	Marketing

Insert Sample Data into employees Table:

%%sql
INSERT INTO employees (first_name, last_name, age, department_id) VALUES
    ('John', 'Doe', 30, 1),
    ('Jane', 'Smith', 25, 2)
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	first_name	last_name	age	department_id
1	John	Doe	30	1
2	Jane	Smith	25	2

Delete a department

%%sql
DELETE FROM departments WHERE id = 1;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

[]

%%sql
SELECT * FROM departments;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	name
2	Marketing

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	first_name	last_name	age	department_id
2	Jane	Smith	25	2
1	John	Doe	30	None

ON DELETE CASCADE#

%%sql
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments (id)
    ON DELETE NO ACTION
);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.
Done.
Done.
Done.

[]

Insert Sample Data into departments Table:

%%sql
INSERT INTO departments (name) VALUES
    ('Engineering'),
    ('Marketing')
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	name
1	Engineering
2	Marketing

Insert Sample Data into employees Table:

%%sql
INSERT INTO employees (first_name, last_name, age, department_id) VALUES
    ('John', 'Doe', 30, 1),
    ('Jane', 'Smith', 25, 2)
RETURNING *;

 * postgresql://postgres:***@localhost:5432/dvdrental
2 rows affected.

id	first_name	last_name	age	department_id
1	John	Doe	30	1
2	Jane	Smith	25	2

%%sql
DELETE FROM departments WHERE id = 1;

 * postgresql://postgres:***@localhost:5432/dvdrental

---------------------------------------------------------------------------
ForeignKeyViolation                       Traceback (most recent call last)
File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

ForeignKeyViolation: update or delete on table "departments" violates foreign key constraint "employees_department_id_fkey" on table "employees"
DETAIL:  Key (id)=(1) is still referenced from table "employees".


The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
Cell In[219], line 1
----> 1 get_ipython().run_cell_magic('sql', '', 'DELETE FROM departments WHERE id = 1;\n')

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/magic.py:95, in SqlMagic.execute(self, line, cell, local_ns)
     92     return self._persist_dataframe(parsed['sql'], conn, user_ns)
     94 try:
---> 95     result = sql.run.run(conn, parsed['sql'], self, user_ns)
     97     if result is not None and not isinstance(result, str) and self.column_local_vars:
     98         #Instead of returning values, set variables directly in the
     99         #users namespace. Variable names given by column names
    101         if self.autopandas:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/run.py:340, in run(conn, sql, config, user_namespace)
    338 else:
    339     txt = sqlalchemy.sql.text(statement)
--> 340     result = conn.session.execute(txt, user_namespace)
    341 _commit(conn=conn, config=config)
    342 if result and config.feedback:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
   1376     util.raise_(
   1377         exc.ObjectNotExecutableError(statement), replace_context=err
   1378     )
   1379 else:
-> 1380     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    330 def _execute_on_connection(
    331     self, connection, multiparams, params, execution_options, _force=False
    332 ):
    333     if _force or self.supports_execution:
--> 334         return connection._execute_clauseelement(
    335             self, multiparams, params, execution_options
    336         )
    337     else:
    338         raise exc.ObjectNotExecutableError(self)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1572, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1560 compiled_cache = execution_options.get(
   1561     "compiled_cache", self.engine._compiled_cache
   1562 )
   1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1565     dialect=dialect,
   1566     compiled_cache=compiled_cache,
   (...)
   1570     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571 )
-> 1572 ret = self._execute_context(
   1573     dialect,
   1574     dialect.execution_ctx_cls._init_compiled,
   1575     compiled_sql,
   1576     distilled_params,
   1577     execution_options,
   1578     compiled_sql,
   1579     distilled_params,
   1580     elem,
   1581     extracted_params,
   1582     cache_hit=cache_hit,
   1583 )
   1584 if has_events:
   1585     self.dispatch.after_execute(
   1586         self,
   1587         elem,
   (...)
   1591         ret,
   1592     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1943, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1940             branched.close()
   1942 except BaseException as e:
-> 1943     self._handle_dbapi_exception(
   1944         e, statement, parameters, cursor, context
   1945     )
   1947 return result

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2124, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123 elif should_wrap:
-> 2124     util.raise_(
   2125         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126     )
   2127 else:
   2128     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    208     exception.__cause__ = replace_context
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle
    216     del exception, replace_context, from_, with_traceback

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                 break
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:
   1905     self.dispatch.after_cursor_execute(
   1906         self,
   1907         cursor,
   (...)
   1911         context.executemany,
   1912     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "departments" violates foreign key constraint "employees_department_id_fkey" on table "employees"
DETAIL:  Key (id)=(1) is still referenced from table "employees".

[SQL: DELETE FROM departments WHERE id = 1;]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

%%sql
SELECT * FROM departments;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	name
2	Marketing

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
1 rows affected.

id	first_name	last_name	age	department_id
2	Jane	Smith	25	2

Modifying Tables#

Adding a Column#

General Syntax

ALTER TABLE table_name
ADD COLUMN column_name datatype constraint;

  Cell In[228], line 1
    ALTER TABLE table_name
          ^
SyntaxError: invalid syntax

Example#

To add a new column to an existing table:

%%sql
ALTER TABLE employees
ADD COLUMN phone_number VARCHAR(15);

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

Explanation:
- ALTER TABLE employees: Specifies the table to be modified.
- ADD COLUMN phone_number VARCHAR(15): Adds a new column named phone_number with a maximum length of 15 characters.

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
0 rows affected.

id	first_name	last_name	age	department_id	phone_number

Modifying a Column#

General Syntax

ALTER TABLE table_name
ALTER COLUMN column_name SET constraint;

Example#

To modify an existing column in a table:

%%sql
ALTER TABLE employees
ALTER COLUMN age SET NOT NULL;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

Explanation:
- ALTER TABLE employees: Specifies the table to be modified.
- ALTER COLUMN age SET NOT NULL: Modifies the age column to ensure it cannot have a NULL value.

%%sql
INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');

 * postgresql://postgres:***@localhost:5432/dvdrental

---------------------------------------------------------------------------
NotNullViolation                          Traceback (most recent call last)
File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

NotNullViolation: null value in column "age" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (1, John, Doe, null, null, null).


The above exception was the direct cause of the following exception:

IntegrityError                            Traceback (most recent call last)
Cell In[232], line 1
----> 1 get_ipython().run_cell_magic('sql', '', "INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');\n")

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/IPython/core/interactiveshell.py:2541, in InteractiveShell.run_cell_magic(self, magic_name, line, cell)
   2539 with self.builtin_trap:
   2540     args = (magic_arg_s, cell)
-> 2541     result = fn(*args, **kwargs)
   2543 # The code below prevents the output from being displayed
   2544 # when using magics with decorator @output_can_be_silenced
   2545 # when the last Python token in the expression is a ';'.
   2546 if getattr(fn, magic.MAGIC_OUTPUT_CAN_BE_SILENCED, False):

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/magic.py:95, in SqlMagic.execute(self, line, cell, local_ns)
     92     return self._persist_dataframe(parsed['sql'], conn, user_ns)
     94 try:
---> 95     result = sql.run.run(conn, parsed['sql'], self, user_ns)
     97     if result is not None and not isinstance(result, str) and self.column_local_vars:
     98         #Instead of returning values, set variables directly in the
     99         #users namespace. Variable names given by column names
    101         if self.autopandas:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sql/run.py:340, in run(conn, sql, config, user_namespace)
    338 else:
    339     txt = sqlalchemy.sql.text(statement)
--> 340     result = conn.session.execute(txt, user_namespace)
    341 _commit(conn=conn, config=config)
    342 if result and config.feedback:

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1380, in Connection.execute(self, statement, *multiparams, **params)
   1376     util.raise_(
   1377         exc.ObjectNotExecutableError(statement), replace_context=err
   1378     )
   1379 else:
-> 1380     return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/sql/elements.py:334, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    330 def _execute_on_connection(
    331     self, connection, multiparams, params, execution_options, _force=False
    332 ):
    333     if _force or self.supports_execution:
--> 334         return connection._execute_clauseelement(
    335             self, multiparams, params, execution_options
    336         )
    337     else:
    338         raise exc.ObjectNotExecutableError(self)

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1572, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
   1560 compiled_cache = execution_options.get(
   1561     "compiled_cache", self.engine._compiled_cache
   1562 )
   1564 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
   1565     dialect=dialect,
   1566     compiled_cache=compiled_cache,
   (...)
   1570     linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571 )
-> 1572 ret = self._execute_context(
   1573     dialect,
   1574     dialect.execution_ctx_cls._init_compiled,
   1575     compiled_sql,
   1576     distilled_params,
   1577     execution_options,
   1578     compiled_sql,
   1579     distilled_params,
   1580     elem,
   1581     extracted_params,
   1582     cache_hit=cache_hit,
   1583 )
   1584 if has_events:
   1585     self.dispatch.after_execute(
   1586         self,
   1587         elem,
   (...)
   1591         ret,
   1592     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1943, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1940             branched.close()
   1942 except BaseException as e:
-> 1943     self._handle_dbapi_exception(
   1944         e, statement, parameters, cursor, context
   1945     )
   1947 return result

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2124, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122     util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123 elif should_wrap:
-> 2124     util.raise_(
   2125         sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126     )
   2127 else:
   2128     util.raise_(exc_info[1], with_traceback=exc_info[2])

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/util/compat.py:211, in raise_(***failed resolving arguments***)
    208     exception.__cause__ = replace_context
    210 try:
--> 211     raise exception
    212 finally:
    213     # credit to
    214     # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
    215     # as the __traceback__ object creates a cycle
    216     del exception, replace_context, from_, with_traceback

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1900, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                 break
   1899     if not evt_handled:
-> 1900         self.dialect.do_execute(
   1901             cursor, statement, parameters, context
   1902         )
   1904 if self._has_events or self.engine._has_events:
   1905     self.dispatch.after_cursor_execute(
   1906         self,
   1907         cursor,
   (...)
   1911         context.executemany,
   1912     )

File /opt/miniconda3/envs/dasc_5410/lib/python3.11/site-packages/sqlalchemy/engine/default.py:736, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
    735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736     cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "age" of relation "employees" violates not-null constraint
DETAIL:  Failing row contains (1, John, Doe, null, null, null).

[SQL: INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

Dropping a Column#

General Syntax

ALTER TABLE table_name
DROP COLUMN column_name;

Example#

To drop an existing column from a table:

%%sql
ALTER TABLE employees
DROP COLUMN phone_number;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

Explanation:
- ALTER TABLE employees: Specifies the table to be modified.
- DROP COLUMN phone_number: Removes the phone_number column from the table.

%%sql
SELECT * FROM employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
0 rows affected.

id	first_name	last_name	age	department_id

Dropping Tables#

General Syntax

DROP TABLE table_name;

Example#

To drop an existing table:

%%sql
DROP TABLE employees;

 * postgresql://postgres:***@localhost:5432/dvdrental
Done.

[]

Explanation:
- DROP TABLE employees: Removes the employees table from the database.

Lecture 7: PostgreSQL part 4

Contents

Lecture 7: PostgreSQL part 4#

Introduction#

Learning Objectives#

Connecting to the Database#

Creating Tables#

PRIMARY KEY#

Example#

FOREIGN KEY#

Example#

UNIQUE#

Example#

NOT NULL#

Example#

DEFAULT#

Example#

CHECK#

Example#

Referential Actions#

Purpose#

Actions#

ON UPDATE CASCADE#

ON DELETE SET NULL#

ON DELETE CASCADE#

Modifying Tables#

Adding a Column#

Example#

Modifying a Column#

Example#

Dropping a Column#

Example#

Dropping Tables#

Example#