• Installing MySQL Server
• Installing MySQL Connector/Python

py -m venv <venv name>

<venv name>\Scripts\activate

(<venv name>)\<folder path>>

(venv) E:\python\mysqltest>

pip install mysql-connector-python

• Establishing a Connection
• Creating a New Database
• Connecting to an Existing Database

1. connect to the MySQL server
2. create a new database
3. connect to the newly created or an existing database
4. execute a SQL query and fetch results
5. inform the database if any changes are made to a table
6. close the connection to the MySQL server

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

CREATE DATABASE books_db;

cursor = connection.cursor()

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        create_db_query = "CREATE DATABASE online_movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(create_db_query)
except Error as e:
    print(e)

can display the name of all databases in the server
use the MySQLConnection object to execute the SHOW DATABASES statement

>>> show_db_query = "SHOW DATABASES"
>>> with connection.cursor() as cursor:
...     cursor.execute(show_db_query)
...     for db in cursor:
...         print(db)
...
('information_schema',)
('mysql',)
('online_movie_rating',)
('performance_schema',)
('sys',)

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        print(connection)
except Error as e:
    print(e)

• Defining the Database Schema
• Creating Tables Using the CREATE TABLE Statement
• Showing a Table Schema Using the DESCRIBE Statement
• Modifying a Table Schema Using the ALTER Statement
• Deleting Tables Using the DROP Statement

1. movies contains general information about movies with attributes
   • id
   • title
   • release_year
   • genre
   • collection_in_mil
2. reviewers contains information about people who posted reviews or ratings with attributes
   • id
   • first_name
   • last_name
3. ratings contains information about ratings that have been posted with attributes
   • movie_id (foreign key)
   • reviewer_id (foreign key)
   • rating

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        create_movies_table_query = """
    CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT)
    """
        
        create_reviewers_table_query = """
    CREATE TABLE reviewers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
)
"""

        create_ratings_table_query = """
    CREATE TABLE ratings (
    movie_id INT,
    reviewer_id INT,
    rating DECIMAL(2,1),
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id),
    PRIMARY KEY(movie_id, reviewer_id)
)
"""
        with connection.cursor() as cursor:       
            cursor.execute(create_movies_table_query)
            cursor.execute(create_reviewers_table_query)
            cursor.execute(create_ratings_table_query)
        connection.commit()

except Error as e:
    print(e)

1. transactional storage engines are transaction safe and allow rolling back transactions using simple commands like rollback
   many popular MySQL engines, including InnoDB and NDB, belong to this category
2. nontransactional storage engines depend on elaborate manual code to undo statements committed on a database
   MyISAM, MEMORY, and many other MySQL engines are nontransactional

DESCRIBE <table_name>

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        show_table_query = "DESCRIBE movies"
        with connection.cursor() as cursor:
            cursor.execute(show_table_query)
            # Fetch rows from last executed query
            result = cursor.fetchall()
            for row in result:
                print(row)
except Error as e:
    print(e)

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        alter_table_query = 'ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1)'
        show_table_query = "DESCRIBE movies"
        with connection.cursor() as cursor:
            cursor.execute(alter_table_query)
            cursor.execute(show_table_query)
            # Fetch rows from last executed query
            result = cursor.fetchall()
            print("Movie Table Schema after alteration:")
            for row in result:
                print(row)  
except Error as e:
    print(e)

drop_table_query = "DROP TABLE ratings"
with connection.cursor() as cursor:
    cursor.execute(drop_table_query)

• Using .execute()
• Using .executemany()

1. .execute() works well when the number of records is small and the records can be hard-coded
2. .executemany() is more popular and is better suited for real-world scenarios

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        insert_movies_query = """
INSERT INTO movies (title, release_year, genre, collection_in_mil)
VALUES
    ("Forrest Gump", 1994, "Drama", 330.2),
    ("3 Idiots", 2009, "Drama", 2.4),
    ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5),
    ("Good Will Hunting", 1997, "Drama", 138.1),
    ("Skyfall", 2012, "Action", 304.6),
    ("Gladiator", 2000, "Action", 188.7),
    ("Black", 2005, "Drama", 3.0),
    ("Titanic", 1997, "Romance", 659.2),
    ("The Shawshank Redemption", 1994, "Drama",28.4),
    ("Udaan", 2010, "Drama", 1.5),
    ("Home Alone", 1990, "Comedy", 286.9),
    ("Casablanca", 1942, "Romance", 1.0),
    ("Avengers: Endgame", 2019, "Action", 858.8),
    ("Night of the Living Dead", 1968, "Horror", 2.5),
    ("The Godfather", 1972, "Crime", 135.6),
    ("Haider", 2014, "Action", 4.2),
    ("Inception", 2010, "Adventure", 293.7),
    ("Evil", 2003, "Horror", 1.3),
    ("Toy Story 4", 2019, "Animation", 434.9),
    ("Air Force One", 1997, "Drama", 138.1),
    ("The Dark Knight", 2008, "Action",535.4),
    ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1),
    ("The Lion King", 1994, "Animation", 423.6),movies_insert_many
    ("Pulp Fiction", 1994, "Crime", 108.8),
    ("Kai Po Che", 2013, "Sport", 6.0),
    ("Beasts of No Nation", 2015, "War", 1.4),
    ("Andadhun", 2018, "Thriller", 2.9),
    ("The Silence of the Lambs", 1991, "Crime", 68.2),
    ("Deadpool", 2016, "Action", 363.6),
    ("Drishyam", 2015, "Mystery", 3.0)
"""
        with connection.cursor() as cursor:
            cursor.execute(insert_movies_query)
        connection.commit()
