How to Connect a Database in Python

How to Connect a Database in Python

A database is a well-organized collection of structured data kept on a computer. Data is organized in a tabular style, and we can retrieve it using queries.

Python provides strong functionality for connecting to databases. MySQL is one of the most extensively used databases. In this tutorial, we will learn how to connect to MySQL using Python. Let us walk through the steps required to work with MySQL in Python.

Complete Python Course with Advance topics:-Click here

Steps to Connect MySQL Database in Python

1. Install MySQL Driver
2. Create a Connection Object
3. Create a Cursor Object
4. Execute SQL Queries

Step 1: Install MySQL Driver

Before connecting to MySQL, we must first install the MySQL driver. We’ll be using the mysql-connector-python package, which can be installed using the pip command. Open the command prompt and execute the following commands:

python -m pip install mysql-connector-python

Pressing Enter will download and install the MySQL connector driver.

Verify the Installation

To ensure the driver is installed correctly, import the module in Python:

import mysql.connector

If this line executes without any errors, the MySQL connector has been installed successfully.

Step 2: Create a Connection Object

The mysql.connector module provides the connect() method, which allows us to establish a connection between the MySQL database and the Python application.

Syntax:

conn_obj = mysql.connector.connect(host="<hostname>", user="<username>", passwd="<password>")

Parameters:

• host – The server name or IP address where MySQL is hosted (e.g., localhost for local machines).
• user – The MySQL username (default: root).
• passwd – The password used during MySQL installation.
• database – The name of the database to connect to (optional).

Example:

import mysql.connector

# Creating the connection object  
conn_obj = mysql.connector.connect(host="localhost", user="root", passwd="admin123")

# Printing the connection object
print(conn_obj)

Output:

<mysql.connector.connection.MySQLConnection object at 0x7fb142edd780>

Step 3: Create a Cursor Object

Once the connection is established, the cursor object helps us execute SQL queries. Cursors can be created using the cursor() method.

Syntax:

cur_obj = conn_obj.cursor()

Example:

import mysql.connector

# Creating the connection object  
conn_obj = mysql.connector.connect(host="localhost", user="root", passwd="admin123", database="mydatabase")

# Creating the cursor object  
cur_obj = conn_obj.cursor()

print(cur_obj)

Output:

MySQLCursor: (Nothing executed yet)

Step 4: Execute SQL Queries

We can now execute SQL queries using the cursor object. Let’s look at an example where we create a new database.

Example:

import mysql.connector

# Creating the connection object  
conn_obj = mysql.connector.connect(host="localhost", user="root", passwd="admin123")

# Creating the cursor object  
cur_obj = conn_obj.cursor()

try:
    # Creating a new database
    cur_obj.execute("CREATE DATABASE New_PythonDB")

    # Fetching the list of all databases
    cur_obj.execute("SHOW DATABASES")
except:
    conn_obj.rollback()  # Rollback if any operation fails

for db in cur_obj:
    print(db)

conn_obj.close()

Output:

('information_schema',)
('updategadh',)
('updategadh1',)
('New_PythonDB',)
('mydb',)
('mydb1',)
('mysql',)
('performance_schema',)
('testDB',)

Conclusion

In this tutorial, we have covered the process of establishing a connection to MySQL using Python. Following these steps, you can efficiently manage MySQL databases within your Python applications.

For more Python and MySQL tutorials, visit updategadh.com.

Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE

How to Connect a Database in Python
how to connect a database in python with mysql
how to connect a database in python using a
how to connect a database in python database connection class example
mysql connector/python
sql database connection using python
pip install mysql-connector-python
How to Connect a Database in python sql connectivity programs
How to Connect a Database python database connection postgresql