Python · Advanced level

Advanced level · Module 3 of 6 · 24 guided hours

Python and SQL Databases

Store application data reliably in a relational database and access it safely from Python.

Learning objectives

  • Design simple related tables
  • Query and update data with SQL from Python
  • Use transactions and protect against SQL injection

Topics

  • relational tables and keys
  • SQL SELECT/INSERT/UPDATE/DELETE
  • sqlite3 module
  • parameterised queries
  • transactions and backups

Lessons

  1. Lesson 1 Relational Data and SQL Essentials

    Design tables with primary and foreign keys and write core SQL statements.

    tableprimary keyforeign keyJOIN

  2. Lesson 2 Using sqlite3 from Python Safely

    Connect to a database from Python and run parameterised queries that are safe from SQL injection.

    connection/cursorparameterised querySQL injectionrow_factory

  3. Lesson 3 Reporting Queries with JOIN and GROUP BY

    Combine related tables and summarise them to produce the reports a business actually needs.

    JOINGROUP BYaggregateview

  4. Lesson 4 Transactions, Integrity and Backups

    Keep data consistent when several changes must succeed or fail together, and protect it with backups.

    transactioncommit/rollbackconstraintbackup

Practical activity

Migrate the shop data from CSV files to SQLite and rebuild the reports from SQL queries.

Expected outcomes

  • Reliable, consistent application data
  • Secure database access habits

Assessment

Practical test: schema, queries and a transaction-based update checked against a marking guide.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.