How to connect Database in Python

Connecting to a database in Python is one of the most common tasks involving specific libraries for communication between your Python program and the database of your choice. Here is how to do it:

1. Installing Required Database Library

Why Do You Need Libraries?

Python does not natively talk to all databases. You require special libraries to work as a mediator between your Python program and the database.

Common Libraries

• SQLite: It comes built-in with Python. You don’t need to install anything.
• MySQL/MariaDB: Use mysql-connector-python or PyMySQL.
• PostgreSQL: Use psycopg2 as it is the one recommended.
• SQL Server: Usually pyodbc or pymssql.
• MongoDB: Use pymongo.

Installation Command

For databases other than SQLite, you install the library using pip. For example:

pip install mysql-connector-python  # For MySQL
pip install psycopg2               # For PostgreSQL
pip install pyodbc                 # For SQL Server

2. Connect to the Database

Step-by-Step Process

2.1 Import the Library

Each library must be imported at the beginning of your script. For example:

import sqlite3  # For SQLite
import mysql.connector  # For MySQL
import psycopg2  # For PostgreSQL

2.2 Establish a Connection

• The connection is the door to the database.
• It takes the following parameters:
  • Host: Server address hosting the database
  • User: The user name to connect to the database
  • Password: The password to the user.
  • Database: The specific database to use.

Examples

• SQLite: Requires only the file name.

conn = sqlite3.connect('example.db')  # Creates or connects to a SQLite database file

• MySQL:

conn = mysql.connector.connect(
    host="localhost",         # Database server
    user="yourusername",      # Username
    password="yourpassword",  # Password
    database="yourdatabase"   # Database name
)

• PostgreSQL:

conn = psycopg2.connect(
    host="localhost",
    database="yourdatabase",
    user="yourusername",
    password="yourpassword"
)

3. Create a Cursor Object

A cursor is used to execute SQL queries and fetch results. It’s like a pointer for the database operations.

Why Use a Cursor?

• It allows you to execute SQL commands.
• It helps in managing the results of queries.

cursor = conn.cursor()

4. Execute SQL Queries

Types of Queries

4.1 Creating a Table

Use CREATE TABLE to define a table structure:

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')

• IF NOT EXISTS ensures the table isn’t created again if it already exists.
• INTEGER PRIMARY KEY defines a unique ID for each row.

4.2 Inserting Data

Use INSERT INTO to add records to the database:

cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")
conn.commit() # Saves the changes to the database

• Use conn.commit() after insert/update/delete queries to make the changes permanent.

4.3 Retrieving Data

Use SELECT to fetch data from the database:

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()  # Retrieves all rows
for row in rows:
    print(row)  # Each row is a tuple

4.4 Updating Data

Use UPDATE to modify records:

cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
conn.commit()

4.5 Deleting Data

Use DELETE to remove records:

cursor.execute("DELETE FROM users WHERE name = 'Alice'")
conn.commit()

5. Close the Connection

Why Close the Connection?

Keeping unused connections open can:

• Waste resources.
• Lead to database locks or connection pool exhaustion.

How to Close It?

Always close both the cursor and the connection:

cursor.close()  # Close the cursor
conn.close()    # Close the connection to the database

6. Complete Example for SQLite

Here’s a practical example combining all the steps:

import sqlite3

# Step 1: Connect to the database
conn = sqlite3.connect('example.db')

# Step 2: Create a cursor
cursor = conn.cursor()

# Step 3: Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER NOT NULL
    )
''')

# Step 4: Insert data
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 30)")
conn.commit()  # Save the changes

# Step 5: Fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Step 6: Close the connection
cursor.close()
conn.close()

7. Error Handling

Databases often produce errors when something goes wrong. Use try-except-finally to manage these:

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()
    for row in rows:
        print(row)
except sqlite3.Error as e:
    print(f"Error occurred: {e}")
finally:
    if conn:
        conn.close()  # Ensure the connection is always closed

8. Key Takeaways

• Always use the correct library for your database.
• Use commit() for any changes: insert, update, delete.
• Always close your connection after completing tasks.
• For large projects, use Object-Relational Mappers(ORMs) like SQLAlchemy for easier management.