Practical SQL With Python In 3 Days: Beginner to Pro

Learn to work with SQLite in Python and databases, connecting to a database, creating tables, inserting, updating, and deleting data, and enforcing data integrity as foundational concepts.

Explore SQLite, an embedded serverless database stored as a single disk file. See its SQL compatibility and how Python's sqlite3 module provides a simple interface.

Import the sqlite3 module from the Python standard library, which provides a DB API 2.0 interface, and use sqlite3.connect to create a connection to a database file named First DB.

Explore the DB API 2.0, a Python standard interface that defines connect and exceptions, enabling compatibility across database modules and easy switching between SQLite, PostgreSQL, and MySQL drivers.

Explore how the connection object uses a cursor to execute sql, create table screen flavors with id as primary key, and verify results in a sqlite database via sql console.

Discover how dml, ddl, dcl, and dql differ, why inserts need commit while create table executes immediately, and how grant, revoke, and select fit in.

Show how to fetch data from a database using a cursor with fetch one, fetch many, and fetch all, including iteration, stop iteration handling, and lazy evaluation.

Master turning sqlite query results into a dictionary cursor via a row factory to enable name-based access to columns like flavor and rating.

Learn how a SQL transaction begins, executes a sequence of statements, and ends with commit or rollback to persist or discard changes across relational databases.

Learn how to prevent SQL injection by using parameterized statements with placeholders in sqlite3, and pass values as a tuple to the execute method.

Refine sql parameter handling by switching to named placeholders, using a dictionary for values and aligning keys. Named placeholders excel with many values, while positional ones suit a few.

Compare execute, execute many, and execute script patterns to run SQL statements in Python, including batching inserts with a list of tuples and committing changes, for DDL and DML workflows.

Learn to generate a complete sql dump of a database using the connection dump method and a generator, producing ddl and insert statements for file-based recreation.

Learn to use a Python database connection as a context manager, leveraging a with block to auto commit on success and auto rollback on error, and to close it.

Develop a Python freight manager with a user menu to add boxes, load containers, and view container summaries for profit estimates, with app-layer validation and relational data handling via skylight.

Create and activate a Python virtual environment to isolate project dependencies. Use the venv module and navigate the lib and bin folders across macOS and Windows.

Create a text-based freight manager interface with six options: add box type, shoebox types, load box to container, show containers, a summary report, and exit; loop after each choice.

Build a text-based freight manager interface in Python 310 with an infinite loop menu and user prompts. Wrap logic in a main menu function and exit with a goodbye message.

Sketch the data model for boxes and freight, with a box table (id, name, teacher, x y z) and a freight table linking box id via a foreign key.

Practice ddl for sql to capture the data model, create a database at startup, and enforce a box volume constraint under ten.

Execute ddl by creating an SQLite database and tables, define primary keys and foreign keys with cascade, and use drop-if-exists logic via a Python interface.

Enable foreign key constraints in sqlite to enforce the box_id foreign key on the freight table referencing boxes.id, by running pragma foreign_keys = 1 on the connection and commit.

This lecture covers implementing the add box type flow, prompting for a box name and three numeric dimensions with float-based validation, and extracting a reusable numeric input helper.

Add the box to the database by building a focused database helper for add box functionality, then integrate it into the interface and commit source attributes.

Add a box to the database via the add_box method using a connection and box attributes, with parameterized SQL, commit, and handling the max volume integrity error.

Display all box types with dimensions from the database, mapping persistence columns to readable labels like height and width. Implement functional retrieval and UI changes, with styling postponed.

The lecture demonstrates displaying all box types by using a get all boxes helper to fetch all records with a select star and present them as box type named tuples.

Learn to load a box by name into a container, validate its existence and space under 30 units, and provide descriptive feedback before returning to the main menu.

Build a load box menu that validates the box exists and the container has capacity, then insert into freight using box_id, with helpers to fetch boxes by name or id.

Create a database view to calculate the occupied volume for each container by joining freight with boxes, summing box dimensions, and grouping by container ID to simplify downstream queries.

