SQLAlchemy -- Python的ORM驱动

> python 领域最著名的 ORM 框架,构建于第三方 DBAPI 之上

**安装**  
```bash
pip install SQLAlchemy mysql-python
```

**使用**  
*定义模型*
```python
from sqlalchemy import Column, Integer, Char, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base() #模型基类

class Test(Base):
    __tablename__ = 'test'

    id   = Column(Integer(unsigned=True), primary_key=True)
    name = Column(Char(32))
    desc = Column(String, nullable=True, index=True)
```

*ORM调用*
```python
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 数据库连接
engine = create_engine('mysql+mysqldb://username:password@host:port/dbname')
# 会话类
Session = sessionmaker(bind=engine)
# 会话实例
session = Session()

# 插入记录
test_item = Test(id=1, name='aaa')
session.add(test_item)
session.commit()

# 查询
query = session.query(Test).filter(Test.id==1, ...).offset(10).limit(5).order_by('id asc')
print query.statement #sql语句
query.all|one|first() #对于first查询结果空返回None
query.get(id) #主键查询
print test_item.name
session.commit()

# 更新
query.update({Test.desc: 'xxx'}) # 批量更新
session.merge(new_test_item)) #主键搜索记录并更新
session.commit()

# 删除
session.delete(test_item)
session.commit()

# 原生SQL
session.execute('select * from test where id = :id', {'id':1}).first()

# 关闭会话
session.close()
```
赞 (0) 评论 分享 ()