How do you connect to a database in Python?

To connect to a database in Python, you typically use a database library or module that corresponds to the database type (e.g., SQLite, MySQL, PostgreSQL). Here are the general steps to connect to a database:

1. Install the Database Driver

Make sure you have the appropriate library installed for your database. For example:

SQLite: No installation required (built into Python’s standard library).
MySQL: Use mysql-connector-python or pymysql.
PostgreSQL: Use psycopg2 or asyncpg for asynchronous operations.

Install using pip:

pip install mysql-connector-python
pip install psycopg2

2. Import the Library

Import the library you installed. For example:

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

3. Connect to the Database

Use the library’s connect method to establish a connection by providing the necessary credentials (host, database name, username, password, etc.).

SQLite Example

conn = sqlite3.connect('example.db')  # Connect to SQLite database file

MySQL Example

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

PostgreSQL Example

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

4. Create a Cursor Object

The cursor is used to execute SQL queries and fetch results.

cursor = conn.cursor()

5. Execute Queries

Use the cursor to execute SQL commands. For example:

cursor.execute("SELECT * FROM your_table")
rows = cursor.fetchall()
for row in rows:
    print(row)

6. Commit Changes (if necessary)

For operations like INSERT, UPDATE, or DELETE, commit the changes:

conn.commit()

7. Close the Connection

Always close the cursor and the connection to free up resources:

cursor.close()
conn.close()

Complete Example

Here’s a full example for SQLite:

import sqlite3

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

# Create a cursor object
cursor = conn.cursor()

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

# Insert a record
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 30))

# Commit the changes
conn.commit()

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

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

This example creates a table, inserts data, retrieves it, and cleans up properly.

Post Views: 88