Define a python container helper to fetch containers, compute occupied volume by box dimensions, enforce a 30 unit capacity, and prepare inserts into the freight table.

Complete the final stretch by implementing the insert method that links a box to a container, validates box and container ids, uses placeholders, and commits the transaction.

Configure seed data to boot the program with predefined box types, eliminating tedious input of box sizes and dimensions. We'll cover this setup in the next lecture.

Define a seed data function, randomize starter boxes, insert them into the database using a connection, and load them into containers to initialize the app and container view.

Implement the choice feature to display containers with a list of container IDs and their occupied volume, building on the existing view.

Implement a get all containers method to query the database, map results to a name tuple, and display a tabulated table of container ID and volume.

Complete the freight company's business summary statistics interface and estimate profit and loss. Compute revenue as 40 times volume and subtract 200 per container.

Compute a profit and loss report by aggregating freight data, containers, and contracted volume; calculate revenue at 40 per cubic meter and costs per container to reveal the bottom line.

Refactor magic numbers by moving them into a database-backed app config table of key-value pairs, load configuration at startup, and replace hard-coded constants throughout the app.

Refactor the configuration by adding an app config table, seeding values, and creating a get config helper to fetch key value pairs from SQLite with Python.

Extend database skills by starting with MySQL, building on SQLite concepts like connections, cursors, and transactions, and explore MySQL connector nuances, cursor types, and repair statements.

Discover MySQL, a popular open-source database management system with a client-server architecture used by WordPress, and learn to connect to a running MySQL server from Python to run SQL statements.

Compare local and cloud MySQL options for Python workflows. Learn to connect to a live cloud database, test connections, and use Railway's free tier with a temporary project URL.

Install the MySQL Connector/Python package, import connect from mysql.connector, and connect to the server using host, port, user, password, and database; verify the connection and close.

Learn to parse database connection URLs with Python's built-in URL library, extracting hostname, port, username, password, and path to build reliable SQL connections.

Explore cursors across SQLite and MySQL using connection.cursor, cursor.execute, and fetch methods. Commit changes to persist data and appreciate the DB API 2.0 standardization across engines.

Use parameterized inserts in Python to securely insert data with placeholders and execute many, ensuring SQL sanitization and protection against injections for single or multiple records.

Discover how prepared statements precompiled queries for repeated execution, giving a single compilation and faster bulk inserts with a prepared cursor and Q mark style placeholders in SQLite.

Fetch data with the MySQL connector by establishing a connection and cursor, executing select queries, and using fetch all, fetch one, and fetch many, with a context manager.

Buffered cursors fetch all query results into memory when buffer is true, preventing unread-result exceptions and allowing fetch-one, fetch-many, or fetch-all without extra server calls.

Use dictionary cursors in Python SQL connections by enabling the dictionary keyword argument to convert results to dictionaries for name-based access and retrieve fields like username and password by name.

Learn to use the name tuple cursor to access fields by name or position, switch from dictionary cursor, create a name tuple type like user, and embrace its immutable structure.

Learn to execute multiple sql statements from a multi-line script in Python by splitting on semicolons and iterating with a cursor to create users and pulse tables.

Learn to use a get DB connection with auto commit to run multi-statement SQL. Understand why you must exhaust the result iterator for reliable transactions.

Build a command line interface to enroll students in courses, enforce prerequisites with database-driven validation, and generate transcripts and GPA reports using a MySQL backend.

Set up a development environment with a virtual environment, Jupyter notebook, and a Railway database to explore sql and Python while managing ddl and seed data.

Learn to build a command line interface that interacts with the terminal and persists state in MySQL, using Typer and Rich to create commands like add student and add course.

Define a connection helper to establish a MySQL server connection and return the connection object. Practice a smooth introduction to practical sql with python by working through this first challenge.

Learn to configure a MySQL connection in Python using .env file and dotenv, load variables into os.environ, avoid hard coding credentials, and implement a helper with error handling for environments.

Explore defining sql ddl for the student and course tables with incrementing integer primary keys, and learn to extract the ddl to a standalone file for python execution.

Set up and run the DDL script to reset and configure a railway database, creating students and courses tables with key constraints.

