A CRUD app with Python and MySQL

Before any theory, Nitish builds a tiny register / login program in Python backed by a MySQL database. The aim is to show you what a database is for: every real application (Google, Facebook, any app you use) stores its data in one and talks to it from code.

Setup: what you need

pip install mysql-connector-python

The project has two files:

• main.py: the application (menus, user input, what to show).
• dbhelper.py: everything that talks to the database.

Databases hold tables, tables hold rows

An important idea: you never store data directly "in a database". Inside a database you create tables, and data lives in a table's rows and columns.

In phpMyAdmin he creates a new database and, inside it, a users table for registration with four columns:

The equivalent SQL (you will learn to write this yourself in Chapter 7):

CREATE TABLE users (
    user_id  INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(255),
    email    VARCHAR(255),
    password VARCHAR(255)
);

CRUD: the only four things you ever do with data

Whatever project you build with a database, every interaction is one of four operations:

You use 50 apps a day; imagine any action in any of them and it is always one of these four. Thinking "divide and rule" this way makes working with databases much easier.

In phpMyAdmin you can do all four by hand: Insert a row (leaving user_id blank, since auto-increment fills it), Browse/Search (read), Edit (update) and Delete. But real applications don't open phpMyAdmin; they do the same four operations through code, in Python or any other language. That's what we build next.

Connecting Python to MySQL (dbhelper.py)

Create a DBHelper class. Its constructor connects to the database once; afterwards every method uses that connection.

import mysql.connector
import sys

class DBHelper:

    def __init__(self):
        # connect to the database once, when the object is created
        try:
            self.conn = mysql.connector.connect(
                host="localhost",
                user="root",
                password="",          # XAMPP's default root password is empty
                database="my_app_db"
            )
            self.mycursor = self.conn.cursor()
        except:
            print("Some error occurred. Could not connect to database")
            sys.exit(0)
        else:
            print("Connected to database")

• Connection object (self.conn): your live link to the database.
• Cursor (self.mycursor): the object you use to execute SQL and fetch results.
• try / except: many things can go wrong (wrong host, wrong password, a database name that doesn't exist, MySQL not running). Nitish demonstrates several of these failures live; on any of them, print a message and exit with sys.exit(0) instead of crashing later.

Create: registration

In main.py, a menu-driven program asks the user what to do. Nitish builds the menu with plain input() (a GUI would need more setup):

import sys
from dbhelper import DBHelper

class Flipkart:

    def __init__(self):
        self.db = DBHelper()       # connect once
        self.menu()

    def menu(self):
        user_input = input("""
        1. Enter 1 to register
        2. Enter 2 to login
        3. Anything else to leave
        """)
        if user_input == "1":
            self.register()
        elif user_input == "2":
            self.login()
        else:
            sys.exit(1000)

    def register(self):
        name = input("Enter the name: ")
        email = input("Enter the email: ")
        password = input("Enter the password: ")

        response = self.db.register(name, email, password)
        if response:
            print("Registration successful")
        else:
            print("Registration failed")
        self.menu()

obj = Flipkart()

The database work lives in DBHelper.register. It builds an INSERT query, executes it, and commits:

    def register(self, name, email, password):
        try:
            self.mycursor.execute("""
            INSERT INTO users (user_id, name, email, password)
            VALUES (NULL, '{}', '{}', '{}')
            """.format(name, email, password))
            self.conn.commit()     # make the change permanent
        except:
            return -1
        else:
            return 1

• NULL for user_id: auto-increment fills it in.
• conn.commit(): without it, the insert isn't saved. (Commit belongs to TCL, see Chapter 5.)
• Return 1 / -1 so the caller can print success or failure.

Read: login

Login is a read: find a row whose email and password match. fetchall() returns a list of matching rows (each a tuple).

    def search(self, email, password):
        self.mycursor.execute("""
        SELECT * FROM users WHERE email LIKE '{}' AND password LIKE '{}'
        """.format(email, password))
        data = self.mycursor.fetchall()
        return data

    def login(self):
        email = input("Enter the email: ")
        password = input("Enter the password: ")

        data = self.db.search(email, password)
        if len(data) == 0:
            print("Incorrect email/password")
            self.login()
        else:
            print("Hello", data[0][1])   # row 0, column 1 = name
            # next: a logged-in menu (view / edit profile, delete account…)

How do we decide whether the login worked? Simply by whether the query returned any rows. An empty list means no user has that email + password.

Try it: the same app, running in your browser

This demo runs a real SQL engine in the page (SQLite compiled to WebAssembly, close enough to MySQL for these queries). Register a few users, log in, and watch the exact SQL each button generates.

Update and delete: the logged-in menu

After a successful login, the app shows a second menu (for example: view profile, edit profile, delete account, log out). Each option is one more CRUD method on DBHelper:

-- Update: edit profile
UPDATE users SET name = 'Nitish Singh' WHERE user_id = 1;

-- Delete: delete account
DELETE FROM users WHERE user_id = 1;

In Python, the same pattern applies: execute(...) then conn.commit(). The full syntax of INSERT, SELECT, UPDATE and DELETE is the subject of the DML chapter.

Recap

Conceptual questions

These check whether you understand why, not just what. Several go one step beyond the video.