GitHub - christianbomholt/SQLAlchemy-cheatsheet

Creating a simple database model

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class Movie(Base):
    __tablename__ = 'movie'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50),unique=True)
    director =  Column(String(50))
    year  =  Column(Integer)
    earnings = Column(Integer, nullable=True)
    
    def __repr__(self):
        return f"Movie(name={self.name}, year={self.year}, director={self.director}, earnings = {self.earnings})"

engine = create_engine('sqlite:///:memory:')

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

Adding objects

dark_knight = Movie(name = "The Dark Knight", director = "Christopher Nolan", year = 2008)
session.add(dark_knight)
session.commit()

Let's query all the objects to see that The Dark Knight got added correctly

session.query(Movie).all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None)]

Note that the object is printed nicely due to the __repr__ method

Let's add a few more movies

director = "Christopher Nolan"
movies = [
    {"name":"Following", "director": director, "year": 1998},
    {"name":"Memento", "director": director, "year": 2000},
    {"name":"Insomnia", "director": director, "year": 2003},
    {"name":"Batman Begins", "director": director, "year": 2005},
    {"name":"The Prestige", "director": director, "year": 2006},
    {"name":"The Darkk Knight", "director": director, "year": 2008},
    {"name":"Inception", "director": director, "year": 2010},
    {"name":"The Dark Knight Rises", "director": director, "year": 2012},
    {"name":"Interstellar", "director": director, "year": 2014},
]

for movie in movies:
    movie_object = Movie(**movie)
    session.add(movie_object)
    session.commit()

We now see that we have a substantial amount of movies

session.query(Movie).all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=Following, year=1998, director=Christopher Nolan, earnings = None),
 Movie(name=Memento, year=2000, director=Christopher Nolan, earnings = None),
 Movie(name=Insomnia, year=2003, director=Christopher Nolan, earnings = None),
 Movie(name=Batman Begins, year=2005, director=Christopher Nolan, earnings = None),
 Movie(name=The Prestige, year=2006, director=Christopher Nolan, earnings = None),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=Inception, year=2010, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = None),
 Movie(name=Interstellar, year=2014, director=Christopher Nolan, earnings = None)]

Lets try using filtering

session.query(Movie).filter_by(name="Interstellar").first()

Movie(name=Interstellar, year=2014, director=Christopher Nolan, earnings = None)

... maybe a little more complex filtering

from sqlalchemy import and_

session.query(Movie).filter(and_(Movie.year>2007, Movie.name.like('The Dark%'))).all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = None)]

What´s the difference between `filter_by`and `filter`

filter_by is used for simple queries on the column names using regular kwargs, like

db.users.filter_by(name='Joe')

The same can be accomplished with filter, not using kwargs, but instead using the '==' equality operator, which has been overloaded on the db.users.name object:

