Object-relational-mapping (ORM) es una técnica (patron de diseño) que permite acceder a Bases de datos relacionase desde un lenguaje de programación orientado a objetos (python, Java, ruby).
http://www.yegor256.com/2014/12/01/orm-offensive-anti-pattern.html
# settings.py
# Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'snapsuits',
'USER': 'snapsuits',
'PASSWORD': 'password',
'HOST': 'localhost',
'PORT': '5432',
}
}
from django.db import models
class Person(models.Model):
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=30)
CREATE TABLE users_person (
"id" serial NOT NULL PRIMARY KEY,
"first_name" varchar(30) NOT NULL,
"last_name" varchar(30) NOT NULL
);
from django.db import models
class Person(models.Model):
name = models.CharField(max_length=128)
class Group(models.Model):
name = models.CharField(max_length=128)
members = models.ManyToManyField(Person, through='Membership')
class Membership(models.Model):
person = models.ForeignKey(Person)
group = models.ForeignKey(Group)
date_joined = models.DateField()
invite_reason = models.CharField(max_length=64)CREATE TABLE "members_person" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" varchar(128) NOT NULL
);
CREATE TABLE "members_group" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" varchar(128) NOT NULL
);
CREATE TABLE "members_membership" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"date_joined" date NOT NULL, "invite_reason" varchar(64) NOT NULL,
"group_id" integer NOT NULL REFERENCES "members_group" ("id"),
"person_id" integer NOT NULL REFERENCES "members_person" ("id")
);
# inserción usando el ORM
p = Person.objects.create(name='Guillermo')
INSERT INTO
"members_person" ("name") VALUES (%s)' - PARAMS = (u'Guillermo',); args=['Guillermo']
# obteniendo un registro
p = Person.objects.get(name='Guillermo')
SELECT
"members_person"."id", "members_person"."name"
FROM
"members_person" WHERE "members_person"."name" = %s' - PARAMS = (u'Guillermo',); args=('Guillermo',)
# obteniendo varios registros
p = Person.objects.filter(name='Guillermo')
SELECT
"members_person"."id", "members_person"."name"
FROM
"members_person"
WHERE "members_person"."name" = %s LIMIT 21' - PARAMS = (u'Guillermo',); args=('Guillermo',)# Actualizando registros
# varios registros
Person.objects.filter(name='Guillermo').update(name='Pedro')
SELECT "members_person"."id", "members_person"."name" FROM "members_person"
WHERE "members_person"."id" = %s' - PARAMS = (1,); args=(1,)
# obteniendo un registro y actualizarlo
p = Person.objects.get(id=1)
p.name = 'Guillermo'
p.save()
UPDATE "members_person" SET "name" = %s WHERE "members_person"."id" = %s'
- PARAMS = (u'Guillermo', 1); args=('Guillermo', 1)
# eliminar un registro
p.delete()
DELETE FROM "members_person" WHERE "members_person"."id" IN (%s)' - PARAMS = (1,); args=(1,)PYTHON ORM
Python SQL toolkit y ORM que da a los desarrolladores de aplicaciones todo el poder y la flexibilidad de SQL
Bases de datos soportadas
# conexion a la BD
from sqlalchemy import create_engine
engine = engine = create_engine('sqlite:///my_database.db')
# Interactuando con sqlAlchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
Base = declarative_base()
class Person(Base):
__tablename__ = 'person'
id = Column(Integer, primary_key=True)
name = Column(String(250), nullable=False)
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
street_name = Column(String(250))
street_number = Column(String(250))
post_code = Column(String(250), nullable=False)
person_id = Column(Integer, ForeignKey('person.id'))
person = relationship(Person)
CREATE TABLE person (
id INTEGER NOT NULL,
name VARCHAR(250) NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE address (
id INTEGER NOT NULL,
street_name VARCHAR(250),
street_number VARCHAR(250),
post_code VARCHAR(250) NOT NULL,
person_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(person_id) REFERENCES person (id)
)
# Queries
from sqlalchemy import create_engine
from sqlalchemy.orm import session maker
engine = create_engine('sqlite:///my_database.db')
DBSession = sessionmaker(bind=engine)
session = DBSession()
# Insertar una persona
new_person = Person(name='new person')
session.add(new_person)
session.commit()
# Insertar una direccion
new_address = Address(post_code='00000', person=new_person)
session.add(new_address)
session.commit()
BEGIN (implicit)
INSERT INTO person (name) VALUES (?) ('new person',)
COMMIT
BEGIN (implicit)
SELECT person.id AS person_id, person.name AS person_name FROM person
WHERE person.id = ? (1,)
INSERT INTO address (street_name, street_number, post_code, person_id) VALUES (?, ?, ?, ?)
(None, None, '00000', 1)
COMMIT
session.query(Person).all()
SELECT person.id AS person_id, person.name AS person_name FROM person
session.query(Person).first()
SELECT person.id AS person_id, person.name AS person_name
FROM person LIMIT ? OFFSET ? (1, 0)
session.query(Address).filter(Address.person == person).all()
SELECT address.id AS address_id, address.street_name AS address_street_name,
address.street_number AS address_street_number, address.post_code AS address_post_code,
address.person_id AS address_person_id
FROM address WHERE ? = address.person_id (1,)
Muchas Gracias