except Error as e:
    print(e)

1. a query which contains placeholders for the records that need to be inserted
2. a list which contains all records to be inserted

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        insert_reviewers_query = """
INSERT INTO reviewers
(first_name, last_name)
VALUES ( %s, %s )
"""
        reviewers_records = [
    ("Chaitanya", "Baweja"),
    ("Mary", "Cooper"),
    ("John", "Wayne"),
    ("Thomas", "Stoneman"),
    ("Penny", "Hofstadter"),
    ("Mitchell", "Marsh"),
    ("Wyatt", "Skaggs"),
    ("Andre", "Veiga"),
    ("Sheldon", "Cooper"),
    ("Kimbra", "Masters"),
    ("Kat", "Dennings"),
    ("Bruce", "Wayne"),
    ("Domingo", "Cortes"),
    ("Rajesh", "Koothrappali"),
    ("Ben", "Glocker"),
    ("Mahinder", "Dhoni"),
    ("Akbar", "Khan"),
    ("Howard", "Wolowitz"),
    ("Pinkie", "Petit"),
    ("Gurkaran", "Singh"),
    ("Amy", "Farah Fowler"),
    ("Marlon", "Crafford"),]

        with connection.cursor() as cursor:
            cursor.executemany(insert_reviewers_query, reviewers_records)
        connection.commit()
except Error as e:
    print(e)

can similarly use .executemany() to insert records in the ratings table

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        insert_ratings_query = """
INSERT INTO ratings
(rating, movie_id, reviewer_id)
VALUES ( %s, %s, %s)
"""
        ratings_records = [
    (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17),
    (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4),
    (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10),
    (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19),
    (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9),
    (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19),
    (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15),
    (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20),
    (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13),
    (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17),
    (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4),
    (9.8, 13, 1)]

        with connection.cursor() as cursor:
            cursor.executemany(insert_ratings_query, ratings_records)
        connection.commit()
except Error as e:
    print(e)

• Reading Records Using the SELECT Statement
• Filtering Results Using the WHERE Clause

query to select all records from the movies table

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = "SELECT * FROM movies LIMIT 5"
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            result = cursor.fetchall()
            for row in result:
                print(row)
except Error as e:
    print(e)

the LIMIT clause takes one or two nonnegative numeric arguments
when using one argument it specifies the maximum number of rows to return
when using both arguments cano specify the offset of the first row to return

SELECT * FROM movies LIMIT 2,5;

can query for selected columns

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = "SELECT title, release_year FROM movies LIMIT 5"
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for row in cursor.fetchall():
                print(row)
except Error as e:
    print(e)

SELECT title, collection_in_mil
FROM movies
WHERE collection_in_mil > 300;

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = """
SELECT title, collection_in_mil
FROM movies
WHERE collection_in_mil > 300
ORDER BY collection_in_mil DESC
"""
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for movie in cursor.fetchall():
                print(movie)
except Error as e:
    print(e)

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = """
SELECT CONCAT(title, " (", release_year, ")"), collection_in_mil
FROM movies
ORDER BY collection_in_mil DESC
LIMIT 5
"""
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for movie in cursor.fetchall():
                print(movie)
except Error as e:
    print(e)

• .fetchone() retrieves either the next row of the result, as a tuple, or None if no more rows are available.
• .fetchmany() retrieves the next set of rows from the result as a list of tuples
  it has a size argument (defaults to 1) which can be used to specify the number of rows to fetch
  if no more rows are available, then the method returns an empty list.

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = """
SELECT CONCAT(title, " (", release_year, ")"), collection_in_mil
FROM movies
ORDER BY collection_in_mil DESC
"""
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for movie in cursor.fetchmany(size=5):
                print(movie)
            cursor.fetchall()
