python -- ORM

ORM – 对象关系映射，将纯SQL语句进行了抽象化处理，即数据库映射，python知名的ORM库SQLAlchemy和SQLObject

安装sqlalchemy

pip install sqlalchemy

关于sqlalchemy的使用，下面举了一个简单的例子：

# coding=utf-8

"""
@version: ??
@author: AA-ldc
@file: testDB.py
@time: 2017/3/20 9:24
@function：ORM--对象关系映射
"""

from sqlalchemy import exc, create_engine, orm
from contextlib import contextmanager
import ConfigParser
from data.User import Student


class SQLAlchemyManage(object):
    def __init__(self, db_name):
        try:
            config = ConfigParser.ConfigParser()
            config.readfp(open('config/config.ini'), "rb")
            dsn = 'mssql+pymssql://%s:%s@%s/%s' % (config.get('mssql', 'user'), config.get('mssql', 'pwd'),
                                                   config.get('mssql', 'host'), db_name)
            eng = create_engine(dsn, echo=False)
        except ImportError:
            raise RuntimeError()
        try:
            eng.connect()
        except exc.OperationalError:
            raise RuntimeError()
        print ('Connect to database <%s> success.' % db_name)
        self.eng = eng

    def GetSession(self):
        # 定义会话类型
        SessionType = orm.scoped_session(orm.sessionmaker(bind=self.eng, expire_on_commit=False))
        return SessionType

    # 定义上下文函数，使能够自动进行事务处理，
    # 定义上下文件函数的方法就是加上contextmanager装饰器
    # 执行逻辑：在函数开始时建立数据库会话，此时会自动建立一个数据库事务；当发生异常时回滚（rollback）事务，当
    # 退出时关闭(close)连接
    @contextmanager
    def session_scope(self):
        session = self.GetSession()

        try:
            yield session
            session.commit()
        except:
            session.rollback()
            raise RuntimeError()
        finally:
            session.close()

    # 查询
    def Query(self):
        with self.session_scope() as session:
            users = session.query(Student).all()
            return users


def main():
    orm = SQLAlchemyManage("test")
    users = orm.Query()
    for user in users:
        print user.FIRST_NAME, user.AGE


if __name__ == '__main__':
    main()

另外创建一个数据类 Student

# coding=utf-8

from sqlalchemy import Column, String, Float, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Student(Base):
    __tablename__ = 'Student'
    FIRST_NAME = Column(String(20), primary_key=True)
    LAST_NAME = Column(String(20))
    AGE = Column(Integer())
    SEX = Column(String(1))
    INCOME = Column(Float(precision=0.3))