python轻量级orm

python下的orm使用SQLAlchemy比较多,用了一段时间感觉不顺手,主要问题是SQLAlchemy太重,所以自己写了一个orm,实现方式和netsharp类似,oql部分因为代码比较多,没有完全实现

下面是源代码

一,系统配置

configuration.py

# !/usr/bin/python
# -*- coding: UTF-8 -*-

host="192.168.4.1"
port=3306
db="wolf"
user="root"
pwd="xxx"
user_name="xxx"

二,orm.py

# !/usr/bin/python
# -*- coding: UTF-8 -*-

import sys
from datetime import *
from enum import Enum
import logging

import MySQLdb
import MySQLdb.cursors

import configuration

#########################################################################
        
class Field(object):
    
    property_name = None
    column_name = None
    group_name = None
    column_type_name = None
    header = None
    memoto = None

    is_primary_key = None
    is_auto = False
    is_name_equals = None
    is_required = None
    is_unique = None

    size = None
    precision = None

    def __init__(self,**kw):        
        for k,v in kw.iteritems():
            setattr(self,k,v) 

class ShortField(Field) :
    
    def __init__(self,**kw):
        self.column_type_name="smallint"
        super(ShortField,self).__init__(**kw)
       

class IntField(Field) :
    
    def __init__(self,**kw):
        self.column_type_name="int"
        super(IntField,self).__init__(**kw)
       

class LongField(Field) :
    
    def __init__(self,**kw):
        self.column_type_name="bigint"
        super(LongField,self).__init__(**kw)
              

class StringFiled(Field) :
    
    def __init__(self,**kw):
        self.column_type_name="nvarchar"
        self.size=50         
        super(StringFiled,self).__init__(**kw)


class BoolFiled(Field) :
    def __init__(self,**kw):
        self.column_type_name="bool"         
        super(BoolFiled,self).__init__(**kw)
       

class FloatFiled(Field) :
    def __init__(self,**kw):
        self.column_type_name="float"
        self.size=8
        self.precision=4         
        super(FloatFiled,self).__init__(**kw)


class DoubleFiled(Field) :
    def __init__(self,**kw):
        self.column_type_name="double"
        self.size=8
        self.precision=4         
        super(DoubleFiled,self).__init__(**kw)


class DecimalFiled(Field) :
    def __init__(self,**kw):
        self.column_type_name="decimal"
        self.size=14
        self.precision=8         
        super(DecimalFiled,self).__init__(**kw)

class DateTimeFiled(Field) :
    def __init__(self,**kw):
       self.column_type_name="datetime"         
       super(DateTimeFiled,self).__init__(**kw)

class BinaryFiled(Field) :
    def __init__(self,**kw):
       self.column_type_name="longblob"         
       super(BinaryFiled,self).__init__(**kw)
       
class Reference(object) :
    
    property_name = None
    header = None
    foreign_key = None
    primary_key = None

    reference_type = None

    def __init__(self,**kw):        
        for k,v in kw.iteritems():
            setattr(self,k,v)

class Subs(object) :
    
    property_name = None
    header = None
    foreign_key = None
    primary_key = None

    sub_type = None

    def __init__(self,**kw):        
        for k,v in kw.iteritems():
            setattr(self,k,v)   

#########################################################################

class EntityState(Enum):
    # 瞬时状态,不受数据库管理的状态
    # 或者是事务提交后不需要更新的实体
    Transient = 0
    # 事务提交后将被新增
    New = 1

    # 事务提交后将被修更新
    Persist = 2

    # 事务提交后将被删除
    Deleted = 3

class Persistable(object) :
    
    __entity_status__ = EntityState.New
    
    def __init__(self,**kw):        
        for k,v in kw.iteritems():
            setattr(self,k,v)

    def to_new(self) :
        self.__entity_status__ = EntityState.New

    def to_persist(self) :
        self.__entity_status__ = EntityState.Persist

    def to_delete(self) :
        self.__entity_status__ = EntityState.Deleted

    def to_transient(self) :
        self.__entity_status__ = EntityState.Transient            


class Entity(Persistable) :
    
    id = IntField(is_primary_key=True,is_auto=True)
    creator = StringFiled(size=100)
    create_time = DateTimeFiled()
    update_time = DateTimeFiled()
    updator = StringFiled(size=100)

