m %Gc*@sdZdklZlZdklZdkZdkZdkZdk Z dddddd d d d d ddddddddddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,d-g*Z d.Z d/Z dd0Zdd1Zddgd2Zd3Zdd4Zddd5Zdd6Zd7Zd8Zd9Zd:ZeZddd;Zd<Zd=Zd>Zd?Zd@ZdAZ dBZ!ddCZ"dDZ#dddEZ$dFZ%ddddGZ&ddHZ'dIZ(dJe)fdKYZ*e*Z+dLZ,dMZ-dNZ.dOZ/de)fdPYZ0d e1fdQYZ2d!e)fdRYZ3d"e)fdSYZ4d#e3fdTYZ5d$e)fdUYZ6dVe)fdWYZ7d-e6fdXYZ8d%e8e7fdYYZ9d&ei:fdZYZ;d'e8fd[YZ<d\e6e7fd]YZ=d^e6fd_YZ>d`e6fdaYZ?dbe9fdcYZ@dde6fdeYZAdfeAfdgYZBdheAe9fdiYZCdjeCe<fdkYZDdle9fdmYZEdne)fdoYZFdpe6fdqYZGdreGfdsYZHdteGe9fduYZId,e<fdvYZJd*e<fdwYZKdxe9fdyYZLeiMeiNeiOdzZPd{e9fd|YZQd(e<fd}YZRd~e)fdYZSd+eSe<fdYZTd)eSe<fdYZUde6fdYZVdeVfdYZWdeVfdYZXdeVfdYZYdS(s4defines the base components of SQL expression trees.(sutils exceptions(stypesNttextttabletcolumntfunctselecttupdatetinserttdeletetjointand_tor_tnot_tbetween_tcasetcasttuniont union_alltnulltdesctasct outerjointaliastsubquerytliteralt bindparamtexiststextracttAbstractDialecttClauseParameterst ClauseVisitortExecutortCompiledt ClauseElementt ColumnElementtColumnCollectiont FromClauset TableClausetSelecttAliastCompoundSelecttJoint SelectablecCstd|dS(s`return a descending ORDER BY clause element, e.g.: order_by = [desc(table1.mycol)] tDESCN(t_CompoundClausetNoneR(R((t2/home/holguin2/public_html/spyce/sqlalchemy/sql.pyRscCstd|dS(s_return an ascending ORDER BY clause element, e.g.: order_by = [asc(table1.mycol)] tASCN(R+R,R(R((R-RscKst|||dt|S(sLreturn an OUTER JOIN clause element. left - the left side of the join right - the right side of the join onclause - optional criterion for the ON clause, is derived from foreign key relationships otherwise To chain joins together, use the resulting Join object's "join()" or "outerjoin()" methods.tisouterN(R(tlefttrighttonclausetTruetkwargs(R0R1R2R4((R-Rs cKst||||S(sWreturn a JOIN clause element (regular inner join). left - the left side of the join right - the right side of the join onclause - optional criterion for the ON clause, is derived from foreign key relationships otherwise To chain joins together, use the resulting Join object's "join()" or "outerjoin()" methods.N(R(R0R1R2R4(R0R1R2R4((R-R(s cKst|d|d||S(sreturns a SELECT clause element. this can also be called via the table's select() method. 'columns' is a list of columns and/or selectable items to select columns from 'whereclause' is a text or ClauseElement expression which will form the WHERE clause 'from_obj' is an list of additional "FROM" objects, such as Join objects, which will extend or override the default "from" objects created from the column list and the whereclause. **kwargs - additional parameters for the Select object. t whereclausetfrom_objN(R%tcolumnsR5R6R4(R7R5R6R4((R-R4s cOst||i|S(N(R%targsR4R(RR8R4((R-RBscKst|||S(syreturns an INSERT clause element. This can also be called from a table directly via the table's insert() method. 'table' is the table to be inserted into. 'values' is a dictionary which specifies the column specifications of the INSERT, and is optional. If left as None, the column specifications are determined from the bind parameters used during the compile phase of the INSERT statement. If the bind parameters also are None during the compile phase, then the column specifications will be generated from the full list of table columns. If both 'values' and compile-time bind parameters are present, the compile-time bind parameters override the information specified within 'values' on a per-key basis. The keys within 'values' can be either Column objects or their string identifiers. Each key may reference one of: a literal data value (i.e. string, number, etc.), a Column object, or a SELECT statement. If a SELECT statement is specified which references this INSERT statement's table, the statement will be correlated against the INSERT statement. N(t_InsertRtvaluesR4(RR:R4((R-RFscKst||||S(sreturns an UPDATE clause element. This can also be called from a table directly via the table's update() method. 'table' is the table to be updated. 'whereclause' is a ClauseElement describing the WHERE condition of the UPDATE statement. 'values' is a dictionary which specifies the SET conditions of the UPDATE, and is optional. If left as None, the SET conditions are determined from the bind parameters used during the compile phase of the UPDATE statement. If the bind parameters also are None during the compile phase, then the SET conditions will be generated from the full list of table columns. If both 'values' and compile-time bind parameters are present, the compile-time bind parameters override the information specified within 'values' on a per-key basis. The keys within 'values' can be either Column objects or their string identifiers. Each key may reference one of: a literal data value (i.e. string, number, etc.), a Column object, or a SELECT statement. If a SELECT statement is specified which references this UPDATE statement's table, the statement will be correlated against the UPDATE statement. N(t_UpdateRR5R:R4(RR5R:R4((R-R]scKst|||S(s returns a DELETE clause element. This can also be called from a table directly via the table's delete() method. 'table' is the table to be updated. 'whereclause' is a ClauseElement describing the WHERE condition of the UPDATE statement. N(t_DeleteRR5R4(RR5R4((R-RtscGs td|S(sZjoins a list of clauses together by the AND operator. the & operator can be used as well.tANDN(t_compound_clausetclauses(R?((R-R ~scGs td|S(sYjoins a list of clauses together by the OR operator. the | operator can be used as well.tORN(R>R?(R?((R-R scCs |iS(s^returns a negation of the given clause, i.e. NOT(clause). the ~ operator can be used as well.N(tclauset_negate(RA((R-R scCs1t|tt||it||idS(s returns BETWEEN predicate clause (clausetest BETWEEN clauseleft AND clauseright). this is better called off a ColumnElement directly, i.e. column.between(value1, value2). tBETWEENN(t_BooleanExpressiontctestR t_check_literaltcleftttypetcright(RERGRI((R-tbetweensc Csg}|D]%\}}|tdd|d|q ~}|o|itdd|nt dd||dg}x|i D]}t |_qW|S(s SQL CASE statement -- whens are a sequence of pairs to be translated into "when / then" clauses; optional [value] for simple case statements, and [else_] for case defaults tWHENtTHENtELSEtCASEtENDN(t_[1]twhenstctrR+R,twhenlisttelse_tappendt_CalculatedClausetvaluetccR?tFalsetparens(RQRXRURRRYRPRSRT((R-R s9  cKst|||S(s returns CAST function CAST(clause AS totype) Use with a sqlalchemy.types.TypeEngine object, i.e cast(table.c.unit_price * table.c.qty, Numeric(10,4)) or cast(table.c.timestamp, DATE) N(t_CastRAttotypeR4(RAR]R4((R-RscCs%tt||d}ti|S(sreturn extract(field FROM expr)tFROMN(t _BinaryClauseRtfieldtexprRR(R`Ra((R-RscOs/t|d to specify bind parameters; they will be compiled to their engine-specific format. engine - an optional engine to be used for this text query. bindparams - a list of bindparam() instances which can be used to define the types and/or initial values for the bind parameters within the textual statement; the keynames of the bindparams must match those within the text of the statement. The types will be used for pre-processing on bind values. typemap - a dictionary mapping the names of columns represented in the SELECT clause of the textual statement to type objects, which will be used to perform post-processing on columns within the result set (for textual statements that produce result sets).tengineN(RfRRtR8R4(RRtR8R4((R-RscCstS(sAreturns a Null object, which compiles to NULL in a sql statement.N(t_Null(((R-Rst_FunctionGatewaycBstZdZdZRS(sireturns a callable based on an attribute name, which then returns a _Function object with that name.cCstt|S(N(tgetattrt_FunctionGeneratorRm(tselfRm((R-t __getattr__s(t__name__t __module__t__doc__Rz(((R-Rvs cGs t||S(N(R+tkeywordR?(R~R?((R-R> scOst|||S(N(R'R~RiR4(R~RiR4((R-RhscCst|t S(N(RrtelementR (R((R-RjscCs t|tS(N(RrtcolR!(R((R-t is_columnscBstZdZRS(sLrepresents the behavior of a particular database. Used by Compiled objects.(R{R|R}(((R-Rs cBsPtZdZedZdZdZdZdZdZ dZ RS(sHrepresents a dictionary/iterator of bind parameter key names/values. Tracks the original BindParam objects as well as the keys/position of each parameter, and can return parameters as a dictionary or a list. Will process parameter values according to the TypeEngine objects present in the BindParams. cCs<tt|i|||_h|_|pg|_dS(N(tsuperRRyt__init__tdialecttbindst positional(RyRR((R-R$s  cCs!|||i<||i|is&                  cBs#tZdZedZdZRS(sHrepresents a 'thing that can produce Compiled objects and execute them'.cKs tdS(sexecute a Compiled object.N(tNotImplementedError(Rytcompiledt parameterstechoR4((R-texecute_compiledUscKs tdS(s@return a Compiled object for the given statement and parameters.N(R(Ryt statementRR4((R-tcompilerXs(R{R|R}R,RR(((R-RSs  cBsGtZdZedZdZdZdZdZdZ RS(srepresents a compiled SQL expression. the __str__ method of the Compiled object should produce the actual text of the statement. Compiled objects are specific to the database library that created them, and also may or may not be specific to the columns referenced within a particular set of bind parameters. In no case should the Compiled object be dependent on the actual values of those bind parameters, even though it may reference those values as defaults.cCs(||_||_||_||_dS(sHconstruct a new Compiled object. statement - ClauseElement to be compiled parameters - optional dictionary indicating a set of bind parameters specified with this Compiled object. These parameters are the "default" values corresponding to the ClauseElement's _BindParamClauses when the Compiled is executed. In the case of an INSERT or UPDATE statement, these parameters will also result in the creation of new _BindParamClause objects for each key and will also affect the generated column list in an INSERT statement and the SET clauses of an UPDATE statement. The keys of the parameter dictionary can either be the string names of columns or _ColumnClause objects. engine - optional Engine to compile this statement againstN(RRyRRRt(RyRRRRt((R-Rds    cCs|ii||idS(N(RyRtaccept_visitort after_compile(Ry((R-tcompilexscCs tdS(s7returns the string text of the generated SQL statement.N(R(Ry((R-t__str__|scKs tdS(sreturns the bind params for this compiled object. Will start with the default parameters specified when this Compiled object was first constructed, and will override those values with those sent via **params, which are key/value pairs. Each key should match one of the _BindParamClause objects compiled into this object; either the "key" or "shortname" property of the _BindParamClause. N(R(RyRd((R-t get_paramsscOs<|i}|djotidn|i|||S(sexecute this compiled object.s0This Compiled object is not bound to any engine.N( RyRtteR,t exceptionstInvalidRequestErrorRt multiparamsRd(RyRRdR((R-texecutes   cOs|i||iS(sBexecute this compiled object and return the result's scalar value.N(RyRRRdtscalar(RyRRd((R-Rs( R{R|R}R,RRRRRR(((R-R\s     cBstZdZdZdZdZdZdZdZe ddd Z d Z d Z e e e e d Zd ZdZdZdZdZRS(sIbase class for elements of a programmatically constructed SQL expression.cCstt|dS(sreturns objects represented in this ClauseElement that should be added to the FROM list of a query, when this ClauseElement is placed in the column clause of a Select statement.N(RtreprRy(Ry((R-t_get_from_objectsscCs@x$|iD]}|i||q W|o|||stdocs_attempts to locate a Engine within this ClauseElement structure, or returns None if none found.cOsFt|o|d}n|}|id|id|i||S(s'compile and execute this ClauseElement.iRtRN(tlenRtcompile_paramsRdRyRRtR(RyRRdR((R-Rs  cOs|i||iS(sUcompile and execute this ClauseElement, returning the result's scalar representation.N(RyRRRdR(RyRRd((R-RscCst|tpt|to|d}n|djos|dj o|i||}q|dj o|i||}q|idj o|ii||}qn|djo+dk i }|i i|d|}n|i |S(scompile this SQL expression. Uses the given Compiler, or the given AbstractDialect or Engine to create a Compiler. If no compiler arguments are given, tries to use the underlying Engine this ClauseElement is bound to to create a Compiler, if any. Finally, if there is no bound Engine, uses an ANSIDialect to create a default Compiler. bindparams is a dictionary representing the default bind parameters to be used with the statement. if the bindparams is a list, it is assumed to be a list of dictionaries and the first dictionary in the list is used with which to compile against. The bind parameters can in some cases determine the output of the compilation, such as for UPDATE and INSERT statements the bind parameters that are present determine the SET and VALUES clause of those statements. iNR( RrRRttupleRR,RRyRttsqlalchemy.ansisqltansisqlt ANSIDialectR(RyRtRRRR((R-Rs       cCst|iS(N(tstrRyR(Ry((R-RscCs t||S(N(R RyR(RyR((R-t__and__scCs t||S(N(R RyR(RyR((R-t__or__scCs |iS(N(RyRB(Ry((R-t __invert__scCst|_ttd|dS(NtNOT(R3RyR[RDRfR,(Ry((R-RBs (R{R|R}RRRRRRtpropertyRtRRR,RRRRRRB(((R-R s             t _CompareMixincBstZdZdZdZdZdZdZdZdZ dZ d Z d Z d Z d Zd ZdZdZdZdZdZdZdZdZdZedZdZdZdZRS(s1defines comparison operations for ClauseElements.cCs|id|S(Nt<(Ryt_compareR(RyR((R-t__lt__scCs|id|S(Ns<=(RyRR(RyR((R-t__le__scCs|id|S(Nt=(RyRR(RyR((R-t__eq__ scCs|id|S(Ns!=(RyRR(RyR((R-t__ne__ scCs|id|S(Nt>(RyRR(RyR((R-t__gt__scCs|id|S(Ns>=(RyRR(RyR((R-t__ge__scCs|id|S(NtLIKE(RyRR(RyR((R-tlikesc Gst|djo|idSnt|djo*t|dd o|i|dSn~t|doJ|idtdt g}|D]}||i |q~ddSn#|idt dt |ddSdS(Niit _selectabletINR[tnegatesNOT IN(RRRyRR,thasattrRjRt ClauseListR3RPtot _bind_paramR(RyRRPR((R-tin_s(JcCs|id|dS(NRt%(RyRR(RyR((R-t startswith scCs|idd|S(NRR(RyRR(RyR((R-tendswith"scCst|||iS(N(RlRmRyRH(RyRm((R-Ro$scCstdd|S(NtDISTINCT(R+R,Ry(Ry((R-tdistinct&scCs+t|t|i||i|dS(NRC(RDRyR RFRGRI(RyRGRI((R-RJ(scs dS(Ncsi|S(N(RyRtoperatorR(R(RRy(R-R+s((RyR((RyRR-top*scCs|id|S(Nt+(Ryt_operateR(RyR((R-t__add__-scCs|id|S(Nt-(RyRR(RyR((R-t__sub__/scCs|id|S(Nt*(RyRR(RyR((R-t__mul__1scCs|id|S(Nt/(RyRR(RyR((R-t__div__3scCs|id|S(NR(RyRR(RyR((R-t__mod__5scCs|id|S(NR(RyRR(RyR((R-t __truediv__7scCstd|ddd|iS(NRRqRH(RkRnR,RyRH(RyRn((R-R9scCs&t|o|i|Sn|SdS(N(RjRRyR(RyR((R-RF;s cCs|djpt|tos|djo#t|itdddSq|djo#t|itdddSqt i dn|i |}t|i||d|i |d|S(NRtISRsIS NOTs!=s-Only '='/'!=' operators can be used with NULLRH(RnR,RrRuRRDRyt _compare_selfRRt ArgumentErrorRFt _compare_typeR(RyRRnR((R-R@s # #cCsEt|o|i|}nt|i||d|i|S(NRH(RjRnRyRt_BinaryExpressionRRR(RyRRn((R-RLs cCs|S(sqallows ColumnImpl to return its Column object for usage in ClauseElements, all others to just return selfN(Ry(Ry((R-RPscCs|iS(sallows subclasses to override the type used in constructing _BinaryClause objects. Default return value is the type of the given object.N(RnRH(RyRn((R-RTs(R{R|R}RRRRRRRRRRRoRRJRRRRRRRRRFR,RRRR(((R-Rs6                        cBs5tZdZdZdZedZdZRS(s(represents a column list-holding object.cCs|S(N(Ry(Ry((R-R\scCstt|dS(N(RRRy(RyR((R-R^scKst|g||S(N(RRyt whereclausesRd(RyRRd((R-R`scCstS(s`indicates if this Selectable requires parenthesis when grouped into a compound statementN(R3(Ry((R-t_group_parenthesizedbs(R{R|R}RRR,RR(((R-R)Ys    cBstZdZedddZedddZedddZdZeeZd Z d Z ee e dd Z d Z e d ZRS(srepresents a column element within the list of a Selectable's columns. A ColumnElement can either be directly associated with a TableClause, or a free-standing textual column with no table, or is a "proxy" column, indicating it is placed on a Selectable such as an Alias or Select statement and ultimately corresponds to a TableClause-attached column (or in the case of a CompositeSelect, a proxy ColumnElement may correspond to several TableClause-attached columns).cCst|dtS(Nt _primary_key(RwRyRZ(Ry((R-RosRsVprimary key flag. indicates if this Column represents part or whole of a primary key.cCst|dgS(Nt _foreign_keys(RwRy(Ry((R-Rpss~foreign key accessor. points to a ForeignKey object which represents a Foreign Key placed on this column's ultimate ancestor.cCs|gS(N(Ry(Ry((R-Rqss[Columns accessor which just returns self, to provide compatibility with Selectable objects.cCs-t|iot|idSndSdS(Ni(RRyR RR,(Ry((R-t _one_fkeyrscCsAy |iSWn/tj o#ti|g|_|iSnXdS(N(Ryt_ColumnElement__orig_setRtutiltSet(Ry((R-t _get_orig_setys  cCs1t|djo|i|n||_dS(Ni(RRetaddRyR (RyRe((R-t _set_orig_setssa Set containing TableClause-bound, non-proxied ColumnElements for which this ColumnElement is a proxy. In all cases except for a column proxied from a Union (i.e. CompoundSelect), this set will be just one element.cCs4x-|iD]}||ijotSq q WtSdS(sTreturns True if the given ColumnElement has a common ancestor to this ColumnElement.N(Rytorig_setRRt othercolumnR3RZ(RyRRR((R-tshares_lineages   cCsE|dj o0t||}|i|_||i|<|Sn|SdS(screates a new ColumnElement representing this ColumnElement as it appears in the select list of a descending selectable. The default implementation returns a _ColumnClause if a name is given, else just returns self.N(RmR,Rpt selectabletcoRyRR7(RyRRmR((R-t _make_proxys   (R{R|R}Rt primary_keyt foreign_keysR7R t foreign_keyRRRRR,R(((R-R!gs      cBs tZdZdZdZRS(san ordered dictionary that stores a list of ColumnElement instances. overrides the __eq__() method to produce SQL clauses between sets of correlated columns.cCs|||i}x5|D]-}|i|o|i||jqqWq Wt|S(N(tlRRRRytlocalRRVR (RyRRRRR((R-Rs(R{R|R}RR(((R-R"s  cBs tZdZedZdZdZdZedZdZ dZ edZ d Z d Z d Zeed Zd ZedZedZedZedZedddZeeZdZdZdZRS(sTrepresents an element that can be used within the FROM clause of a SELECT statement.cCs ||_dS(N(RmRy(RyRm((R-RscCsgS(N((Ry((R-RscCs |igS(N(Ryt oid_column(Ry((R-tdefault_order_byscCs|i|dS(N(RRRy(RyR((R-RscKsht|iot|id}nt|id}tti|i dg|d|g|S(Nit tbl_row_countR6( RRyRRRR7RRtcountRoR5Rd(RyR5RdR((R-RscOst||||S(N(R(RyR1R8R4(RyR1R8R4((R-RscOst||dt||S(NR/(R(RyR1R3R8R4(RyR1R8R4((R-RscCs t||S(N(R&RyRm(RyRm((R-RscCstS(s]True if the name of this FromClause may be prepended to a column in a generated SQL statementN(RZ(Ry((R-tnamed_with_columnscCsdS(s<subclasses override this to return an appropriate OID columnN(R,(Ry((R-t_locate_oid_columnscCs*t|dp|i|_n|iS(Nt _oid_column(RRyR!R"(Ry((R-t_get_oid_columnscCsx|iD]+}y|i|SWq tj oq Xq W|o,y|i|iSWqltj oqlXn|pdSn/t i dt |t |i |ifdS(sgiven a ColumnElement, return the ColumnElement object from this Selectable which corresponds to that original Column via a proxy relationship.sbGiven column '%s', attached to table '%s', failed to locate a corresponding column from table '%s'N(RRRRRytoriginal_columnstKeyErrortkeys_okRstraiseerrR,RRRRRm(RyRR'R&RR((R-tcorresponding_columns   cCsByt||SWn*tj o|it||SnXdS(N(RwRyRmRt_export_columns(RyRm((R-t_get_exported_attributes  cCs |idS(Nt_columns(ReR*(Re((R-RscCs |idS(NR+(ReR*(Re((R-RscCs |idS(NR(ReR*(Re((R-RscCs |idS(NR (ReR*(Re((R-RscCs |idS(Nt _orig_cols(ReR*(Re((R-RsRs[a dictionary mapping an original Table-bound column to a proxied column in this FromClause.cCst|dodSnt|_t|_ti|_h|_|i }x{|D]s}y|i }Wntj o q[nXxA|iD]6}|i|}x|iD]}||i|s(R{R|R}RRR(((R-R78s   RfcBsDtZdZdeeedZedZdZdZRS(sdrepresents literal a SQL text fragment. public constructor is the text() function. tcst_|_h_|_|dj o2x/|iD]}t i ||||Wnd}t idt ii||_|dj o%x"|D]}|i|i |io|i|d|iSn|i|d|iSdS(NRm(RyR]RRRRRm(RyR((R-R/s cCs|id|dS(NR5(Ryt_append_conditionR5(RyR5((R-RscCs|id|dS(NR(RyRR(RyR((R-RscCst|tjot|}n|i|i|i|it t ||dj o&t ||tt |||nt |||dS(N(RHt conditionRRfRRyRRRRZRwt attributeR,tsetattrR (RyRR((R-Rs&cCst|i|RXR%RRjRRR%(RyRRGRRRRXRsR((R-t_process_colparamsEs2        cCs |iiS(N(RyRRt(Ry((R-Ras(R{R|R}RR(((R-RCs  R9cBstZddZdZRS(NcCs(||_d|_|i||_dS(N(RRyR,RRR:R(RyRR:((R-Res  cCs5|idj o|ii|n|i|dS(N(RyRR,RRt visit_insert(RyR((R-Rjs(R{R|R,RR(((R-R9ds R;cBstZddZdZRS(NcCs(||_||_|i||_dS(N(RRyR5RR:R(RyRR5R:((R-Rqs  cCs5|idj o|ii|n|i|dS(N(RyR5R,RRt visit_update(RyR((R-Rvs(R{R|R,RR(((R-R;ps R<cBstZdZdZRS(NcKs||_||_dS(N(RRyR5(RyRR5Rd((R-R|s cCs5|idj o|ii|n|i|dS(N(RyR5R,RRt visit_delete(RyR((R-Rs(R{R|RR(((R-R<{s (ZR}t sqlalchemyR RttypesR2tstringR?Rdtsetst__all__RRR,RRRRRRRR R R RJR R RRRRRRRFRRoRRRRRtobjectRvRR>RhRjRRtdictRRRRR RR)R!tOrderedPropertiesR"R#RkR7RfRuRR+RWRIR\RxR_RDRR(R&RlR t ascii_letterstdigitsRjRpR$RrR'R%RR9R;R<(PR7RuRRR$RR RdR+R<R RR>R RRoRRRRDRfRR\RjRRRRR R!RR;R2RR?RRR9RJRR)RRjRR RFRRRvRR&RRpRR_RRRRxRWRR RRhRR#RkR R'R"RIRRRlR(RRRrR%R((R-t?s $                        " :nU1c*  %%!  P+.?7!