Creating and Connecting to the MySQL Database

00:00 In this lesson, you will learn how to create a new database and to connect to it. Firstly, open your MySQL connection code from the previous lesson. You will use it as a basis for your code.

00:15 Let’s give it a different name:

00:20 MySQLcode_db. Next, let’s copy and paste this snippet of code

00:35 and import getpass, and connect the libraries. Next, you are going to create a new database for this purpose. Under the with clause, create a new variable, sql as a string containing a query in a SQL language. Type CREATE DATABASE online _movie_rating.

01:04 To execute an SQL query in Python, you’ll need to use a cursor object and in order to automatically handle the database cleanup process, I recommend using a context manager with the cursor object.

01:22 with connection.cursor(). as cursor: cursor. execute(sql). This command sends the CREATE DATABASE query in the string format to cursor.execute().

01:44 You can confirm if this database was indeed created by executing the SHOW DATABASES statement. Let’s do this. Let’s copy and paste this code snippet

02:02 and instead of sql, let’s type show_ db_query

02:13 = "SHOW DATABASES" with connection.cursor(). as cursor: cursor.execute(show_ db_query). Here you would like to see a list of databases. For this purpose, type for db in cursor: print(db).

02:46 You’re ready to execute the cell. Type your username as root, followed by your password.

02:57 As a result, the above code prints the names of all the databases currently in your MySQL server. The SHOW DATABASES command also outputs some databases that you did not create in your server, like information_schema, performance_schema, and sys.

03:18 These databases are generated automatically by the MySQL server when you install it. They provide access to a variety of database metadata and MySQL server settings.

03:31 You have confirmed the creation of online_movie_rating database, however, you have not connected to it yet. As such, you should update your connection by including the database name.

03:47 Let’s grab the code snippet from the previous cell.

03:56 Let’s remove SQL commands and add database parameter that is equal to online_movie _rating. Let’s establish the connection to the database.

04:17 Run the cell, enter your username as root, and your password.

04:26 You have established the connection to the database. Congratulations. You have learned how to create and to connect to a new database using Python, MySQL Connector, and MySQLCursor classes.