python的ORM技巧记录
# -*- coding:utf-8 -*- from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Index from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship, sessionmaker from django.db import models Base = declarative_base() class Hobby(Base): __tablename__ = 'hobby' id = Column(Integer, primary_key = True) caption = Column(String(50), default = '篮球') class Person(Base): __tablename__ = 'Person' nid = Column(Integer, primary_key = True) name = Column(String(32), index = True, nullable = True) hobby_id = Column(Integer, ForeignKey("hobby.id")) # hobby属性不管有没有数据库都不会生成这个字段,该字段只作为属性正向和反向关联Hobby hobby = relationship("Hobby", backref = 'pers') # 创建连接池,pool_size限制连接数10个,max_overflow设置最多多2个连接,也就是最多12个连接。 engine = create_engine("mysql+pymysql://username:password@127.0.0.1:3306/testdb?charset=utf8", max_overflow=2, pool_size=10) Session = sessionmaker(bind = engine) # 从连接池中获取连接 session = Session() # 查询一,直接查询表Person的数据 obj1 = session.query(models.Person).all() for row in obj1: print(row.name, row.hobby_id) # 查询二,Person和Hobby的关联查询,使用join方式 obj2 = session.query(models.Person.name, models.Hobby.caption).join(models.Hobby, isouter = True).all() for row in obj2: print(row[0].name, row[1].caption) # 查询三,Person和Hobby的关联查询,使用relationship方式,正向关联 obj3 = session.query(models.Person).all() for row in obj3: print(row.name, row.hobby.caption) # 查询四,查询喜欢篮球的所有人,使用relationship方式,反向向关联 obj4 = session.query(models.Hobby).filter(models.Hobby.id == 2).first() persons = obj4.pers for row in persons: print(row.name) # 关闭数据库连接 session.close() ====================================== #实现根据列表值拿到下标写到数据库 def enums(items): enums_index = dict(enumerate(items)) class Enums(object): def __init__(self): for v, i in enums_index.items(): setattr(self, i, v) def __getitem__(self, val): return enums_index.get(val) def __iter__(self): return iter(items) return Enums() RefreshStatus = enums([ "pending", "ready", "working", "commited", "successed", "merged", "aborted", "failed", "exceed", ]) print RefreshStatus.ready #打印出ready的index为1.