except Error as e:
    print(e)

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = """
SELECT title, AVG(rating) as average_rating
FROM ratings
INNER JOIN movies
    ON movies.id = ratings.movie_id
GROUP BY movie_id
ORDER BY average_rating DESC
LIMIT 5
"""
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for movie in cursor.fetchall():
                print(movie)
except Error as e:
    print(e)

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        select_movies_query = """
SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num
FROM reviewers
INNER JOIN ratings
    ON reviewers.id = ratings.reviewer_id
GROUP BY reviewer_id
ORDER BY num DESC
LIMIT 1
"""
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for movie in cursor.fetchall():
                print(movie)
except Error as e:
    print(e)

• UPDATE Command
• DELETE Command

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        update_query = """
UPDATE
    reviewers
SET
    last_name = "Cooper"
WHERE
    first_name = "Amy"
"""
        with connection.cursor() as cursor:
            cursor.execute(update_query)
            connection.commit()
except Error as e:
    print(e)

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:
        update_query = """
UPDATE
    ratings
SET
    rating = 5.0
WHERE
    movie_id = 18 AND reviewer_id = 15;
"""
        check_query = '''
SELECT *
FROM ratings
WHERE
    movie_id = 18 AND reviewer_id = 15;'''
        with connection.cursor() as cursor:
            cursor.execute(update_query)
        connection.commit()
        with connection.cursor() as cursor:
            cursor.execute(check_query)
            result = cursor.fetchall()
            for row in result:
                print(row)
except Error as e:
    print(e)

if multi is True, then cursor.execute() returns an iterator
each item in the iterator corresponds to a cursor object that executes a statement passed in the query
the above code runs a for loop on this iterator and then calls .fetchall() on each cursor object

if no result set is fetched on an operation, then .fetchall() raises an exception
to avoid this error, in the code above you use the cursor.with_rows property
indicates whether the most recently executed operation produced rows

the WHERE clause is a prime target for web hackers in its current state
vulnerable to a SQL injection attack
a hacker might send a hidden command in the input

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A
Enter new rating: 5.0
Enter username: <user_name>
Enter password:
[(18, 15, Decimal('5.0'))]

to prevent such attacks don't add the query values provided by the user directly to the query string

from getpass import getpass
from mysql.connector import connect, Error

movie_id = input("Enter movie id: ")
reviewer_id = input("Enter reviewer id: ")
new_rating = input("Enter new rating: ")
update_query = """
UPDATE
    ratings
SET
    rating = %s
WHERE
    movie_id = %s AND reviewer_id = %s;

SELECT *
FROM ratings
WHERE
    movie_id = %s AND reviewer_id = %s
"""
val_tuple = (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, val_tuple, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

cursor.execute() makes sure that the values in the tuple received as argument are of the required data type
if a user tries to sneak in some problematic characters, then the code will raise an exception

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A
Enter new rating: 5.0
Enter username: 
Enter password:
1292 (22007): Truncated incorrect DOUBLE value: '15";
UPDATE reviewers SET last_name = "A'

to remove all ratings given by reviewer_id = 2, should first run the corresponding SELECT query

from getpass import getpass
from mysql.connector import connect, Error

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating"
    ) as connection:

        select_movies_query = """
SELECT reviewer_id, movie_id FROM ratings
WHERE reviewer_id = 2
"""
        with connection.cursor() as cursor:
            cursor.execute(select_movies_query)
            for movie in cursor.fetchall():
                print(movie)

except Error as e:
    print(e)

delete_query = "DELETE FROM ratings WHERE reviewer_id = 2"
with connection.cursor() as cursor:
    cursor.execute(delete_query)
    connection.commit()

1. mysqlclient is a library that is a close competitor to the official connector and is actively updated with new features
   its core is written in C so it has better performance than the pure-Python official connector
   a big drawback is that it's fairly difficult to set up and install, especially on Windows
2. MySQLdb is a legacy software that's still used in commercial applications
   it's written in C and is faster than MySQL Connector/Python
   is available only for Python 2

1. SQLAlchemy is an ORM that facilitates communication between Python and other SQL databases
   can create different engines for different databases like MySQL, PostgreSQL, SQLite, etc.
   commonly used alongside the pandas library to provide complete data-handling functionality
2. peewee is a lightweight and fast ORM that's quick to set up
   quite useful when the interaction with the database is limited to extracting a few records
3. Django ORM is one of the most powerful features of Django
   is supplied alongside the Django web framework
   can interact with a variety of databases such as SQLite, PostgreSQL, and MySQL
   many Django-based applications use the Django ORM for data modeling and basic queries but often switch to SQLAlchemy for more complex requirements