SQL Alchemy Tutorial

This is a walk through the SQL Alchemy Tutorial. There's nothing original here, I'm just trying to re-acquaint myself with SQL Alchemy.

Imports

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    String,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Create the Engine

This is going to connect to SQLite (in memory).

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
engine = create_engine("sqlite:///:memory:", echo=True)

The echo parameter tells SQLAlchemy to start logging.

Declare a Mapping

The Base

This creates a base-class that our classes will inherit from.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
Base = declarative_base()

A User

First we're going to create a User class that maps to a user-table in the database.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __repr__(self):
        return "<User (name={} fullname={} password={})".format(self.name,
                                                                self.fullname,
                                                                self.password)

Create a Schema

SQL Alchemy automatically creates a Table object for us.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
User.__table__
Table('users', MetaData(bind=None), Column('id', Integer(), table=<users>, primary_key=True, nullable=False), Column('name', String(), table=<users>), Column('fullname', String(), table=<users>), Column('password', String(), table=<users>), schema=None)

If you try and print it you will only get the table-name 'users'.

Create the Table

Now we actually create the table using the Base object.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
Base.metadata.create_all(engine)
2018-11-30 18:05:27,009 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-11-30 18:05:27,010 INFO sqlalchemy.engine.base.Engine ()
2018-11-30 18:05:27,011 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-11-30 18:05:27,011 INFO sqlalchemy.engine.base.Engine ()
2018-11-30 18:05:27,013 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2018-11-30 18:05:27,014 INFO sqlalchemy.engine.base.Engine ()
2018-11-30 18:05:27,015 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE users (
        id INTEGER NOT NULL, 
        name VARCHAR, 
        fullname VARCHAR, 
        password VARCHAR, 
        PRIMARY KEY (id)
)


2018-11-30 18:05:27,016 INFO sqlalchemy.engine.base.Engine ()
2018-11-30 18:05:27,017 INFO sqlalchemy.engine.base.Engine COMMIT

Create a User

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
bob = User(name="bob", fullname="Bob Dobolina", password="bob spelled backwards")

print(bob.name)
print(bob.fullname)
print(bob.id)
bob
Bob Dobolina
None

Create a Session

So far we've only been working with python objects, you need a session to actually talk to a database.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
Session = sessionmaker(bind=engine)
/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
session = Session()

Add Bob to the Session

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
session.add(bob)

This still doesn't actually add it to the database, we need to commit it. This is useful because you can continue to create objects and update them without continuously hitting the database.

Now we actually do our first communication with the database.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
session.commit()
2018-12-03 14:39:51,652 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-12-03 14:39:51,655 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-03 14:39:51,656 INFO sqlalchemy.engine.base.Engine ('bob', 'Bob Dobolina', 'bob spelled backwards')
2018-12-03 14:39:51,658 INFO sqlalchemy.engine.base.Engine COMMIT
/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
print("Bob's ID: {}".format(bob.id))
Bob's ID: 1

Even though we didn't specify the ID, it gets added when the entry is committed to the database.

More Users

Instead of adding them one at a time you can pass in a list of users.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
session.add_all([
    User(name="ted", fullname="Ted Thompson", password="wordpass"),
    User(name="tom", fullname="Tom Tuttle", password="tacoma")
])
/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
session.commit()
2018-12-03 14:46:48,764 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-03 14:46:48,765 INFO sqlalchemy.engine.base.Engine ('ted', 'Ted Thompson', 'wordpass')
2018-12-03 14:46:48,766 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-03 14:46:48,767 INFO sqlalchemy.engine.base.Engine ('tom', 'Tom Tuttle', 'tacoma')
2018-12-03 14:46:48,768 INFO sqlalchemy.engine.base.Engine COMMIT

Now we can make a query to see what's there.

/home/athena/.virtualenvs/necromuralist.github.io/bin/python3: No module named virtualfish
for row in session.query(User).order_by(User.id):
    print(row.id, row.fullname)
2018-12-03 14:48:53,556 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password 
FROM users ORDER BY users.id
2018-12-03 14:48:53,557 INFO sqlalchemy.engine.base.Engine ()
1 Bob Dobolina
2 Ted Thompson
3 Tom Tuttle