<jupyter_start><jupyter_text>Core  Create an engine. For this demo we're using SQLite in memory, and echoing the SQL as it's executed.<jupyter_code>from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')<jupyter_output><empty_output><jupyter_text>Let's declare a basic table<jupyter_code>from sqlalchemy import *
metadata = MetaData()

vehicles_table = Table('vehicles', metadata,
				       Column('model', String),
                       Column('registration', String),
                       Column('odometer', Integer),
                       Column('last_service', Date),)
vehicles_table<jupyter_output><empty_output><jupyter_text>The table doesn't exist yet, so let's create it<jupyter_code>vehicles_table.create(bind=engine)<jupyter_output><empty_output><jupyter_text>SQLAlchemy core's main job is to generate SQL<jupyter_code>query = vehicles_table.select()
print("type:",type(query))
print("query:",query)<jupyter_output>type: <class 'sqlalchemy.sql.selectable.Select'>
query: SELECT vehicles.model, vehicles.registration, vehicles.odometer, vehicles.last_service 
FROM vehicles<jupyter_text>The _select_ above is a shortcut for...<jupyter_code>query = select([vehicles_table])
print("type:",type(query))
print("query:",query)<jupyter_output>type: <class 'sqlalchemy.sql.selectable.Select'>
query: SELECT vehicles.model, vehicles.registration, vehicles.odometer, vehicles.last_service 
FROM vehicles<jupyter_text>Access the table's column metadata<jupyter_code>vehicles_table.c.odometer<jupyter_output><empty_output><jupyter_text>Using method chaining we can add to the query. Note how column operators are overloaded to produce SQL<jupyter_code>query = query.where(vehicles_table.c.odometer < 10000)
print(query)<jupyter_output>SELECT vehicles.model, vehicles.registration, vehicles.odometer, vehicles.last_service 
FROM vehicles 
WHERE vehicles.odometer < :odometer_1<jupyter_text>Using the engine created earlier, let's now run our query<jupyter_code>results = engine.execute(query)
for row in results:
    print(row,end=" ")<jupyter_output><empty_output><jupyter_text>No results, let's insert some rows<jupyter_code>values = [
    { 'model': 'Ford Festiva', 'registration': 'HAX00R', 'odometer': 3141 },
    { 'model': 'Lotus Elise', 'registration': 'DELEG8', 'odometer': 31415 },
]
rows = engine.execute(vehicles_table.insert(), list(values)).rowcount
print(rows, "rows inserted")<jupyter_output>2 rows inserted<jupyter_text>Try our query again<jupyter_code>results = engine.execute(query)
for row in results:
    print(row,end=" ")<jupyter_output>('<NAME>', 'HAX00R', 3141, None)<jupyter_text>Trying something more elaborate, let's see the SQL for adding up the odometer readings by model<jupyter_code>print(select([vehicles_table.c.model, 
              func.sum(vehicles_table.c.odometer).label('total_km')
             ]).group_by(vehicles_table.c.model))<jupyter_output>SELECT vehicles.model, sum(vehicles.odometer) AS total_km 
FROM vehicles GROUP BY vehicles.model<jupyter_text>There's much much more we can do. Literal SQL, functions, joins, aliases, unions, ...  ORM<jupyter_code>from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()

class Person(Base):
    __tablename__ = 'people' # Choose your own table name!
    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    email = Column(String)
    birthday = Column(DateTime)
    appointments = relationship("Appointment", backref="person")

    def __init__(self, firstname, lastname, email):
        """ Constructor is optional """
        self.first_name = firstname
        self.last_name = lastname
        self.email = email

class Appointment(Base):
    __tablename__ = 'appointments'
    id = Column(Integer, primary_key=True)
    person_id = Column(Integer, ForeignKey('people.id')) # <-- Table name
    meeting_at = Column(DateTime)
    notes = Column(String)
print("Underlying table object:\n", repr(Person.__table__))
print("*"*30)
print("Mapper that's taking care of things:\n", repr(Person.__mapper__))
print("*"*30)
print("What does the declarative base know?\n", repr(Base.metadata.tables))<jupyter_output>Underlying table object:
 Table('people', MetaData(bind=None), Column('id', Integer(), table=<people>, primary_key=True, nullable=False), Column('first_name', String(), table=<people>), Column('last_name', String(), table=<people>), Column('email', String(), table=<people>), Column('birthday', DateTime(), table=<people>), schema=None)
