Working with Databases

Python supports working with databases like SQLite, MySQL, and PostgreSQL.

It's built into Python, so you don’t need to install anything. It’s great for small, local projects or when you want quick setup without a server.

import sqlite3

connection = sqlite3.connect("mydatabase.db")
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS users (name TEXT, age INTEGER)")
cursor.execute("INSERT INTO users VALUES ('Alice', 25)")
cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():
    print(row)

connection.commit()
connection.close()

To connect to a MySQL database, you'll first install the mysql connector python package. Then, you can use it to connect to your MySQL server and run standard SQL queries.

First install

pip install mysql-connector-python

import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()

for row in results:
    print(row)

conn.close()

PostgreSQL is another relational database, often used for complex or large-scale applications. Use the psycopg2 library to connect and interact with it through Python.

First install

pip install psycopg2-binary

import psycopg2

conn = psycopg2.connect(
    host='localhost',
    database='your_database',
    user='your_username',
    password='your_password'
)

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

for row in rows:
    print(row)

conn.close()

If you're working with NoSQL data, MongoDB is a popular choice. Use the pymongo library to connect and interact with collections, which work like JSON objects.

First install

pip install pymongo

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db = client["my_database"]
collection = db["users"]

# Insert a document
collection.insert_one({"name": "Alice", "email": "alice@example.com"})

# Find documents
for user in collection.find():
    print(user)