class BizEntity(Entity) :
    
    code = StringFiled()
    name = StringFiled(size=100) 
    memoto = StringFiled(size=500)  

#########################################################################

class NColumn :
    
    property_name = None
    column_name = None
    group_name = None
    column_type_name = None
    header = None
    memoto = None
    column_type_name = None

    is_primary_key = None
    is_auto = False
    is_name_equals = None
    is_required = None
    is_unique = None

    size = None
    precision = None

    def __repr__(self):
        return self.property_name + "["+self.column_name+"]"


class NEntity :
    
    name = None
    table_name = None
    entity_id = None
    header= None
    is_view= None
    is_refcheck= None
    key_column = None
    auto_column = None
    order_by= None
    type = None

    columns = {}
    fields = {}
    subs = {}
    references = {}

    # def __repr__(self):
    #     return self.__name__ + "["+self.table_name+"]"

class NReference :
    header = None
    foreign_key = None
    primary_key = None

    foreign_key_column = None
    primary_key_column = None

    reference_type = None

class NSubs :
    header = None
    foreign_key = None
    primary_key = None

    foreign_key_column = None
    primary_key_column = None

    sub_type = None

class EntityManager :
    
    entityMap = {}

    @classmethod
    def get_meta(cls,type) :
        
        
        ne = cls.entityMap.get(type.__name__)
        if ne == None :
            ne = cls.parse_entity(type)
            cls.entityMap[type.__name__]= ne

        return ne

    @classmethod
    def parse_entity(cls,type) :
        
        ne = NEntity()
        ne.type = type

        ne.table_name= type.__table_name__
        ne.name = type.__name__
        ne.entity_id=type.__name__
        ne.header= type.__doc__
        ne.is_view= False
        ne.is_refcheck= False

        ne.key_column = None
        ne.auto_column = None
        ne.order_by= None

        ne.columns = {}
        ne.fields = {}
        ne.full_columns = {}
        ne.full_fields = {}
        ne.subs = {}
        ne.references = {}

        for k in dir(type) :
            
            v = getattr(type,k)

            if isinstance(v, Field) :

                c = cls.parse_field(k,v)

                ne.full_fields[k]=c
                ne.full_columns[c.column_name] = c

                if c.is_primary_key :
                    ne.key_column = c
                else :
                    ne.fields[k]=c
                    ne.columns[c.column_name] = c

                if c.is_auto :
                    ne.auto_column = c

            if isinstance(v, Reference) :
                r = cls.parse_reference(k,v)
                ne.references[k]=r

            if isinstance(v, Subs) :
                s = cls.parse_subs(k,v)
                ne.subs[k]=s  
        
        for r in ne.references.values() :
            r.foreign_key_column = ne.full_fields[r.foreign_key]

        for r in ne.subs.values() :
            r.primary_key_column = ne.key_column

        return ne

    @classmethod
    def parse_field(cls,name,field) :
        
        c = NColumn()
        
        c.property_name = name
        c.column_name = name

        if field.column_name != None :
            c.column_name = name

        c.is_primary_key = False
        c.is_auto= False
        c.is_name_equals = True
        c.is_required = False
        c.group_name = ""
        c.column_type_name = field.column_type_name
        c.size= field.size
        c.precision = field.precision
        c.group_name = field.group_name
        c.is_primary_key = field.is_primary_key
        c.is_auto = field.is_auto

        return c

    @classmethod
    def parse_reference(cls,name,field) :
        r = NReference()

        r.property_name = name
        r.foreign_key = field.foreign_key
        r.header = field.header
        r.primary_key = field.primary_key

        if r.primary_key == None :
            r.primary_key = "id"

        r.reference_type = field.reference_type

        r.foreign_key_column = None

        rne = EntityManager.get_meta( field.reference_type )
        r.primary_key_column = rne.full_fields[r.primary_key]

        return r

    @classmethod
    def parse_subs(cls,name,field) :
        
        s = NSubs()

        s.property_name = name
        s.foreign_key = field.foreign_key
        s.header = field.header
        s.primary_key = field.primary_key

        if s.primary_key == None :
            s.primary_key = "id"

        s.primary_key_column = None

        s.sub_type = field.sub_type  

        rne = EntityManager.get_meta( field.sub_type )
        s.foreign_key_column = rne.full_fields[field.foreign_key]          

        return s