Learn how the reset database command drops the database and recreates its tables, invoking all prior functionality with no arguments and a built-in confirmation step to prevent data loss.

Connect the interface to the reset database function, prompt for confirmation, and run the reset when the user confirms; upon success, recreate the students and courses tables.

Complete the add student command by collecting the first name, last name, and Unix ID from the user, and inserting a new user record into the database.

Add a new student by collecting first name, last name, and unix id with a dedicated helper using type annotations for input validation, inserting data via placeholders and committing.

Complete the add course command by inserting course records into the database using moniker, name, and department, building on the ad students approach.

Define a helper function to add a new course with moniker, name, and department, insert it into the courses table, and test the terminal command to verify the entry.

Implement a DDL for a prerequisite table linking courses to prerequisites and enforcing a 0–100 minimum grade with SQL data integrity rules; enable an add_prereq command in the interface.

Define the prereq data model with a prerequisites table linked by foreign keys to the courses table, enforce a 0–100 grade check, and implement add prereq command in the interface.

Address the inconsistency in prerequisites by making minimum grade explicit and non-null. Reset the database to apply changes and ensure the data model matches the interface.

Refactor query execution to reduce boilerplate by introducing a standalone query helper that takes a connection and a SQL statement and handles execution, with optional data and fetch arguments.

Learn to reduce boilerplate by building a reusable query helper that creates a cursor, executes statements, handles data and optional fetch, and commits or returns results.

Seed the database with sample student, course, and prerequisite data after resets to avoid a blank slate. Extend the query helper to support batch inserts using Python data structures.

Define a Python data module with students, courses, and prerequisites, and implement an initialize data routine using execute many to seed the database, with a with_data flag to control resets.

Demonstrates using typer to toggle data initialization with a reset database command, showing how --no-with-data clears tables and how --with-data initializes data for subsequent checks.

Add a new show prerequisites command that displays prerequisite courses with their minimum grade requirements for a given course moniker. Use rich tables to render a readable terminal table.

Define a show prerequisites helper to fetch a course's prerequisites from the database using a query helper, and prepare to display results in a Rich terminal table.

Enhance terminal output by building and styling tables with Rich, defining headers like prerequisites and minimum grade, transforming data to strings, and creating reusable helpers to render readable tables.

Learn to use the Python registrar API to show students by last name containing a pattern and to show courses by department, like computer science, with a pretty table display.

Build Python SQL helpers to display students and courses, using like with wildcards for last names and exact matches for departments, and print results in a simple user interface.

Add a student_course enrollment table with foreign keys to students and courses, capturing year and grade, validating grade 0–100, enforcing unique yearly enrollments, and enabling enroll command with a UI.

Model a student_course enrollment table, enforce data integrity with foreign keys and a unique (student, course, year) constraint, and implement an enrollment command to insert records.

Define the grid command to update an enrollment grade, with an optional year dimension and the current year fallback, demonstrated on a John Doe econ 101 example.

Use the set grade command with a database helper to update a specific student’s course grade, guarding against unintended updates by filtering on student, course, and year.

Apply database programming to enforce course prerequisites during enrollment, validating prerequisites before enrollment and contrasting approaches across application, Python, and database layers.

Validate prerequisites before enrolling students by using a before insert trigger to enforce minimum grades and raise a user defined exception when unmet.

Hide internal errors in a sql with python workflow by using try-except to catch database and integrity errors, and colorize error and success messages with typer during enrollment.

Enable verbose query execution in the reset database flow by adding a verbose flag to the interface, printing the executed sql statements and their statuses for clearer feedback.

Unroll students from a course using the onion roll command, with a year parameter that defaults to the current year, in this practical SQL with Python course.

Learn how to unenroll a student from a course using a Python registrar API, implementing a delete query on the student course table, and validating removal in the interface.

Add the current courses command that shows courses a student is currently taking, using the student's unique ID as the sole argument; if no grade exists, mark as in progress.

Build a current enrollment report by querying a student's courses with no grade, using a Python SQL helper; expose via a current courses for student command and print in green.

