Connecting to Different Databases

Python is a versatile programming language known for its simplicity and readability. One of its strengths is its ability to connect to various databases, enabling developers to manage and manipulate data efficiently. In this guide, we will explore how to connect to different types of databases using Python, including SQLite, MySQL, PostgreSQL, and MongoDB.

SQLite

SQLite is a lightweight, serverless database engine that is easy to set up and use. It is ideal for small-scale applications or when you need a simple database solution.

Connecting to SQLite

To connect to an SQLite database using Python, you can use the sqlite3 module, which is included in the standard library.

import sqlite3

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

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

# Execute SQL queries
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)')

# Insert data into the table
cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', ('John Doe', 'john@example.com'))

# Commit the changes
conn.commit()

# Close the connection
conn.close()

Querying Data

To retrieve data from an SQLite database, you can use the fetchone() or fetchall() methods of the cursor object.

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

MySQL

MySQL is a popular relational database management system (RDBMS) that is widely used in web development. To connect to a MySQL database in Python, you can use the mysql-connector-python package, which provides an interface for interacting with MySQL databases.

Installing the MySQL Connector

You can install the mysql-connector-python package using pip:

pip install mysql-connector-python

Connecting to MySQL

import mysql.connector

# Connect to the database
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='mydatabase'
)

cursor = conn.cursor()

# Execute SQL queries
cursor.execute('CREATE TABLE IF NOT EXISTS customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255))')

cursor.execute('INSERT INTO customers (name, email) VALUES (%s, %s)', ('John Doe', 'john@example.com'))

conn.commit()

# Close the connection
conn.close()

Querying Data

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='mydatabase'
)

cursor = conn.cursor()

cursor.execute('SELECT * FROM customers')
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

PostgreSQL

PostgreSQL is a powerful open-source relational database system known for its robustness and extensibility. To connect to a PostgreSQL database in Python, you can use the psycopg2 package.

Installing the psycopg2 Package

You can install the psycopg2 package using pip:

pip install psycopg2

Connecting to PostgreSQL

import psycopg2

# Connect to the database
conn = psycopg2.connect(
    host='localhost',
    user='postgres',
    password='password',
    database='mydatabase'
)

cursor = conn.cursor()

# Execute SQL queries
cursor.execute('CREATE TABLE IF NOT EXISTS products (id SERIAL PRIMARY KEY, name VARCHAR(255), price NUMERIC(10, 2))')

cursor.execute('INSERT INTO products (name, price) VALUES (%s, %s)', ('Product 1', 10.99))

conn.commit()

# Close the connection
conn.close()

Querying Data

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    user='postgres',
    password='password',
    database='mydatabase'
)

cursor = conn.cursor()

cursor.execute('SELECT * FROM products')
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

MongoDB

MongoDB is a popular NoSQL database that stores data in a flexible, JSON-like format. To connect to a MongoDB database in Python, you can use the pymongo package.

Installing the pymongo Package

pip install pymongo

Connecting to MongoDB

import pymongo

# Connect to the MongoDB server
client = pymongo.MongoClient('mongodb://localhost:27017/')

# Connect to the database
db = client['mydatabase']

# Create a collection (or get a reference to an existing one)
collection = db['customers']

# Insert a document into the collection
collection.insert_one({'name': 'John Doe', 'email': 'john@example.com'})

# Query all documents in the collection
for customer in collection.find():
    print(customer)

# Close the connection
client.close()

I hope you found this guide to database connectivity in Python helpful! If you have any questions or would like to share your own experiences with database connectivity in Python, please feel free to do so in the comments below. Your feedback and insights are valuable to us and the community.