Establishing a database is the initial phase of developing data-centric applications in Python. To efficiently link Python with the MySQL database server, it is necessary to import the mysql.connector module. In this segment of the tutorial, we will set up a new database named testdb.
Syntax for Creating Database
The syntax for establishing a database in MySQL is as follows:
CREATE DATABASE DATABASE_NAME
Example 1: Creating a Database in MySQL Using Python
To illustrate the process of creating a database in MySQL with the aid of Python, let us consider a practical example.
Example
import mysql.connector
# This code is to connect to the MySQL server
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root"
)
# The below code is to create an object
db_cursor = db_connection.cursor()
# Creating a new database
db_cursor.execute("CREATE DATABASE testdb")
print("Database 'testdb' created successfully!")
Output:
When your Python application executes without encountering any errors, this indicates that the database has been successfully established. The aforementioned program will perform the following actions:
- Establish a connection to your MySQL server utilizing the specified username and password.
- It will generate a new database titled testdb.
Example 2: Program to Create a Table in MySQL Database using Python
Example
import mysql.connector
# connecting to the database
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password123", # corrected parameter name
database="testdb" # removed extra spaces
)
# preparing a cursor object
cur = conn.cursor()
# creating a table
create_table_query = """
CREATE TABLE students (
name VARCHAR(20) NOT NULL,
department VARCHAR(50),
roll_no INT NOT NULL PRIMARY KEY,
section VARCHAR(5),
age INT
)
"""
# Execute table creation
cur.execute(create_table_query)
print("Table 'students' created successfully!")
# disconnecting from server
conn.close()
Output:
Explanation:
In the program outlined above, the MySQL.connector library is utilized to link Python with a MySQL database. The connection is established using a host (localhost), a username (root), a password (Root), and the pre-existing database (testdb). Following this, a cursor referred to as cur is created to execute commands.
We created a string called createtablequery that contains the SQL statement for creating a table called students. This table is comprised of five fields: name, department, roll_no, section, and age. To insert records into our table, we utilized the cur.execute function. In conclusion, we employed the conn.close method to terminate the connection to the MySQL server and release all allocated resources.
Check if Database Exists
Execute the command "SHOW DATABASES" to verify the existence of a database by displaying all databases available in your system:
Example
import mysql.connector
# Establishing the connection
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root"
)
# Creating a cursor object
cursor_handler = db_connection.cursor()
# Executing the command to show databases
cursor_handler.execute("SHOW DATABASES")
# Printing all databases
for db in cursor_handler:
print(db)
Output:
('information schema',)
('mydatabase',)
('mysql',)
('parks_and_recreation',)
('performance_schema',)
('sakila',)
('sales',)
('sys',)
('testdb',)
('world',)
>>> |
Explanation:
In the program presented above, the mysql.connector library is utilized to link Python with a MySQL database. This connection is established using a username (adminuser) and a password (adminpass).
Subsequently, a cursor referred to as cur is established to execute the commands. We have utilized a for loop to traverse through the results of the SQL query 'SHOW DATABASES'. Consequently, the aforementioned program will output each database name sequentially, one per line.
Conclusion
Establishing databases is the fundamental objective for linking Python with MySQL. By utilizing the mysql.connector module, Python can seamlessly communicate with MySQL servers. In this guide, we have explored how to establish a connection to a MySQL server, generate a new database, and manage an array of database tasks. Mastering advanced SQL functionalities is crucial for engaging in data-centric projects and developing dynamic, real-world applications.
Frequently Asked Questions/FAQs
1. Can we create a database directly using Python?
Indeed, we can effortlessly generate a new database by utilizing the execute method along with the CREATE DATABASE statement.
2. Is it necessary to close the connection?
Indeed, it is highly recommended to terminate connections by utilizing the conn.close method in order to release the resources that have been allocated.
3. Why do we use mysql.connector in Python SQL?
In our Python module, we utilize the syntax mysql.connector to establish a connection between the Python application and a MySQL database. After successfully connecting, we can effortlessly execute a variety of database operations, including inserting, updating, deleting, and querying data.
4. Which of the given SQL commands is correct to create a new database in MySQL?
- MAKE DATABASE
- INIT DATABASE
- CREATE NEW DATABASE
- CREATE DATABASE
The CREATE DATABASE statement is utilized to establish a new database within MySQL.
5. How can we check if Python is installed on the system or not?
To verify if Python is installed on your machine, follow the steps outlined below:
Step 1. Simultaneously press the Windows key and the R key to launch the Command Prompt.
Step 2. Type the command python --version .
Step 3. In the event that Python has already been installed, the system will display the version of Python that is currently in use.
Step 4. In the event that Python is not currently installed, it is necessary for you to carry out the installation process.