db.users.filter(db.users.name=='Joe')`

You can also write more powerful queries using filter, such as expressions like:

db.users.filter(or_(db.users.name=='Ryan', db.users.country=='England'))

We can order the data in our table using order_by

session.query(Movie).order_by(Movie.year).all()

[Movie(name=Following, year=1998, director=Christopher Nolan, earnings = None),
 Movie(name=Memento, year=2000, director=Christopher Nolan, earnings = None),
 Movie(name=Insomnia, year=2003, director=Christopher Nolan, earnings = None),
 Movie(name=Batman Begins, year=2005, director=Christopher Nolan, earnings = None),
 Movie(name=The Prestige, year=2006, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=Inception, year=2010, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = None),
 Movie(name=Interstellar, year=2014, director=Christopher Nolan, earnings = None)]

from sqlalchemy import desc

session.query(Movie).order_by(desc(Movie.year)).all()

[Movie(name=Interstellar, year=2014, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = None),
 Movie(name=Inception, year=2010, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Prestige, year=2006, director=Christopher Nolan, earnings = None),
 Movie(name=Batman Begins, year=2005, director=Christopher Nolan, earnings = None),
 Movie(name=Insomnia, year=2003, director=Christopher Nolan, earnings = None),
 Movie(name=Memento, year=2000, director=Christopher Nolan, earnings = None),
 Movie(name=Following, year=1998, director=Christopher Nolan, earnings = None)]

Let's try using the data from a query

Aggregation

First let add a few movies to the database - props to Srashti13 for curating the dataset https://github.com/Srashti13/Movie-Data-set.

import pandas as pd

movies = pd.read_csv('https://raw.githubusercontent.com/Srashti13/Movie-Data-set/master/Moviedf_full2.csv')

movies = movies[["Movie.Name", "Year", "Director", "Total.Gross.Earning.million."]]
movies.columns = ["Name", "year", "director", "earnings"]

movies.head()

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	Name	year	director	earnings
0	Black Panther	2018	Ryan Coogler	700059566
1	Avengers: Infinity War	2018	Anthony Russo, Joe Russo	678815482
2	Incredibles 2	2018	Brad Bird	608563044
3	Jurassic World: Fallen Kingdom	2018	J.A. Bayona	416769345
4	Deadpool 2	2018	David Leitch	318491426

for movie_tuple in movies.iterrows():
    movie = movie_tuple[1]
    exists = session.query(Movie).filter_by(name=movie.Name).first()
    if not exists:
        movie_object = Movie(
            name = movie.Name, 
            year = movie.year, 
            director = movie.director, 
            earnings=movie.earnings
        )
        
        session.add(movie_object)
    
session.commit()

session\
    .query(Movie)\
    .order_by(desc(Movie.earnings))\
    .limit(5)\
    .all()

[Movie(name=Star Wars: The Force Awakens, year=2015, director=J.J. Abrams, earnings = 936662225),
 Movie(name=Avatar, year=2009, director=James Cameron, earnings = 749766139),
 Movie(name=Black Panther, year=2018, director=Ryan Coogler, earnings = 700059566),
 Movie(name=Avengers: Infinity War, year=2018, director=Anthony Russo, Joe Russo, earnings = 678815482),
 Movie(name=Jurassic World, year=2015, director=Colin Trevorrow, earnings = 652270625)]

Group by and aggregate

session\
    .query(
        func.sum(Movie.name)
    ).all()


session\
    .query(func.sum(Item.nominal))\
    .filter(Item.mod.in_ (selected_mods))\
    .group_by(Item.item_type)\
    .all()

[(0.0)]

from sqlalchemy import func

result = session\
    .query(
        Movie.year,
        func.avg(Movie.earnings).label('avg_earnings')
    )\
    .filter(Movie.earnings.isnot(None))\
    .group_by(Movie.year)

result.all()

[(2001, 72022668.40816326),
 (2002, 81750440.3367347),
 (2003, 78470774.58585858),
 (2004, 83239208.49),
 (2005, 74872675.85714285),
 (2006, 79145198.73737374),
 (2007, 85367481.36363636),
 (2008, 80702203.48484848),
 (2009, 98637101.44),
 (2010, 91022223.62244898),
 (2011, 90576888.64),
 (2012, 97337851.91836734),
 (2013, 100596875.22),
 (2014, 94919040.54545455),
 (2015, 103213559.59),
 (2016, 103817633.94),
 (2017, 102710781.84),
 (2018, 93537240.24)]

To print this more nicely

[r._asdict() for r in result]

[{'year': 2001, 'avg_earnings': 72022668.40816326},
 {'year': 2002, 'avg_earnings': 81750440.3367347},
 {'year': 2003, 'avg_earnings': 78470774.58585858},
 {'year': 2004, 'avg_earnings': 83239208.49},
 {'year': 2005, 'avg_earnings': 74872675.85714285},
 {'year': 2006, 'avg_earnings': 79145198.73737374},
 {'year': 2007, 'avg_earnings': 85367481.36363636},
 {'year': 2008, 'avg_earnings': 80702203.48484848},
 {'year': 2009, 'avg_earnings': 98637101.44},
 {'year': 2010, 'avg_earnings': 91022223.62244898},
 {'year': 2011, 'avg_earnings': 90576888.64},
 {'year': 2012, 'avg_earnings': 97337851.91836734},
 {'year': 2013, 'avg_earnings': 100596875.22},
 {'year': 2014, 'avg_earnings': 94919040.54545455},
 {'year': 2015, 'avg_earnings': 103213559.59},
 {'year': 2016, 'avg_earnings': 103817633.94},
 {'year': 2017, 'avg_earnings': 102710781.84},
 {'year': 2018, 'avg_earnings': 93537240.24}]

Group by and count

Generally a good idea to check averages against the count or standard deviation - general syntax is

session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()

session.query(Movie.year, func.count(Movie.year)).group_by(Movie.year).all()

[(1998, 1),
 (2000, 1),
 (2001, 98),
 (2002, 98),
 (2003, 100),
 (2004, 100),
 (2005, 99),
 (2006, 100),
 (2007, 99),
 (2008, 101),
 (2009, 100),
 (2010, 99),
 (2011, 100),
 (2012, 99),
 (2013, 100),
 (2014, 100),
 (2015, 100),
 (2016, 100),
 (2017, 100),
 (2018, 100)]

Getting number of items

session\
    .query(Movie)\
    .count()

Rolling back

Let's say we accidently add a movie with the same name, despite having the Column set to unique

from sqlalchemy import exc
try:
    dark_knight = Movie(name = "The Dark Knight", director = "Christopher Nolan", year = 2008)
    session.add(dark_knight)
    session.commit()
except exc.IntegrityError as e:
    print(e)

(sqlite3.IntegrityError) UNIQUE constraint failed: movie.name
[SQL: INSERT INTO movie (name, director, year, earnings) VALUES (?, ?, ?, ?)]
[parameters: ('The Dark Knight', 'Christopher Nolan', 2008, None)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)

We need to roll back this transaction to restore order

session.rollback()

session.query(Movie).filter_by(name="The Dark Knight").all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None)]

Updating rows

Let's try updating the earnings for all Nolan movies for which we have data.

We first start in pandas land

nolan_movies = movies.query("director=='Christopher Nolan'")
nolan_movies

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

</style>

	Name	year	director	earnings
113	Dunkirk	2017	Christopher Nolan	188045546
415	Interstellar	2014	Christopher Nolan	188020017
601	The Dark Knight Rises	2012	Christopher Nolan	448139099
805	Inception	2010	Christopher Nolan	292576195
1000	The Dark Knight	2008	Christopher Nolan	533345358
1260	The Prestige	2006	Christopher Nolan	53089891
1307	Batman Begins	2005	Christopher Nolan	205343774
1640	Insomnia	2002	Christopher Nolan	67355513
1787	Memento	2001	Christopher Nolan	25544867

Let's compare with what we have in sqlalchemy

session.query(Movie).filter_by(director="Christopher Nolan").all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=Following, year=1998, director=Christopher Nolan, earnings = None),
 Movie(name=Memento, year=2000, director=Christopher Nolan, earnings = None),
 Movie(name=Insomnia, year=2003, director=Christopher Nolan, earnings = None),
 Movie(name=Batman Begins, year=2005, director=Christopher Nolan, earnings = None),
 Movie(name=The Prestige, year=2006, director=Christopher Nolan, earnings = None),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=Inception, year=2010, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = None),
 Movie(name=Interstellar, year=2014, director=Christopher Nolan, earnings = None),
 Movie(name=Dunkirk, year=2017, director=Christopher Nolan, earnings = 188045546)]

for movie_tuple in nolan_movies.iterrows():
    movie = movie_tuple[1]
    existing_nolan_movie = session.query(Movie).filter_by(name=movie.Name)
    if existing_nolan_movie:
        existing_nolan_movie.update({Movie.earnings: movie.earnings}) 
        
session.commit()

We now see that we have correct earnings for all our Nolan movies

session.query(Movie).filter_by(director="Christopher Nolan").all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = 533345358),
 Movie(name=Following, year=1998, director=Christopher Nolan, earnings = None),
 Movie(name=Memento, year=2000, director=Christopher Nolan, earnings = 25544867),
 Movie(name=Insomnia, year=2003, director=Christopher Nolan, earnings = 67355513),
 Movie(name=Batman Begins, year=2005, director=Christopher Nolan, earnings = 205343774),
 Movie(name=The Prestige, year=2006, director=Christopher Nolan, earnings = 53089891),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=Inception, year=2010, director=Christopher Nolan, earnings = 292576195),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = 448139099),
 Movie(name=Interstellar, year=2014, director=Christopher Nolan, earnings = 188020017),
 Movie(name=Dunkirk, year=2017, director=Christopher Nolan, earnings = 188045546)]

Deleting rows

Let's delete the erronous record with The Darkk Knight

session.query(Movie).filter_by(name="The Darkk Knight").delete()

session.query(Movie).filter(and_(Movie.year>2007, Movie.name.like('The Dark%'))).all()

[Movie(name=The Dark Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Darkk Knight, year=2008, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Knight Rises, year=2012, director=Christopher Nolan, earnings = None),
 Movie(name=The Dark Tower, year=2017, director=Nikolaj Arcel, earnings = 50701325)]

More complex data models

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import random

Base = declarative_base()

class Item(Base):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50),unique=True)
    nominal  =  Column(Integer)
    rarity =  Column(String(50))
    
    def __repr__(self):
        return f"Item(name={self.name}, nominal={self.nominal}, rarity={self.rarity})"


class LinkAttachments(Base):
    __tablename__ = "link_attachments"
    id = Column(Integer, primary_key=True)
    itemname = Column(String)
    attachname = Column(String)
    
    def __repr__(self):
        return f"Link(itemname={self.itemname}, attachname={self.attachname})"

class LinkMags(Base):
    __tablename__ = "link_mags"
    id = Column(Integer, primary_key=True)
    itemname = Column(String)
    magname = Column(String)    
    
class LinkBullets(Base):
    __tablename__ = "link_bullets"
    id = Column(Integer, primary_key=True)
    itemname = Column(String)
    bulletname = Column(String)

class LinkBulletMag(Base):
    __tablename__ = "link_bullet_to_mags"
    id = Column(Integer, primary_key=True)
    magname = Column(String)
    bulletname = Column(String)
    
class Attachments(Base):
    __tablename__ = "attachments"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    
    def __repr__(self):
        return f"Attach(name={self.name})"

class Bullets(Base):
    __tablename__ = "bullets"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    bulletcount = Column(Integer, default=7)
    
    def __repr__(self):
        return f"Bullet(name={self.name}, bullet_count={self.bulletcount})"

class Magazines(Base):
    __tablename__ = "magazines"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    magbulletcount = Column(Integer, default=30)
    
    def __repr__(self):
        return f"Mag(name={self.name}, bullets_in_mag={self.magbulletcount})"

    
engine = create_engine('sqlite:///:memory:')

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

import json

with open('DumpAttatch.json', 'r') as myfile:
    data=myfile.read()
    
attachments = json.loads(data)["HlyngeWeapons"]

Lets load in the items to have some baseline data

for item in attachments:
    item_name = item.get("name")
    exists = session.query(Item).filter_by(name=item_name).first()
    if not exists:
        item_obj = Item(name = item_name, nominal = random.randint(1, 300), rarity = "Common")
        session.add(item_obj)
    
session.commit()

session.query(Item).limit(5).all()

[Item(name=access, nominal=237, rarity=Common),
 Item(name=Weapon_Base, nominal=49, rarity=Common),
 Item(name=DefaultWeapon, nominal=192, rarity=Common),
 Item(name=PistolCore, nominal=293, rarity=Common),
 Item(name=RifleCore, nominal=29, rarity=Common)]

attachments[100]

{'name': 'LAW', 'attachments': [], 'bullets': ['Ammo_LAW_HE'], 'magazines': []}

Next we load in the appropriate attachments

for item in attachments:
    item_name = item.get("name")
    for attach in item.get("attachments"):
        
        exists = session.query(Attachments).filter_by(name=attach).first()
        if not exists:
            item_obj = Attachments(name = attach)
            session.add(item_obj)
        
        exists = session.query(LinkAttachments).filter_by(attachname=attach, itemname=item_name).first()
        if not exists:
            item_obj = LinkAttachments(attachname=attach, itemname=item_name)
            session.add(item_obj)

session.commit()

session.query(Attachments).limit(5).all()

[Attach(name=FNP45_MRDSOptic),
 Attach(name=Crossbow_RedpointOptic),
 Attach(name=PistolOptic),
 Attach(name=AK_WoodBttstck),
 Attach(name=AK_WoodBttstck_Black)]

session.query(LinkAttachments).limit(5).all()

[Link(itemname=Crossbow, attachname=FNP45_MRDSOptic),
 Link(itemname=Crossbow, attachname=Crossbow_RedpointOptic),
 Link(itemname=Crossbow, attachname=PistolOptic),
 Link(itemname=AK101, attachname=AK_WoodBttstck),
 Link(itemname=AK101, attachname=AK_WoodBttstck_Black)]

An example of how to get all attachments for a specific item

session.query(
         Item, Attachments,
    ).filter(
         Item.name == "Crossbow",
    ).filter(
        Item.name == LinkAttachments.itemname
    ).filter(
        LinkAttachments.attachname == Attachments.name
    ).all()

[(Item(name=Crossbow, nominal=47, rarity=Common),
  Attach(name=FNP45_MRDSOptic)),
 (Item(name=Crossbow, nominal=47, rarity=Common),
  Attach(name=Crossbow_RedpointOptic)),
 (Item(name=Crossbow, nominal=47, rarity=Common), Attach(name=PistolOptic))]

Or simpler if we dont need the item

session.query(
         Attachments,
    ).filter(
         Item.name == "Crossbow",
    ).filter(
        Item.name == LinkAttachments.itemname
    ).filter(
        LinkAttachments.attachname == Attachments.name
    ).all()

[Attach(name=FNP45_MRDSOptic),
 Attach(name=Crossbow_RedpointOptic),
 Attach(name=PistolOptic)]

Adding magazines

for item in attachments:
    item_name = item.get("name")
    for mag in item.get("magazines"):
        
        exists = session.query(Magazines).filter_by(name=mag).first()
        if not exists:
            item_obj = Magazines(name = mag)
            session.add(item_obj)
        
        exists = session.query(LinkMags).filter_by(magname=mag, itemname=item_name).first()
        if not exists:
            item_obj = LinkMags(magname=mag, itemname=item_name)
            session.add(item_obj)

session.commit()

session.query(
         Magazines,
    ).filter(
         Item.name == "AK101",
    ).filter(
        Item.name == LinkMags.itemname
    ).filter(
        LinkMags.magname == Magazines.name
    ).all()

[Mag(name=Mag_AK101_30Rnd, bullets_in_mag=30),
 Mag(name=Mag_AK101_30Rnd_Black, bullets_in_mag=30),
 Mag(name=Mag_AK101_30Rnd_Green, bullets_in_mag=30)]

Adding bullets

for item in attachments:
    item_name = item.get("name")
    for bullet in item.get("bullets"):
        
        exists = session.query(Bullets).filter_by(name=bullet).first()
        if not exists:
            item_obj = Bullets(name = bullet)
            session.add(item_obj)
        
        exists = session.query(LinkBullets).filter_by(bulletname=bullet, itemname=item_name).first()
        if not exists:
            item_obj = LinkBullets(bulletname=bullet, itemname=item_name)
            session.add(item_obj)
            
        for mag in item.get("magazines"):
            exists = session.query(LinkBulletMag).filter_by(bulletname=bullet, magname=mag).first()
            if not exists:
                item_obj = LinkBulletMag(bulletname=bullet, magname=mag)
                session.add(item_obj)
            
session.commit()

session.query(
         Bullets,
    ).filter(
         Item.name == "AK101",
    ).filter(
        Item.name == LinkBullets.itemname
    ).filter(
        LinkBullets.bulletname == Bullets.name
    ).all()

[Bullet(name=Ammo_556x45, bullet_count=7),
 Bullet(name=Ammo_556x45Tracer, bullet_count=7)]

Querying which mags fits the bullets

session.query(
         Magazines
    ).filter(
         Bullets.name == "Ammo_556x45",
    ).filter(
        Bullets.name == LinkBulletMag.bulletname
    ).filter(
        LinkBulletMag.magname == Magazines.name
    ).all()

[Mag(name=Mag_AK101_30Rnd, bullets_in_mag=30),
 Mag(name=Mag_AK101_30Rnd_Black, bullets_in_mag=30),
 Mag(name=Mag_AK101_30Rnd_Green, bullets_in_mag=30),
 Mag(name=Mag_STANAG_30Rnd, bullets_in_mag=30),
 Mag(name=Mag_STANAGCoupled_30Rnd, bullets_in_mag=30),
 Mag(name=Mag_CMAG_10Rnd, bullets_in_mag=30),
 Mag(name=Mag_CMAG_20Rnd, bullets_in_mag=30),
 Mag(name=Mag_CMAG_30Rnd, bullets_in_mag=30),
 Mag(name=Mag_CMAG_40Rnd, bullets_in_mag=30),
 Mag(name=Mag_CMAG_10Rnd_Green, bullets_in_mag=30),
 Mag(name=Mag_CMAG_20Rnd_Green, bullets_in_mag=30),
 Mag(name=Mag_CMAG_30Rnd_Green, bullets_in_mag=30),
 Mag(name=Mag_CMAG_40Rnd_Green, bullets_in_mag=30),
 Mag(name=Mag_CMAG_10Rnd_Black, bullets_in_mag=30),
 Mag(name=Mag_CMAG_20Rnd_Black, bullets_in_mag=30),
 Mag(name=Mag_CMAG_30Rnd_Black, bullets_in_mag=30),
 Mag(name=Mag_CMAG_40Rnd_Black, bullets_in_mag=30),
 Mag(name=Mag_M249_Box200Rnd, bullets_in_mag=30)]

And the converse...

session.query(
         Bullets
    ).filter(
         Magazines.name == "Mag_M249_Box200Rnd",
    ).filter(
        Magazines.name == LinkBulletMag.magname
    ).filter(
        LinkBulletMag.bulletname == Bullets.name
    ).all()

[Bullet(name=Ammo_556x45, bullet_count=7),
 Bullet(name=Ammo_556x45Tracer, bullet_count=7)]

Engine connection string

#the general form of a connection string:
`dialect+driver://username:password@host:port/database` 