******************************
Mapper that's taking care of things:
 <Mapper at 0x10527e278; Person>
******************************
What does the declarative base know?
 immutabledict({'appointments': Table('appointments', MetaData(bind=None), Column('id', Integer(), table=<appointments>, primary_key=True, nullable=False), Column('person_id', Integer(), ForeignKey('people.id'), table=<appointments>), Column('meeting_at', DateTime(), table=<appointments>), Column('notes', String(), table=<appointments>), schema=None), 'people': Table('people', MetaData(bind=None), Column('id', Integer(), table=<people>, primary_key=True, nullable=False), Column('first_name[...]<jupyter_text>Create the table!<jupyter_code>Base.metadata.create_all(engine)<jupyter_output><empty_output><jupyter_text>Now let's create a session<jupyter_code>from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

p = Person('newfirst', 'newlast', '<EMAIL>');
session.add(p)<jupyter_output><empty_output><jupyter_text>The query below will cause the above addition to be automatically flushed.<jupyter_code>for p in session.query(Person):
    print(p.first_name)<jupyter_output>newfirst<jupyter_text>The ORM uses the underlying core to generate SQL<jupyter_code>print(session.query(Person).filter_by(id=1))<jupyter_output>SELECT people.id AS people_id, people.first_name AS people_first_name, people.last_name AS people_last_name, people.email AS people_email, people.birthday AS people_birthday 
FROM people 
WHERE people.id = :id_1<jupyter_text>Session queries can be built up to retrieve specific elements<jupyter_code>for row in session.query(Person, Person.first_name, Person.email.label('address')).filter_by(id=1):
    print("Person object:", row.Person)
    print("Selected attributes:", row.first_name, row.address)<jupyter_output>Person object: <__main__.Person object at 0x104f72d68>
Selected attributes: newfirst <EMAIL><jupyter_text>The session keeps track of objects and their state, so the exact same object is returned even via another query.<jupyter_code>queried_person = session.query(Person).filter_by(first_name='newfirst').first()

p is queried_person<jupyter_output><empty_output><jupyter_text>Queries are generative<jupyter_code>query = session.query(Person).filter(~Person.first_name.in_(['ed', 'wendy', 'jack']))
print(query)<jupyter_output>SELECT people.id AS people_id, people.first_name AS people_first_name, people.last_name AS people_last_name, people.email AS people_email, people.birthday AS people_birthday 
FROM people 
WHERE people.first_name NOT IN (:first_name_1, :first_name_2, :first_name_3)<jupyter_text>Additional filter criteria is added with an AND operator<jupyter_code>from datetime import datetime, timedelta
query = query.filter(Person.birthday < datetime.now() - timedelta(days=1))
print(query)<jupyter_output>SELECT people.id AS people_id, people.first_name AS people_first_name, people.last_name AS people_last_name, people.email AS people_email, people.birthday AS people_birthday 
FROM people 
WHERE people.first_name NOT IN (:first_name_1, :first_name_2, :first_name_3) AND people.birthday < :birthday_1<jupyter_text>Relationships  Let's create an appointment related to our person<jupyter_code>appointment = Appointment(person=session.query(Person).first(),
                          notes="Appointment date TBC")
session.add(appointment)<jupyter_output><empty_output><jupyter_text>Now we can query from either end of the relationship<jupyter_code>for a in session.query(Appointment):
    print(a.person)
for p in session.query(Person):
    print(p.appointments)<jupyter_output>[<__main__.Appointment object at 0x104f7e400>]
{'fraction_non_alphanumeric': 0.12711659409772616, 'fraction_numerical': 0.007861635220125786, 'mean_word_length': 6.852801519468186, 'pattern_counts': {'":': 0, '<': 99, '<?xml version=': 0, '>': 94, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '448662', 'license': 'Apache-2.0', 'n_tokens_mistral': 2685, 'n_tokens_neox': 2567, 'n_words': 589, 'path': '03-data-x-data-handling/m350.1-sql/extra_material/SQLAlchemy.ipynb', 'repo_name': 'UCBerkeley-SCET/DataX-Berkeley', 'size': '8290'}