In [1]:
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
In [2]:
engine = create_engine("sqlite:///database.sqlite", echo=True)
In [4]:
Base = declarative_base()
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship
In [6]:
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    tweets = relationship("Tweet", back_populates="user")
    def __repr__(self):
        return (f"<User(first_name={self.first_name!r}, "
                f"last_name={self.last_name!r}), id = {self.id!r}>")
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Tweet(Base):
    __tablename__ = 'tweets'
    id = Column(Integer, primary_key=True)
    text = Column(String)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="tweets")
    def __repr__(self):
        return (f"Tweet(text={self.text!r}, user={self.user!r}) with id = {self.id}")
In [8]:
alice = User(first_name="Alice", last_name='Smith')
In [9]:
alice.first_name
Out[9]:
'Alice'
In [9]:
alice
Out[9]:
<User(first_name='Alice', last_name='Smith'), id = None>
In [10]:
Base.metadata.create_all(engine)
2019-05-17 16:06:37,245 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine ()
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine ()
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine ()
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("tweets")
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine ()
2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tweets (
	id INTEGER NOT NULL, 
	text VARCHAR, 
	user_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(user_id) REFERENCES users (id)
)


2019-05-17 16:06:37,260 INFO sqlalchemy.engine.base.Engine ()
2019-05-17 16:06:37,435 INFO sqlalchemy.engine.base.Engine COMMIT
In [12]:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
In [13]:
session.add(alice)
In [14]:
users = session.query(User).all()
2019-05-17 16:07:09,796 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-05-17 16:07:09,796 INFO sqlalchemy.engine.base.Engine INSERT INTO users (first_name, last_name) VALUES (?, ?)
2019-05-17 16:07:09,796 INFO sqlalchemy.engine.base.Engine ('Alice', 'Smith')
2019-05-17 16:07:09,796 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users
2019-05-17 16:07:09,796 INFO sqlalchemy.engine.base.Engine ()
In [16]:
users
Out[16]:
[<User(first_name='Alice', last_name='Doe'), id = 1>,
 <User(first_name='Jon', last_name='Snow'), id = 2>,
 <User(first_name='Harry', last_name='Potter'), id = 3>,
 <User(first_name='Donald', last_name='Trump'), id = 4>,
 <User(first_name='Alice', last_name='Smith'), id = 5>]
In [17]:
session.delete(session.query(User).get(5))
In [ ]:
 
In [18]:
session.query(User).all()
2019-05-17 16:08:34,183 INFO sqlalchemy.engine.base.Engine SELECT tweets.id AS tweets_id, tweets.text AS tweets_text, tweets.user_id AS tweets_user_id 
FROM tweets 
WHERE ? = tweets.user_id
2019-05-17 16:08:34,183 INFO sqlalchemy.engine.base.Engine (5,)
2019-05-17 16:08:34,183 INFO sqlalchemy.engine.base.Engine DELETE FROM users WHERE users.id = ?
2019-05-17 16:08:34,198 INFO sqlalchemy.engine.base.Engine (5,)
2019-05-17 16:08:34,198 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users
2019-05-17 16:08:34,198 INFO sqlalchemy.engine.base.Engine ()
Out[18]:
[<User(first_name='Alice', last_name='Doe'), id = 1>,
 <User(first_name='Jon', last_name='Snow'), id = 2>,
 <User(first_name='Harry', last_name='Potter'), id = 3>,
 <User(first_name='Donald', last_name='Trump'), id = 4>]
In [19]:
session.commit()
2019-05-17 16:08:53,613 INFO sqlalchemy.engine.base.Engine COMMIT
In [22]:
session.add_all([User(first_name="Jon", last_name="Snow"),
                 User(first_name="Harry", last_name="Potter"),
                 User(first_name="Donald", last_name="Trump")])
In [26]:
harry = session.query(User).get(3)
In [31]:
session.query(User).get(3).first_name
Out[31]:
'Harry'
In [27]:
type(harry)
Out[27]:
__main__.User
In [28]:
harry
Out[28]:
<User(first_name='Harry', last_name='Potter'), id = 3>
In [29]:
harry.first_name
Out[29]:
'Harry'
In [30]:
harry.last_name
Out[30]:
'Potter'
In [33]:
session.query(User).filter(User.last_name == "Trump").all()
2019-05-17 15:52:35,593 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users 
WHERE users.last_name = ?
2019-05-17 15:52:35,593 INFO sqlalchemy.engine.base.Engine ('Trump',)
Out[33]:
[<User(first_name='Donald', last_name='Trump'), id = 4>]
In [34]:
session.query(User).filter(User.last_name == "Trump").one()
2019-05-17 15:52:55,283 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users 
WHERE users.last_name = ?
2019-05-17 15:52:55,283 INFO sqlalchemy.engine.base.Engine ('Trump',)
Out[34]:
<User(first_name='Donald', last_name='Trump'), id = 4>
In [36]:
session.query(User).filter(User.last_name.like("%r%")).all()
2019-05-17 15:54:25,291 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users 
WHERE users.last_name LIKE ?
2019-05-17 15:54:25,291 INFO sqlalchemy.engine.base.Engine ('%r%',)
Out[36]:
[<User(first_name='Harry', last_name='Potter'), id = 3>,
 <User(first_name='Donald', last_name='Trump'), id = 4>]
