🐍 Python Q46 / 170

How to connect Python with a database?

AI-Powered Answer ✓ Answered

Python offers robust capabilities for interacting with various database systems, from lightweight embedded databases like SQLite to powerful client-server databases like PostgreSQL, MySQL, and NoSQL solutions like MongoDB. The primary method involves using database-specific drivers or Object-Relational Mappers (ORMs).

1. Understanding Database Drivers

Each database type (SQL or NoSQL) typically requires a specific Python library, often referred to as a 'driver' or 'connector,' to establish a connection and interact with the database. These drivers translate Python commands into the database's native protocol.

2. Connecting to SQL Databases (e.g., SQLite, PostgreSQL, MySQL)

SQL databases are relational databases that store data in tables. Python's Database API Specification (DB-API 2.0) provides a standard interface for database drivers, making code generally portable across different SQL databases once a connection is established.

2.1. SQLite (Built-in)

SQLite is a self-contained, file-based SQL database that comes built into Python's standard library via the sqlite3 module. It's excellent for small-scale applications, local development, or when you don't need a separate database server.

Example: SQLite Connection

python 
import sqlite3

try:
    # Connect to a database (or create it if it doesn't exist)
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()

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

    # Insert data
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))

    # Commit changes
    conn.commit()

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

except sqlite3.Error as e:
    print(f"Database error: {e}")
finally:
    if conn:
        conn.close()
        print("Database connection closed.")

2.2. PostgreSQL (psycopg2)

PostgreSQL is a powerful, open-source object-relational database system. The most popular Python driver is psycopg2.

Installation:

bash 
pip install psycopg2-binary

Example: PostgreSQL Connection

python 
import psycopg2

try:
    conn = psycopg2.connect(
        host="localhost",
        database="mydatabase",
        user="myuser",
        password="mypassword"
    )
    cursor = conn.cursor()

    cursor.execute("SELECT version();")
    db_version = cursor.fetchone()
    print(f"PostgreSQL database version: {db_version}")

    conn.commit()

except psycopg2.Error as e:
    print(f"PostgreSQL error: {e}")
finally:
    if conn:
        cursor.close()
        conn.close()
        print("PostgreSQL connection closed.")

2.3. MySQL (mysql-connector-python or PyMySQL)

MySQL is another widely used open-source relational database. Common Python connectors include mysql-connector-python (official) and PyMySQL.

Installation (for mysql-connector-python):

bash 
pip install mysql-connector-python

Example: MySQL Connection

python 
import mysql.connector

try:
    conn = mysql.connector.connect(
        host="localhost",
        user="myuser",
        password="mypassword",
        database="mydatabase"
    )
    cursor = conn.cursor()

    cursor.execute("SHOW TABLES;")
    tables = cursor.fetchall()
    print("Tables in the database:", tables)

    conn.commit()

except mysql.connector.Error as e:
    print(f"MySQL error: {e}")
finally:
    if conn and conn.is_connected():
        cursor.close()
        conn.close()
        print("MySQL connection closed.")

3. Connecting to NoSQL Databases (e.g., MongoDB)

NoSQL databases provide a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases. They are often used for large datasets and real-time web applications.

3.1. MongoDB (PyMongo)

MongoDB is a popular document-oriented NoSQL database. PyMongo is the official Python driver for MongoDB.

Installation:

bash 
pip install pymongo

Example: MongoDB Connection

python 
from pymongo import MongoClient
from pymongo.errors import ConnectionFailure

try:
    # Connect to MongoDB (default host and port: localhost:27017)
    client = MongoClient('mongodb://localhost:27017/')

    # The ping command is cheap and does not require auth.
    client.admin.command('ping')
    print("Connected to MongoDB successfully!")

    # Access a database
    db = client.mydatabase

    # Access a collection
    collection = db.mycollection

    # Insert a document
    post_id = collection.insert_one({"title": "My first post", "content": "This is my post."}).inserted_id
    print(f"Inserted document with ID: {post_id}")

    # Find a document
    post = collection.find_one({"title": "My first post"})
    print(f"Found post: {post}")

except ConnectionFailure as e:
    print(f"Could not connect to MongoDB: {e}")
finally:
    if 'client' in locals() and client:
        client.close()
        print("MongoDB connection closed.")

4. Using ORMs (Object-Relational Mappers)

ORMs provide an abstraction layer over raw SQL, allowing you to interact with your database using Python objects rather than writing SQL queries directly. This can improve code readability, maintainability, and reduce the risk of SQL injection.

  • SQLAlchemy: A powerful and flexible ORM for SQL databases, compatible with many different SQL dialects.
  • Django ORM: Built-in ORM for the Django web framework, tightly integrated with its model system.
  • SQLModel: A library for interacting with SQL databases, designed for use with Pydantic and SQLAlchemy.

Example: Basic SQLAlchemy Connection and Query

python 
from sqlalchemy import create_engine, text

# Connect to an SQLite database file
# 'sqlite:///example.db' is the connection string (URL)
engine = create_engine('sqlite:///sqlalchemy_example.db')

try:
    with engine.connect() as connection:
        # Execute a raw SQL statement
        connection.execute(text("CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, name TEXT)"))
        connection.execute(text("INSERT INTO items (name) VALUES ('Apple')"))
        connection.execute(text("INSERT INTO items (name) VALUES ('Banana')"))

        # Commit the transaction
        connection.commit()

        # Query data
        result = connection.execute(text("SELECT * FROM items"))
        for row in result:
            print(row)

except Exception as e:
    print(f"SQLAlchemy error: {e}")

5. Best Practices

  • Use Context Managers: For database connections and cursors (with conn:), ensuring they are properly closed even if errors occur.
  • Error Handling: Wrap database operations in try...except blocks to catch database-specific exceptions (e.g., sqlite3.Error, psycopg2.Error).
  • Parameterized Queries: Always use parameterized queries (e.g., INSERT INTO users (name, email) VALUES (?, ?) or ...VALUES (%s, %s)) to prevent SQL injection vulnerabilities.
  • Connection Pooling: For high-traffic applications, use connection pooling (often provided by ORMs or dedicated libraries) to manage and reuse database connections efficiently.
  • Environment Variables: Store sensitive connection details (passwords, usernames) in environment variables, not directly in your code.
← All Python questions