Execute a new Transcript command to display a student's completed courses with grades and years, and calculate the average GPA across those graded courses.

Guide the creation of a transcript feature by querying the student_course table for a student’s completed courses, display results with color, and compute an average GPA.

Add a new letter grade column to the transcript by converting integer grades to letter grades using thresholds: 90 for A, 80 for B, 70 for C, and so on.

Learn how to convert integer grades to letter grades using a threshold table in SQL, including joining thresholds to student grades and selecting the correct letter by ordering.

Implement the database integration by creating tables with ddl statements, seeding data on reset, and updating the transcript query to return course, grade, and letter grade.

Add a new command to display the most enrolled courses by counting all enrollments, current and historical, with an optional limit that defaults to ten.

Build a SQL query to fetch the top N most enrolled courses by joining courses and student_course, then group by course and order by enrollment.

Introduce the top students command, listing students by average grade earned, with an optional limit defaulting to ten, showing Unix IP, first name, last name, courses taken, and average grade.

Identify top performing students by querying the enrollment data, joining the students table on Unix ID, and computing each student's average grade, filtered by non-null grades and sorted descending.

Explore working with Postgres in Python, reviewing connections, cursors, query parameters, and statement execution from inside and outside context managers, then dive into dynamic SQL generation with the SQL module.

Install and verify the Postgres driver psycopg2, set up a virtual environment, and run a PyCharm notebook to explore its fast C implementation and multithreaded support.

Learn the precompiled alternative for psycopg2, comparing source builds with the binary package to simplify installation, avoid missing build dependencies, and address potential segmentation faults in multithreaded SSH apps.

Set up a database server, choosing local macOS or Windows installations or cloud services, and connect via a PostgreSQL data source using a connection URL.

Learn to connect to a Postgres database with psycopg2 using a parsed URL, extracting host, user, password, and database, while appreciating DB-API consistency across libraries.

Open a database connection, obtain a cursor, and execute SQL to create or drop tables; learn when to commit versus rely on context managers for automatic commit handling.

Insert multiple cpus into the cpus table using execute many with positional parameters or named parameters, using tuples or dictionaries and understanding placeholder syntax and parameter mapping.

Learn to build dynamic, safe sql queries in python by parameterizing tables, columns, and clauses. Use sql constructs like identifier, literal, and placeholder to create reusable, scalable queries.

Practice building dynamic SQL queries by replacing a fixed price threshold with a flexible where clause that accepts an arbitrary dictionary of column-value pairs, generating SQL fragments and placeholders.

Refactor code quickly by simplifying where checks, building composable SQL from inner conditions outward, and improving readability of dynamic query construction.

build a web guest book api with json responses, http basic auth, password hashing, email activation, private/public messages, and like constraints, using grass as the persistence layer.

Create and activate a Python virtual environment, then install dependencies such as Test API to begin working with SQL and Python.

Build a web-based API with FastAPI and uvicorn, define endpoints that return JSON, handle HTTP GET requests, and run on localhost:8000 with auto reload, persisting state in PostgreSQL.

Explore automatic documentation with FastAPI and Swagger, using the docs endpoint to visualize, test, and interact with your API via an open API spec.

Set up a Postgres database using railway, provision a new project, test the connection, and configure a data source to begin writing exploratory SQL and running initial queries.

Apply object-oriented design to build a generic database class, initialize attributes via __init__, and implement an open method to connect with psycopg2 using a URL, enabling instance-based data access.

Develop an object-oriented database type with open and close methods, manage a connection and dictionary cursor, and prepare for adding generic API-integrated data operations.

Load the database connection URL from a .env file with python-dotenv to enable environment-specific values. Expose the URL as an optional parameter and prepare for dependency injection.

Explore dependency injection in fast api by declaring a database dependency, injecting it into path operations with depends, and managing connections via a generator that opens and closes the database.

Create the users and tokens tables for registration. Define six user fields (email as primary key, password, active flag, two timestamps) and four token fields (token, user_id, created_at) with defaults.

Create tables using the progress dialect: users with id serial primary key and tokens with user_id foreign key referencing users on delete cascade.