class ORMException(Exception):
    def __init__(self,value):
            self.value=value

#########################################################################

class DataAccess :
    
    conn = None
    cursor = None
    isClosed = True

    def open(self,host=configuration.host,port=configuration.port,db=configuration.db,user=configuration.user,pwd=configuration.pwd) :
        
        self.conn = MySQLdb.connect(host=host,port=port,db=db,user=user,passwd=pwd,charset="utf8")
        self.cursor = self.conn.cursor()
        self.isClosed = False

    def execute(self,cmd,pars = None) :
        if self.isClosed :
            raise Exception("db is not opened!")

        logging.info(cmd)

        ret = self.cursor.execute(cmd,pars)
        return ret

    def get_last_row_id(self) :
        return int(self.cursor.lastrowid)

    def fetchone(self,cmd,pars = None) :
        if self.isClosed :
            raise Exception("db is not opened!")

        logging.info(cmd)
        self.cursor.execute(cmd,pars)
        row = self.cursor.fetchone()

        return row

    def fetchall(self,cmd,pars = None) :
        if self.isClosed :
            raise Exception("db is not opened!")
            
        logging.info(cmd)
        self.cursor.execute(cmd,pars)
        rows = self.cursor.fetchall()

        return rows

    def executeScalar(self,cmd,pars = None) :
        if self.isClosed :
            raise Exception("db is not opened!")

        self.cursor.execute(cmd,pars)
        row = self.cursor.fetchone()

        if row == None :
            return None
        if len(row)==0 :
            return None
        return row[0]


    def commit(self):
        self.conn.commit()
    
    def rolback(self):
        self.conn.rolback()

    def close(self) :
        if self.isClosed :
            pass;
        
        self.conn.close()
        self.isClosed=True
        self.conn = None
        self.cursor = None

#########################################################################

class SqlGenerator(object):
    
    @classmethod
    def generate_insert(cls,ne) :
        
        columns = ne.full_columns
        if ne.key_column.is_auto :
            columns = ne.columns
        
        sql = 'insert into %s (%s) values (%s);' % ( ne.table_name, ', '.join(columns.keys()), ', '.join(['%s'] * len(columns)))

        return sql

    @classmethod
    def generate_update(cls,ne) :

        sets = []

        for c in ne.columns.keys() :
            sets.append("%s = %s" % (c,'%s'))

        sql = 'update %s set %s where %s = %s;' % ( ne.table_name, ",".join(sets),ne.key_column.column_name,"%s")
        return sql

    @classmethod
    def generate_delete(cls,ne) :
        
        sql = "delete from %s where %s = %s" % (ne.table_name,ne.key_column.column_name,"%s")

        return sql

    @classmethod
    def generate_byid(cls,ne) :
        
        sql = "select %s from %s where %s = %s" % (",".join(ne.full_columns.keys()),ne.table_name,ne.key_column.column_name,'%s')

        return sql

class Db(object) :
    
    dao = DataAccess()

    def open(self) :
        self.dao.open()

    def create_db(self,db_name) :
        
        sql = "CREATE SCHEMA %s DEFAULT CHARACTER SET ucs2 ;" % db_name
        self.dao.execute(sql)

    def drop_db(self,db_name) :
        sql ="DROP DATABASE %s;" % db_name
        self.dao.execute(sql)
    
    def create_table(self,cls) :
        
        ne= EntityManager.get_meta(cls)
        
        columns = []
        for column in ne.full_columns.values() :
            c = self.generate_column(column)
            columns.append(c)
            
        sql ="create table %s( %s )" % (ne.table_name,",".join(columns))

        self.dao.execute(sql,None)

    def generate_column(self,column) :
        sql = "%s %s" % (column.column_name , column.column_type_name)

        if column.size != None :
            if column.precision != None :
                sql += "(%d,%d)" % (column.size ,column.precision)
            else:
                sql += "(%d)" % column.size 

        if column.is_auto :
            sql += " AUTO_INCREMENT"

        if column.is_primary_key :
            sql +=" PRIMARY KEY"

        return sql

    def drop_table(self,cls) :
        ne= EntityManager.get_meta(cls)
        sql ="drop table if exists %s" % ne.table_name

        self.dao.execute(sql,None)

    def commit(self) :
        self.dao.commit()

    def close(self) :
        self.dao.close()  

