How to connect Database in Python?

Most applications need database connectivity to store and retrieve data. Python provides several ways to connect to databases, with MySQL being one of the most popular choices. This tutorial shows how to establish a MySQL connection using the mysql-connector-python library.

Installation

First, install the MySQL Connector module using pip −

python -m pip install mysql-connector-python

This installs the MySQL Connector which enables Python applications to connect to MySQL databases.

Creating a Database Connection

To connect to a MySQL database, you need the host address, username, password, and database name. Here's how to establish a connection −

import mysql.connector

# Create connection to MySQL database
mydatabase = mysql.connector.connect(
    host="localhost",
    user="your_user_name", 
    password="your_password",
    database="database_name"
)

print("Connection established successfully!")
print("Database:", mydatabase.database)

Connection established successfully!
Database: database_name

Creating a Cursor Object

A cursor object is required to execute SQL queries. Create it using the cursor() method −

import mysql.connector

# Establish connection
mydatabase = mysql.connector.connect(
    host="localhost",
    user="your_user_name",
    password="your_password", 
    database="database_name"
)

# Create cursor object
mycursor = mydatabase.cursor()

print("Cursor created successfully!")
print("Cursor type:", type(mycursor))

Cursor created successfully!
Cursor type: <class 'mysql.connector.cursor.MySQLCursor'>

Executing SQL Queries

Once you have a cursor object, you can execute SQL commands using the execute() method −

import mysql.connector

try:
    # Connect to database
    mydatabase = mysql.connector.connect(
        host="localhost",
        user="your_user_name",
        password="your_password",
        database="database_name"
    )
    
    # Create cursor
    mycursor = mydatabase.cursor()
    
    # Execute a simple query
    mycursor.execute("SHOW TABLES")
    
    # Fetch results
    tables = mycursor.fetchall()
    print("Tables in database:")
    for table in tables:
        print("-", table[0])
        
except mysql.connector.Error as error:
    print("Error connecting to database:", error)
    
finally:
    # Close connections
    if mydatabase.is_connected():
        mycursor.close()
        mydatabase.close()
        print("Database connection closed")

Tables in database:
- users
- products  
- orders
Database connection closed

Best Practices

Conclusion

Connecting to MySQL in Python requires installing mysql-connector-python, creating a connection object, and then a cursor object to execute SQL queries. Always handle errors properly and close connections to maintain good resource management.