Image by Editor | Midjourney

Connecting to a SQL database with Python helps you store and manage data easily. Python has libraries like sqlite3, pymysql, psycopg2, and SQLAlchemy for this. These tools let you connect to a database, run queries, and retrieve data.

This article will guide you through the process of connecting to different SQL databases using Python.

Database Connection and Execution

When using databases in Python, it is important to handle connections and run queries efficiently. Three common operations involved are:

1. Connecting to the Database (conn) – Establishes an active session with the database using libraries like sqlite3.connect() or mysql.connector.connect()
2. Executing Queries (conn.execute) – Runs SQL commands such as SELECT, INSERT, UPDATE, and DELETE to interact with the database
3. Closing the Connection (conn.close) – Terminates the database session to free up resources and prevent potential issues

Connecting to SQLite Database

SQLite is a lightweight, serverless database used for local storage. In Python, you can connect to it using the built-in sqlite3 module. The connection is made with sqlite3.connect(“database_name.db”). If the file does not exist, it creates a new database. A cursor object is used to run SQL commands.

import sqlite3

# Connect to database (creates a new one 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, age INTEGER)")

# Commit changes and close connection
conn.commit()
conn.close()

Connecting to MySQL Database

MySQL is a popular database for storing structured data. In Python, the pymysql library helps connect to MySQL. To connect, you need to provide host, username, password, and database name using pymysql.connect(). After connecting, a cursor object is used to run SQL queries.

pip install pymysql

import pymysql

# Establish connection
conn = pymysql.connect(
    host="your_host",
    user="your_username",
    password="your_password",
    database="your_database"
)
cursor = conn.cursor()

# Execute a query
cursor.execute("SHOW TABLES")
for table in cursor.fetchall():
    print(table)

# Close connection
conn.close()

Connecting to PostgreSQL Database

PostgreSQL is an open-source database known for reliability. In Python, the psycopg2 library is used to connect to it. To establish a connection, you need to provide host, database name, user, and password using psycopg2.connect(). After connecting, a cursor object is created to run SQL queries.

pip install psycopg2

import psycopg2

# Establish connection
conn = psycopg2.connect(
    dbname="your_database",
    user="your_username",
    password="your_password",
    host="your_host",
    port="5432"
)
cursor = conn.cursor()

# Execute a simple query
cursor.execute("SELECT version();")
print(cursor.fetchone())

# Close connection
conn.close()

Connecting Using SQLAlchemy

SQLAlchemy is a Python toolkit for working with databases. It simplifies database connections and queries. It supports SQLite, MySQL, and PostgreSQL. A connection is made using create_engine(), where the database URL defines the database type, credentials, and connection details.

pip install sqlalchemy

from sqlalchemy import create_engine

# SQLite Connection
engine = create_engine("sqlite:///example.db")

# MySQL Connection
# engine = create_engine("mysql+mysqlconnector://root:yourpassword@localhost/testdb")

# PostgreSQL Connection
# engine = create_engine("postgresql://postgres:yourpassword@localhost/testdb")

# Connect to the database
conn = engine.connect()

# Run a raw SQL query
result = conn.execute("SELECT 1")
print(result.fetchone())

# Close connection
conn.close()

Conclusion

Connecting to a SQL database with Python is important for managing data. It helps applications work with databases smoothly. Libraries like sqlite3, MySQL Connector, and psycopg2 make the process easier. A secure connection, proper query execution, and error handling are key to reliable database operations. Closing the connection helps free resources and keeps the system running well.

The following practices ensure safe and efficient database usage:

• Use Safe Queries – Prevent SQL injection by using placeholders instead of inserting values directly
• Reuse Connections – Use connection pooling to improve speed and avoid creating new connections every time
• Keep Credentials Safe – Store database credentials securely in environment variables or config files
• Close Connections – Always close database connections and cursors after use to save resources