#########################################################################

class SetQuery(object) :

    dao = None

    def __init__(self,dao) :
        self.dao = dao

    def query(self,ne,entity) :
        
        id = getattr(entity,ne.key_column.property_name)
        if id == None :
            raise ORMException("%s.id不能为空" % ne.table_name)
        
        #当前实体
        pars = [ id ] 
        sql = SqlGenerator.generate_byid(ne)
        row = self.dao.fetchone(sql,pars)
        if row == None :
            entity = None
            return None
        entity = self.read_row(ne,row,entity)

        self.query_iter(entity,ne)

        return entity
    
    def query_iter(self,entity,ne) :
        
        #查询引用实体
        for rm in ne.references.values() :
            filter = "%s = %s" % (rm.primary_key_column.column_name,"%s")
            pars = [getattr(entity,rm.foreign_key)]
            re = EntityManager.get_meta(rm.reference_type)

            sql = SqlGenerator.generate_byid(re)
            row = self.dao.fetchone(sql,pars) 
            ref = self.read_row(re,row)
            setattr(entity,rm.property_name,ref)        
        
        #查询子实体
        for sm in ne.subs.values() :
            filter = "%s = %s" % (sm.foreign_key_column.column_name,"%s")
            pars = [ getattr(entity,ne.key_column.property_name) ] 
            se = EntityManager.get_meta(sm.sub_type)
            subs = self.do_query(se,filter,pars)

            setattr(entity,sm.property_name,subs)

            for sub in subs :
                self.query_iter(sub,se)

    def do_query(self,ne,filter,pars) :
        
        sql = "select %s from %s where %s" % (",".join(ne.full_columns.keys()),ne.table_name,filter)
        rows = self.dao.fetchall(sql,pars)

        entities = []
        for row in rows :
            entity = self.read_row(ne,row)
            entities.append(entity)

        return entities

    def read_row(self,ne,row,entity = None) :
        
        if entity == None :
            entity = ne.type()

        index = 0;
        for c in ne.full_columns.values() :
            cell_value = row[index]
            setattr(entity,c.property_name,cell_value)
            index +=1

        return entity  
#########################################################################
          
class Persister(object) :
    
    isClosed = True
    dao = DataAccess()

    def query_first(self,type,filters=None,pars=None):
        
        lis = self.query_list(type,filters,pars)
        if len(lis) == 0 :
            return None
        
        entity = lis[0]
        entity = self.byid(entity)

        return entity

    #实体列表查询
    #只支持基于主实体的一级查询
    def query_list(self,type,filters=None,pars=None) :
        
        ne = EntityManager.get_meta( type )

        columns = []
        for key in ne.full_columns.keys() :
            columns.append(ne.name+"."+key)

        sqls =["select %s from %s as %s" % (",".join(columns),ne.table_name,ne.name)]
        for r in ne.references.values() :
            re = EntityManager.get_meta(r.reference_type)
            sqls.append( "left join %s as %s on %s.%s = %s.%s" % (re.table_name, r.property_name,ne.name,r.foreign_key_column.column_name,r.property_name,re.key_column.column_name))

        if filters != None and filters.strip() != "" :
            sqls.append("where " + filters)

        sql = "
