How to call SQL from python script

Integrating the power of SQL databases with the flexibility of Python is a cornerstone of modern data-driven applications. Whether you are building a web application, performing data analysis, or automating a backend process, knowing how to execute SQL queries from your Python scripts is an essential skill. This guide will walk you through the most popular and efficient ways to achieve this.

Choosing the Right Tool: The sqlite3 and psycopg2 Modules

Python's standard library includes the sqlite3 module for working with SQLite databases, which is perfect for prototyping, simple applications, and learning. For production-grade applications using databases like PostgreSQL, MySQL, or SQL Server, third-party drivers are the industry standard. The most widely used and recommended library for this purpose is psycopg2 for PostgreSQL. For this article, we will focus on these two popular options.

1. Connecting to a SQLite Database

SQLite is a serverless, file-based database, making it incredibly easy to get started. The connection is established by simply providing a path to the database file.

Step-by-Step Code Example

import sqlite3

# Establish a connection to the database (creates the file if it doesn't exist)
connection = sqlite3.connect('my_database.db')

# Create a cursor object to execute SQL commands
cursor = connection.cursor()

# Execute a SQL query to create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
)
""")

# Execute a query to insert data
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))

# Commit the transaction to save the changes
connection.commit()

# Don't forget to close the connection when done
connection.close()

2. Connecting to a PostgreSQL Database with Psycopg2

For more robust applications, you will likely use a database server like PostgreSQL. The psycopg2 library is the most popular PostgreSQL adapter for Python. You will need to install it first using pip:

pip install psycopg2-binary

Step-by-Step Code Example

import psycopg2

# Establish a connection using your database credentials
connection = psycopg2.connect(
    host="your_host",
    database="your_database",
    user="your_username",
    password="your_password"
)

# Create a cursor
cursor = connection.cursor()

# Execute a query to create a table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price NUMERIC(10, 2)
    )
""")

# Execute a parameterized query to insert data (safer and prevents SQL injection)
cursor.execute("INSERT INTO products (name, price) VALUES (%s, %s)", ('Laptop', 999.99))

# Commit the transaction
connection.commit()

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

3. Fetching Data from the Database

Executing SELECT queries allows you to retrieve data. The cursor object provides methods like fetchone(), fetchall(), and fetchmany() to get the results.

Fetching Data Example

# ... after establishing a connection and cursor ...

# Execute a SELECT query
cursor.execute("SELECT * FROM users")

# Fetch all rows from the result set
rows = cursor.fetchall()

# Iterate through the rows and print them
for row in rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

# Alternatively, fetch one row at a time
# row = cursor.fetchone()
# while row is not None:
#     print(row)
#     row = cursor.fetchone()

Best Practices and Security

When calling SQL from Python, always keep these points in mind:

Use Parameterized Queries: Never use string formatting (f"SELECT * FROM users WHERE name = {name}") to build queries. This makes your application vulnerable to SQL injection attacks. Always use the parameter substitution provided by the module (? for sqlite3, %s for psycopg2).
Manage Resources with Context Managers: Use the with statement to ensure connections and cursors are closed properly, even if an error occurs.
Handle Exceptions: Wrap your database operations in try-except blocks to handle potential errors gracefully.

Example with Context Manager (Recommended)

import sqlite3

# Using a context manager for the connection
with sqlite3.connect('my_database.db') as conn:
    # Using a context manager for the cursor
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM users")
        rows = cur.fetchall()
        for row in rows:
            print(row)
    # The cursor is automatically closed here
# The connection is automatically committed (if changes were made) and closed here

Incoming search terms
- How to connect to SQL database from Python script
- Execute SQL query in Python using psycopg2
- Python SQLite3 tutorial for beginners
- Best way to run SQL commands from Python
- How to prevent SQL injection in Python code
- Fetch data from PostgreSQL using Python script
- Python database connectivity example code
- Using parameterized queries in Python SQL
- Difference between sqlite3 and psycopg2 in Python
- How to insert data into SQL table from Python
- Python script to update SQL database records
- Context manager for database connection in Python

HexaPython - How to Tutorials