Database Programming with Python

Explore the fundamentals of relational databases, including tables, normalization, primary and foreign keys, and constraints, while designing a five-table help desk schema for vector Corp with Python.

This lecture presents the relational model with tables and primary and foreign keys. It covers constraints, autoincrement keys, and SQL querying, noting declarative versus procedural approaches.

Plan the database content structure, determine purpose, information, tables, fields, and relationships, review access, and create and test; normalization helps reduce duplicates.

Explore building tables, records, and fields (columns), assign data types and sizes, and normalize the database by establishing keys and relationships across related tables.

Define each table column's data type and size to guide SQL in storing values. Use types such as char, varchar, text, int, date, time, timestamp, and boolean, with cross-db variations.

Explore how normalization organizes data into tables with primary and foreign keys to remove redundancy and inconsistent dependencies, achieving first and third normal forms for scalable databases.

Learn how normalization creates separate tables and uses primary and foreign keys to manage one-to-many, one-to-one, many-to-many, and self-referencing relationships.

Build a help desk ticket allocation database for Vector Corp., featuring five tables: employees, roles, tickets, solutions, and status, linked by foreign keys to manage customer submissions and staff responses.

Explore the employees table with an auto-incrementing employee id primary key, including name, username, password, email, a role id foreign key to roles, and a link to tickets.

Explore the roles table in a database, linked to employees, with an auto-incrementing role_id primary key and a role field (up to 15 chars) including support, developer, and administrator.

Create and manage the tickets table to track customer issues, linking to employees, solutions, and status through foreign keys, with fields for issue details, customer info, and submitted date.

The solutions table uses solution_id as the primary key to reference customers having problems and creates a foreign key relationship with the tickets table.

Learn to design a status table with an auto-incrementing status_id primary key for open, in-progress, and closed states, and relate it to the tickets table via status_id.

Create the helpdesk database in sqlite using sequel lite studio, defining five tables—employees, roles, tickets, solutions, and status—with basic columns and primary keys, preparing for future relationships.

Create a database and tables in SQL Server Management Studio, including employees, roles, tickets, status, and solutions, with primary keys, identity, and not-null constraints, preparing relationships in the next lab.

Create a MySQL database and tables with phpMyAdmin, defining columns, data types, and auto-incrementing primary keys. Build employees, roles, tickets, status, and solutions tables, and prepare to establish relationships.

Establish relationships for all tables in the help desk database by defining foreign keys to employees, tickets, status, and solutions from their primary keys.

Establish relationships in SQL Server by creating foreign keys between tickets and related tables (solutions, statuses, employees) using a visual database diagram to define constraints and cascading delete options.

Establish relationships between employees, tickets, status, solutions, and roles by linking primary keys to foreign keys with the database designer, creating the necessary indexes for a complete usable database.

Learn how to manually add data to SQLite Studio tables by inserting multiple rows into solutions, statuses, and roles, and verify autoincremented ids.

Add data to SQL Server databases using SQL Server Management Studio, inserting records into rolls, solutions, and status tables; practice insert, update, delete, and browse on employees and tickets.

Practice manually adding data to MySQL tables like roles, solutions, and status using insert, edit, and delete in a web-based admin interface.

Master the fundamentals of SQL, including SELECT, WHERE, INSERT, UPDATE, and DELETE commands, aliases, and common SQL functions. Learn to perform CRUD operations in Python, and join related tables.

Explore SQL to select, extract, insert, update, and delete data from relational databases using Python, and learn core statements for creating and altering tables.

Learn how the select command retrieves data from a database, using select and from, with all fields or specific columns, and explore distinct, top, and limit options.

Learn to filter and order data using the where and order by clauses in SQL, with practical examples on employees and roles, including null handling and ascending or descending sorting.

Explore SQL operators for advanced querying, including and, not, or, in, between, like and the percent wildcard, within the where clause, plus arithmetic and comparison operators.