".join(sqls)

        setter = SetQuery(self.dao)

        rows = self.dao.fetchall(sql,pars)
        entities = []

        for row in rows :
            entity = setter.read_row(ne,row)
            entities.append(entity)

        return entities

    def byid(self,entity) :
        
        ne = EntityManager.get_meta(entity.__class__)
        query = SetQuery(self.dao)
        entity = query.query(ne,entity)
        
        return entity        

    def save(self,entity) :
        
        if entity.__entity_status__ == None :
            raise ORMException("调用save方法必须设置实体__entity_state__")
        
        ne = EntityManager.get_meta(entity.__class__)

        for r in ne.references.values() :
            ref = getattr(entity,r.property_name)
            fk = getattr(entity,r.foreign_key)
            if (fk == None or isinstance(fk,Field)) and ref != None :
                pk = getattr(ref,r.primary_key)
                setattr(entity,r.foreign_key,pk)

        if entity.__entity_status__ == EntityState.New :
            self.add(entity)
            id = getattr(entity,ne.key_column.property_name)

            for r in ne.subs.values() :
                subs = getattr(entity,r.property_name)
                if isinstance(subs,Subs) :
                    break
                if subs is None :
                    continue
                for sub in subs :
                    sub.to_new()
                    setattr(sub,r.foreign_key_column.property_name,id)
                    self.save(sub)
            entity.to_transient()     

        if entity.__entity_status__ == EntityState.Persist :
            self.update(entity)
            id = getattr(entity,ne.key_column.property_name)

            for r in ne.subs.values() :
                subs = getattr(entity,r.property_name)
                if isinstance(subs,Subs) :
                    break
                if subs is None :
                    continue                
                for sub in subs :
                    setattr(sub,r.foreign_key_column.property_name,id)
                    self.save(sub) 
            entity.to_transient()

        if entity.__entity_status__ == EntityState.Deleted :
            for r in ne.subs.values() :
                subs = getattr(entity,r.property_name)
                for sub in subs :
                    sub.to_delete()
                    self.save(sub)
            self.delete(entity)                    

        if entity.__entity_status__ == EntityState.Transient :
            id = getattr(entity,ne.key_column.property_name)
            for r in ne.subs.values() :
                subs = getattr(entity,r.property_name)
                if isinstance(subs,Subs) :
                    break
                if subs is None :
                    continue                
                for sub in subs :
                    setattr(sub,r.foreign_key_column.property_name,id)
                    self.save(sub)

    def add(self,entity) :
        ne = EntityManager.get_meta(entity.__class__)
        sql = SqlGenerator.generate_insert(ne)

        if isinstance(entity,Entity) :
            entity.create_time = datetime.now()
            entity.creator = configuration.user_name

        pars = []

        fields = ne.full_fields
        if ne.key_column.is_auto :
            fields = ne.fields    

        for c in fields.keys() :
            field_value = getattr(entity,c)
            
            if isinstance(field_value,Field) :
                field_value = None

            pars.append(field_value)

        self.dao.execute(sql,pars)

        if ne.auto_column != None :
            auto_id = self.dao.get_last_row_id()
            setattr(entity, ne.auto_column.column_name,auto_id)

    def update(self,entity) :
        
        ne = EntityManager.get_meta(entity.__class__)
        sql = SqlGenerator.generate_update(ne)

        if isinstance(entity,Entity) :
            entity.update_time = datetime.now()
            entity.updator = configuration.user_name

        pars = []
        for c in ne.fields.keys() :
            field_value = getattr(entity,c)
            # if field_value == None :
            #     field_value = "null"
            if isinstance(field_value,Field) :
                field_value = None

            pars.append(field_value)
        
        pars.append( getattr(entity,ne.key_column.property_name) )

        self.dao.execute(sql,pars)
        
    def delete(self,entity) :
        
        ne = EntityManager.get_meta(entity.__class__)
        sql = SqlGenerator.generate_delete(ne)

        pars = [ getattr(entity,ne.key_column.property_name) ] 

        self.dao.execute(sql,pars)

    def execute(self,cmd,pars = None) :
        return self.dao.execute(cmd,pars)

    def fetchone(self,cmd,pars = None) :
        return self.dao.fetchone(cmd,pars)

    def fetchall(self,cmd,pars = None) :
        return self.dao.fetchall(cmd,pars)

    def executeScalar(self,cmd,pars = None) :
        return self.dao.executeScalar(cmd,pars)      
    
    def commit(self) :
        self.dao.commit()
    
    def open(self,host=configuration.host,port=configuration.port,db=configuration.db,user=configuration.user,pwd=configuration.pwd) :
        
        self.dao = DataAccess()
        self.dao.open(host=host,port=port,db=db,user=user,pwd=pwd)
        self.isClosed = False

    def close(self):
        self.dao.close()
        self.dao.isClosed=True

#########################################################################     