Define the register endpoint with email and password as required query parameters, implement initial validation, and return a json response while planning to use the identity library in FastAPI.

Define a user type with the Pydantic base model, enforce email and password validation using an email string, and install the email validator to enable robust api input checks.

Register and validate a new user through an api endpoint, enforcing email format and password handling, and wrap validation in try-catch to return an error instead of a server error.

Hash passwords with a crypt context and store only the resulting hashes to protect user data, then verify plaintext inputs against the stored hash using the verify method.

Create a utils module with a password hash helper and a verify password helper, use get password hash to hash and store passwords during registration, and verify inputs at login.

Switch from get to post to create a user, enforce an eight-character password with a fastapi query parameter, and insert the email and hashed password into database via dependency injection.

Add and enforce a unique constraint on user emails in the database, handle unique violations gracefully in the UI, and display a friendly message when the email is already registered.

Use FastAPI's HTTPException to return accurate HTTP status codes, like 201 created and 400 bad request, by importing and using the status module.

Refactor your database write operation into an object-oriented method on the database type, returning the new record's primary key via a generalized insert using generated sql.

Refactor the database write operation to support inserting into a table with specified columns and values, generating dynamic SQL and returning the inserted record's ID.

Generate a version four universally unique identifier as a registration token in python, stringify it with the string builtin, and save it to the tokens table.

Learn how to generate a token, convert it to a string, and save it with a user ID in a tokens table. Observe the auto-incremented primary key and created timestamp.

Validate the activation token against the user’s stored token to activate the account. Create an activate endpoint under accounts, wire in database checks, and improve swagger grouping.

Extend the database type with a generic get one method that dynamically builds and runs a select query for any table and columns, returning the first result.

Implement a generalized get method with a limit parameter to fetch multiple records, and refactor get one as its special case with limit set to one, keeping the interface unchanged.

Implement a generalized get with an optional limit parameter, extending the get method to support limited queries and return the first dictionary for get one to maintain interface compatibility.

Implement an update method in the database type to modify specified columns or entire rows, support multiple columns and conditions, generate dynamic SQL, and return the number of affected rows.

Implement a database update method that specifies a table, columns, and new values, with an optional where clause, using a generator-based SQL builder for safe substitutions and cursor execution.

Integrate the update method to complete the activate workflow for the /activate endpoint by verifying tokens, activating user accounts, and recording activation timestamps, while raising exceptions on invalid tokens.

Locate the activation token in the database, retrieve the associated user ID, and set that user's active status to true with the current timestamp, returning invalid token when not found.

activate account once only when inactive; set active to true and record the current timestamp for valid tokens tied to an inactive user, using the activate endpoint.

Build a messages endpoint for the guest book in FastAPI using form data and multipart/form-data, posting to /messages, returning the message, with persistence added later.

Compare query parameters and form data in http requests, showing how data appears in the url versus the body. Clarify media types, url encoding, and python multipart for file uploads.

Reorganize a fastapi project by introducing api routers to group endpoints under accounts and messages, refactor the main interface, and isolate dependencies, schemas, and utilities for clarity and scalability.

Create a guest_book table for user messages. Define a primary key, non-empty content, a user_id foreign key to users, a private flag, and created_at and updated_at timestamps for data integrity.

Persist messages by creating a guestbook table with user_id referencing users, a private flag, and timestamps, then insert in the messages router via dependency injection and return the message id.

Identify and fix a timestamp typo, update it correctly, drop and recreate the guestbook table, and perform a sanity check to ensure the update is correct.

Implement http basic authentication with a security context to validate credentials against the database, verify password against stored hash, ensure active accounts, and return the user id for authenticated requests.

Integrate a validate user dependency into the authenticated post messages endpoint to obtain the user ID, ensure the account is active, and persist messages via the database.

Fix a bug in account activation by extracting the active boolean from the user dictionary, replacing the flawed dictionary-based check and correctly handling 'account already activated' errors.

Implement a put route to update a message by path /messages/{id} for authenticated users, enforcing ownership with existing database methods and validation dependencies.

