Creating Tables

00:00 In this lesson, you will learn how to create related MySQL tables in Python via primary and foreign keys. These tables will be a part of the online movie rating database that you created in the previous lesson.

00:15 You may remember from the first lesson that ID columns in reviewers and movies tables play a role of primary keys, whereas the ratings table has a combination of these keys as a foreign key.

00:28 This diagram illustrates the end result of this lesson.

00:34 To get started, you should create a new file.

00:47 You can name it MySQLcode_tables.

00:54 You will need the database connection code for database transactions. You can grab it from your previous file,

01:08 and you will need these two lines of code.

01:15 Before you proceed, note that you cannot use the same MySQL connection object from earlier due to the context manager with closing it after the connection to the database was created and used.

01:29 You can omit this in this piece of code to allow the creation of MySQL connection object for multiple query executions. Let’s modify this code as follows.

01:43 Type connection = connect(

01:51 ) print(connection) You are ready to establish the connection. Enter your username as root, followed by your password.

02:06 The connection has been established. Next, you are going to create a movies table using the CREATE TABLE SQL command. For this purpose, you’ll create a new variable sql1.

02:22 You will assign the SQL statement to this variable. Since it’s going to be a long statement spanning multiple lines, you should use triple quotes. Type, CREATE TABLE movies ( Firstly, you should start with the primary key definition.

02:47 MySQL uses the AUTO_INCREMENT keyword when a column value has to be incremented automatically on the insertion of new records. In our case, the primary key is the id column of INT type AUTO_INCREMENT PRIMARY KEY,

03:14 Next, you will have to define movie attributes and their data types. Specifically, title is of VARCHAR type with the maximum length of 100 characters

03:30 followed by release_year of DATE type, namely YEAR consisting of four characters, genre of VARCHAR type with the maximum length of 100 characters.

03:54 collection_in_mil of INT type. collection_in_mil means revenue that this movie has generated in million dollars, and it is of INT type.

04:11 Then you need to pass this query to cursor.execute(). Type with connection. cursor() as cursor: cursor.execute (sql1).

04:31 It accepts a MySQL query and executes the query on the connected MySQL database. Next, you will have to end this code with the connection.commit() command. Type connection. commit(). Here you are performing a so-called transaction, or an operation of defining a new table in an online movie ratings database.

04:56 By default, your MySQL connector does not commit transactions. Therefore, you should always use commit() method to perform a new transaction.

05:10 Next, you will have to create the reviewers table. Firstly, define a new variable sql2, and in triple quotes type the SQL statement, CREATE TABLE reviewers ( As always, you start with the primary key, which is the id column of INT type AUTO_INCREMENT, PRIMARY KEY, first_name of VARCHAR type with the maximum length of 100 characters followed by the second name, second_name of VARCHAR type with the maximum length of 100 characters.

06:09 Then use a context manager with to execute this SQL query and to commit this transaction to make sure that a new table is indeed created. with connection.cursor() as cursor:

06:27 cursor.execute(sql2) connection.commit() At last, you have to create the ratings table. As you may remember, this is a child table having movies and reviewers as its parents.

06:50 Again, you start with the creation of a new variable, sql3 and type the SQL statement in triple quotes, CREATE TABLE ratings, (, movie_id INT, reviewer_id, of INT type as well, rating DECIMAL type parentheses, and in parentheses, you type (2, 1).

07:29 2 means two digits in the value on both sides of the decimal point, and 1 is the number of digits after the decimal point. You will have to specify the foreign key that is a combination of movie_id and reviewer_id in the SQL statement.

07:49 So you type FOREIGN KEY and in parentheses type movie_id REFERENCES movies ( id). This statement references the id column in the movies table,

08:16 FOREIGN KEY ( reviewer_id) REFERENCES reviews ( id). This statement references the id column in the reviewers table, comma, PRIMARY KEY

08:44 (movie_id, reviewer_id).

08:54 To execute this query, type with connection. cursor() as cursor: cursor. execute(sql3) connection .commit(). When you run all three cells and close the connection, next, you should open the MySQL Workbench to confirm that all three tables were indeed created.

09:28 First of all, let’s close the connection

09:35 and let’s open MySQL Workbench. Here, under the tables, you see movies, ratings, and reviewers tables. Under movies, you can expand columns and see id, title, release_year, genre, and collection_in_mil as columns of the movies table, and ratings table has movie_id, reviewer_id, and rating columns as expected.

10:08 And reviewers table has id, first_name, and second_name columns. Congratulations. You have learned how to create a new database that consists of the related tables using Python, MySQL Connector and MySQLCursor classes.