import sqlite3
# Connect to a SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('my_database.db')
# Create a cursor object to interact with the database
cursor = connection.cursor()
# Create a table (if it doesn't already exist)
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# Insert data into the table
cursor.execute("INSERT INTO users (name, age) VALUES ('John', 30)")
# Commit the transaction
connection.commit()
# Query the database
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)
# Close the connection
connection.close()

import java.sql.*;
public class Main {
    public static void main(String[] args) {
        try {
            // Load the SQLite JDBC driver
            Class.forName("org.sqlite.JDBC");
            // Connect to the SQLite database
            Connection conn = DriverManager.getConnection("jdbc:sqlite:my_database.db");
            // Create a statement
            Statement stmt = conn.createStatement();
            // Create a table
            stmt.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");
            // Insert data into the table
            stmt.execute("INSERT INTO users (name, age) VALUES ('John', 30)");
            // Query the database
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" + rs.getInt("age"));
            }
            // Close the connection
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

# Inserting new data into the 'users' table
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")
connection.commit()

# Selecting all rows from the 'users' table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

# Updating an existing record
cursor.execute("UPDATE users SET age = 26 WHERE name = 'Alice'")
connection.commit()

# Deleting a record
cursor.execute("DELETE FROM users WHERE name = 'Alice'")
connection.commit()

pip install sqlalchemy

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# Define a base class
Base = declarative_base()
# Define a User class mapped to the 'users' table
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
# Create a SQLite database engine
engine = create_engine('sqlite:///my_database.db', echo=True)
# Create the 'users' table
Base.metadata.create_all(engine)
# Create a session to interact with the database
Session = sessionmaker(bind=engine)
session = Session()
# Create a new user
new_user = User(name='Bob', age=40)
session.add(new_user)
session.commit()
# Query all users
users = session.query(User).all()
for user in users:
    print(f"{user.id} - {user.name} - {user.age}")
# Close the session
session.close()

import sqlite3
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# SQLite3 Example
print("Using sqlite3:")
# Connect to the SQLite database
connection = sqlite3.connect('my_database.db')
cursor = connection.cursor()
# Create a table if not already created
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
# Check if user already exists before inserting
name = 'Alice'
age = 25
cursor.execute("SELECT * FROM users WHERE name = ? AND age = ?", (name, age))
existing_user = cursor.fetchall()
if existing_user:
    print(f"User {name}, {age} already exists!")
else:
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))
    connection.commit()
    print(f"User {name}, {age} added successfully!")
# Query all users from the database
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)
# Close the SQLite3 connection
connection.close()
# SQLAlchemy Example
print("\nUsing SQLAlchemy:")
Base = declarative_base()
# Define a User class that maps to the 'users' table
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
# Create an engine and session
engine = create_engine('sqlite:///my_database.db', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
# Start an explicit transaction with the session
session = Session()
try:
    # Check if user already exists in SQLAlchemy before inserting
    existing_user = session.query(User).filter_by(name='Alice', age=25).first()
    if existing_user:
        print(f"User {name}, {age} already exists!")
    else:
        # Create a new user and add it to the session
        new_user = User(name='Alice', age=25)
        session.add(new_user)
        session.commit()  # Commit the transaction explicitly
        print(f"User {name}, {age} added successfully!")
    # Query all users in the SQLAlchemy database
    users = session.query(User).all()
    for user in users:
        print(f"{user.id} - {user.name} - {user.age}")
    # Commit all changes
    session.commit()
except Exception as e:
    print(f"Error: {e}")
    session.rollback()  # Rollback the transaction if any error occurs
finally:
    # Close the session after all operations
    session.close()

### Conclusion
In this post, we've explored how to use **sqlite3** for direct SQL queries and **SQLAlchemy** for Object Relational Mapping (ORM) in Python. We also looked at how these Python approaches compare to **JDBC** and **Hibernate** in Java. By using these methods, you can easily integrate Python with databases, perform CRUD operations, and manage your data effectively, whether you're working with lightweight SQLite databases or full-scale relational systems.
Happy coding!