MySQL Connector Python - Naukri Code 360

Introduction

MySQL Connector/Python is a library that allows Python programs to interact with MySQL databases. It provides a simple & efficient way to connect to MySQL servers, execute SQL queries, & retrieve results. With MySQL Connector/Python, you can perform many different database operations such as creating databases, tables, inserting, updating, & deleting data.

In this article, we will discuss the installation process, connecting to a MySQL server, & executing common SQL queries using MySQL Connector/Python.

Installation

To use MySQL Connector/Python, you need to install it first. The installation process is straightforward & can be done using pip, the Python package installer. Open your terminal or command prompt & run the following command:

install mysql-connector-python

This command will download & install the latest version of MySQL Connector/Python along with its dependencies. Once the installation is complete, you are ready to use the library in your Python programs.

Connecting to MySQL Server

To connect to a MySQL server using MySQL Connector/Python, you need to provide the necessary connection details such as the host, port, username, & password.

Let’s see an example code that shows how to establish a connection:

import mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Execute a query

cursor.execute("SELECT * FROM your_table")

# Fetch the results

results = cursor.fetchall()

# Process the results

for row in results:
    print(row)

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we import the mysql.connector module & use the connect() function to establish a connection. We provide the host, user, password, & database parameters to specify the connection details. Replace "your_username", "your_password", & "your_database" with your actual MySQL server credentials.

After establishing the connection, we create a cursor object using connection.cursor(). The cursor allows us to execute SQL queries & fetch the results. We can then use the execute() method to run SQL queries & the fetchall() method to retrieve the results.

Finally, it's important to close the cursor & connection objects when you're done to release the resources.

Creating Database

To create a new database using MySQL Connector/Python, you can use the CREATE DATABASE SQL statement.

Example :

import mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password"
)

# Create a cursor object

cursor = connection.cursor()

# Create a new database

database_name = "your_database_name"
cursor.execute(f"CREATE DATABASE {database_name}")

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we establish a connection to the MySQL server without specifying a database, as we want to create a new one. We create a cursor object & then use the execute() method to run the CREATE DATABASE statement.

Replace "your_database_name" with the desired name for your new database. The f"CREATE DATABASE {database_name}" syntax uses an f-string to insert the value of the database_name variable into the SQL statement.

After executing the statement, a new database will be created with the specified name. Remember to close the cursor & connection objects when you're done.

Creating Tables

Once you have a database created, you can create tables within that database using MySQL Connector/Python.

Example :

import mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Create a new table

table_name = "your_table_name"
create_table_query = f"""
CREATE TABLE {table_name} (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
)
"""
cursor.execute(create_table_query)

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we establish a connection to the MySQL server & specify the database where we want to create the table. We create a cursor object & then use the execute() method to run the CREATE TABLE statement.

Replace "your_table_name" with the desired name for your new table. The create_table_query variable contains the SQL statement that defines the structure of the table. In this example, the table has three columns: id (an integer primary key), name (a VARCHAR column with a maximum length of 255 characters), & age (an integer column).

You can customize the table structure based on your requirements by modifying the CREATE TABLE statement. You can add more columns, specify different data types, & include constraints as needed.

After executing the statement, a new table will be created within the specified database. Remember to close the cursor & connection objects when you're done.

Insert Data into Tables

After creating a table, you can insert data into it using MySQL Connector/Python. Here's an example code snippet that demonstrates how to insert data into a table:

pythonCopyimport mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Insert data into the table

table_name = "your_table_name"
insert_query = f"""
INSERT INTO {table_name} (id, name, age)
VALUES (1, 'Rahul', 25),
       (2, 'Rinki', 30),
       (3, 'Harsh', 35)
"""
cursor.execute(insert_query)

# Commit the changes

connection.commit()

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we establish a connection to the MySQL server & specify the database where the table is located. We create a cursor object & then use the execute() method to run the INSERT INTO statement.

Replace "your_table_name" with the name of the table you want to insert data into. The insert_query variable contains the SQL statement that specifies the values to be inserted. In this example, we are inserting three rows of data into the table. Each row contains values for the id, name, & age columns.

You can modify the INSERT INTO statement to include your own data. Make sure to provide values for all the columns specified in the INSERT INTO clause & ensure that the values match the data types & constraints defined in the table schema.

After executing the statement, the data will be inserted into the table. It's important to call connection.commit() to commit the changes & ensure that the data is permanently stored in the database.

Finally, remember to close the cursor & connection objects when you're done.

Fetching Data

Once you have data inserted into a table, you can fetch it using MySQL Connector/Python. Here's an example code snippet that demonstrates how to fetch data from a table:

pythonCopyimport mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Fetch data from the table

table_name = "your_table_name"
select_query = f"SELECT * FROM {table_name}"
cursor.execute(select_query)

# Fetch all the rows

rows = cursor.fetchall()

# Process the fetched data

for row in rows:
    print(row)

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we establish a connection to the MySQL server & specify the database where the table is located. We create a cursor object & then use the execute() method to run the SELECT statement.

