python - 如何从非声明性对象自动生成 SQLAlchemy 架构？

Question

我从 API 获得了一个对象:

class Order(object):
    """ generated source for class Order """

    #  main order fields
    m_orderId = 0
    m_clientId = 0
    m_permId = 0
    m_action = ""
    m_totalQuantity = 0
    m_orderType = ""
    m_lmtPrice = float()
    m_auxPrice = float()

    #  extended order fields
    m_tif = ""  #  "Time in Force" - DAY, GTC, etc.
    m_activeStartTime = ""  #  GTC orders
    m_activeStopTime = ""  #  GTC orders
    m_ocaGroup = "" #  one cancels all group name
    m_ocaType = 0   #  1 = CANCEL_WITH_BLOCK, 2 = REDUCE_WITH_BLOCK, 3 = REDUCE_NON_BLOCK
    m_orderRef = ""
    m_transmit = bool() #  if false, order will be created but not transmited
    m_parentId = 0  #  Parent order Id, to associate Auto STP or TRAIL orders with the original order.
    m_blockOrder = bool()
    m_sweepToFill = bool()
    m_displaySize = 0
    m_triggerMethod = 0 #  0=Default, 1=Double_Bid_Ask, 2=Last, 3=Double_Last, 4=Bid_Ask, 7=Last_or_Bid_Ask, 8=Mid-point
    m_outsideRth = bool()
    m_hidden = bool()
    m_goodAfterTime = ""    #  FORMAT: 20060505 08:00:00 {time zone}
    m_goodTillDate = ""     #  FORMAT: 20060505 08:00:00 {time zone}
    m_overridePercentageConstraints = bool()
    m_rule80A = ""  #  Individual = 'I', Agency = 'A', AgentOtherMember = 'W', IndividualPTIA = 'J', AgencyPTIA = 'U', AgentOtherMemberPTIA = 'M', IndividualPT = 'K', AgencyPT = 'Y', AgentOtherMemberPT = 'N'
    m_allOrNone = bool()
    m_minQty = 0
    m_percentOffset = float()   #  REL orders only; specify the decimal, e.g. .04 not 4
    m_trailStopPrice = float()  #  for TRAILLIMIT orders only
    m_trailingPercent = float()  #  specify the percentage, e.g. 3, not .03

    #  Financial advisors only 
    m_faGroup = ""
    m_faProfile = ""
    m_faMethod = ""
    m_faPercentage = ""

    #  Institutional orders only
    m_openClose = ""            #  O=Open, C=Close
    m_origin = 0                #  0=Customer, 1=Firm
    m_shortSaleSlot = 0         #  1 if you hold the shares, 2 if they will be delivered from elsewhere.  Only for Action="SSHORT
    m_designatedLocation = ""   #  set when slot=2 only.
    m_exemptCode = 0

    #  SMART routing only
    m_discretionaryAmt = float()
    m_eTradeOnly = bool()
    m_firmQuoteOnly = bool()
    m_nbboPriceCap = float()
    m_optOutSmartRouting = bool()

    #  BOX or VOL ORDERS ONLY
    m_auctionStrategy = 0   #  1=AUCTION_MATCH, 2=AUCTION_IMPROVEMENT, 3=AUCTION_TRANSPARENT


    #  BOX ORDERS ONLY
    m_startingPrice = float()
    m_stockRefPrice = float()
    m_delta = float()

    #  pegged to stock or VOL orders
    m_stockRangeLower = float()
    m_stockRangeUpper = float()

    #  VOLATILITY ORDERS ONLY
    m_volatility = float()  #  enter percentage not decimal, e.g. 2 not .02
    m_volatilityType = 0        #  1=daily, 2=annual
    m_continuousUpdate = 0
    m_referencePriceType = 0    #  1=Bid/Ask midpoint, 2 = BidOrAsk
    m_deltaNeutralOrderType = ""
    m_deltaNeutralAuxPrice = float()
    m_deltaNeutralConId = 0
    m_deltaNeutralSettlingFirm = ""
    m_deltaNeutralClearingAccount = ""
    m_deltaNeutralClearingIntent = ""
    m_deltaNeutralOpenClose = ""
    m_deltaNeutralShortSale = bool()
    m_deltaNeutralShortSaleSlot = 0
    m_deltaNeutralDesignatedLocation = ""

    #  COMBO ORDERS ONLY
    m_basisPoints = float() #  EFP orders only, download only
    m_basisPointsType = 0   #  EFP orders only, download only

    #  SCALE ORDERS ONLY
    m_scaleInitLevelSize = 0
    m_scaleSubsLevelSize = 0
    m_scalePriceIncrement = float()
    m_scalePriceAdjustValue = float()
    m_scalePriceAdjustInterval = 0
    m_scaleProfitOffset = float()
    m_scaleAutoReset = bool()
    m_scaleInitPosition = 0
    m_scaleInitFillQty = 0
    m_scaleRandomPercent = bool()
    m_scaleTable = ""

    #  HEDGE ORDERS ONLY
    m_hedgeType = ""    #  'D' - delta, 'B' - beta, 'F' - FX, 'P' - pair
    m_hedgeParam = ""   #  beta value for beta hedge (in range 0-1), ratio for pair hedge

    #  Clearing info
    m_account = ""          #  IB account
    m_settlingFirm = ""
    m_clearingAccount = ""  #  True beneficiary of the order
    m_clearingIntent = ""   #  "" (Default), "IB", "Away", "PTA" (PostTrade)

    #  ALGO ORDERS ONLY
    m_algoStrategy = ""
    m_algoParams = None

    #  What-if
    m_whatIf = bool()

    #  Not Held
    m_notHeld = bool()

    #  Smart combo routing params
    m_smartComboRoutingParams = None

    #  order combo legs
    m_orderComboLegs = []

    def __init__(self):
        """ generated source for method __init__ """
        self.m_lmtPrice = Double.MAX_VALUE
        self.m_auxPrice = Double.MAX_VALUE
        self.m_activeStartTime = self.EMPTY_STR
        self.m_activeStopTime = self.EMPTY_STR
        self.m_outsideRth = False
        self.m_openClose = "O"
        self.m_origin = self.CUSTOMER
        self.m_transmit = True
        self.m_designatedLocation = self.EMPTY_STR
        self.m_exemptCode = -1
        self.m_minQty = Integer.MAX_VALUE
        self.m_percentOffset = Double.MAX_VALUE
        self.m_nbboPriceCap = Double.MAX_VALUE
        self.m_optOutSmartRouting = False
        self.m_startingPrice = Double.MAX_VALUE
        self.m_stockRefPrice = Double.MAX_VALUE
        self.m_delta = Double.MAX_VALUE
        self.m_stockRangeLower = Double.MAX_VALUE
        self.m_stockRangeUpper = Double.MAX_VALUE
        self.m_volatility = Double.MAX_VALUE
        self.m_volatilityType = Integer.MAX_VALUE
        self.m_deltaNeutralOrderType = self.EMPTY_STR
        self.m_deltaNeutralAuxPrice = Double.MAX_VALUE
        self.m_deltaNeutralConId = 0
        self.m_deltaNeutralSettlingFirm = self.EMPTY_STR
        self.m_deltaNeutralClearingAccount = self.EMPTY_STR
        self.m_deltaNeutralClearingIntent = self.EMPTY_STR
        self.m_deltaNeutralOpenClose = self.EMPTY_STR
        self.m_deltaNeutralShortSale = False
        self.m_deltaNeutralShortSaleSlot = 0
        self.m_deltaNeutralDesignatedLocation = self.EMPTY_STR
        self.m_referencePriceType = Integer.MAX_VALUE
        self.m_trailStopPrice = Double.MAX_VALUE
        self.m_trailingPercent = Double.MAX_VALUE
        self.m_basisPoints = Double.MAX_VALUE
        self.m_basisPointsType = Integer.MAX_VALUE
        self.m_scaleInitLevelSize = Integer.MAX_VALUE
        self.m_scaleSubsLevelSize = Integer.MAX_VALUE
        self.m_scalePriceIncrement = Double.MAX_VALUE
        self.m_scalePriceAdjustValue = Double.MAX_VALUE
        self.m_scalePriceAdjustInterval = Integer.MAX_VALUE
        self.m_scaleProfitOffset = Double.MAX_VALUE
        self.m_scaleAutoReset = False
        self.m_scaleInitPosition = Integer.MAX_VALUE
        self.m_scaleInitFillQty = Integer.MAX_VALUE
        self.m_scaleRandomPercent = False
        self.m_scaleTable = self.EMPTY_STR
        self.m_whatIf = False
        self.m_notHeld = False