#SQLITE:
'sqlite:///:memory:' #store everything in memory, data is lost when program exits
'sqlite:////absolute/path/to/project.db')  #Unix/Mac
'sqlite:///C:\\path\\to\\project.db' #Windows
r'sqlite:///C:\path\to\project.db' #Windows alternative

#PostgreSQL

'postgresql://user:pass@localhost/mydatabase'
'postgresql+psycopg2://user:pass@localhost/mydatabase'
'postgresql+pg8000://user:pass@localhost/mydatabase'

#Oracle
'oracle://user:[email protected]:1521/sidname'
'oracle+cx_oracle://user:pass@tnsname'

#Microsoft SQL Server
'mssql+pyodbc://user:pass@mydsn'
'mssql+pymssql://user:pass@hostname:port/dbname'

Name		Name	Last commit message	Last commit date
Latest commit History 4 Commits
.ipynb_checkpoints		.ipynb_checkpoints
DumpAttatch.json		DumpAttatch.json
README.md		README.md
SQL Alchemy cheatsheet.ipynb		SQL Alchemy cheatsheet.ipynb
erd.png		erd.png

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

Creating a simple database model

Adding objects

What´s the difference between `filter_by`and `filter`

Aggregation

Group by and aggregate

To print this more nicely

Group by and count

Getting number of items

Rolling back

Updating rows

Deleting rows

More complex data models

Lets load in the items to have some baseline data

Next we load in the appropriate attachments

An example of how to get all attachments for a specific item

Or simpler if we dont need the item

Adding magazines

Adding bullets

Querying which mags fits the bullets

Engine connection string

About

Releases

Packages

Languages

christianbomholt/SQLAlchemy-cheatsheet

Folders and files

Latest commit

History

Repository files navigation

Creating a simple database model

Adding objects

What´s the difference between filter_byand filter

Aggregation

Group by and aggregate

To print this more nicely

Group by and count

Getting number of items

Rolling back

Updating rows

Deleting rows

More complex data models

Lets load in the items to have some baseline data

Next we load in the appropriate attachments

An example of how to get all attachments for a specific item

Or simpler if we dont need the item

Adding magazines

Adding bullets

Querying which mags fits the bullets

Engine connection string

About

Resources

Stars

Watchers

Forks

Releases

Packages 0

Languages

What´s the difference between `filter_by`and `filter`

Packages