Job Ready SQL

Appendix ABonus Lesson on Applying SQL with Python

In this bonus lesson, you'll work with MySQL and Python. It is assumed that you already know how to program in Python, so the focus will be on learning how to manage databases, tables, and data.

Learning Objectives

By the end of this lesson, you will be able to:

• Use PyMySQL to connect a Python script to a MySQL instance
• Perform basic create, retrieve, update, and delete (CRUD) operations on a MySQL database using Python scripts, including:
  • Creating and deleting databases
  • Creating and deleting tables
  • Adding, updating, and deleting fields in a table
  • Adding, updating, retrieving, and deleting data in a table

Because it is assumed you are already familiar with Python, prior to starting this lesson, you should have the following:

• The PyMySQL package installed in Python
• A MySQL instance running in the background
• The username and password for the running MySQL instance

You may also find it useful to have an active MySQL window open (command line or GUI, such as MySQL Workbench) that you can use to confirm activities managed by Python during this lesson.

NOTE This lesson assumes a working understanding of basic CRUD operations in SQL. If you've covered the lessons in this book, then you should already be familiar with these!

DATABASE OPERATIONS

While it is possible to create databases in a MySQL interface and then connect to those databases using Python, you can use the PyMySQL package to manage all database operations ...