Working examples from https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/
- Run
docker-compose up
- Configure db through pgadmin (described under "Postgresql & PgAdmin powered by compose" further down below)
- Run:
python -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt -r requirements-dev.txt
python src/inserts.py # populate the database
python src/queries.py # perform queries
Output from queries.py
:
### All movies:
The Bourne Identity was released on 2002-10-11
Furious 7 was released on 2015-04-02
Pain & Gain was released on 2013-08-23
### Recent movies:
Furious 7 was released after 2015
### Dwayne Johnson movies:
The Rock starred in Pain & Gain
The Rock starred in Furious 7
### Actors that live in Glendale:
Dwayne Johnson has a house in Glendale
Mark Wahlberg has a house in Glendale
- SQLAlchemy expose interfaces in accordance with the Python DBAPI, specified in PEP-249
- PostgreSQL, MySQL, Oracle, Microsoft SQL Server, and SQLite can be used with SQLAlchemy
- Psycopg fully implements the Pyhton DBAPI implementation for PostgreSQL
- SQLAlchemy supports various SQL dialects
- SQLAlchemy supports generic types, vendor types and custom types
- SQLAlchemy supports four types of relationships:
- SQLAlchemy ORM cascading are handled through the
relationship()
construct. The most common cascade strategies:save-update
: Indicates that when a parent object is saved/updated, child objects are saved/updated as well.delete
: Indicates that when a parent object is deleted, children of this object will be deleted as well.delete-orphan
: Indicates that when a child object loses reference to a parent, it will get deleted.merge
: Indicates that merge() operations propagate from parent to children.
- SQLAlchemy ORM sessions is used to maintain a list of objects affected by a business transaction and to coordinate the writing out of these changes
- The Query API provides dozens of useful functions like
all()
. In the following list, we can see a brief explanation about the most important ones:count()
: Returns the total number of rows of a query.filter()
: Filters the query by applying a criteria.delete()
: Removes from the database the rows matched by a query.distinct()
: Applies a distinct statement to a query.exists()
: Adds an exists operator to a subquery.first()
: Returns the first row in a query.get()
: Returns the row referenced by the primary key parameter passed as argument.join()
: Creates a SQL join in a query.limit()
: Limits the number of rows returned by a query.order_by()
: Sets an order in the rows returned by a query.
This part of the project was forked from: https://github.com/khezen/compose-postgres
- docker >= 17.12.0+
- docker-compose
- Clone or download this repository
- Go inside of directory,
cd compose-postgres
- Run this command
docker-compose up -d
This Compose file contains the following environment variables:
POSTGRES_USER
the default value is postgresPOSTGRES_PASSWORD
the default value is changemePGADMIN_PORT
the default value is 5050PGADMIN_DEFAULT_EMAIL
the default value is [email protected]PGADMIN_DEFAULT_PASSWORD
the default value is admin
localhost:5432
- Username: postgres (as a default)
- Password: changeme (as a default)
- URL:
http://localhost:5050
- Username: [email protected] (as a default)
- Password: admin (as a default)
- Host name/address
postgres
- Port
5432
- Username as
POSTGRES_USER
, by default:postgres
- Password as
POSTGRES_PASSWORD
, by defaultchangeme