Mirror Mirror Study - 6

Chapter 4 covered the basic concepts of relational databases and how to work with sqlite3. Data is stored in tables made up of rows and columns, each row is distinguished by a unique key, and tables can also be linked to other tables.

The core SQL commands covered were as follows.

• create table: Create a new table
• select: Query data from specific columns
• where: Filter rows based on conditions
• insert into: Add data

With Python's built-in sqlite3 module, I could use a lightweight database without installing a separate DB. I created an in-memory DB with :memory:, accessed a file-based DB with sqlite3.connect(), and handled table creation and saving with execute() and commit().

Inserting and querying data followed the same flow in practice. I prepared a list of tuples, bound variables in the form values(?,?,?,?), inserted multiple rows using execute() or executemany(), and retrieved the results with fetchall(). When updating records was needed, I created a cursor with cursor(), ran INSERT INTO or UPDATE, and saved the changes with commit().

In the advanced session, I used psycopg2 and SQLAlchemy to connect to a database server and run SQL queries, and also used the getpass module when entering login credentials in Jupyter Notebook. It was easy to understand since I could use queries I'd actually used at work directly in Python, and practicing with the company's actual DB was especially helpful.