class PTable(Persistable):
    
    __table_name__ = 'tables'

    table_catalog =  StringFiled(size=512)              #
    table_schema = StringFiled(size=64)                 #
    table_name = StringFiled(size=64, is_primary_key=True)
    table_type = StringFiled(size=64)                   #
    engine = StringFiled(size=64)                       #
    version = LongField()                      #
    row_format = StringFiled(size=10)                   #
    table_rows = LongField()                   #
    avg_row_length = LongField()               #
    data_length = LongField()                  #
    max_data_length = LongField()              #
    index_length = LongField()                 #
    data_free = LongField()                    #
    auto_increment = LongField()               #
    create_time = DateTimeFiled()                        #
    update_time = DateTimeFiled()                        #
    check_time = DateTimeFiled()                         #
    table_collation = StringFiled(size=32)              #
    checksum = LongField()                     #
    create_options = StringFiled(size=2048)             #
    table_comment = StringFiled(size=2048)              #

class PColumn(Persistable):
    
    __table_name__ = 'columns'

    table_schema = StringFiled(size=255)
    table_name = StringFiled(size=255)
    column_name = StringFiled(size=50, is_primary_key=True)
    data_type = StringFiled(size=255)
    character_maximum_length = StringFiled(size=255) #字符类型时，字段长度
    column_key = StringFiled(size=255) #PRI为主键，UNI为unique，MUL是什么意思？
    column_comment = StringFiled(size=255) #字段说明
    extra = StringFiled(size=255) #'auto_increment'
    numeric_precision = IntField()
    numeric_scale= IntField()

class GedColumn(Persistable):
    batch = IntField()
    dbtype = StringFiled(size=50)    

class dbtype(Persistable) :
    
    __table_name__ = 'dbtype'

    id = IntField()                                    #
    code = StringFiled(size=50, is_primary_key=True)                               #
    name = StringFiled(size=50)                               #
    host = StringFiled(size=50)                               #
    port = IntField()                                  #
    user = StringFiled(size=50)                               #
    passwd = StringFiled(size=50)                             #
    db = StringFiled(size=50)                                 #
    charset = StringFiled(size=50)                            #    


class EntityGenerator(object) :

    pm = Persister()
    dic = {}

    def open(self) :
        
        self.pm.open(configuration.host,configuration.port,"information_schema",configuration.user,configuration.pwd)

        self.dic["tinyint"] = "BoolFiled"
        self.dic["smallint"] = "ShortField"
        self.dic["mediumint"] = "IntField"
        self.dic["int"] = "IntField"
        self.dic["integer"] = "IntField"
        self.dic["bigint"] = "LongField"
        self.dic["float"] = "FloatFiled"
        self.dic["double"] = "DoubleFiled"
        self.dic["decimal"] = "DecimalFiled"
        self.dic["date"] = "DateTimeFiled"
        self.dic["time"] = "DateTimeFiled"
        self.dic["year"] = "IntField"
        self.dic["datetime"] = "DateTimeFiled"
        self.dic["timestamp"] = "DateTimeFiled"
        self.dic["char"] = "StringFiled"
        self.dic["varchar"] = "StringFiled"
        self.dic["tinyblob"] = "StringFiled" 
        self.dic["tinytext"] = "StringFiled"
        self.dic["blob"] = "StringFiled"
        self.dic["text"] = "StringFiled"
        self.dic["mediumblob"] = "BinaryFiled"
        self.dic["mediumtext"] = "StringFiled"
        self.dic["longblob"] = "BinaryFiled"
        self.dic["longtext"] = "StringFiled"       

    def close(self) :
        self.pm.close()

    # 根据数据库生成实体
    def generate_db(self,db_name) :
        ts = self.pm.query_list(PTable,"table_schema = %s",[db_name])

        for t in ts:
            self.generate_table(t.table_name,t.table_comment)

    def generate_table(self,table_name,memoto) : 
        
        cls_name = self.get_class_name(table_name)

        self.out_put( "")
        self.out_put(  "#%s" % memoto)
        self.out_put(  "class %s(Persistable) : " % cls_name)
        self.out_put(  "" )
        self.out_put(  "    __table_name__ = '%s'" % table_name )
        self.out_put(  "" )
        
        cs = self.pm.query_list(PColumn,"table_name = %s ",[table_name] )

        for c in cs:
            item = self.generate_column(c)
            self.out_put(  item )

    def out_put(self,txt) :
        print txt
    
    def get_class_name(self,table_name) :
        cls_name = table_name
        splits = cls_name.split("_")
        if len(splits) > 1 :
            items = []
            
            is_first = True
            for item in splits :
                if is_first :
                    is_first = False
                    continue
                    
                items.append(item[0].upper()+item[1:len(item)]) 

            cls_name = "".join(items)

        return cls_name

    def generate_column(self,c) : 
        

        properties = []

        if c.character_maximum_length != None :
            properties.append("size = %d" % c.character_maximum_length)
        
        if c.data_type == "decimal" :
            properties.append("size = %d" % c.numeric_precision)
            properties.append("precision = %d" % c.numeric_scale)

        if c.column_key == "PRI":
            properties.append("is_primary_key=True")

        if c.extra == 'auto_increment' :
            properties.append( "is_auto = True" )

        item = "    %s = %s( %s )" % (c.column_name.lower(),self.dic[c.data_type],",".join(properties))
        item = item.ljust(60)

        if c.column_comment != None :
            item = item +"# "+c.column_comment
        return item

    #把数据库表结构生成到ged的columns表中
    def ged_db(self) :
        
        db = Persister()
        db.open(configuration.host,configuration.port,"ged",configuration.user,configuration.pwd)
        ds = db.query_list(dbtype)

        columns = []

        for d in ds :
            self.ged_fields(d,columns)

        for column in columns :
            db.add(column)

        db.commit()
        db.close()

    def ged_fields(self,d,columns) :
        db = Persister()
        db.open(d.host,d.port,"information_schema",d.user,d.passwd)
        cs = db.query_list(PColumn,"table_schema = %s",[d.db] )

        for c in cs :
            gedc = GedColumn()
            gedc.dbtype=d.code
            gedc.column_comment=c.column_comment
            gedc.column_key=c.column_key
            gedc.column_name=c.column_name
            gedc.data_type=c.data_type
            gedc.extra=c.extra
            gedc.table_name=c.table_name
            gedc.table_schema=c.table_schema
            gedc.character_maximum_length=c.character_maximum_length

            columns.append(gedc)

        db.close()
        
