|
Покритикуйте кот(оберька для MySQL)!
#37786639
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Stuttgart, Baden-Württemberg.
Сообщения: 3 651
|
|
Няша рррНа пидоне бы кот выглядел компактнее, плохо что нет однодолларовых хостингов с пидоном для экспериментов
1.
user = db.createSelect('users AS u', 'u.name, u.realname, u.city, r.name AS role').join('roles AS r', 'u.role_id = r.id').where('u.id = 123').fetch();
Нет Но красивее уж точно.
python 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship, backref, joinedload
engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
class User(Base):
__tablename__ = 't_users'
id = Column(Integer, primary_key=True)
name = Column(String)
realname = Column(String)
city = Column(String)
role_id = Column(Integer, ForeignKey('t_roles.id'))
def __init__(self, name, realname, city):
self.name, self.realname, self.city = name, realname, city
def __repr__(self):
return "<User('%s', '%s', '%s')>" % (self.name, self.realname, self.city)
class Role(Base):
__tablename__ = 't_roles'
id = Column(Integer, primary_key=True)
name = Column(String)
users = relationship("User", order_by="User.id", backref="role")
def __init__(self, name):
self.name = name
def __repr__(self):
return "<Role('%s')>" % (self.name,)
Base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
vasia = User('vasia', 'Вася Пупкин', 'ДС')
vasiasrole = Role('sysdba')
vasia.role = vasiasrole
session.add_all([vasia, vasiasrole])
print('Можно так')
print(session.query(User.name, User.realname, User.city, Role.name).\
filter(Role.id == User.role_id).filter(User.id == 1).one())
print('А можно вот так')
user = session.query(User).\
options(joinedload(User.role)).filter(User.id == 1).one()
print(user.name, user.realname, user.city, user.role.name)
лог
2012-05-09 11:06:45,273 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("t_roles")
2012-05-09 11:06:45,273 INFO sqlalchemy.engine.base.Engine ()
2012-05-09 11:06:45,273 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("t_users")
2012-05-09 11:06:45,273 INFO sqlalchemy.engine.base.Engine ()
2012-05-09 11:06:45,273 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t_roles (
id INTEGER NOT NULL,
name VARCHAR,
PRIMARY KEY (id)
)
2012-05-09 11:06:45,275 INFO sqlalchemy.engine.base.Engine ()
2012-05-09 11:06:45,275 INFO sqlalchemy.engine.base.Engine COMMIT
2012-05-09 11:06:45,276 INFO sqlalchemy.engine.base.Engine
CREATE TABLE t_users (
id INTEGER NOT NULL,
name VARCHAR,
realname VARCHAR,
city VARCHAR,
role_id INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(role_id) REFERENCES t_roles (id)
)
2012-05-09 11:06:45,276 INFO sqlalchemy.engine.base.Engine ()
2012-05-09 11:06:45,278 INFO sqlalchemy.engine.base.Engine COMMIT
Можно так
2012-05-09 11:06:45,283 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-05-09 11:06:45,285 INFO sqlalchemy.engine.base.Engine INSERT INTO t_roles (name) VALUES (?)
2012-05-09 11:06:45,285 INFO sqlalchemy.engine.base.Engine ('sysdba',)
2012-05-09 11:06:45,286 INFO sqlalchemy.engine.base.Engine INSERT INTO t_users (name, realname, city, role_id) VALUES (?, ?, ?, ?)
2012-05-09 11:06:45,286 INFO sqlalchemy.engine.base.Engine ('vasia', 'Вася Пупкин', 'ДС', 1)
2012-05-09 11:06:45,288 INFO sqlalchemy.engine.base.Engine SELECT t_users.name AS t_users_name, t_users.realname AS t_users_realname, t_users.city AS t_users_city, t_roles.name AS t_roles_name
FROM t_users, t_roles
WHERE t_roles.id = t_users.role_id AND t_users.id = ?
2012-05-09 11:06:45,288 INFO sqlalchemy.engine.base.Engine (1,)
('vasia', 'Вася Пупкин', 'ДС', 'sysdba')
А можно вот так
2012-05-09 11:06:45,290 INFO sqlalchemy.engine.base.Engine SELECT t_users.id AS t_users_id, t_users.name AS t_users_name, t_users.realname AS t_users_realname, t_users.city AS t_users_city, t_users.role_id AS t_users_role_id, t_roles_1.id AS t_roles_1_id, t_roles_1.name AS t_roles_1_name
FROM t_users LEFT OUTER JOIN t_roles AS t_roles_1 ON t_roles_1.id = t_users.role_id
WHERE t_users.id = ?
2012-05-09 11:06:45,290 INFO sqlalchemy.engine.base.Engine (1,)
vasia Вася Пупкин ДС sysdba
Process finished with exit code 0
|
|
|
