ORM - Object Relational Mapping
Django Extensions Documentation
- Navigate to settings.py
- Approximately, in line 76 of code, this is the database config part
- Copy the code below, then change it to your corresponding parameters
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": '',
"USER": '',
"PASSWORD": '',
"HOST": "localhost",
"PORT": "5432",
}
}
- NAME → Database name e.g. django-project previously created in pgAdmin
![image](https://private-user-images.githubusercontent.com/68276889/278862117-b0bcf050-8686-4b26-bcfd-dcd59a0b3f0a.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjYxMTQwMzcsIm5iZiI6MTcyNjExMzczNywicGF0aCI6Ii82ODI3Njg4OS8yNzg4NjIxMTctYjBiY2YwNTAtODY4Ni00YjI2LWJjZmQtZGNkNTlhMGIzZjBhLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA5MTIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwOTEyVDA0MDIxN1omWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTkyYjRmYjFmNDE0ZTA0MDk4YjQ5ZDY2YmZlNzY0ZDUwOTQwZmM3MzliZTczMGQ5NDYzZjU5YWJlNDM5OTdiNzEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.v0-4mpq6SRHrRQHkHXnqzuQBiGvjLU1QCrXj2KEHVrI)
- USER → Database username (default is postgres)
- PASSWORD → Database password
- HOST → Database host (In development stage, use localhost or IP Address 127.0.0.1 also available)
- PORT → The port that used to run the database (Default port is 5432)
- 1.Create in your project a file named .env
![image](https://private-user-images.githubusercontent.com/68276889/278863219-00c235b0-3d44-45bb-9b43-3e1f95931b6e.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjYxMTQwMzcsIm5iZiI6MTcyNjExMzczNywicGF0aCI6Ii82ODI3Njg4OS8yNzg4NjMyMTktMDBjMjM1YjAtM2Q0NC00NWJiLTliNDMtM2UxZjk1OTMxYjZlLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA5MTIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwOTEyVDA0MDIxN1omWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTZhY2JjNWU1NTc3YzJhOTlhNjg1MTE5ODU0NzIyMzY5YmM1NDBjZmYyMjQyMDhiOTI1YWQ0OWY5Mzg0YzdiNzQmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.yIAJ_45ozqWV_bbdKJNWP1IDzz3Y4a-iifyWI_SuaKA)
-
2.Add this to the file:
DATABASES_NAME = 'working-queries-django-lab' DATABASES_USER = 'postgres' DATABASES_PASSWORD = '1234' MY_OWN_KEY = 'django-insecure-cd!p_@ut(kc8)%b_*@)i@kff^oGFrkvy=!c#i!lk9'
-
3.Change the data with yours in the variables!!!
- DATABASES_NAME
- DATABASES_USER
- DATABASES_PASSWORD
- MY_OWN_KEY
-
4.After adding the .env file, the database settings should look like this:
SECRET_KEY = config('MY_OWN_KEY')
DATABASES = { "default": { "ENGINE": "django.db.backends.postgresql", "NAME": config('DATABASES_NAME'), "USER": config('DATABASES_USER'), "PASSWORD": config('DATABASES_PASSWORD'), "HOST": "localhost", "PORT": "5432", } }
-
5.In your settings.py file add:
from decouple import config
-
6.Run in the Terminal:
- pip install python-decouple
django-admin startproject <projectname>
python manage.py startapp <appname>
python manage.py runserver
python manage.py makemigrations
python manage.py migrate
python manage.py createsuperuser
python manage.py collectstatic
python manage.py makemigrations <appname>
python manage.py migrate <appname>
python manage.py migrate <appname>
python manage.py shell
python manage.py create_command my_custom_command
python manage.py test appname
python manage.py makemigrations <main_app> --name <migrate_age_group> --empty
Open the generated data migration file and modify it to use
RunPython with a custom Python function.
from django.db import migrations
def set_age_group(apps, schema_editor):
person_model = apps.get_model('main_app', 'Person')
persons = person_model.objects.all()
for person_record in persons:
if person_record.age <= 12:
person_record.age_group = 'Child'
elif person_record.age <= 17:
person_record.age_group = 'Teen'
else:
person_record.age_group = 'Adult'
# We save changes to the base once, not every iteration
person_model.objects.bulk_update(persons, ['age_group'])
def set_age_group_default(apps, schema_editor):
person_model = apps.get_model('main_app', 'Person')
age_group_default = person_model._meta.get_field('age_group').default
for person in person_model.objects.all():
person.age_group = age_group_default
person.save()
class Migration(migrations.Migration):
dependencies = [
('main_app', '0009_person'),
]
operations = [
migrations.RunPython(set_age_group, reverse_code=set_age_group_default)
]
-
install:
pip install ipython
-
Run in the Terminal:
python manage.py shell
Note: When you run python manage.py shell you run a python (or IPython) interpreter but inside it load all your Django project configurations so you can execute commands against the database or any other resources that are available in your Django project
-
python manage.py migrate <app_name> zero
- Delete all migrations from the 'migrations' folder.
- python manage.py makemigrations.
- python manage.py migrate.
pip install ipdb
We put this where we want to debug:
import ipdb; ipdb.set_trace()
Define your model in the app's models.py file, then create a migration and apply it.
from appname.models import ModelName
data = ModelName.objects.all()
data = ModelName.objects.filter(fieldname=value)
new_record = ModelName(field1=value1, field2=value2)
new_record.save()
record = ModelName.objects.get(pk=pk)
record.field1 = new_value
record.save()
record = ModelName.objects.get(pk=pk)
record.delete()
Model.objects.filter(pk=1).update(is_capital=True)
or
location = Model.objects.first()
location.is_capital = True
location.save()
locations = Model.objects.all().order_by('-id')
return '\n'.join(str(x) for x in locations)
Model.objects.all().delete()
Model.objects.first().delete()
Model.objects.last().delete()
Model.objects.filter(year__gte=2020).values('price', 'price_with_discount')
Note: Returns records that have a year after 2020. In the QuerySet, show the price and the price with the discount (fields in the database).
unfinished_task = Model.objects.filter(is_finished=False)
return '\n'.join(str(t) for t in unfinished_task)
Note: Filters by boolean value. Returns all records for which the is_finished field in the database is False.
for task in Model.objects.all():
if task.id % 2 != 0:
task.is_finished = True
task.save()
Note: Loops through all objects in the database of the given model.Then it checks if the id is odd. If so, set the field is_finished=True.
def decodes_and_replace(text: str, task_title: str) -> None:
decoded_text = ''.join(chr(ord(x) - 3) for x in text) # -> Wash the dishes!
Model.objects.filter(title=task_title).update(description=decoded_text)
encode_and_replace("Zdvk#wkh#glvkhv$", "Simple Task")
tasks_with_matching_title = Model.objects.filter(title=task_title)
decoded_text = ''.join(chr(ord(x) - 3) for x in text)
for task in tasks_with_matching_title:
task.description = decoded_text
task.save()
encode_and_replace("Zdvk#wkh#glvkhv$", "Simple Task")
![image](https://private-user-images.githubusercontent.com/68276889/278715712-60582671-4604-4ed9-b0c1-9ee3fa121760.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjYxMTQwMzcsIm5iZiI6MTcyNjExMzczNywicGF0aCI6Ii82ODI3Njg4OS8yNzg3MTU3MTItNjA1ODI2NzEtNDYwNC00ZWQ5LWIwYzEtOWVlM2ZhMTIxNzYwLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA5MTIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwOTEyVDA0MDIxN1omWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTgwOTYwMTA0ZTU4NDljZTI3OTExYjA5NTZmNWU3YzRkYTdiNjk4NDBiZmFmNDI3Njk5ZWMxMmZlZDI0N2E1YTMmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.GGVBcNhCm8GOi89T0j3Ygf-23ppzOn1q_YQCDGU_zQE)
![image](https://private-user-images.githubusercontent.com/68276889/278718979-ba3f2eab-ecff-4786-a161-bf8132753c9b.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjYxMTQwMzcsIm5iZiI6MTcyNjExMzczNywicGF0aCI6Ii82ODI3Njg4OS8yNzg3MTg5NzktYmEzZjJlYWItZWNmZi00Nzg2LWExNjEtYmY4MTMyNzUzYzliLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA5MTIlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwOTEyVDA0MDIxN1omWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPTA3NGU0M2Y1YTFiNGIwMTc4ZTQ1NjczYzRjZWU4MTM2YTJmOTQ0NzJiZTg4ZjIzYjg1Yjk5M2UwNjRhODI3MmEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.y7w-im1o2BSEUD3k076LCVVpdvVOHZuR2RjdnAmpHHo)
Note: Filters by title "Simple task", changes description to "Zdvk#wkh#glvkhv$", which decodes to "Wash the dishes!'
# If the class name is "Mage" - increase the level by 3 and decrease the intelligence by 7.
Model.objects.filter(calss_name="Mage").update(
level=F('level') + 3,
intelligence=F('intelligence') - 7,
)
# If the class name is "Warrior" - decrease the hit points by half and increase the dexterity by 4.
Model.objects.filter(class_name="Warrior").update(
hit_points=F('hit_points') / 2,
dexterity=F('dexterity') + 4
)
# If the class name is "Assassin" or "Scout" - update their inventory to "The inventory is empty".
Model.objects.filter(class_name__in=["Assassin", "Scout"]).update(
inventory="The inventory is empty",
)
Note: An F() object represents the value of a model field, transformed value of a model field, or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory. Instead, Django uses the F() object to generate an SQL expression that describes the required operation at the database level.
11. Update a field in the database of a specific model. With SQL query and Python ORM on database column named "class_name" with value "Mage":
SQL query:
UPDATE <model>
SET level = level + 3
WHERE class_name = "Mage";
Python ORM:
Model.objects.filter(calss_name="Mage").update(
level=F('level') + 3,
)
best_artwork = Model.objects.order_by('-rating', 'id').first()
- '-rating': Filter by the 'rating' field and take the entry with the highest rating.
- 'id': If all records have the same rating then filter and take the record with the highest 'id'.
Model.objects.filter(rating__lt=0).delete()
- rating__lt < 0 (delete all entries below zero)
- ratin <= 0 (delete all entries below zero including zero)
Model.objects.filter(Q(brand='Lenovo') | Q(brand='Asus')).update(storage=512)
Model.objects.filter(brand_in=['Lenovo', 'Asus']).update(storage=512)
Note: The database field names are 'brand' and 'storage.
Laptop.objects.filter(brand=['Asus']).update(operation_system='Windows')
Laptop.objects.filter(brand=['Apple']).update(operation_system='MacOS')
Laptop.objects.filter(brand__in=['Dell', 'Acer']).update(operation_system='Linux')
Laptop.objects.filter(brand=['Lenovo']).update(operation_system='Chrome OS')
Laptop.objects.update(
operation_system=Case(
When(brand=['Asus'], then=Value('Windows')),
When(brand=['Apple'], then=Value('MacOS')),
When(brand=['Dell', 'Acer'], then=Value('Linux')),
When(brand=['Lenovo'], then=Value('Chrome OS')),
default=F('operation_system')
)
)
- We import them from django: Value(), When(), F()
Note: It checks the laptop brand and records the specific operating system.
Model.objects.exlude(difficulty='Easy').update(boss_health=500)
Note: Exclude all entries except entry with difficulty 'Easy'. And updated it with boss_health=500.
-
In your settings.py file add:
LOGGING = { 'version': 1, 'disable_existing_loggers': False, 'handlers': { 'console': { 'class': 'logging.StreamHandler'}}, 'root': { 'handlers': ['console'], 'level': 'DEBUG', }, 'loggers': { 'django.db.backends': { 'handlers': ['console'], 'level': 'DEBUG', 'propagate': False, }} }
-
One-To-Many Relationship
class Lecturer(models.Model): first_name = models.CharField(max_length=100) last_name = models.CharField(max_length=100) def __str__(self): return f"{self.first_name} {self.last_name}" # many class Subject(models.Model): name = models.CharField(max_length=100) code = models.CharField(max_length=100) # One-To-Many Relationship lecturer = models.ForeignKey('Lecturer', on_delete=models.SET_NULL, null=True) def __str__(self): return f"{self.name}"
Note:
Lecturer can have many subjects. One subject can have one lecturer.