#########################################################################  

三,test_orm.py

# !/usr/bin/python
# -*- coding: UTF-8 -*-

import sys
from dao.configuration import *
from dao.orm import *
import startup
import logging
import unittest

##############################################################
class Customer(BizEntity) :
    '客户'

    __table_name__ = "ns_customer"
    

class Product(BizEntity) :
    '产品'

    __table_name__ = "ns_product"
    

class OrderItem(Entity) :
    '订单明细'

    __table_name__ = "ns_order_item"

    quantity = DecimalFiled()
    price = DecimalFiled()
    amount = DecimalFiled()   

    product_id = IntField()
    product = Reference(foreign_key = "product_id",header="产品",reference_type = Product)  

    order_id = IntField()    

class SalesOrder(Entity) :
    '销售订单'
    
    __table_name__ = "ns_order"

    code = StringFiled()
    quantity = DecimalFiled()
    price = DecimalFiled()
    amount = DecimalFiled()

    customer_id = IntField()
    customer = Reference(foreign_key = "customer_id",header="客户",reference_type = Customer)

    items = Subs(foreign_key = "order_id",header="订单明细",sub_type = OrderItem)

class Wdbtype(Persistable) :
    
    __table_name__ = 'ns_dbtype'

    code = StringFiled( size = 50,is_primary_key=True )     #
    name = StringFiled( size = 50 )                         #
    host = StringFiled( size = 50 )                         #
    port = IntField(  )                                     #
    user = StringFiled( size = 50 )                         #
    passwd = StringFiled( size = 50 )                       #
    db = StringFiled( size = 50 )                           #
    charset = StringFiled( size = 50 )                      #    

##############################################################


__author__ = 'xufangbo'