它非常冗长，我需要为 SQLAlchemy 的 Order(Base) 对象上的每个变量生成一个字段。有没有办法以编程方式执行此操作，还是必须复制/粘贴此操作？

Answer 1

我可能会创建一个 git 存储库，并尝试更详细地解决这个问题，因为我不知道为什么似乎还没人做到这一点:P 除非他们做到了，而我只是找不到答案.. . ?

from sqlalchemy import Table, MetaData, Column, Integer, String, Float, Boolean

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.orm import mapper

from sqlalchemy import create_engine

metadata = MetaData()       

class Hero:
    def __init__(self):
        self.name = "name"
        self.xp = 500
        self.luck = 3.7
        self.is_alive = True
        self.quests = []
        self.attributes = {}
        self.town = None

hero = Hero() #Some generic object. I was using it for a game database :P

class BuildTable:
    def __init__(self, obj):
        self.obj = obj
        self.tablename = BuildTable.get_table_name(obj)
        self.column_names = BuildTable.get_column_names(obj)
        self.table = self.build_table()

    def build_table(self):
        return Table(self.tablename, metadata,
            Column('id', Integer, primary_key=True),
            *self.build_columns()
        )

    def build_columns(self):
        """Generate columns for table.

        Also builds extra attribute self.extra that will accomodate relationships and such
        using recursion.
        """
        data = vars(self.obj)
        relationships = []
        dicts = []
        nones = []
        for name in data.keys():
            column_type = type(data[name])
            if type(list()) == column_type:
                relationships.append(name)
            elif type(int()) == column_type:
                yield Column(name, Integer)
            elif type(str()) == column_type:
                yield Column(name, String)
            elif type(dict()) == column_type:
                dicts.append(name)
            elif type(float()) == column_type:
                yield Column(name, Float)
            elif type(None) == column_type:
                nones.append(name)
            elif type(bool()) == column_type:
                yield Column(name, Boolean)
            else:
                raise TypeError("Can't yet handle type {}".format(type(data[name])))

        #Methods that requir extra work.
        #They might be relationships, foreign keys or children or parents or something.
        #Or just basic lists, or dicts or booleans
        self.extra = {'relationships': relationships, 'dictionaries': dicts, 'Nones': nones}

    def get_column_names(obj):
        return (attr for attr in vars(obj))

    def get_table_name(obj):
        return obj.__class__.__name__

hero_table = BuildTable(hero).table
mapper(Hero, hero_table)

for t in metadata.sorted_tables:
    print("Table name: ", t.name)
    print("t is page_table: ", t is hero_table)

for column in hero_table.columns:
    print("Column Table name: ", column.type)

engine = create_engine('sqlite:///:memory:', echo=True)
metadata.bind = engine

metadata.create_all(checkfirst=True)

警告:目前无法处理字典、无类型或列表。这是因为它们都需要某种“关系”，而且你知道关系有时非常复杂:)。我认为为此需要某种递归。哦，如果你解决了这个问题，请制作一个答案的 git 存储库，因为我也在尝试解决这个问题。

我认为这忽略了函数。我相信它可能会忽略类变量......但尚未测试。它仅为初始化对象而设计。我希望这对你仍然有效。或者您可以对其进行足够的修改以使其工作。