In [37]:
session.commit()
2019-05-17 15:55:51,534 INFO sqlalchemy.engine.base.Engine COMMIT
In [23]:
alice = session.query(User).get(1)
In [24]:
alice.tweets
2019-05-17 16:11:13,093 INFO sqlalchemy.engine.base.Engine SELECT tweets.id AS tweets_id, tweets.text AS tweets_text, tweets.user_id AS tweets_user_id 
FROM tweets 
WHERE ? = tweets.user_id
2019-05-17 16:11:13,093 INFO sqlalchemy.engine.base.Engine (1,)
Out[24]:
[]
In [26]:
alice.tweets.append(Tweet(text="This is my first tweet!"))
In [27]:
alice.tweets
Out[27]:
[Tweet(text='This is my first tweet!', user=<User(first_name='Alice', last_name='Doe'), id = 1>) with id = None]
In [28]:
alice.tweets.append(Tweet(text="This is my second tweet!"))
In [29]:
session.query(Tweet).all()
2019-05-17 16:13:21,996 INFO sqlalchemy.engine.base.Engine INSERT INTO tweets (text, user_id) VALUES (?, ?)
2019-05-17 16:13:21,996 INFO sqlalchemy.engine.base.Engine ('This is my first tweet!', 1)
2019-05-17 16:13:21,996 INFO sqlalchemy.engine.base.Engine INSERT INTO tweets (text, user_id) VALUES (?, ?)
2019-05-17 16:13:21,996 INFO sqlalchemy.engine.base.Engine ('This is my second tweet!', 1)
2019-05-17 16:13:21,996 INFO sqlalchemy.engine.base.Engine SELECT tweets.id AS tweets_id, tweets.text AS tweets_text, tweets.user_id AS tweets_user_id 
FROM tweets
2019-05-17 16:13:22,012 INFO sqlalchemy.engine.base.Engine ()
Out[29]:
[Tweet(text='This is my first tweet!', user=<User(first_name='Alice', last_name='Doe'), id = 1>) with id = 1,
 Tweet(text='This is my second tweet!', user=<User(first_name='Alice', last_name='Doe'), id = 1>) with id = 2]
In [31]:
for tweet in alice.tweets:
    print(tweet.text)
This is my first tweet!
This is my second tweet!
In [33]:
trump = session.query(User).filter(User.last_name == "Trump").one()
2019-05-17 16:16:45,764 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users 
WHERE users.last_name = ?
2019-05-17 16:16:45,764 INFO sqlalchemy.engine.base.Engine ('Trump',)
In [36]:
trump.tweets.append(Tweet(text="Make America great again!!!"))
trump.tweets.append(Tweet(text="Build the WALL!"))
In [39]:
session.query(Tweet).get(2).user
Out[39]:
<User(first_name='Alice', last_name='Doe'), id = 1>
In [40]:
session.commit()
2019-05-17 16:18:46,016 INFO sqlalchemy.engine.base.Engine COMMIT
In [41]:
alice.tweets
2019-05-17 16:19:09,386 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-05-17 16:19:09,386 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name 
FROM users 
WHERE users.id = ?
2019-05-17 16:19:09,386 INFO sqlalchemy.engine.base.Engine (1,)
2019-05-17 16:19:09,386 INFO sqlalchemy.engine.base.Engine SELECT tweets.id AS tweets_id, tweets.text AS tweets_text, tweets.user_id AS tweets_user_id 
FROM tweets 
WHERE ? = tweets.user_id
2019-05-17 16:19:09,386 INFO sqlalchemy.engine.base.Engine (1,)
Out[41]:
[Tweet(text='This is my first tweet!', user=<User(first_name='Alice', last_name='Doe'), id = 1>) with id = 1,
 Tweet(text='This is my second tweet!', user=<User(first_name='Alice', last_name='Doe'), id = 1>) with id = 2]
In [45]:
for user, tweet in session.query(User, Tweet).join(Tweet).all():
    print(user.first_name, user.last_name, "wrote", tweet.text)
2019-05-17 16:20:41,189 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.first_name AS users_first_name, users.last_name AS users_last_name, tweets.id AS tweets_id, tweets.text AS tweets_text, tweets.user_id AS tweets_user_id 
FROM users JOIN tweets ON users.id = tweets.user_id
2019-05-17 16:20:41,189 INFO sqlalchemy.engine.base.Engine ()
Alice Doe wrote This is my first tweet!
Alice Doe wrote This is my second tweet!
Donald Trump wrote Make America great again!!!
Donald Trump wrote Build the WALL!
In [ ]: