D# 0x0F. Python – Object-relational mapping

## Learning Objectives

### General

– Why Python programming is awesome
– How to connect to a MySQL database from a Python script
– How to SELECT rows in a MySQL table from a Python script
– How to INSERT rows in a MySQL table from a Python script
– What ORM means
– How to map a Python Class to a MySQL table
– How to create a Python Virtual Environment

## Requirements

### General

– Allowed editors: vi, vim, emacs
– All files will be interpreted/compiled on Ubuntu 20.04 LTS using python3 (version 3.8.5)
– Files will be executed with MySQLdb version 2.0.x
– Files will be executed with SQLAlchemy version 1.4.x
– The first line of all files should be exactly #!/usr/bin/python3
– A README.md file, at the root of the folder of the project, is mandatory
– The code should use the pycodestyle (version 2.8.*)
– All files must be executable
– The length of files will be tested using wc
– All modules should have a documentation (python3 -c ‘print(__import__( my_module ).__doc__)’)
– All classes should have a documentation (python3 -c ‘print(__import__( my_module ).MyClass.__doc__)’)
– All functions (inside and outside a class) should have a documentation (python3 -c ‘print(__import__( my_module ).my_function.__doc__)’ and python3 -c ‘print(__import__( my_module ).MyClass.my_function.__doc__)’)
– A documentation is not a simple word, it’s a real sentence explaining what’s the purpose of the module, class or method (the length of it will be verified)
– You are not allowed to use execute with sqlalchemy

## More Info

### Install MySQL 8.0 on Ubuntu 20.04 LTS

“`
$ sudo apt update
$ sudo apt install mysql-server
…
$ mysql –version
mysql Ver 8.0.25-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
$
“`

Connect to your MySQL server:

“`
$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> quit
Bye
$
“`

### Install MySQLdb module version 2.0.x

For installing MySQLdb, you need to have MySQL installed: How to install MySQL 8.0 in Ubuntu 20.04

“`
$ sudo apt-get install python3-dev
$ sudo apt-get install libmysqlclient-dev
$ sudo apt-get install zlib1g-dev
$ sudo pip3 install mysqlclient
…
$ python3
>>> import MySQLdb
>>> MySQLdb.__version__
‘2.0.3’
“`

### Install SQLAlchemy module version 1.4.x

“`
$ sudo pip3 install SQLAlchemy
…
$ python3
>>> import SQLAlchemy
>>> SQLAlchemy.__version__
‘1.4.22’
“`

Also, you can have this warning message:

“`
/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py:552: Warning: (1681, ‘@@SESSION.GTID_EXECUTED’ is deprecated and will be re
moved in a future release. )
cursor.execute(statement, parameters)
“`

You can ignore it.

## Tasks

### 0. Get all states

Write a script that lists all states from the database hbtn_0e_0_usa:

– Your script should take 3 arguments: mysql username, mysql password and database name (no argument validation needed)
– You must use the module MySQLdb (import MySQLdb)
– Your script should connect to a MySQL server running on localhost at port 3306
– Results must be sorted in ascending order by states.id
– Results must be displayed as they are in the example below
– Your code should not be executed when imported

“`
guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql
— Create states table in hbtn_0e_0_usa with some data
CREATE DATABASE IF NOT EXISTS hbtn_0e_0_usa;
USE hbtn_0e_0_usa;
CREATE TABLE IF NOT EXISTS states (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO states (name) VALUES ( California ), ( Arizona ), ( Texas ), ( New York ), ( Nevada );

guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql | mysql -uroot -p
Enter password:
guillaume@ubuntu:~/0x0F$ ./0-select_states.py root root hbtn_0e_0_usa
(1, ‘California’)
(2, ‘Arizona’)
(3, ‘Texas’)
(4, ‘New York’)
(5, ‘Nevada’)
guillaume@ubuntu:~/0x0F$
“`

**Repo:**

– GitHub repository: alx-higher_level_programming
– Directory: 0x0F-python-object_relational_mapping
– File: 0-select_states.py

### 1. Filter states

Write a script that lists all states with a name starting with N (upper N) from the database hbtn_0e_0_usa:

– Your script should take 3 arguments: mysql username, mysql password and database name (no argument validation needed)
– You must use the module MySQLdb (import MySQLdb)
– Your script should connect to a MySQL server running on localhost at port 3306
– Results must be sorted in ascending order by states.id
– Results must be displayed as they are in the example below
– Your code should not be executed when imported

“`
guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql
— Create states table in hbtn_0e_0_usa with some data
CREATE DATABASE IF NOT EXISTS hbtn_0e_0_usa;
USE hbtn_0e_0_usa;
CREATE TABLE IF NOT EXISTS states (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO states (name) VALUES ( California ), ( Arizona ), ( Texas ), ( New York ), ( Nevada );

guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql | mysql -uroot -p
Enter password:
guillaume@ubuntu:~/0x0F$ ./1-filter_states.py root root hbtn_0e_0_usa
(4, ‘New York’)
(5, ‘Nevada’)
guillaume@ubuntu:~/0x0F$
“`

**Repo:**

– GitHub repository: alx-higher_level_programming
– Directory: 0x0F-python-object_relational_mapping
– File: 1-filter_states.py

### 2. Filter states by user input

Write a script that takes in an argument and displays all values in the states table of hbtn_0e_0_usa where name matches the argument.

– Your script should take 4 arguments: mysql username, mysql password, database name and state name searched (no argument validation needed)
– You must use the module MySQLdb (import MySQLdb)
– Your script should connect to a MySQL server running on localhost at port 3306
– Results must be sorted in ascending order by states.id
– Results must be displayed as they are in the example below
– Your code should not be executed when imported

“`
guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql
— Create states table in hbtn_0e_0_usa with some data
CREATE DATABASE IF NOT EXISTS hbtn_0e_0_usa;
USE hbtn_0e_0_usa;
CREATE TABLE IF NOT EXISTS states (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO states (name) VALUES ( California ), ( Arizona ), ( Texas ), ( New York ), ( Nevada );

guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql | mysql -uroot -p
Enter password:
guillaume@ubuntu:~/0x0F$ ./2-my_filter_states.py root root hbtn_0e_0_usa ‘Arizona’
(2, ‘Arizona’)
guillaume@ubuntu:~/0x0F$
“`

**Repo:**

– GitHub repository: alx-higher_level_programming
– Directory: 0x0F-python-object_relational_mapping
– File: 2-my_filter_states.py

### 3. SQL Injection…

Wait, do you remember the previous task? Did you test Arizona’; TRUNCATE TABLE states ; SELECT * FROM states WHERE name = ‘ as an input?

“`
guillaume@ubuntu:~/0x0F$ ./2-my_filter_states.py root root hbtn_0e_0_usa Arizona’; TRUNCATE TABLE states ; SELECT * FROM states WHERE name = ‘ (2, ‘Arizona’)
guillaume@ubuntu:~/0x0F$ ./0-select_states.py root root hbtn_0e_0_usa
guillaume@ubuntu:~/0x0F$
“`

What? Empty?

Yes, it’s an SQL injection to delete all records of a table…

Once again, write a script that takes in arguments and displays all values in the states table of hbtn_0e_0_usa where name matches the argument. But this time, write one that is safe from MySQL injections!

– Your script should take 4 arguments: mysql username, mysql password, database name and state name searched (safe from MySQL injection)
– You must use the module MySQLdb (import MySQLdb)
– Your script should connect to a MySQL server running on localhost at port 3306
– Results must be sorted in ascending order by states.id
– Results must be displayed as they are in the example below
– Your code should not be executed when imported

“`
guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql
— Create states table in hbtn_0e_0_usa with some data
CREATE DATABASE IF NOT EXISTS hbtn_0e_0_usa;
USE hbtn_0e_0_usa;
CREATE TABLE IF NOT EXISTS states (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO states (name) VALUES ( California ), ( Arizona ), ( Texas ), ( New York ), ( Nevada );

guillaume@ubuntu:~/0x0F$ cat 0-select_states.sql | mysql -uroot -p
Enter password:
guillaume@ubuntu:~/0x0F$ ./3-my_safe_filter_states.py root root hbtn_0e_0_usa ‘Arizona’
(2, ‘Arizona’)
guillaume@ubuntu:~/0x0F$
“`

