# Python SQLAlchemy

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper (ORM) that gives application developers the full power and flexibility of SQL.

## Installation

```bash
pip install SQLAlchemy
```

## Connecting to a Database

To connect to a database, we use `create_engine()`:

```python
from sqlalchemy import create_engine

# SQLite database
engine = create_engine('sqlite:///example.db', echo=True)
```

## Declarative Mapping

SQLAlchemy uses a system known as Declarative to define classes that map to database tables.

```python
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    nickname = Column(String)

    def __repr__(self):
       return f"<User(name='{self.name}', fullname='{self.fullname}', nickname='{self.nickname}')>"
```

## Creating the Schema

```python
Base.metadata.create_all(engine)
```

## Creating a Session

The `Session` is the handle to the database.

```python
from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()
```

## CRUD Operations

### Create

```python
ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
session.add(ed_user)
session.commit()
```

### Read

```python
our_user = session.query(User).filter_by(name='ed').first()
print(our_user)
```

### Update

```python
ed_user.nickname = 'eddie'
session.commit()
```

### Delete

```python
session.delete(ed_user)
session.commit()
```

[[programming/python/python]]