Learn to insert data into a database using the insert into command, specify table and fields, supply values in order, and understand auto-incremented IDs and data type constraints.

Use the update command to modify existing database records by setting fields to new values, and rely on a where clause or unique id to target a specific row.

Use the delete command to remove specific records with a where clause, avoiding deleting all rows. The lesson shows deleting by employee id or criteria and confirms changes with select.

Learn how SQL expressions create calculated values, combine first and last names with the as keyword to form a virtual field, and use aliases to streamline Python database queries.

Explore common SQL functions such as count, average, sum, min, max, along with group by and having, with notes on syntax differences across databases and date functions.

Explore inner and outer joins to combine data from related tables using primary and foreign keys, with practical examples from employees and roles.

Connect Python to relational databases, set up the project, import required modules, and connect to three database solutions to perform select, insert, update, delete, handle exceptions, and access views.

Set up a Python database project in Eclipse and import the necessary modules. Create three files for SQLite, SQL Server, and MySQL to prepare for using different databases.

Connect to a SQLite database by calling the connect method with the database file path, then iterate through data and perform crud operations, finally closing the connection to free resources.

Learn how to connect to a MySQL database from Python by constructing a connection string with an odbc driver, and managing server, database, user, and password details, then close connection.

Construct a Python connection string to connect to a SQL Server database via an ODBC driver, specifying driver, server, database, user id, and password; then open and close the connection.

Execute select statements in python by creating a cursor from the connection and running the query, with optional parameters. Automatically close the cursor with a closing context to release resources.

Learn how to retrieve rows in Python using cursor.fetchone and cursor.fetchall, access data by column index or by name with a row factory, and display employee details from a query.

Learn how to create and query database views to simplify complex joins, using SQLite, SQL Server, and MySQL, then connect to these views from Python with simple select statements.

Insert data with parameterized statements and commit to save changes. See an example adding an employee to the employees table using name, username, password, email, and role id.

Update an existing record with an update statement that sets name and password via parameters and a where clause on employee id, including verification and password hashing and salting.

Learn to delete data from a database with a delete from employees where employee_id equals the parameter passed in, using the commit method, and relate it to insert and update.

Apply try statements to catch database exceptions in Python, print errors, and set variables to None, then log issues to a logfile while executing queries and handling results.

This lecture introduces building a two-part admin and help desk dashboard in three layers—UI, object, and database—focusing on employee and ticket management, login security, and labs for practice.

Set up a three-layer Python dashboard (db, employee object, ui) and migrate a sqlite database, implementing employee CRUD and exploring an ORM to simplify data access.

Establishes the database layer for the admin dashboard, enabling create, read, update, and delete operations on employees via a connection, cursor, and SQL queries.

Create the employee object layer by defining an Employee class with id, name, username, password, email, and role id, and map database rows to Employee objects with a make_employee function.

Build a Python user interface layer for a help desk database, connecting to the database and providing a command-driven admin menu with view, add, and delete employee operations.

Show the user role by joining the roles table, extend the employee object to include role, and update UI and queries to display the role name instead of the ID.

Build an admin login for the console by adding a database query that checks username, password, and role id, then loop until valid credentials grant access to the admin menu.

Set up the help desk dashboard by structuring Python project with admin and help desk components, configuring the database path, and creating db, objects, and ui modules.

Create a ticket object for the helpdesk tickets table with properties like id, customer name, customer email, submitted date, employee, status, and issue, using dynamic typing for flexible data types.

Create the database layer by implementing connect and close, and functions to create tickets, get open tickets, fetch a ticket’s issue, and update ticket status via joins with related tables.

Design the user interface layer for a Python help desk app, wiring console menus to view, create, and update tickets, view ticket issues, and handle login and dates.

Build a lab feature to display employee names, IDs, and roles when adding a ticket by querying the employees_roles view and returning results via a new get_employees function.