**Repo:**

– GitHub repository: alx-higher_level_programming
– Directory: 0x0F-python-object_relational_mapping
– File: 3-my_safe_filter_states.py

### 4. Cities by states

Write a script that lists all cities from the database hbtn_0e_4_usa

– Your script should take 3 arguments: mysql username, mysql password and database name
– You must use the module MySQLdb (import MySQLdb)
– Your script should connect to a MySQL server running on localhost at port 3306
– Results must be sorted in ascending order by cities.id
– Results must be displayed as they are in the example below
– Your code should not be executed when imported

“`
guillaume@ubuntu:~/0x0F$ cat 4-cities_by_state.sql
— Create states table in hbtn_0e_4_usa with some data
CREATE DATABASE IF NOT EXISTS hbtn_0e_4_usa;
USE hbtn_0e_4_usa;
CREATE TABLE IF NOT EXISTS states (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO states (name) VALUES ( California ), ( Arizona ), ( Texas ), ( New York ), ( Nevada );

CREATE TABLE IF NOT EXISTS cities (
id INT NOT NULL AUTO_INCREMENT,
state_id INT NOT NULL,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(state_id) REFERENCES states(id)
);
INSERT INTO cities (state_id, name) VALUES (1, San Francisco ), (1, San Jose ), (1, Los Angeles ), (1, Fremont ), (1, Livermore );
INSERT INTO cities (state_id, name) VALUES (2, Page ), (2, Phoenix );
INSERT INTO cities (state_id, name) VALUES (3, Dallas ), (3, Houston ), (3, Austin );
INSERT INTO cities (state_id, name) VALUES (4, New York );
INSERT INTO cities (state_id, name) VALUES (5, Las Vegas ), (5, Reno ), (5, Henderson ), (5, Carson City );

guillaume@ubuntu:~/0x0F$ cat 4-cities_by_state.sql | mysql -uroot -p
Enter password:
guillaume@ubuntu:~/0x0F$ ./4-cities_by_state.py root root hbtn_0e_4_usa
(1, ‘San Francisco’, ‘California’)
(2, ‘San Jose’, ‘California’)
(3, ‘Los Angeles’, ‘California’)
(4, ‘Fremont’, ‘California’)
(5, ‘Livermore’, ‘California’)
(6, ‘Page’, ‘Arizona’)
(7, ‘Phoenix’, ‘Arizona’)
(8, ‘Dallas’, ‘Texas’)
(9, ‘Houston’, ‘Texas’)
(10, ‘Austin’, ‘Texas’)
(11, ‘New York’, ‘New York’)
(12, ‘Las Vegas’, ‘Nevada’)
(13, ‘Reno’, ‘Nevada’)
(14, ‘Henderson’, ‘Nevada’)
(15, ‘Carson City’, ‘N Nevada’)
guillaume@ubuntu:~/0x0F$
“`

**Repo:**

– GitHub repository: alx-higher_level_programming
– Directory: 0x0F-python-object_relational_mapping
– File: 4-cities_by_state.py

### 5. All cities by state

Write a script that takes in the name of a state as an argument and lists all cities of that state, using the database hbtn_0e_4_usa

– Your script should take 4 arguments: mysql username, mysql password, database name and state name (SQL injection free!)
– You must use the module MySQLdb (import MySQLdb)
– Your script should connect to a MySQL server running on localhost at port 3306
– Results must be sorted in ascending order by cities.id
– Results must be displayed as they are in the example below
– Your code should not be executed when imported

“`
guillaume@ubuntu:~/0x0F$ cat 4-cities_by_state.sql
— Create states table in hbtn_0e_4_usa with some data
CREATE DATABASE IF NOT EXISTS hbtn_0e_4_usa;
USE hbtn_0e_4_usa;
CREATE TABLE IF NOT EXISTS states (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(256) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO states (name) VALUES ( California ), (