Why does sqlalchemy use DeclarativeMeta class inheritance to map objects to tables

user_2146894 0
Viewed 265

I'm learning sqlalchemy's ORM and I'm finding it very confusing / unintuitive. Say I want to create a User class and corresponding table.. I think I should do something like

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

engine = create_engine("sqlite:///todooo.db")
Base = declarative_base()

class User(Base):
    __tablename__ = 'some_table'
    id = Column(Integer, primary_key=True)
    name =  Column(String(50))

Base.metadata.create_all(engine)

Session = sessionmaker(bind = engine)
session1 = Session()

user1 = User(name='fred')
session1.add(user1)
session1.commit()

Here I

  1. create an Engine. My understanding is that the engine is like the front-line communicator between my code and the database.
  2. create a DeclarativeMeta, a metaclass whose job I think is to keep track of a mapping between my Python classes and my SQL tables
  3. create a User class
  4. initialize my database and tables with Base.metadata.create_all(engine)
  5. create a Session metaclass
  6. create a Session instance, session1
  7. create a User instance, user1

The thing I find quite confusing here is the Base superclass. What is the benefit to using it as opposed to doing something like engine.create_table(User)?

Additionally, if we don't need a Session to create the database and insert tables, why do we need a Session to insert records?

sqlalchemy python
1 Answers

SQLAlchemy needs a mechanism to hook the classes being mapped to the database rows. Basically, you have to tell it:

Use the class User as a mapped class for the table some_table. One way to do it is to use a common base class - declarative base. Another way would be calling a function to register you class into the mapper. This declarative base used to be an extension of sqlalchemy IIRC but later it became a standard.

Now, having a common base makes perfect sense for me, because I do not have to make an extra step to call a function to register the mapping. Instead, whatever I inherit from the declarative base is automatically mapped. Both attitudes can work in general.

Engine is able to give you connections and can take care of connection pooling. Connection is able to run things on the database. No ORM as yet. With a connection, you can create and run queries using QL (query language), but you have no mapping of database data to python objects.

Session uses a connection and takes care of ORM. Better read the docs here, but the simplest case is:

user1.name = "Different Fred"

That's it. It will generate and execute the SQL at the right moment. Really, read the docs.

Now, you can create a table only with a connection, as it does not make much sense to include the session in the process, because session takes care of the current mapping session. There is nothing to map if you do not physically have the table yet. So you create the tables with the connection, then you can make a session and use mapping. Also, tables creation is usually a once-off action done separately from the normal program run (at least create_all).

user_39399920
Related