Introduction to Database Connectivity Using Python

Databases are the backbone of modern applications. Whether you are building a web app, a desktop tool, or a data-driven solution, connecting your Python program to a database is a critical skill. Python provides several libraries and frameworks to make database connectivity easy, reliable, and efficient.

In this article, we’ll cover the basics of database connectivity using Python, explore popular libraries, and walk through examples of connecting to databases like SQLite and MySQL.

What is Database Connectivity?

Database connectivity is the process of establishing a link between a programming language (like Python) and a database management system (DBMS). This allows applications to:

• Store data (Insert records)

• Retrieve data (Select queries)

• Update existing records

• Delete unwanted data

Python supports multiple databases, including SQLite, MySQL, PostgreSQL, Oracle, and MongoDB.

Popular Python Libraries for Database Connectivity

1. SQLite3 – Built into Python, lightweight, great for small applications.

2. MySQL Connector/Python – Official MySQL driver for Python.

3. psycopg2 – Popular library for PostgreSQL.

4. SQLAlchemy – A powerful ORM (Object Relational Mapper) that supports multiple databases.

Connecting to SQLite Database

SQLite is a lightweight database that comes pre-installed with Python. It is widely used for local storage and small-scale applications.

import sqlite3

# Connect to database (or create it if it doesn’t exist)
connection = sqlite3.connect("mydatabase.db")

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

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

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))

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

This example shows how to create a database, table, and insert a record in SQLite.

Connecting to MySQL Database

To connect Python with MySQL, install the MySQL Connector:

pip install mysql-connector-python

Example:

import mysql.connector

# Establish connection
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="testdb"
)

cursor = connection.cursor()

# Create table
cursor.execute("CREATE TABLE IF NOT EXISTS employees (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), salary FLOAT)")

# Insert data
cursor.execute("INSERT INTO employees (name, salary) VALUES (%s, %s)", ("John", 50000))

connection.commit()
connection.close()

This connects to MySQL, creates a table, and inserts data.

Best Practices for Database Connectivity in Python

1. Always close connections after queries to free resources.

2. Use parameterized queries (? or %s) to prevent SQL injection.

3. Use context managers (with statement) to simplify connection handling.

4. Leverage ORMs like SQLAlchemy for complex applications.

Real-World Applications of Python Database Connectivity

• Web development – Store user data, sessions, and content.

• Data analysis – Retrieve and process data from large datasets.

• Machine learning – Store training data and model results.

• Enterprise software – Manage employees, inventory, sales, etc.

Conclusion

Database connectivity is an essential skill for any Python developer. With libraries like SQLite3, MySQL Connector, and SQLAlchemy, Python makes it easy to interact with relational databases. By mastering these techniques, you can build scalable, data-driven applications with confidence.