Data Management With Python - 2
Table of Contents
Introduction
In our previous article, we explored ways to work with data in Python and focused on two methodologies - working with CSV files, where we read a flat file into memory and perform operations on the data, and exploring persistence with databases, where we discovered SQLite. This post continues that journey, delving deeper into relational database properties.
A relational database gets its name from its ability to store relationships between data. It does this by treating data as a series of tuples, similar to rows in a CSV file. The relationships are established through the use of keys, including primary keys and foreign keys.
Primary keys are unique identifiers for a tuple, often as auto-incremented integers, and serve as a unique identifier. Foreign keys, on the other hand, are references to primary keys in another table, indicating a relationship between tuples.
Note: Foreign key doesn’t have to be to a different table. In fact, it can be to another row in the same table. This new column(Alias) allows us to indicate that Stephen and Richard are the same person.
SQL has a fundamental set of concepts that are universally adopted by all databases. One such database, SQLite, offers a user-friendly command-line tool that functions similarly to a Python REPL. This tool, called sqlite3, allows for direct interaction with the database by simply specifying the file name that will serve as the storage mechanism.
Exploring SQLite Command Line Tool
creating a database called “books.db”
sqlite3 books.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> -- Creation
sqlite> CREATE TABLE author (
..> author_id INTEGER NOT NULL PRIMARY KEY,
..> first_name VARCHAR,
..> last_name VARCHAR
..> );
Checking all the Tables available in the Books Database:
sqlite3 books.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .tables
author
we can see the table we had previously created persisted even after we had closed the session. Next we can explore more with the tables in the books database
Common SQL commands - SELECT, INSERT, UPDATE, DELETE (CRUD)
sqlite> SELECT * FROM author;
1|Isaac|Asimov
2|Pearl|Buck
sqlite> INSERT INTO author (first_name,last_name) VALUES ("Tom","Clancey");
sqlite> SELECT * FROM author;
1|Isaac|Asimov
2|Pearl|Buck
3|Tom|Clancey
sqlite> UPDATE author SET last_name = "Clancy" WHERE author_id = 3;
sqlite> SELECT * FROM author;
1|Isaac|Asimov
CREATE TABLE book (
...> book_id INTEGER NOT NULL PRIMARY KEY,
...> title VARCHAR,
...> author_id INTEGER REFERENCES author
...> );
sqlite> .tables
author book
sqlite> INSERT INTO book (title,author_id) VALUES ("Foundation",1);
sqlite> SELECT * FROM book;
1|Foundation|1
sqlite> INSERT INTO book (title,author_id) VALUES ('The Good Earth',2);
sqlite> SELECT * FROM book;
1|Foundation|1
2|The Good Earth|2
sqlite> -- join
sqlite> SELECT
...> a.first_name || ' ' || a.last_name AS author_name,
...> b.title as book_title
...> FROM author a
...> JOIN book b
...> ON b.author_id = a.author_id
...> ORDER BY a.last_name ASC;
Isaac Asimov|Foundation
Pearl Buck|The Good Earth
Note the foreign key in the book table specified by using the references keyword. author_id integer references author says that the book table contains a foreign key to the table author.
In the next post we will discuss more upon the tables we created in SQLite and dive deeper into SQLAlchemy - A Database toolkit for Python.