# 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.4.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.4.x

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

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

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

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

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

**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
– You must use format to create the SQL query with the user input
– 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$
“`

**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?

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

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

**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
– You can use only execute() once
– 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 ),