what is the difference between schema and column?

Question

This may be a naive question I am teaching myself Postgresql and sqlalchemy,

in this example : https://groups.google.com/forum/#!topic/sqlalchemy/p6U-pzZe6II

     from sqlalchemy import schema

     from sqlalchemy import types
     moment = schema.Column(types.Time(timezone=True))

but all this time I was learning is

    from sqlalchemy import DateTime
    app = Flask(__name__)
    db = SQLAchemy(app)
    visitor_time = db.Column(DateTime(timezone=True))

so what is the difference between these two?

Theoretically schema is a representation of a database while a column is a particular field in a table. So schema is a collection of whole lots of fields and rows etc. — 0decimal0, Aug 22 '17 at 16:14

rojeeer · Answer 1 · 2017-08-23T16:12:47.537

Well, you are using two different packages: SQLAlchemy (first case) vs Flask-SQLAlchemy(second case). And Flask-SQLAlchemy is a kind of extension of SQLAlchemy, as it says

It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks.

Okay, let's start with how you start to use a database. For most database, the logic hierarchy from top to bottom is cluster->database(catalog)->schema->table->column, please check Catalog, and also the official definitions of each level. The top three levels (Cluster, Database, and Schema) are often done by DB manager. A DB manager should set up clusters, and create database and user for you. And then you can log into the database with the client (e.g., psql for PostgreSQL). After these steps, you are assigned a database with access information (host, database, username, password, and etc.). These are necessary information to connect the database (SQLAlchemy create_engine)

We do not use SQLAlchemy to do the above three steps. Instead, we use SQLAlchemy to define down levels(Tables and Columns) and to get the benefits of ORM. And this is called Data Definition Language, DDL.

Now comes to the first case, SQLAlchemy provides a package sqlalchemy.schema to implement DDL. As it says:

Database metadata can be expressed by explicitly naming the various components and their properties, using constructs such as Table, Column, ForeignKey and Sequence, all of which are imported from the sqlalchemy.schema package.

For your second case, Flask-SQLAlchemy, a customized version of SQLAlchemy. They provide a new class SQLAlchemy, which works like a wrapper, as the docs says:

This class also provides access to all the SQLAlchemy functions and classes from the sqlalchemy and sqlalchemy.orm modules.

If you are interested, you may check the source code of Flask-SQLAlchemy. Here are some code snippets for SQLAlchemy class in the Flask-SQLAlchemy:

class SQLAlchemy(object):
    def __init__(self, app=None, use_native_unicode=True, 
                 session_options=None,
                 metadata=None, query_class=BaseQuery, model_class=Model):

        self.use_native_unicode = use_native_unicode
        self.Query = query_class
        self.session = self.create_scoped_session(session_options)
        self.Model = self.make_declarative_base(model_class, metadata)
        self._engine_lock = Lock()
        self.app = app
        _include_sqlalchemy(self, query_class)


def _include_sqlalchemy(obj, cls):
    for module in sqlalchemy, sqlalchemy.orm:
        for key in module.__all__:
            if not hasattr(obj, key):
                setattr(obj, key, getattr(module, key))
    # Note: obj.Table does not attempt to be a SQLAlchemy Table class.
    obj.Table = _make_table(obj)
    obj.relationship = _wrap_with_default_query_class(obj.relationship, cls)
    obj.relation = _wrap_with_default_query_class(obj.relation, cls)
    obj.dynamic_loader = _wrap_with_default_query_class(obj.dynamic_loader, cls)
    obj.event = event


def _make_table(db):
    def _make_table(*args, **kwargs):
        if len(args) > 1 and isinstance(args[1], db.Column):
            args = (args[0], db.metadata) + args[1:]
        info = kwargs.pop('info', None) or {}
        info.setdefault('bind_key', None)
        kwargs['info'] = info
        return sqlalchemy.Table(*args, **kwargs)
    return _make_table

As you can see, db.Model is actually an instance of DeclarativeBase. And when you call db.Table (This is a function), it actually calls _make_table and finally sqlalchemy.Table.

Hope this would be helpful. Thanks.

what is the difference between schema and column?

1 Answers1