SQL (structured query language) - used to describe database structure, manage data (add, edit, delete, receive), manage access rights to database and its objects, and manage transactions.

SQL language is divided into the following categories:

  • DDL (Data Definition Language)

  • DML (Data Manipulation Language)

  • DCL (Data Control Language)

  • TCL (Transaction Control Language)

Each category has its own operators (not all operators are listed):

  • DDL

    • CREATE - create new table, DBMS, schemas

    • ALTER - change of existing table, columns

    • DROP - removing existing objects from DBMS

  • DML

    • SELECT - data selection

    • INSERT - adding new data

    • UPDATE - updating existing data

    • DELETE - deleting data

  • DCL

    • GRANT - Allow users to read/write certain objects to DBMS

    • REVOKE - - withdrawal of prior authorizations

  • TCL

    • COMMIT - committing of transaction

    • ROLLBACK - rollback of all changes made in the current transaction

SQL and Python#

Two approaches can be used to work with a relational DBMS in Python:

  • work with a library that corresponds to a specific database and use SQL language to work with database. For example, sqlite uses sqlite3 module

  • work with ORM which uses an object-oriented approach to work with database. For example, Sqlalchemy