class orm_test(unittest.TestCase) :
    
    item_size = 10
    order = None
    
    def setUp(self):

        self.set_db()
        self.test_create()

    def tearDown(self) :
        
        db = Db()
        db.open()

        clss = [SalesOrder,OrderItem,Customer,Product,Wdbtype]

        for cls in clss :
            db.drop_table(cls)

        db.commit()
        db.close() 

    def test_byid(self) :
        
        order = self.order
        
        pm = Persister()
        pm.open()        
        
        pm.byid(order)

        self.assertEqual(order.code,"DD001")
        self.assertEqual(len(order.items),self.item_size)
        self.assertIsNotNone(order.create_time)
        self.assertIsNotNone(order.creator)

        self.assertIsNotNone(order.customer)
        self.assertIsNotNone(order.items[0].product)

        pm.commit()
        pm.close()        
    
    def test_persist(self) :

        pm = Persister()
        pm.open()

        order = self.order

        order.code="dd002"
        order.to_persist()

        order.items[1].to_delete()
        order.items[2].price = 18
        order.items[2].to_persist()

        pm.save(order)

        self.assertIsNotNone(order.update_time)
        self.assertIsNotNone(order.updator)    

        pm.byid(order)

        self.assertEqual(len(order.items),self.item_size-1)
        self.assertEqual(order.code,"dd002")
        self.assertIsNotNone(order.update_time)
        self.assertIsNotNone(order.updator)

        pm.commit()
        pm.close()

    def test_delete(self) :
    
        pm = Persister()
        pm.open()

        order = self.order

        order.to_delete()
        pm.save(order)

        o = pm.byid(order)
        self.assertIsNone(o)

        pm.commit()
        pm.close()       

        #=============================
        dao = DataAccess()
        dao.open()
        
        sql = 'select count(0) from %s where order_id = %s' % (EntityManager.get_meta(OrderItem).table_name,'%s')
        pars = [order.id]
        count = int(dao.executeScalar(sql,pars))

        self.assertEqual(count,0)

        dao.close()            

    def test_create(self) :
        
        pm = Persister()
        pm.open()

        customer = Customer()
        customer.code = "C001"
        customer.name="北京千舟科技发展有限公司"
        pm.save(customer)

        product = Product()
        product.code = "P001"
        product.name="电商ERP标准版4.0"
        pm.save(product)

        order = SalesOrder();
        order.code="DD001"
        order.quantity=2
        order.price =2 
        order.amount=4.6
        order.customer = customer
        order.items = []

        for i in range(self.item_size) :
            item = OrderItem()
            item.quantity=i+1
            item.price =i+1
            item.amount= (i+1)*(i+1)
            item.product = product
            order.items.append(item)            

        pm.save(order)

        self.order = order

        self.assertIsNotNone(order.id)
        self.assertIsNotNone(order.create_time)
        self.assertIsNotNone(order.creator)

        pm.commit()
        pm.close()

    def test_query(self) :
        
        pm = Persister()
        pm.open()

        orders = pm.query_list(SalesOrder)
        count1 = len(orders)

        customer = Customer()
        customer.code = "C003"
        customer.name="北京千舟科技发展有限公司"
        pm.save(customer)

        product = Product()
        product.code = "P003"
        product.name="电商ERP标准版4.0"
        pm.save(product)

        for i in range(self.item_size) :
            order = SalesOrder();
            order.code="DD001"
            order.quantity=2
            order.price =2 
            order.amount=4.6
            order.customer = customer
            order.items = []

            pm.save(order)

        orders = pm.query_list(SalesOrder)
        count2 = len(orders)

        self.assertEquals( count1 +self.item_size,count2 ) 

        orders = pm.query_list(SalesOrder,"customer.code = %s",["C003"])
        count3 = len(orders)
        self.assertEqual(count3,self.item_size)

        pm.commit()
        pm.close()
    
    def test_customer_primary_key(self) :
        pm = Persister()
        pm.open()

        dt = Wdbtype(code='zl44',name='专利',host='mysql',port=3310,db='patent',user='root',passwd='mysql123',charset='utf8')

        pm.add(dt)
        pm.delete(dt)

        pm.commit()
        pm.close()        

    def set_db(self) :
        
        db = Db()
        db.open()

        clss = [SalesOrder,OrderItem,Customer,Product,Wdbtype]

        for cls in clss :
            db.drop_table(cls)
            db.create_table(cls)

        db.commit()
        db.close() 

class EntityGeneratorTest(unittest.TestCase) :

    def test_generator(self) :
        
        generator = EntityGenerator()
        generator.open()
        generator.generate_db("wolf")
        generator.close()        


if __name__ == '__main__':
    unittest.main()