choice of web-framework

[Chris Angelico]

On Tue, Oct 24, 2017 at 6:57 AM, Chris Warrick <kwpolska at gmail.com> wrote:
> On 23 October 2017 at 21:37, John Black <jblack at nopam.com> wrote:
>> Chris, thanks for all this detailed information.  I am confused though
>> with your database recommendation.  You say you teach SQLAlchemy but
>> generally use PostgreSQL yourself.  I can maybe guess why there seems to
>> be this contradiction.  Perhaps PostgreSQL is better but too advanced for
>> the class you are teaching?  Can you clarify on which you think is the
>> better choice?  Thanks.
>
> Different Chris, but I’ll answer. Those are two very different things.
>
> PostgreSQL is a database server. It talks SQL to clients, stores data,
> retrieves it when asked. The usual stuff a database server does.
> Alternatives: SQLite, MySQL, MS SQL, Oracle DB, …
>
> SQLAlchemy is an ORM: an object-relational mapper, and also a database
> toolkit. SQLAlchemy can abstract multiple database servers/engines
> (PostgreSQL, SQLite, MySQL, etc.) and work with them from the same
> codebase. It can also hide SQL from you and instead give you Python
> classes. If you use an ORM like SQLAlchemy, you get database support
> without writing a single line of SQL on your own. But you still need a
> database engine — PostgreSQL can be one of them. But you can deploy
> the same code to different DB engines, and it will just work™
> (assuming you didn’t use any DB-specific features). Alternatives:
> Django ORM.
>
> psycopg2 is an example of a PostgreSQL client library for Python. It
> implements the Python DB-API and lets you use it to talk to a
> PostgreSQL server. When using psycopg2, you’re responsible for writing
> your own SQL statements for the server to execute. In that approach,
> you’re stuck with PostgreSQL and psycopg2 unless you rewrite your code
> to be compatible with the other database/library. Alternatives (other
> DBs): sqlite3, mysqlclient. There are also other PostgreSQL libraries
> available.
>

Thanks, namesake :)

The above is correct and mostly accurate. It IS possible to switch out
your back end fairly easily, though, even with psycopg2; there's a
standard API that most Python database packages follow. As long as you
stick to standard SQL (no PostgreSQL extensions) and the standard API
(no psycopg2 extensions), switching databases is as simple as changing
your "import psycopg2" into "import cx_oracle" or something. (And,
most likely, changing your database credentials.)

The point of an ORM is to make your databasing code look and feel like
Python code, rather than manually crafting SQL statements everywhere.
Here's how a simple database operation looks in SQLAlchemy:

def spammify(id):
    person = session.query(Person).get(id)
    person.style = "spam"
    session.commit()

Here's the equivalent using psycopg2:

def spammify(id):
    with db, db.cursor() as cur:
        cur.execute("update people set style='spam' where id=%s", id)

With SQLAlchemy, you ask for a particular record, and you get back an
object. That object has attributes for all the person's information,
and you can both read and write those attributes. Then you commit when
you're done. Without SQLAlchemy, you use another language (SQL),
embedded within your Python code.

The choice is mostly one of style and preference. But if you don't
currently have a preference, I would recommend using an ORM.

(There are other ORMs than SQLAlchemy, of course; I can't recall the
exact syntax for Django's off the top of my head, but it's going to be
broadly similar to this.)

ChrisA