# 0x0F. Python – Object-relational mapping

## Learning Objectives

At the end of this project, you are expected to be able to explain to anyone, without the help of Google:

### 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

## Requirements

### General

– Allowed editors: vi, vim, emacs
– All your files will be interpreted/compiled on Ubuntu 20.04 LTS using python3 (version 3.8.5)
– Your files will be executed with MySQLdb version 2.0.x
– Your files will be executed with SQLAlchemy version 1.3.x
– All your files should end with a new line
– The first line of all your files should be exactly #!/usr/bin/python3
– A README.md file, at the root of the folder of the project, is mandatory
– Your code should use the pycodestyle (version 2.8.*)
– All your files must be executable
– The length of your files will be tested using wc
– All your modules should have a documentation (python3 -c ‘print(__import__( my_module ).__doc__)’)
– All your classes should have a documentation (python3 -c ‘print(__import__( my_module ).MyClass.__doc__)’)
– All your 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

“`bash
$ 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:

“`bash
$ 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

“`bash
$ 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.3.x

“`bash
$ sudo pip3 install SQLAlchemy
…
$ python3
>>> import sqlalchemy
>>> sqlalchemy.__version__
‘1.3.20’
“`

Also, you can have this warning message:

“`bash
/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py:552: Warning: (1681, ‘@@SESSION.GTID_EXECUTED’ is deprecated and will be removed 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

“`bash
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$
“`

**Solution:** [0-select_states.py](https://github.com/justinmajetich/alx-higher_level_programming/blob/master/0x0F-python-object_relational_mapping/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

“`bash
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$
“`

**Solution:** [1-filter_states.py](https://github.com/justinmajetich/alx-higher_level_programming/blob/master/0x0F-python-object_relational_mapping/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

“`bash
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$
“`

**Solution:** [2-my_filter_states.py](https://github.com/justinmajetich/alx-higher_level_programming/blob/master/0x0F-python-object_relational_mapping/2-my_filter_states.py)

### 3. SQL Injection…

Wait, do you remember the previous task? Did you test Arizona’; TRUNCATE TABLE states ; — as an input?

“`bash
guillaume@ubuntu:~/0x0F$ ./2-my_filter_states.py root root hbtn_0e_0_usa Arizona’; TRUNCATE TABLE states ; — (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

“`bash
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$
“`

**Solution:** [3-my_safe_filter_states.py](https://github.com/justinmajetich/alx-higher_level_programming/blob/master/0x0F-python-object_relational_mapping/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

“`bash
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 ), (4, Albany ), (4, Buffalo ), (4, Rochester ), (4, Yonkers );
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, ‘Albany’, ‘New York’)
(13, ‘Buffalo’, ‘New York’)
(14, ‘Rochester’, ‘New York’)
(15, ‘Yonkers’, ‘New York’)
(16, ‘Las Vegas’, ‘Nevada’)
(17, ‘Reno’, ‘Nevada’)
(18, ‘Henderson’, ‘Nevada’)
(19, ‘Carson City’, ‘Nevada’)
guillaume@ubuntu:~/0x0F$
“`

**Solution:** [4-cities_by_state.py](https://github.com/justinmajetich/alx-higher_level_programming/blob/master/0x0F-python-object_relational_mapping/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

“`bash
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 ), (4, Albany ), (4, Buffalo ), (4, Rochester ), (4, Yonkers );
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$ ./5-filter_cities.py root root hbtn_0e_4_usa Texas
Dallas, Houston, Austin
guillaume@ubuntu:~/0x0F$
“`

**Solution:** [5-filter_cities.py](https://github.com/justinmajetich/alx-higher_level_programming/blob/master/0x0F-python-object_relational_mapping/5-filter_cities.py)

### 6. First state