Learn how to implement a PATCH /messages endpoint that updates parts of a message, with path parameter message_id, handling authentication, ownership checks, and partial field updates.

Implement an authenticated get by id endpoint to fetch a single message, enforce access to hide private messages from others, and return json with id, content, created_at.

Define a get message route at /messages/{message_id} that fetches a message from the database. Use authenticated user info and exclude user_id and private from the response.

Implement an authenticated get messages route that returns all public and owned private messages, limited by a num parameter, as an array of objects with id, message, and created at.

Implement a get /messages route that returns public messages and private messages owned by the authenticated user, defaulting to ten results, by combining two data sets from the database.

Refactors the get messages query to fetch public and private messages for the authenticated user in one go using an or where clause, boosting performance.

Develop a dynamic delete method in a database class that builds SQL from a table name and a conditions dictionary, executes it, and returns the number of affected rows.

Implement a delete operation in Python SQL code. Build a composable delete statement with an optional where clause, execute via a cursor, and return the number of affected rows.

Refactor database methods by introducing a private static helper that generates SQL fragments in the form key = value with a separator, reducing repetition across get, update, and delete operations.

Implement an authenticated http delete route that lets users delete only their own messages, enforce login and ownership, preventing deletion of others' public messages, using the database delete method.

Integrate a delete endpoint into the messages interface using HTTP delete, validate authentication and ownership, delete by message id, and return a 'message deleted' on success or 404/403 errors.

Implement a dynamic sql contains method using the like operator to search across multiple columns and return matching records in a limited result set.

Implement a get contains method that searches a table across multiple columns using the SQL like operator, building dynamic query parts with identifiers, literals, and an optional limit.

Refactor the existing get method to add a contains dictionary for multiple search terms across columns using like, and handle both where and contains logic to return matching records.

Implement a new authenticated route for message search using a like operator to return all public messages or private messages of the authenticated user that mention the search term.

Implement a FastAPI message search endpoint that returns public messages and the authenticated user's private messages, using a search term, a limit, and a contains-based query with proper query construction.

Refactor the get function to generate coherent SQL for the messages search, prioritizing a contains clause, where and or where blocks, and clearer, reusable query components.

Refactor the final .get() to render SQL with a connection context, removing self references and restructuring contains and where clauses for valid SQL.

Create the upvotes table with four columns: id (primary key), user_id (references to the user's primary key), message_id (references to the guest book id), and created_at, to support uploading functionality.

Learn to define an upvotes table with proper ddl, including primary key, foreign keys to users and messages, cascade on delete, and default timestamps for data integrity.

Create an authenticated route at messages/{messageid} to upvote messages, ensuring only identified users can do it, restricting private messages from being upvoted and preventing multiple votes on a single message.

Introduce the upvote interface in the API with a post route using message_id, enforce authentication, validate message existence and privacy, prevent self and duplicate upvotes, and record the vote.

Diagnose and fix a bug in the message upload flow by correcting the ownership check: ensure the message owner user id differs from the authenticated user.

Implement an unauthenticated http get route returning ten most popular guest book messages, ordered by upvotes, as a json list including id, content, and upvotes; use a view for join.

Learn to compute top messages by left joining guestbook with votes, filtering public messages, and grouping and ordering by upvotes, exposed via a database view and public route.

Learn to send registration tokens by email using Python and Gmail, enabling two-factor authentication, generating an app password, and configuring a lightweight SMTP client to deliver tokens.

Learn to implement an email activation flow by sending activation links via Gmail SMTP, using environment variables for credentials, and composing a dynamic activation URL with a token.

Apply two tweaks: return the activation URL when sending email fails, and remove the Swagger schema section by adjusting Swagger UI parameters.

Isolate dependencies with a requirements file, then deploy the API using the data CLI by logging in, creating a micro, and deploying to a data cloud endpoint.

Deploy and test a python-driven api linked to a cloud Postgres database. Configure environment variables, register and activate accounts, authenticate users, and post, upvote, and delete messages.

We wrap up a three-day journey from space database connection to a deployed API, highlighting SQLAlchemy Core for dynamic queries. Prioritize raw SQL and database drivers.