Бази от данни и ORM

„ Програмиране с Python“, ФМИ

SELECT *
FROM lecturers
WHERE name IN ("Стефан Кънев", "Николай Бачийски", "Точо Точев", "Димитър Димитров")

13.05.2009

Persistency

DBMS & RDBMS

Организация на данните

Таблични данни

RDBMS Table

Комуникация със СУБД

Операции

SQL

DDL & DML

DDL

CREATE DATABASE demo;

CREATE TABLE Users (...);

ALTER TABLE Users CHARSET=utf8;
ALTER TABLE Users ADD COLUMN email VARCHAR(200) NOT NULL DEFAULT '';
 
DROP TABLE Users;

DDL (2)

DML: вмъкване

INSERT INTO sometable (some, fields)
VALUES (1, 22), (73, 321), (NULL, 13), (32, 102)

DML: извличане

SELECT some, fields
FROM sometable
WHERE fields > 10 AND (some IS NULL OR some = 42)
ORDER BY fields DESC
LIMIT 20;

DML: обновление

UPDATE sometable SET fields = fields + 10;
UPDATE sometable SET some = -1 WHERE some IS NULL;
UPDATE sometable SET fields = 1000 + fields, some = 200 + some WHERE fields > 42;

DML: изтриване

DELETE FROM sometable WHERE fields <= 42;
DELETE FROM sometable WHERE some IS NULL OR fields = 17;

Обемни бази данни

Пример

Countries:
id name town_id
-- ---- ----------
1  BG   1
2  UK   5
3  RUS  6

Towns:
id name    country_id
-- ------- ----------
1  Sofia   1
2  Plovdiv 1
3  Varna   1
4  Burgas  1
5  London  2
6  Moscow  3

FK и взаимовръзки

(Де)нормализация

ORM

ORM тук и там

Dejavu

Примери

stefan = User(user = "skanev", name = "Стефан Кънев")
# box идва отнякъде
box.memorize(stefan)
print(stefan.id)

Още примери

active_admins = box.recall(User, 
   lambda user: user.is_admin and user.enabled)
for admin in active_admins: print(admin.email)

Пример

import dejavu

class Zoo(dejavu.Unit):
    Name = dejavu.UnitProperty()
    Size = dejavu.UnitProperty(int)
    
    def total_legs(self):
        return sum([x.Legs for x in self.Animal()])

class Animal(dejavu.Unit):
    Legs = dejavu.UnitProperty(int, default=4)

Animal.set_properties({"Name": unicode, "ZooID": int})
Animal.many_to_one('ZooID', Zoo, 'ID')

arena = dejavu.Arena() # Set up a global Arena object.
conf = {'Connect': r"PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\zookeeper.mdb;"}
arena.add_store("main", "access", conf)
arena.register_all(globals())

Още въпроси?