Replace "your_table_name" with the name of the table you want to fetch data from. The select_query variable contains the SQL statement that retrieves all the columns (*) from the specified table.

After executing the statement, we use the fetchall() method to fetch all the rows returned by the query. The fetched rows are stored in the rows variable as a list of tuples, where each tuple represents a row of data.

We then iterate over the rows using a for loop & print each row. You can process the fetched data according to your requirements, such as displaying it on the console, storing it in a file, or performing further operations.

Finally, remember to close the cursor & connection objects when you're done.

You can modify the SELECT statement to fetch specific columns, apply filters, or include other clauses based on your needs. For example, you can use SELECT name, age FROM your_table_name WHERE age > 30 to fetch only the name & age columns for rows where the age is greater than 30.

Update Data

MySQL Connector/Python allows you to update existing data in a table using the UPDATE statement.

Example

import mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Update data in the table

table_name = "your_table_name"
update_query = f"""
UPDATE {table_name}
SET age = 40
WHERE name = 'Harsh'
"""
cursor.execute(update_query)

# Commit the changes

connection.commit()

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we establish a connection to the MySQL server & specify the database where the table is located. We create a cursor object & then use the execute() method to run the UPDATE statement.

Replace "your_table_name" with the name of the table you want to update. The update_query variable contains the SQL statement that specifies the column(s) to be updated & the condition for selecting the rows to update. In this example, we are updating the age column to the value of 40 for rows where the name column equals 'Harsh'.

You can modify the UPDATE statement to update different columns or use different conditions based on your requirements. You can also update multiple columns in a single statement by separating them with commas, like SET age = 40, name = 'Sanjana'.

After executing the statement, the specified rows will be updated with the new values. It's important to call connection.commit() to commit the changes & ensure that the updates are permanently stored in the database.

Finally, remember to close the cursor & connection objects when you're done.

Delete Data from Table

MySQL Connector/Python allows you to delete data from a table using the DELETE statement.

Example

import mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Delete data from the table

table_name = "your_table_name"
delete_query = f"""
DELETE FROM {table_name}
WHERE age > 30
"""
cursor.execute(delete_query)

# Commit the changes

connection.commit()

# Close the cursor & connection

cursor.close()
connection.close()

In this example, we establish a connection to the MySQL server & specify the database where the table is located. We create a cursor object & then use the execute() method to run the DELETE statement.

Replace "your_table_name" with the name of the table from which you want to delete data.

The delete_query variable contains the SQL statement that specifies the condition for selecting the rows to delete. In this example, we are deleting rows where the age column is greater than 30.

You can modify the DELETE statement to use different conditions based on your requirements. You can also delete all rows from a table by omitting the WHERE clause, like DELETE FROM your_table_name. However, be cautious when using this, as it will permanently remove all data from the table.

After executing the statement, the specified rows will be deleted from the table. It's important to call connection.commit() to commit the changes & ensure that the deletions are permanent.

Finally, remember to close the cursor & connection objects when you're done.

Drop Tables

MySQL Connector/Python allows you to drop (delete) a table from a database using the DROP TABLE statement.

Example :

import mysql.connector

# Establish a connection

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object

cursor = connection.cursor()

# Drop a table

table_name = "your_table_name"
drop_query = f"DROP TABLE {table_name}"
cursor.execute(drop_query)
# Close the cursor & connection
cursor.close()
connection.close()

Replace "your_table_name" with the name of the table you want to drop. The drop_query variable contains the SQL statement that specifies the table to be dropped.

After executing the statement, the specified table will be permanently deleted from the database along with all its data. Exercise caution when using the DROP TABLE statement, as it is an irreversible operation.

Finally, remember to close the cursor & connection objects when you're done.

Frequently Asked Questions

Do I need to install MySQL separately to use MySQL Connector/Python?

Yes, you need to have MySQL server installed & running on your system or have access to a remote MySQL server to use MySQL Connector/Python.

Is MySQL Connector/Python the only way to connect to MySQL from Python?

No, there are other libraries available, such as PyMySQL & SQLAlchemy, that also allow you to connect to MySQL from Python. However, MySQL Connector/Python is the official & recommended library.

Can I use MySQL Connector/Python with other databases besides MySQL?

No, MySQL Connector/Python is specifically designed to work with MySQL databases. If you need to connect to other databases, you would need to use the appropriate library for that database.

Conclusion

In this article, we learned about MySQL Connector Python, a library that enables Python programs to interact with MySQL databases. We covered the installation process, connecting to a MySQL server, creating databases & tables, inserting, fetching, updating, & deleting data, as well as dropping tables.

You can also practice coding questions commonly asked in interviews on Coding Ninjas Code360.

Also, check out some of the Guided Paths on topics such as Data Structure and Algorithms, Competitive Programming, Operating Systems, Computer Networks, DBMS, System Design, etc., as well as some Contests, Test Series, and Interview Experiences curated by top Industry Experts.