In [1]:
from pathlib import Path
In [3]:
# Version Check
import sqlalchemy
print(sqlalchemy.__version__)
In [4]:
sqlite_engine_prefix_for_relative_paths = 'sqlite://'
sqlite_engine_prefix_for_absolute_paths = 'sqlite:///'
In [5]:
print(Path.cwd())
print(str(Path.cwd() / "example.db"))
In [6]:
# Create subdirectory if it doesn't exists
data_path = Path.cwd() / "data"
if not data_path.exists():
data_path.mkdir(mode=0o777)
print(data_path.exists())
data_path.resolve()
Out[6]:
In [7]:
from sqlalchemy import create_engine
In [8]:
create_engine_input = \
sqlite_engine_prefix_for_absolute_paths + \
str((data_path / "example.db").resolve())
print(create_engine_input)
# Works
#create_engine_input = \
# sqlite_engine_prefix_for_relative_paths + \
# "/example.db"
print(create_engine_input)
In [9]:
engine = \
create_engine(create_engine_input,
echo=True)
The return value of create_engine() is an instance of Engine, and it represents the core interface to the database.
The first time a method like Engine.execute() or Engine.connect() is called, the Engine establishes a real DBAPI connection to the databse, which is ten used to emit the SQL.
Engine directly once created; instead it's used behind the scenes by the ORM. Engine, when first returned by create_engine(), hasn't actually tried to connect to the database yet; that happens only the first time it's asked to perform a task against the databse.create_engine()When using ORM, the configurational process starts by describing database tables we're dealing with, and define own classes that'll be mapped to those tables. These 2 tasks are usually performed together in modern SQLAlchemy, using a system known as Declarative, which allows us to create classes that include directives to describe the actual database table they'll be mapped to.
Classes mapped using the Declarative system are defined in terms of a base class which maintains a catalog of classes and tables relative to that base, known as the declarative base class. Our application will usually have just 1 instance of this base in a commonly imported module.
Create this base class using declarative_base():
In [10]:
from sqlalchemy.ext.declarative import declarative_base
In [11]:
Base = declarative_base()
cf. https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/api.html
sqlalchemy.ext.declarative.declarative_base(bind=None, metadata=None, mapper=None, cls=<class 'object'>, name='Base', constructor=<function_declarative_constructor>, class_registry=None, metaclass=<class 'sqlalchemy.ext.declarative.api.DeclarativeMeta'>new base class will be given a metaclass that produces appropriate Table objects and makes the appropriate mapper() calls based on information provided declaratively in the class and any subclasses of the class.
Parameters:
Connectable, will be assigned the bind attribute on MetaData instance.metadata - optional MetaData instance. All Table objects implicitly declared by subclasses of base will share this MetaData.
class_registry - optional dictionary that'll serve as the registry of class names->mapped classes when string names are used to identify classes inside of relationship(), and others.
Now that we have a "base", define any number of mapped classes in terms of it.
start with
table called "users" <-> class User map to this table "users"
In [12]:
from sqlalchemy import Column, Integer, String
In [13]:
class User(Base):
# class using Declarative at minimum needs __tablename__ atttribute
__tablename__ = 'users'
# class using Declarative needs at least 1 Column which is part of a
# primary key.
#
# SQLAlchemy never makes any assumptions by itself about table to which
# a class refers, including that it has no built-in conventions for
# names, datatypes, or constraints.
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
nickname = Column(String)
def __repr__(self):
return "<User(name='%s', fullname='%s', nickname='%s')>" % (
self.name, self.fullname, self.nickname)
Create your own automated conventions using helper functions and mixin classes, described in Mixin and Custom Base Classes
When class constructed, Declarative replaces all Column objects with special Python accessors known as descriptors, process known as instrumentation.
With User class constructed via Declarative system, we have defined information about our table, known as table metadata.
Object used by SQLAlchemy to represent this information for a specific table is called Table object, and here Declarative has made 1 for us. We see this by inspecting __table__ attribute:
In [14]:
User.__table__
Out[14]:
When we declared our class, Declarative also created Table object according to our specifications, and associated it with class by constructing Mapper object.
Classical Mappings, any plain Python class can be mapped to any Table using mapper() function, described in Classical Mappings
Table object is a member of larger collection known as MetaData. When using Declarative, this object is available using .metadata attribute.
In [15]:
Base.metadata
Out[15]:
In [17]:
User.metadata
Out[17]:
In [16]:
# MetaData.create_all() checks first presence of tables, in actual
# CREATE TABLE statement
# Note, if the database hadn't been created yet, this will literally create the database in the file system.
Base.metadata.create_all(engine)
VARCHAR columns were generated without length on SQLite and PostgreSQL, this is a valid datatype, but not on others.
Length may be provided to String type:
Column(String(50))The length field on String as well as similar precision/scale fields available on Integer, Numeric, etc. aren't referenced by SQLAlchemy other than when creating tables.
Additionally, Firebird and Oracle require sequences to generate primary key identifiers, and SQLAlchemy doesn't generate or assume these without being instructed.
If otherwise, use Sequence construct
from sqlalchemy import Sequence
Column(Integer, sequence('user_id_seq'), primary_key=True)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
name = Column(string(50))
fullname = Column(String(50))
nickname = Column(String(50))
def __repr__(self):
return "<Username>='%s', fullname='%s', nickname='%s')>" % (
self.name, self.fullname, self.nickname)
In [18]:
ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
print(ed_user.name)
In [19]:
ed_user.nickname
Out[19]:
In [20]:
str(ed_user.id)
Out[20]:
Even though we didn't specify it in ctor, id attribute produces value None when we access it (as opposed to Python's raising AttributeError for undefined attribute). SQLAlchemy's instrumentation normally produces this default value for column-mapped attributes when 1st accessed.
In [21]:
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
In the case where your application doesn't have an Engine when you define your module-level objects, just set it up like this:
Session = sessionmaker()
Later, when you create your engine with create_engine(), connect it to Session using configure():
Session.configure(bind=engine)When do I construct a Session when do I commit it, and when do I close it?
When you need to have a conversation with the database, instantiate a Session; Session associated with our SQLite-enabled Engine, but hasn't opened any connections yet.
In [22]:
session = Session()
In [23]:
ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
session.add(ed_user)
At this point, instance is pending; no SQL has yet been issued and object isn't represented yet by row in the database.
Session will issue SQL to persist Ed Jones as soon as needed, process known as flushIf we query database for Ed Jones, all pending information will be first be flushed, and query immediately issued.
e.g. below, we create a new Query object which loads instances of Users. We "filter by" name attribute of ed
In [24]:
our_user = session.query(User).filter_by(name='ed').first()
In [25]:
our_user
Out[25]:
In [26]:
# Session identified that row returned is the same row as 1 already
# represented within its internal map of objects
ed_user is our_user
Out[26]:
ORM concept of identity map ensures that all operations upon a particular row within a Session operate upon same set of data.
In [27]:
# Add more `User` objects at once using `add_all()`
session.add_all([
User(name='wendy', fullname='Wendy Williams', nickname='windy'),
User(name='mary', fullname='Mary Contrary', nickname='mary'),
User(name='fred', fullname='Fred Flintstone', nickname='freddy')
])
In [28]:
# change Ed's nickname
ed_user.nickname = 'eddie'
In [29]:
# Session is paying attention, e.g.
session.dirty
Out[29]:
In [30]:
# Sessino knows 3 new User objects are pending
session.new
Out[30]:
We tell Session we'd like to issue all remaining changes to the database and commit the transaction, which has been in progress throughout.
commit()Session emits UPDATE statement for the nickname change on "ed", as well as INSERT statements for 3 new User objects added
In [31]:
session.commit()
In [32]:
ed_user.id # Now is 1
Out[32]:
In [33]:
ed_user
Out[33]:
In [34]:
ed_user.name = 'Edwardo'
In [35]:
fake_user = User(name='fakeuser', fullname='Invalid', nickname='12345')
session.add(fake_user)
In [36]:
# Querying session, they're flushed into current transaction
session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()
Out[36]:
Rolling back, we can see that ed_user's name is back to ed, and fake_user has been kicked out of the session
In [37]:
session.rollback()
In [38]:
ed_user.name
Out[38]:
In [39]:
fake_user in session
Out[39]:
In [40]:
session.query(User).filter(User.name.in_(['ed', 'fakeuser'])).all()
Out[40]:
In [41]:
for instance in session.query(User).order_by(User.id):
print(instance.name, instance.fullname)
In [43]:
for name, fullname in session.query(User.name, User.fullname):
print(name, fullname)
In [42]:
for row in session.query(User, User.name).all():
print(row.User, row.name)
In [44]:
for row in session.query(User.name.label('name_label')).all():
print(row.name_label)
The name given to a full entity such as User, assuming that multiple entities are present in the call to query(), can be controlled using aliased()
In [45]:
from sqlalchemy.orm import aliased
user_alias = aliased(User, name='user_alias')
In [46]:
for row in session.query(user_alias, user_alias.name).all():
print(row.user_alias)
Basic operations with Query (i.e. actual SQL commands) include LIMIT and OFFSET, most conveniently using Python array slices, in conjunction with ORDER BY:
In [50]:
for u in session.query(User).order_by(User.id)[1:3]:
print(u)
In [51]:
# filtering results
for name, in session.query(User.name).filter_by(fullname='Ed Jones'):
print(name)
or filter(), which uses more flexible SQL expression language constructs. These allow you to use regular Python operators with the class-level attributes on your mapped class:
In [52]:
for name, in session.query(User.name).filter(User.fullname=='Ed Jones'):
print(name)
In [53]:
# further criteria may be added
for user in session.query(User).filter(User.name=='ed').filter(User.fullname=='Ed Jones'):
print(user)
In [58]:
# equals
list(session.query(User).filter(User.name == 'ed'))
Out[58]:
In [59]:
# not equals
list(session.query(User).filter(User.name != 'ed'))
Out[59]:
In [61]:
# LIKE
list(session.query(User).filter(User.name.like('%ed%')))
Out[61]:
In [62]:
# ILIKE (case-insensitive LIKE)
list(session.query(User).filter(User.name.ilike('%ed')))
Out[62]:
In [63]:
# IN
list(session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])))
Out[63]:
In [ ]:
In [47]:
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
In [48]:
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
# ForeignKey here expresses that values in the addresses.user_id
# column should be constrained to those values in the users.id column,
# i.e. its primary key.
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship("User", back_populates="addresses")
def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address
In [49]:
# relationship tells the ORM that Address class itself should be linked to
# User class, using the attribute Address.user
User.addresses = relationship(
"Address", order_by=Address.id, back_populates="user")
relationship() uses foreign key relationships between the 2 tables to determine the nature of the linkage, determining that Address.user will be many to one.
relationship.back_populates is assigned to refer to the complementary attribute names; by doing so, each relationship() can make intelligent decision about same relaitonship as expressed in reverse; Address.user refers to User instance, and on other side User.addresses refers to list of Address instances.
More on back_populates, a newer version of back_ref, in Linking Relationships with Backref
In [ ]: