import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
Engine(sqlite:///:memory:)
class City(Base):
__tablename__ = 'city'
id = Column(Integer, primary_key=True)
name = Column(String)
province = Column(String)
country = Column(String)
def __repr__(self):
return "<City(name='%s', province='%s', country='%s')>" % (
self.name, self.province, self.country)
Table('city', MetaData(bind=None), Column('id', Integer(), table=<city>, primary_key=True, nullable=False), Column('name', String(), table=<city>), Column('province', String(), table=<city>), Column('country', String(), table=<city>), schema=None)
Base.metadata.create_all(engine)
2019-05-20 11:24:13,262 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2019-05-20 11:24:13,263 INFO sqlalchemy.engine.base.Engine ()
2019-05-20 11:24:13,265 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2019-05-20 11:24:13,266 INFO sqlalchemy.engine.base.Engine ()
2019-05-20 11:24:13,269 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("city")
2019-05-20 11:24:13,270 INFO sqlalchemy.engine.base.Engine ()
2019-05-20 11:24:13,272 INFO sqlalchemy.engine.base.Engine
CREATE TABLE city (
id INTEGER NOT NULL,
name VARCHAR,
province VARCHAR,
country VARCHAR,
PRIMARY KEY (id)
)
2019-05-20 11:24:13,273 INFO sqlalchemy.engine.base.Engine ()
2019-05-20 11:24:13,275 INFO sqlalchemy.engine.base.Engine COMMIT
toronto = City(name='Toronto', province='Ontario', country='Canada')
# Create a session
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
cities = session.query(City).all()
2019-05-20 11:24:38,464 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-05-20 11:24:38,468 INFO sqlalchemy.engine.base.Engine INSERT INTO city (name, province, country) VALUES (?, ?, ?)
2019-05-20 11:24:38,469 INFO sqlalchemy.engine.base.Engine ('Toronto', 'Ontario', 'Canada')
2019-05-20 11:24:38,472 INFO sqlalchemy.engine.base.Engine SELECT city.id AS city_id, city.name AS city_name, city.province AS city_province, city.country AS city_country
FROM city
2019-05-20 11:24:38,474 INFO sqlalchemy.engine.base.Engine ()
[<City(name='Toronto', province='Ontario', country='Canada')>]
montreal = City(name='Montreal', province='Quebec', country='Canada')
cities = session.query(City).all()
2019-05-20 11:24:57,033 INFO sqlalchemy.engine.base.Engine INSERT INTO city (name, province, country) VALUES (?, ?, ?)
2019-05-20 11:24:57,036 INFO sqlalchemy.engine.base.Engine ('Montreal', 'Quebec', 'Canada')
2019-05-20 11:24:57,038 INFO sqlalchemy.engine.base.Engine SELECT city.id AS city_id, city.name AS city_name, city.province AS city_province, city.country AS city_country
FROM city
2019-05-20 11:24:57,039 INFO sqlalchemy.engine.base.Engine ()
[<City(name='Toronto', province='Ontario', country='Canada')>,
<City(name='Montreal', province='Quebec', country='Canada')>]
for city in cities:
print(city)
<City(name='Toronto', province='Ontario', country='Canada')>
<City(name='Montreal', province='Quebec', country='Canada')>
for city in cities:
print(city.name)
Toronto
Montreal
session.add_all([
City(name='Waterloo', province='Ontario', country='Canada'),
City(name='Kitchener', province='Ontario', country='Canada'),
City(name='Quebec City', province='Quebec', country='Canada')
])
cities = session.query(City).all()
2019-05-20 11:25:27,067 INFO sqlalchemy.engine.base.Engine INSERT INTO city (name, province, country) VALUES (?, ?, ?)
2019-05-20 11:25:27,068 INFO sqlalchemy.engine.base.Engine ('Waterloo', 'Ontario', 'Canada')
2019-05-20 11:25:27,070 INFO sqlalchemy.engine.base.Engine INSERT INTO city (name, province, country) VALUES (?, ?, ?)
2019-05-20 11:25:27,071 INFO sqlalchemy.engine.base.Engine ('Kitchener', 'Ontario', 'Canada')
2019-05-20 11:25:27,072 INFO sqlalchemy.engine.base.Engine INSERT INTO city (name, province, country) VALUES (?, ?, ?)
2019-05-20 11:25:27,073 INFO sqlalchemy.engine.base.Engine ('Quebec City', 'Quebec', 'Canada')
2019-05-20 11:25:27,076 INFO sqlalchemy.engine.base.Engine SELECT city.id AS city_id, city.name AS city_name, city.province AS city_province, city.country AS city_country
FROM city
2019-05-20 11:25:27,076 INFO sqlalchemy.engine.base.Engine ()
for city in cities:
print(city.name, city.province)
Toronto Ontario
Montreal Quebec
Waterloo Ontario
Kitchener Ontario
Quebec City Quebec