What are the advantages of using ORM compared to manually writing SQL queries?

• Abstraction: Working with Python objects instead of SQL strings. This reduces the coupling of code to a specific database schema and simplifies refactoring.
• Portability: The ability to easily change the database without rewriting most of the code. ORM adapts queries to the syntax of the used DBMS.
• Security: Built-in protection against SQL injection through query parameterization.
• Convenience:
  • Automatic data conversion between Python types and database types.
  • Easy management of relationships between tables (one-to-many, many-to-many).
  • Often includes database schema migration tools.
• Performance: ORM can optimize queries in some cases (e.g., lazy loading of data), although manual writing can be faster for very specific scenarios.

# Example with ORM (SQLAlchemy)
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Creating session and query
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

new_user = User(name='Alice', age=30)
session.add(new_user)
session.commit()

user = session.query(User).filter_by(name='Alice').first()
print(user.age) # Outputs 30

session.close()

# Equivalent in plain SQL
import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        age INTEGER
    )
''')

# Insert data
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Alice', 30))
conn.commit()

# Fetch data
cursor.execute("SELECT age FROM users WHERE name = ?", ('Alice',))
age = cursor.fetchone()[0]
print(age) # Outputs 30

conn.close()