MySQL Create Database - Python

You are strongly advised to check corresponding YouTube video at the end of this article.

MySQL Connector is a library that allows Python programs to communicate with a MySQL database. It is used to establish a connection between Python applications and MySQL server to perform various operations such as inserting, deleting, updating, and retrieving data from a database.

In simple terms, MySQL Connector is a bridge between Python and MySQL server that allows Python developers to create, read, update, and delete data from MySQL databases using Python code.

MySQL Connector is important because it allows developers to integrate MySQL databases with their Python applications seamlessly. Without MySQL Connector, developers would have to write their own SQL code to interact with a MySQL database, which would be time-consuming and error-prone. By using MySQL Connector, developers can save time and focus on building their applications rather than writing low-level database code. 

C:\>pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.20-py2.py3-none-any.whl (358 kB)
     |████████████████████████████████| 358 kB 3.3 MB/s
Collecting protobuf>=3.0.0
  Downloading protobuf-3.12.2-py2.py3-none-any.whl (443 kB)
     |████████████████████████████████| 443 kB 6.8 MB/s
Requirement already satisfied: setuptools in c:\python38-64\lib\....
Requirement already satisfied: six>=1.9 in c:\python38-64\lib\....)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.20 protobuf-3.12.2

C:\>

These commands are used to install the Python package "mysql-connector-python" using pip.

The last line "Successfully installed mysql-connector-python-8.0.20 protobuf-3.12.2" confirms that both packages have been installed successfully. 

import mysql.connector

db_handle = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = '12345'
)

print(db_handle)

kursor = db_handle.cursor()
kursor.execute("CREATE DATABASE test_dba")

This script uses the mysql.connector Python library to connect to a MySQL database server running on the same machine at the default port, using the username 'root' and password '12345'. It then creates a new database named 'test_dba' using a SQL command executed via a cursor object.

Here's what each line does:

1. import mysql.connector: imports the mysql.connector module, which provides a Python interface for working with MySQL databases.

2. db_handle = mysql.connector.connect(host='localhost', user='root', passwd='12345'): creates a connection object named db_handle using the connect() method of the mysql.connector module. The host, user, and passwd parameters specify the server name, username, and password used to connect to the database.

3. print(db_handle): prints the connection object to the console, which should output a message indicating that the connection was successful.

4. kursor = db_handle.cursor(): creates a cursor object named kursor using the cursor() method of the db_handle object. Cursors are used to execute SQL commands and retrieve data from the database.

5. kursor.execute("CREATE DATABASE test_dba"): executes a SQL command to create a new database named test_dba. The execute() method of the cursor object is used to execute the command.

Note that this script assumes that the MySQL server is already installed and running on the local machine, and that the root user has sufficient privileges to create new databases. 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| 192_168_0_103      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_dba           |
+--------------------+
6 rows in set (0.00 sec)

mysql>

This is a MySQL report showing the result of the command "show databases;" executed in the MySQL command-line interface.

The output shows a table with the list of databases available in the MySQL server. Each row represents a database, and the only column in the table is named "Database".

The report shows that there are six databases available in the MySQL server:

• 192_168_0_103
• information_schema
• mysql
• performance_schema
• sys
• test_dba

The last database is "test_dba", which means that our script works just fine.