0001"""0002sqlobject.sqlbuilder0003--------------------00040005:author: Ian Bicking <ianb@colorstudy.com>00060007Builds SQL expressions from normal Python expressions.00080009Disclaimer0010----------00110012This program is free software; you can redistribute it and/or modify0013it under the terms of the GNU Lesser General Public License as0014published by the Free Software Foundation; either version 2.1 of the0015License, or (at your option any later version.00160017This program is distributed in the hope that it will be useful,0018but WITHOUT ANY WARRANTY; without even the implied warranty of0019MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the0020GNU General Public License for more details.00210022You should have received a copy of the GNU Lesser General Public0023License along with this program; if not, write to the Free Software0024Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301,0025USA.00260027Instructions0028------------00290030To begin a SQL expression, you must use some sort of SQL object -- a0031field, table, or SQL statement (``SELECT``, ``INSERT``, etc.) You can0032then use normal operators, with the exception of: `and`, `or`, `not`,0033and `in`. You can use the `AND`, `OR`, `NOT`, and `IN` functions0034instead, or you can also use `&`, `|`, and `~` for `and`, `or`, and0035`not` respectively (however -- the precidence for these operators0036doesn't work as you would want, so you must use many parenthesis).00370038To create a sql field, table, or constant/function, use the namespaces0039`table`, `const`, and `func`. For instance, ``table.address`` refers0040to the ``address`` table, and ``table.address.state`` refers to the0041``state`` field in the address table. ``const.NULL`` is the ``NULL``0042SQL constant, and ``func.NOW()`` is the ``NOW()`` function call0043(`const` and `func` are actually identicle, but the two names are0044provided for clarity). Once you create this object, expressions0045formed with it will produce SQL statements.00460047The ``sqlrepr(obj)`` function gets the SQL representation of these0048objects, as well as the proper SQL representation of basic Python0049types (None==NULL).00500051There are a number of DB-specific SQL features that this does not0052implement. There are a bunch of normal ANSI features also not present.00530054See the bottom of this module for some examples, and run it (i.e.0055``python sql.py``) to see the results of those examples.00560057"""00580059########################################0060# Constants0061########################################00620063importfnmatch0064importoperator0065importre0066importthreading0067importtypes0068importweakref00690070from.importclassregistry0071from.convertersimportregisterConverter,sqlrepr,quote_str,unquote_str0072from.compatimportPY2,string_type007300740075classVersionError(Exception):0076pass007700780079classNoDefault:0080pass008100820083classSQLObjectState(object):0084def__init__(self,soObject,connection=None):0085self.soObject=weakref.proxy(soObject)0086self.connection=connection008700880089safeSQLRE=re.compile(r'^[a-zA-Z_][a-zA-Z0-9_\.]*$')009000910092defsqlIdentifier(obj):0093# some db drivers return unicode column names0094returnisinstance(obj,string_type)andbool(safeSQLRE.search(obj.strip()))009500960097defexecute(expr,executor):0098ifhasattr(expr,'execute'):0099returnexpr.execute(executor)0100else:0101returnexpr010201030104def_str_or_sqlrepr(expr,db):0105ifisinstance(expr,string_type):0106returnexpr0107returnsqlrepr(expr,db)010801090110########################################0111# Expression generation0112########################################011301140115classSQLExpression:0116def__add__(self,other):0117returnSQLOp("+",self,other)01180119def__radd__(self,other):0120returnSQLOp("+",other,self)01210122def__sub__(self,other):0123returnSQLOp("-",self,other)01240125def__rsub__(self,other):0126returnSQLOp("-",other,self)01270128def__mul__(self,other):0129returnSQLOp("*",self,other)01300131def__rmul__(self,other):0132returnSQLOp("*",other,self)01330134def__div__(self,other):0135returnSQLOp("/",self,other)01360137def__rdiv__(self,other):0138returnSQLOp("/",other,self)01390140def__pos__(self):0141returnSQLPrefix("+",self)01420143def__neg__(self):0144returnSQLPrefix("-",self)01450146def__pow__(self,other):0147returnSQLConstant("POW")(self,other)01480149def__rpow__(self,other):0150returnSQLConstant("POW")(other,self)01510152def__abs__(self):0153returnSQLConstant("ABS")(self)01540155def__mod__(self,other):0156returnSQLModulo(self,other)01570158def__rmod__(self,other):0159returnSQLConstant("MOD")(other,self)01600161def__lt__(self,other):0162returnSQLOp("<",self,other)01630164def__le__(self,other):0165returnSQLOp("<=",self,other)01660167def__gt__(self,other):0168returnSQLOp(">",self,other)01690170def__ge__(self,other):0171returnSQLOp(">=",self,other)01720173def__eq__(self,other):0174ifotherisNone:0175returnISNULL(self)0176else:0177returnSQLOp("=",self,other)01780179def__ne__(self,other):0180ifotherisNone:0181returnISNOTNULL(self)0182else:0183returnSQLOp("<>",self,other)01840185def__and__(self,other):0186returnSQLOp("AND",self,other)01870188def__rand__(self,other):0189returnSQLOp("AND",other,self)01900191def__or__(self,other):0192returnSQLOp("OR",self,other)01930194def__ror__(self,other):0195returnSQLOp("OR",other,self)01960197def__invert__(self):0198returnSQLPrefix("NOT",self)01990200def__call__(self,*args):0201returnSQLCall(self,args)02020203def__repr__(self):0204try:0205returnself.__sqlrepr__(None)0206exceptAssertionError:0207return'<%s %s>'%(0208self.__class__.__name__,hex(id(self))[2:])02090210def__str__(self):0211returnrepr(self)02120213def__cmp__(self,other):0214raiseVersionError("Python 2.1+ required")02150216def__rcmp__(self,other):0217raiseVersionError("Python 2.1+ required")02180219defstartswith(self,s):0220returnSTARTSWITH(self,s)02210222defendswith(self,s):0223returnENDSWITH(self,s)02240225defcontains(self,s):0226returnCONTAINSSTRING(self,s)02270228defcomponents(self):0229return[]02300231deftablesUsed(self,db):0232returnself.tablesUsedSet(db)02330234deftablesUsedSet(self,db):0235tables=set()0236fortableinself.tablesUsedImmediate():0237ifhasattr(table,'__sqlrepr__'):0238table=sqlrepr(table,db)0239tables.add(table)0240forcomponentinself.components():0241tables.update(tablesUsedSet(component,db))0242returntables02430244deftablesUsedImmediate(self):0245return[]024602470248#######################################0249# Converter for SQLExpression instances0250#######################################025102520253defSQLExprConverter(value,db):0254returnvalue.__sqlrepr__()02550256registerConverter(SQLExpression,SQLExprConverter)025702580259deftablesUsedSet(obj,db):0260ifhasattr(obj,"tablesUsedSet"):0261returnobj.tablesUsedSet(db)0262else:0263return{}026402650266ifPY2:0267div=operator.div0268else:0269div=operator.truediv027002710272operatorMap={0273"+":operator.add,0274"/":div,0275"-":operator.sub,0276"*":operator.mul,0277"<":operator.lt,0278"<=":operator.le,0279"=":operator.eq,0280"!=":operator.ne,0281">=":operator.ge,0282">":operator.gt,0283"IN":operator.contains,0284"IS":operator.eq,0285}028602870288classSQLOp(SQLExpression):0289def__init__(self,op,expr1,expr2):0290self.op=op.upper()0291self.expr1=expr10292self.expr2=expr202930294def__sqlrepr__(self,db):0295s1=sqlrepr(self.expr1,db)0296s2=sqlrepr(self.expr2,db)0297ifs1[0]!='('ands1!='NULL':0298s1='('+s1+')'0299ifs2[0]!='('ands2!='NULL':0300s2='('+s2+')'0301return"(%s %s %s)"%(s1,self.op,s2)03020303defcomponents(self):0304return[self.expr1,self.expr2]03050306defexecute(self,executor):0307ifself.op=="AND":0308returnexecute(self.expr1,executor)andexecute(self.expr2,executor)0310elifself.op=="OR":0311returnexecute(self.expr1,executor)orexecute(self.expr2,executor)0313else:0314returnoperatorMap[self.op.upper()](execute(self.expr1,executor),0315execute(self.expr2,executor))03160317registerConverter(SQLOp,SQLExprConverter)031803190320classSQLModulo(SQLOp):0321def__init__(self,expr1,expr2):0322SQLOp.__init__(self,'%',expr1,expr2)03230324def__sqlrepr__(self,db):0325ifdb=='sqlite':0326returnSQLOp.__sqlrepr__(self,db)0327s1=sqlrepr(self.expr1,db)0328s2=sqlrepr(self.expr2,db)0329return"MOD(%s, %s)"%(s1,s2)03300331registerConverter(SQLModulo,SQLExprConverter)033203330334classSQLCall(SQLExpression):0335def__init__(self,expr,args):0336self.expr=expr0337self.args=args03380339def__sqlrepr__(self,db):0340return"%s%s"%(sqlrepr(self.expr,db),sqlrepr(self.args,db))03410342defcomponents(self):0343return[self.expr]+list(self.args)03440345defexecute(self,executor):0346raiseValueError("I don't yet know how to locally execute functions")03470348registerConverter(SQLCall,SQLExprConverter)034903500351classSQLPrefix(SQLExpression):0352def__init__(self,prefix,expr):0353self.prefix=prefix0354self.expr=expr03550356def__sqlrepr__(self,db):0357return"%s %s"%(self.prefix,sqlrepr(self.expr,db))03580359defcomponents(self):0360return[self.expr]03610362defexecute(self,executor):0363prefix=self.prefix0364expr=execute(self.expr,executor)0365ifprefix=="+":0366returnexpr0367elifprefix=="-":0368return-expr0369elifprefix.upper()=="NOT":0370returnnotexpr03710372registerConverter(SQLPrefix,SQLExprConverter)037303740375classSQLConstant(SQLExpression):0376def__init__(self,const):0377self.const=const03780379def__sqlrepr__(self,db):0380returnself.const03810382defexecute(self,executor):0383raiseValueError("I don't yet know how to execute SQL constants")03840385registerConverter(SQLConstant,SQLExprConverter)038603870388classSQLTrueClauseClass(SQLExpression):0389def__sqlrepr__(self,db):0390return"1 = 1"03910392defexecute(self,executor):0393return103940395SQLTrueClause=SQLTrueClauseClass()03960397registerConverter(SQLTrueClauseClass,SQLExprConverter)03980399########################################0400# Namespaces0401########################################040204030404classField(SQLExpression):0405def__init__(self,tableName,fieldName):0406self.tableName=tableName0407self.fieldName=fieldName04080409def__sqlrepr__(self,db):0410returnself.tableName+"."+self.fieldName04110412deftablesUsedImmediate(self):0413return[self.tableName]04140415defexecute(self,executor):0416returnexecutor.field(self.tableName,self.fieldName)041704180419classSQLObjectField(Field):0420def__init__(self,tableName,fieldName,original,soClass,column):0421Field.__init__(self,tableName,fieldName)0422self.original=original0423self.soClass=soClass0424self.column=column04250426def_from_python(self,value):0427column=self.column0428ifnotisinstance(value,SQLExpression)andcolumnandcolumn.from_python:0430value=column.from_python(value,SQLObjectState(self.soClass))0431returnvalue04320433def__eq__(self,other):0434ifotherisNone:0435returnISNULL(self)0436other=self._from_python(other)0437returnSQLOp('=',self,other)04380439def__ne__(self,other):0440ifotherisNone:0441returnISNOTNULL(self)0442other=self._from_python(other)0443returnSQLOp('<>',self,other)04440445defstartswith(self,s):0446s=self._from_python(s)0447returnSTARTSWITH(self,s)04480449defendswith(self,s):0450s=self._from_python(s)0451returnENDSWITH(self,s)04520453defcontains(self,s):0454s=self._from_python(s)0455returnCONTAINSSTRING(self,s)04560457registerConverter(SQLObjectField,SQLExprConverter)045804590460classTable(SQLExpression):0461FieldClass=Field04620463def__init__(self,tableName):0464self.tableName=tableName04650466def__getattr__(self,attr):0467ifattr.startswith('__'):0468raiseAttributeError0469returnself.FieldClass(self.tableName,attr)04700471def__sqlrepr__(self,db):0472return_str_or_sqlrepr(self.tableName,db)04730474defexecute(self,executor):0475raiseValueError("Tables don't have values")047604770478classSQLObjectTable(Table):0479FieldClass=SQLObjectField04800481def__init__(self,soClass):0482self.soClass=soClass0483assertsoClass.sqlmeta.table,(0484"Bad table name in class %r: %r"0485%(soClass,soClass.sqlmeta.table))0486Table.__init__(self,soClass.sqlmeta.table)04870488def__getattr__(self,attr):0489ifattr.startswith('__'):0490raiseAttributeError0491ifattr=='id':0492returnself._getattrFromID(attr)0493elifattrinself.soClass.sqlmeta.columns:0494column=self.soClass.sqlmeta.columns[attr]0495returnself._getattrFromColumn(column,attr)0496elifattr+'ID'in[kfor(k,v)inself.soClass.sqlmeta.columns.items()0498ifv.foreignKey]:0499attr+='ID'0500column=self.soClass.sqlmeta.columns[attr]0501returnself._getattrFromColumn(column,attr)0502else:0503raiseAttributeError(0504"%s instance has no attribute '%s'"%(self.soClass.__name__,0505attr))05060507def_getattrFromID(self,attr):0508returnself.FieldClass(self.tableName,self.soClass.sqlmeta.idName,0509attr,self.soClass,None)05100511def_getattrFromColumn(self,column,attr):0512returnself.FieldClass(self.tableName,column.dbName,attr,0513self.soClass,column)051405150516classSQLObjectTableWithJoins(SQLObjectTable):05170518def__getattr__(self,attr):0519ifattr+'ID'in[kfor(k,v)inself.soClass.sqlmeta.columns.items()0521ifv.foreignKey]:0522column=self.soClass.sqlmeta.columns[attr+'ID']0523returnself._getattrFromForeignKey(column,attr)0524elifattrin[x.joinMethodNameforxinself.soClass.sqlmeta.joins]:0525join=[xforxinself.soClass.sqlmeta.joins0526ifx.joinMethodName==attr][0]0527returnself._getattrFromJoin(join,attr)0528else:0529returnSQLObjectTable.__getattr__(self,attr)05300531def_getattrFromForeignKey(self,column,attr):0532ret=getattr(self,column.name)==getattr(self.soClass,'_SO_class_'+column.foreignKey).q.id0534returnret05350536def_getattrFromJoin(self,join,attr):0537ifhasattr(join,'otherColumn'):0538returnAND(0539join.otherClass.q.id==Field(join.intermediateTable,0540join.otherColumn),0541Field(join.intermediateTable,0542join.joinColumn)==self.soClass.q.id)0543else:0544returngetattr(join.otherClass.q,join.joinColumn)==self.soClass.q.id054605470548classTableSpace:0549TableClass=Table05500551def__getattr__(self,attr):0552ifattr.startswith('__'):0553raiseAttributeError0554returnself.TableClass(attr)055505560557classConstantSpace:0558def__getattr__(self,attr):0559ifattr.startswith('__'):0560raiseAttributeError0561returnSQLConstant(attr)056205630564########################################0565# Table aliases0566########################################05670568classAliasField(Field):0569def__init__(self,tableName,fieldName,alias,aliasTable):0570Field.__init__(self,tableName,fieldName)0571self.alias=alias0572self.aliasTable=aliasTable05730574def__sqlrepr__(self,db):0575fieldName=self.fieldName0576ifisinstance(fieldName,SQLExpression):0577fieldName=sqlrepr(fieldName,db)0578returnself.alias+"."+fieldName05790580deftablesUsedImmediate(self):0581return[self.aliasTable]058205830584classAliasTable(Table):0585as_string=''# set it to "AS" if your database requires it0586FieldClass=AliasField05870588_alias_lock=threading.Lock()0589_alias_counter=005900591def__init__(self,table,alias=None):0592ifhasattr(table,"sqlmeta"):0593tableName=SQLConstant(table.sqlmeta.table)0594elifisinstance(table,(Select,Union)):0595assertaliasisnotNone,"Alias name cannot be constructed from Select instances, ""please provide an 'alias' keyword."0598tableName=Subquery('',table)0599table=None0600else:0601tableName=SQLConstant(table)0602table=None0603Table.__init__(self,tableName)0604self.table=table0605ifaliasisNone:0606self._alias_lock.acquire()0607try:0608AliasTable._alias_counter+=10609alias="%s_alias%d"%(tableName,AliasTable._alias_counter)0610finally:0611self._alias_lock.release()0612self.alias=alias06130614def__getattr__(self,attr):0615ifattr.startswith('__'):0616raiseAttributeError0617ifself.table:0618attr=getattr(self.table.q,attr).fieldName0619returnself.FieldClass(self.tableName,attr,self.alias,self)06200621def__sqlrepr__(self,db):0622return"%s %s %s"%(sqlrepr(self.tableName,db),self.as_string,0623self.alias)062406250626classAlias(SQLExpression):0627def__init__(self,table,alias=None):0628self.q=AliasTable(table,alias)06290630def__sqlrepr__(self,db):0631returnsqlrepr(self.q,db)06320633defcomponents(self):0634return[self.q]063506360637classUnion(SQLExpression):0638def__init__(self,*tables):0639tabs=[]0640fortintables:0641ifnotisinstance(t,SQLExpression)andhasattr(t,'sqlmeta'):0642t=t.sqlmeta.table0643ifisinstance(t,Alias):0644t=t.q0645ifisinstance(t,Table):0646t=t.tableName0647ifnotisinstance(t,SQLExpression):0648t=SQLConstant(t)0649tabs.append(t)0650self.tables=tabs06510652def__sqlrepr__(self,db):0653return" UNION ".join([str(sqlrepr(t,db))fortinself.tables])06540655########################################0656# SQL Statements0657########################################065806590660classSelect(SQLExpression):0661def__init__(self,items=NoDefault,where=NoDefault,groupBy=NoDefault,0662having=NoDefault,orderBy=NoDefault,limit=NoDefault,0663join=NoDefault,lazyColumns=False,distinct=False,0664start=0,end=None,reversed=False,forUpdate=False,0665clause=NoDefault,staticTables=NoDefault,0666distinctOn=NoDefault):0667self.ops={}0668ifnotisinstance(items,(list,tuple,types.GeneratorType)):0669items=[items]0670ifclauseisNoDefaultandwhereisnotNoDefault:0671clause=where0672ifstaticTablesisNoDefault:0673staticTables=[]0674self.ops['items']=items0675self.ops['clause']=clause0676self.ops['groupBy']=groupBy0677self.ops['having']=having0678self.ops['orderBy']=orderBy0679self.ops['limit']=limit0680self.ops['join']=join0681self.ops['lazyColumns']=lazyColumns0682self.ops['distinct']=distinct0683self.ops['distinctOn']=distinctOn0684self.ops['start']=start0685self.ops['end']=end0686self.ops['reversed']=reversed0687self.ops['forUpdate']=forUpdate0688self.ops['staticTables']=staticTables06890690defclone(self,**newOps):0691ops=self.ops.copy()0692ops.update(newOps)0693returnself.__class__(**ops)06940695defnewItems(self,items):0696returnself.clone(items=items)06970698defnewClause(self,new_clause):0699returnself.clone(clause=new_clause)07000701deforderBy(self,orderBy):0702returnself.clone(orderBy=orderBy)07030704defunlimited(self):0705returnself.clone(limit=NoDefault,start=0,end=None)07060707deflimit(self,limit):0708self.clone(limit=limit)07090710deflazyColumns(self,value):0711returnself.clone(lazyColumns=value)07120713defreversed(self):0714returnself.clone(reversed=notself.ops.get('reversed',False))07150716defdistinct(self):0717returnself.clone(distinct=True)07180719deffilter(self,filter_clause):0720iffilter_clauseisNone:0721# None doesn't filter anything, it's just a no-op:0722returnself0723clause=self.ops['clause']0724ifisinstance(clause,string_type):0725clause=SQLConstant('(%s)'%clause)0726returnself.newClause(AND(clause,filter_clause))07270728def__sqlrepr__(self,db):07290730select="SELECT"0731ifself.ops['distinct']:0732select+=" DISTINCT"0733ifself.ops['distinctOn']isnotNoDefault:0734select+=" ON(%s)"%_str_or_sqlrepr(0735self.ops['distinctOn'],db)0736ifnotself.ops['lazyColumns']:0737select+=" %s"%", ".join(0738[str(_str_or_sqlrepr(v,db))forvinself.ops['items']])0739else:0740select+=" %s"%_str_or_sqlrepr(self.ops['items'][0],db)07410742join=[]0743join_str=''0744ifself.ops['join']isnotNoDefaultandself.ops['join']isnotNone:0745_join=self.ops['join']0746ifisinstance(_join,str):0747join_str=" "+_join0748elifisinstance(_join,SQLJoin):0749join.append(_join)0750else:0751join.extend(_join)0752tables=set()0753forxinself.ops['staticTables']:0754ifisinstance(x,SQLExpression):0755x=sqlrepr(x,db)0756tables.add(x)0757things=list(self.ops['items'])+join0758ifself.ops['clause']isnotNoDefault:0759things.append(self.ops['clause'])0760forthinginthings:0761ifisinstance(thing,SQLExpression):0762tables.update(tablesUsedSet(thing,db))0763forjinjoin:0764t1=_str_or_sqlrepr(j.table1,db)0765ift1intables:0766tables.remove(t1)0767t2=_str_or_sqlrepr(j.table2,db)0768ift2intables:0769tables.remove(t2)0770iftables:0771select+=" FROM %s"%", ".join(sorted(tables))0772elifjoin:0773select+=" FROM"0774tablesYet=tables0775forjinjoin:0776iftablesYetandj.table1:0777sep=", "0778else:0779sep=" "0780select+=sep+sqlrepr(j,db)0781tablesYet=True07820783ifjoin_str:0784select+=join_str07850786ifself.ops['clause']isnotNoDefault:0787select+=" WHERE %s"%_str_or_sqlrepr(self.ops['clause'],db)0788ifself.ops['groupBy']isnotNoDefault:0789groupBy=_str_or_sqlrepr(self.ops['groupBy'],db)0790ifisinstance(self.ops['groupBy'],(list,tuple)):0791groupBy=groupBy[1:-1]# Remove parens0792select+=" GROUP BY %s"%groupBy0793ifself.ops['having']isnotNoDefault:0794select+=" HAVING %s"%_str_or_sqlrepr(self.ops['having'],db)0795ifself.ops['orderBy']isnotNoDefaultandself.ops['orderBy']isnotNone:0797orderBy=self.ops['orderBy']0798ifself.ops['reversed']:0799reverser=DESC0800else:0801defreverser(x):0802returnx0803ifisinstance(orderBy,(list,tuple)):0804select+=" ORDER BY %s"%", ".join(0805[_str_or_sqlrepr(reverser(_x),db)for_xinorderBy])0806else:0807select+=" ORDER BY %s"%_str_or_sqlrepr(0808reverser(orderBy),db)0809start,end=self.ops['start'],self.ops['end']0810ifself.ops['limit']isnotNoDefault:0811end=start+self.ops['limit']0812ifstartorend:0813from.dbconnectionimportdbConnectionForScheme0814select=dbConnectionForScheme(db)._queryAddLimitOffset(select,0815start,end)0816ifself.ops['forUpdate']:0817select+=" FOR UPDATE"0818returnselect08190820registerConverter(Select,SQLExprConverter)082108220823classInsert(SQLExpression):0824def__init__(self,table,valueList=None,values=None,template=NoDefault):0825self.template=template0826self.table=table0827ifvalueList:0828ifvalues:0829raiseTypeError("You may only give valueList *or* values")0830self.valueList=valueList0831else:0832self.valueList=[values]08330834def__sqlrepr__(self,db):0835ifnotself.valueList:0836return''0837insert="INSERT INTO %s"%self.table0838allowNonDict=True0839template=self.template0840if(templateisNoDefault)andisinstance(self.valueList[0],dict):0841template=list(sorted(self.valueList[0].keys()))0842allowNonDict=False0843iftemplateisnotNoDefault:0844insert+=" (%s)"%", ".join(template)0845insert+=" VALUES "0846listToJoin=[]0847listToJoin_app=listToJoin.append0848forvalueinself.valueList:0849ifisinstance(value,dict):0850iftemplateisNoDefault:0851raiseTypeError(0852"You can't mix non-dictionaries with dictionaries "0853"in an INSERT if you don't provide a template (%s)"%0854repr(value))0855value=dictToList(template,value)0856elifnotallowNonDict:0857raiseTypeError(0858"You can't mix non-dictionaries with dictionaries "0859"in an INSERT if you don't provide a template (%s)"%0860repr(value))0861listToJoin_app("(%s)"%", ".join([sqlrepr(v,db)forvinvalue]))0862insert="%s%s"%(insert,", ".join(listToJoin))0863returninsert08640865registerConverter(Insert,SQLExprConverter)086608670868defdictToList(template,dict):0869list=[]0870forkeyintemplate:0871list.append(dict[key])0872iflen(dict.keys())>len(template):0873raiseTypeError(0874"Extra entries in dictionary that aren't asked for in template "0875"(template=%s, dict=%s)"%(repr(template),repr(dict)))0876returnlist087708780879classUpdate(SQLExpression):0880def__init__(self,table,values,template=NoDefault,where=NoDefault):0881self.table=table0882self.values=values0883self.template=template0884self.whereClause=where08850886def__sqlrepr__(self,db):0887update="%s %s"%(self.sqlName(),self.table)0888update+=" SET"0889first=True0890ifself.templateisnotNoDefault:0891foriinrange(len(self.template)):0892iffirst:0893first=False0894else:0895update+=","0896update+=" %s=%s"%(self.template[i],0897sqlrepr(self.values[i],db))0898else:0899forkey,valueinsorted(self.values.items()):0900iffirst:0901first=False0902else:0903update+=","0904update+=" %s=%s"%(key,sqlrepr(value,db))0905ifself.whereClauseisnotNoDefault:0906update+=" WHERE %s"%_str_or_sqlrepr(self.whereClause,db)0907returnupdate09080909defsqlName(self):0910return"UPDATE"09110912registerConverter(Update,SQLExprConverter)091309140915classDelete(SQLExpression):0916"""To be safe, this will signal an error if there is no where clause,0917 unless you pass in where=None to the constructor."""0918def__init__(self,table,where=NoDefault):0919self.table=table0920ifwhereisNoDefault:0921raiseTypeError(0922"You must give a where clause or pass in None "0923"to indicate no where clause")0924self.whereClause=where09250926def__sqlrepr__(self,db):0927whereClause=self.whereClause0928ifwhereClauseisNone:0929return"DELETE FROM %s"%self.table0930whereClause=_str_or_sqlrepr(whereClause,db)0931return"DELETE FROM %s WHERE %s"%(self.table,whereClause)09320933registerConverter(Delete,SQLExprConverter)093409350936classReplace(Update):0937defsqlName(self):0938return"REPLACE"09390940registerConverter(Replace,SQLExprConverter)09410942########################################0943# SQL Builtins0944########################################094509460947classDESC(SQLExpression):09480949def__init__(self,expr):0950self.expr=expr09510952def__sqlrepr__(self,db):0953ifisinstance(self.expr,DESC):0954returnsqlrepr(self.expr.expr,db)0955return'%s DESC'%sqlrepr(self.expr,db)095609570958defAND(*ops):0959ifnotops:0960returnNone0961op1=ops[0]0962ops=ops[1:]0963ifops:0964returnSQLOp("AND",op1,AND(*ops))0965else:0966returnop1096709680969defOR(*ops):0970ifnotops:0971returnNone0972op1=ops[0]0973ops=ops[1:]0974ifops:0975returnSQLOp("OR",op1,OR(*ops))0976else:0977returnop1097809790980defNOT(op):0981returnSQLPrefix("NOT",op)098209830984def_IN(item,list):0985returnSQLOp("IN",item,list)098609870988defIN(item,list):0989from.sresultsimportSelectResults# Import here to avoid circular import0990ifisinstance(list,SelectResults):0991query=list.queryForSelect()0992query.ops['items']=[list.sourceClass.q.id]0993list=query0994ifisinstance(list,Select):0995returnINSubquery(item,list)0996else:0997return_IN(item,list)099809991000defNOTIN(item,list):1001ifisinstance(list,Select):1002returnNOTINSubquery(item,list)1003else:1004returnNOT(_IN(item,list))100510061007defSTARTSWITH(expr,pattern):1008returnLIKE(expr,_LikeQuoted(pattern)+'%',escape='\\')100910101011defENDSWITH(expr,pattern):1012returnLIKE(expr,'%'+_LikeQuoted(pattern),escape='\\')101310141015defCONTAINSSTRING(expr,pattern):1016returnLIKE(expr,'%'+_LikeQuoted(pattern)+'%',escape='\\')101710181019defISNULL(expr):1020returnSQLOp("IS",expr,None)102110221023defISNOTNULL(expr):1024returnSQLOp("IS NOT",expr,None)102510261027classColumnAS(SQLOp):1028''' Just like SQLOp('AS', expr, name) except without the parentheses '''1029def__init__(self,expr,name):1030ifisinstance(name,string_type):1031name=SQLConstant(name)1032SQLOp.__init__(self,'AS',expr,name)10331034def__sqlrepr__(self,db):1035return"%s %s %s"%(sqlrepr(self.expr1,db),self.op,1036sqlrepr(self.expr2,db))103710381039class_LikeQuoted:1040# It assumes prefix and postfix are strings; usually just a percent sign.10411042# @@: I'm not sure what the quoting rules really are for all the1043# databases10441045def__init__(self,expr):1046self.expr=expr1047self.prefix=''1048self.postfix=''10491050def__radd__(self,s):1051self.prefix=s+self.prefix1052returnself10531054def__add__(self,s):1055self.postfix+=s1056returnself10571058def__sqlrepr__(self,db):1059s=self.expr1060ifisinstance(s,SQLExpression):1061values=[]1062ifself.prefix:1063values.append(quote_str(self.prefix,db))1064s=_quote_like_special(sqlrepr(s,db),db)1065values.append(s)1066ifself.postfix:1067values.append(quote_str(self.postfix,db))1068ifdb=="mysql":1069return"CONCAT(%s)"%", ".join(values)1070else:1071return" || ".join(values)1072elifisinstance(s,string_type):1073s=_quote_like_special(unquote_str(sqlrepr(s,db)),db)1074returnquote_str("%s%s%s"%(self.prefix,s,self.postfix),db)1075else:1076raiseTypeError(1077"expected str, unicode or SQLExpression, got %s"%type(s))107810791080def_quote_like_special(s,db):1081ifdbin('postgres','rdbhost'):1082escape=r'\\'1083else:1084escape='\\'1085s=s.replace('\\',r'\\').replace('%',escape+'%').replace('_',escape+'_')1088returns108910901091########################################1092# SQL JOINs1093########################################109410951096classSQLJoin(SQLExpression):1097def__init__(self,table1,table2,op=','):1098ifhasattr(table1,'sqlmeta'):1099table1=table1.sqlmeta.table1100ifhasattr(table2,'sqlmeta'):1101table2=table2.sqlmeta.table1102ifisinstance(table1,str):1103table1=SQLConstant(table1)1104ifisinstance(table2,str):1105table2=SQLConstant(table2)1106self.table1=table11107self.table2=table21108self.op=op11091110def__sqlrepr__(self,db):1111ifself.table1:1112return"%s%s %s"%(sqlrepr(self.table1,db),self.op,1113sqlrepr(self.table2,db))1114else:1115return"%s %s"%(self.op,sqlrepr(self.table2,db))11161117registerConverter(SQLJoin,SQLExprConverter)111811191120defJOIN(table1,table2):1121returnSQLJoin(table1,table2," JOIN")112211231124defINNERJOIN(table1,table2):1125returnSQLJoin(table1,table2," INNER JOIN")112611271128defCROSSJOIN(table1,table2):1129returnSQLJoin(table1,table2," CROSS JOIN")113011311132defSTRAIGHTJOIN(table1,table2):1133returnSQLJoin(table1,table2," STRAIGHT JOIN")113411351136defLEFTJOIN(table1,table2):1137returnSQLJoin(table1,table2," LEFT JOIN")113811391140defLEFTOUTERJOIN(table1,table2):1141returnSQLJoin(table1,table2," LEFT OUTER JOIN")114211431144defNATURALJOIN(table1,table2):1145returnSQLJoin(table1,table2," NATURAL JOIN")114611471148defNATURALLEFTJOIN(table1,table2):1149returnSQLJoin(table1,table2," NATURAL LEFT JOIN")115011511152defNATURALLEFTOUTERJOIN(table1,table2):1153returnSQLJoin(table1,table2," NATURAL LEFT OUTER JOIN")115411551156defRIGHTJOIN(table1,table2):1157returnSQLJoin(table1,table2," RIGHT JOIN")115811591160defRIGHTOUTERJOIN(table1,table2):1161returnSQLJoin(table1,table2," RIGHT OUTER JOIN")116211631164defNATURALRIGHTJOIN(table1,table2):1165returnSQLJoin(table1,table2," NATURAL RIGHT JOIN")116611671168defNATURALRIGHTOUTERJOIN(table1,table2):1169returnSQLJoin(table1,table2," NATURAL RIGHT OUTER JOIN")117011711172defFULLJOIN(table1,table2):1173returnSQLJoin(table1,table2," FULL JOIN")117411751176defFULLOUTERJOIN(table1,table2):1177returnSQLJoin(table1,table2," FULL OUTER JOIN")117811791180defNATURALFULLJOIN(table1,table2):1181returnSQLJoin(table1,table2," NATURAL FULL JOIN")118211831184defNATURALFULLOUTERJOIN(table1,table2):1185returnSQLJoin(table1,table2," NATURAL FULL OUTER JOIN")118611871188classSQLJoinConditional(SQLJoin):1189"""Conditional JOIN"""1190def__init__(self,table1,table2,op,1191on_condition=None,using_columns=None):1192"""For condition you must give on_condition or using_columns1193 but not both11941195 on_condition can be a string or SQLExpression, for example1196 Table1.q.col1 == Table2.q.col21197 using_columns can be a string or a list of columns, e.g.1198 (Table1.q.col1, Table2.q.col2)1199 """1200ifnoton_conditionandnotusing_columns:1201raiseTypeError("You must give ON condition or USING columns")1202ifon_conditionandusing_columns:1203raiseTypeError(1204"You must give ON condition or USING columns but not both")1205SQLJoin.__init__(self,table1,table2,op)1206self.on_condition=on_condition1207self.using_columns=using_columns12081209def__sqlrepr__(self,db):1210ifself.on_condition:1211on_condition=self.on_condition1212ifhasattr(on_condition,"__sqlrepr__"):1213on_condition=sqlrepr(on_condition,db)1214join="%s %s ON %s"%(self.op,sqlrepr(self.table2,db),1215on_condition)1216ifself.table1:1217join="%s %s"%(sqlrepr(self.table1,db),join)1218returnjoin1219elifself.using_columns:1220using_columns=[]1221forcolinself.using_columns:1222ifhasattr(col,"__sqlrepr__"):1223col=sqlrepr(col,db)1224using_columns.append(col)1225using_columns=", ".join(using_columns)1226join="%s %s USING (%s)"%(self.op,sqlrepr(self.table2,db),1227using_columns)1228ifself.table1:1229join="%s %s"%(sqlrepr(self.table1,db),join)1230returnjoin1231else:1232RuntimeError,"Impossible error"12331234registerConverter(SQLJoinConditional,SQLExprConverter)123512361237defINNERJOINConditional(table1,table2,1238on_condition=None,using_columns=None):1239returnSQLJoinConditional(table1,table2,"INNER JOIN",1240on_condition,using_columns)124112421243defLEFTJOINConditional(table1,table2,on_condition=None,using_columns=None):1244returnSQLJoinConditional(table1,table2,"LEFT JOIN",1245on_condition,using_columns)124612471248defLEFTOUTERJOINConditional(table1,table2,1249on_condition=None,using_columns=None):1250returnSQLJoinConditional(table1,table2,"LEFT OUTER JOIN",1251on_condition,using_columns)125212531254defRIGHTJOINConditional(table1,table2,1255on_condition=None,using_columns=None):1256returnSQLJoinConditional(table1,table2,"RIGHT JOIN",1257on_condition,using_columns)125812591260defRIGHTOUTERJOINConditional(table1,table2,1261on_condition=None,using_columns=None):1262returnSQLJoinConditional(table1,table2,"RIGHT OUTER JOIN",1263on_condition,using_columns)126412651266defFULLJOINConditional(table1,table2,on_condition=None,using_columns=None):1267returnSQLJoinConditional(table1,table2,"FULL JOIN",1268on_condition,using_columns)126912701271defFULLOUTERJOINConditional(table1,table2,1272on_condition=None,using_columns=None):1273returnSQLJoinConditional(table1,table2,"FULL OUTER JOIN",1274on_condition,using_columns)127512761277classSQLJoinOn(SQLJoinConditional):1278"""Conditional JOIN ON"""1279def__init__(self,table1,table2,op,on_condition):1280SQLJoinConditional.__init__(self,table1,table2,op,on_condition)12811282registerConverter(SQLJoinOn,SQLExprConverter)128312841285classSQLJoinUsing(SQLJoinConditional):1286"""Conditional JOIN USING"""1287def__init__(self,table1,table2,op,using_columns):1288SQLJoinConditional.__init__(self,table1,table2,1289op,None,using_columns)12901291registerConverter(SQLJoinUsing,SQLExprConverter)129212931294defINNERJOINOn(table1,table2,on_condition):1295returnSQLJoinOn(table1,table2,"INNER JOIN",on_condition)129612971298defLEFTJOINOn(table1,table2,on_condition):1299returnSQLJoinOn(table1,table2,"LEFT JOIN",on_condition)130013011302defLEFTOUTERJOINOn(table1,table2,on_condition):1303returnSQLJoinOn(table1,table2,"LEFT OUTER JOIN",on_condition)130413051306defRIGHTJOINOn(table1,table2,on_condition):1307returnSQLJoinOn(table1,table2,"RIGHT JOIN",on_condition)130813091310defRIGHTOUTERJOINOn(table1,table2,on_condition):1311returnSQLJoinOn(table1,table2,"RIGHT OUTER JOIN",on_condition)131213131314defFULLJOINOn(table1,table2,on_condition):1315returnSQLJoinOn(table1,table2,"FULL JOIN",on_condition)131613171318defFULLOUTERJOINOn(table1,table2,on_condition):1319returnSQLJoinOn(table1,table2,"FULL OUTER JOIN",on_condition)132013211322defINNERJOINUsing(table1,table2,using_columns):1323returnSQLJoinUsing(table1,table2,"INNER JOIN",using_columns)132413251326defLEFTJOINUsing(table1,table2,using_columns):1327returnSQLJoinUsing(table1,table2,"LEFT JOIN",using_columns)132813291330defLEFTOUTERJOINUsing(table1,table2,using_columns):1331returnSQLJoinUsing(table1,table2,"LEFT OUTER JOIN",using_columns)133213331334defRIGHTJOINUsing(table1,table2,using_columns):1335returnSQLJoinUsing(table1,table2,"RIGHT JOIN",using_columns)133613371338defRIGHTOUTERJOINUsing(table1,table2,using_columns):1339returnSQLJoinUsing(table1,table2,"RIGHT OUTER JOIN",using_columns)134013411342defFULLJOINUsing(table1,table2,using_columns):1343returnSQLJoinUsing(table1,table2,"FULL JOIN",using_columns)134413451346defFULLOUTERJOINUsing(table1,table2,using_columns):1347returnSQLJoinUsing(table1,table2,"FULL OUTER JOIN",using_columns)134813491350########################################1351# Subqueries (subselects)1352########################################13531354classOuterField(SQLObjectField):1355deftablesUsedImmediate(self):1356return[]135713581359classOuterTable(SQLObjectTable):1360FieldClass=OuterField136113621363classOuter:1364def__init__(self,table):1365self.q=OuterTable(table)136613671368classLIKE(SQLExpression):1369op="LIKE"13701371def__init__(self,expr,string,escape=None):1372self.expr=expr1373self.string=string1374self.escape=escape13751376def__sqlrepr__(self,db):1377escape=self.escape1378like="%s %s (%s)"%(sqlrepr(self.expr,db),1379self.op,sqlrepr(self.string,db))1380ifescapeisNone:1381return"(%s)"%like1382else:1383return"(%s ESCAPE %s)"%(like,sqlrepr(escape,db))13841385defcomponents(self):1386return[self.expr,self.string]13871388defexecute(self,executor):1389ifnothasattr(self,'_regex'):1390# @@: Crude, not entirely accurate1391dest=self.string1392dest=dest.replace("%%","\001")1393dest=dest.replace("*","\002")1394dest=dest.replace("%","*")1395dest=dest.replace("\001","%")1396dest=dest.replace("\002","[*]")1397self._regex=re.compile(fnmatch.translate(dest),re.I)1398returnself._regex.search(execute(self.expr,executor))139914001401classRLIKE(LIKE):1402op="RLIKE"14031404op_db={1405'firebird':'RLIKE',1406'maxdb':'RLIKE',1407'mysql':'RLIKE',1408'postgres':'~',1409'rdbhost':'~',1410'sqlite':'REGEXP'1411}14121413def_get_op(self,db):1414returnself.op_db.get(db,'LIKE')14151416def__sqlrepr__(self,db):1417return"(%s %s (%s))"%(1418sqlrepr(self.expr,db),self._get_op(db),sqlrepr(self.string,db)1419)14201421defexecute(self,executor):1422self.op=self._get_op(self.db)1423returnLIKE.execute(self,executor)142414251426classINSubquery(SQLExpression):1427op="IN"14281429def__init__(self,item,subquery):1430self.item=item1431self.subquery=subquery14321433defcomponents(self):1434return[self.item]14351436def__sqlrepr__(self,db):1437return"%s %s (%s)"%(sqlrepr(self.item,db),1438self.op,sqlrepr(self.subquery,db))143914401441classNOTINSubquery(INSubquery):1442op="NOT IN"144314441445classSubquery(SQLExpression):1446def__init__(self,op,subquery):1447self.op=op1448self.subquery=subquery14491450def__sqlrepr__(self,db):1451return"%s (%s)"%(self.op,sqlrepr(self.subquery,db))145214531454defEXISTS(subquery):1455returnSubquery("EXISTS",subquery)145614571458defNOTEXISTS(subquery):1459returnSubquery("NOT EXISTS",subquery)146014611462defSOME(subquery):1463returnSubquery("SOME",subquery)146414651466defANY(subquery):1467returnSubquery("ANY",subquery)146814691470defALL(subquery):1471returnSubquery("ALL",subquery)147214731474####147514761477classImportProxyField(SQLObjectField):1478deftablesUsedImmediate(self):1479return[str(self.tableName)]148014811482classImportProxy(SQLExpression):1483'''Class to be used in column definitions that rely on other tables that might1484 not yet be in a classregistry.1485 '''1486FieldClass=ImportProxyField14871488def__init__(self,clsName,registry=None):1489self.tableName=_DelayClass(self,clsName)1490self.sqlmeta=_Delay_proxy(table=_DelayClass(self,clsName))1491self.q=self1492self.soClass=None1493classregistry.registry(registry).addClassCallback(1494clsName,lambdaforeign,me:setattr(me,'soClass',foreign),self)14951496def__nonzero__(self):1497returnTrue14981499def__getattr__(self,attr):1500ifself.soClassisNone:1501return_Delay(self,attr)1502returngetattr(self.soClass.q,attr)150315041505class_Delay(SQLExpression):1506def__init__(self,proxy,attr):1507self.attr=attr1508self.proxy=proxy15091510def__sqlrepr__(self,db):1511ifself.proxy.soClassisNone:1512return'_DELAYED_'+self.attr1513val=self._resolve()1514ifisinstance(val,SQLExpression):1515val=sqlrepr(val,db)1516returnval15171518deftablesUsedImmediate(self):1519returngetattr(self._resolve(),'tablesUsedImmediate',lambda:[])()15201521defcomponents(self):1522returngetattr(self._resolve(),'components',lambda:[])()15231524def_resolve(self):1525returngetattr(self.proxy,self.attr)15261527# For AliasTable etc1528deffieldName(self):1529class_aliasFieldName(SQLExpression):1530def__init__(self,proxy):1531self.proxy=proxy15321533def__sqlrepr__(self,db):1534returnself.proxy._resolve().fieldName1535return_aliasFieldName(self)1536fieldName=property(fieldName)153715381539class_DelayClass(_Delay):1540def_resolve(self):1541returnself.proxy.soClass.sqlmeta.table154215431544class_Delay_proxy(object):1545def__init__(self,**kw):1546self.__dict__.update(kw)15471548######154915501551########################################1552# Global initializations1553########################################15541555table=TableSpace()1556const=ConstantSpace()1557func=const15581559########################################1560# Testing1561########################################15621563if__name__=="__main__":1564tests="""1565>>> AND(table.address.name == "Ian Bicking", table.address.zip > 30000)1566>>> table.address.name1567>>> AND(LIKE(table.address.name, "this"), IN(table.address.zip, [100, 200, 300]))1568>>> Select([table.address.name, table.address.state], where=LIKE(table.address.name, "%ian%"))1569>>> Select([table.user.name], where=AND(table.user.state == table.states.abbrev))1570>>> Insert(table.address, [{"name": "BOB", "address": "3049 N. 18th St."}, {"name": "TIM", "address": "409 S. 10th St."}])1571>>> Insert(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))1572>>> Delete(table.address, where="BOB"==table.address.name)1573>>> Update(table.address, {"lastModified": const.NOW()})1574>>> Replace(table.address, [("BOB", "3049 N. 18th St."), ("TIM", "409 S. 10th St.")], template=('name', 'address'))1575"""# noqa: allow long (> 79) lines1576forexprintests.split('\n'):1577ifnotexpr.strip():1578continue1579ifexpr.startswith('>>> '):1580expr=expr[4:]