a#nSc @sdZddlmZddlmZmZdZddlmZddl Z ddl Z ddl Z ddlm Z dd d dd Zd d dd dd d ddddd Zd d d dd dddZddZdZddZedZddddZddd ddddZd d d dd dddZedkrddlZejZejdejdejdejd ejd ddlmZddlZej j!ej"d!d"Z#ee#d#endS($s a set of functions for interacting with databases When possible, it's probably preferable to use a _DbConnection.DbConnect_ object i(tRDConfig(t DbResultSettRandomAccessDbResultSetcCst|d|S(NcSs||S(N((txty((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pyts(tmap(tfromLtwhat((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pyt_takes(tDbModuleN(tDbInfotsysdbat masterkeytc Cs|stj|||}n|j}d||f}|ry|jjddkrhd|}n|d|7}n|j||jS(sp gets a set of data from a table **Arguments** - dBase: database name - table: table name - fieldString: a string with the names of the fields to be extracted, this should be a comma delimited list - user and password: - join: a join clause (omit the verb 'join') **Returns** - a list of the data sselect %s from %stjoinisjoin %st (R tconnecttcursortstriptfindtexecutetfetchall( tdBasettablet fieldStringtusertpasswordRtcntctcmd((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pyt GetColumnss   t*iic  Cs| stj|||} n| j} d||f}|ry|jjddkrhd|}n|d|7}n|r|jjddkrd|}n|d|7}n|ry*| s| j|n| j|| Wn2tjjd|d d l }|j d SX| d k r5t d n| sGt d n| j }|dkrg}xC|D]7}|||kr|j|qm|j||qmWqn9| rt}nt}|| | |d |d| d| }|S(s# a more flexible method to get a set of data from a table **Arguments** - fields: a string with the names of the fields to be extracted, this should be a comma delimited list - where: the SQL where clause to be used with the DB query - removeDups indicates the column which should be used to screen out duplicates. Only the first appearance of a duplicate will be left in the dataset. **Returns** - a list of the data **Notes** - EFF: this isn't particularly efficient sselect %s from %sRisjoin %sRtwhereswhere %ss#the command "%s" generated errors: iNs4forceList and transform arguments are not compatibles5when forceList is set, randomAccess must also be usedt removeDupst transformtextras(R RRRRRtsyststderrtwritet tracebackt print_exctNonet ValueErrorRtremovetappendRR(RRRt whereStringRRR"Rt forceListR#t randomAccessR$RRRR(tdatatseententrytklass((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pytGetData;sL           $t,c Cst|r4|jjddkr4d|}nt|rh|jjddkrhd|}nd||||f} |stj|||}n|j} | j| g} g} x^tt| jD]G} | j| }|dt j kr| j | | j |dqqWg}|j |j | | j }x<|D]4}t|| }|j |j tt|qQWd j |S( sW Pulls the contents of a database and makes a deliminted text file from them **Arguments** - dBase: the name of the DB file to be used - table: the name of the table to query - fields: the fields to select with the SQL query - join: the join clause of the SQL query (e.g. 'join foo on foo.bar=base.bar') - where: the where clause of the SQL query (e.g. 'where foo = 2' or 'where bar > 17.6') - user: the username for DB access - password: the password to be used for DB access **Returns** - the CSV data (as text) R!iswhere %sRsjoin %ssselect %s from %s %s %siis (tlenRRR RRRtranget descriptionR t sqlBinTypesR-RRR Rtstr(RRtfieldsRR!RRtdelimRt sqlCommandRtheaderst colsToTaketititemtlinestresultstrestd((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pytDatabaseToTexts.' '       #cCsidtj6dtj6dtj6dd6}dg|}xt|D]}ddg}xt|D]}|||} | dk rdt| } | tjkr | tjkr tj} yt| } Wq.tk r} d|d|fGHdt | GHt| q.Xn#t |dt t| |d<| tjkr|dksU| |krt t | |d} tj| g}qq yt t | } Wntk rtj} nX| | krtj} n|dtjkr || ||dkr | |d%siN(ttypest FloatTypetIntTypet StringTypeR*txrangettypeR;t UnicodeErrortreprtmaxR7tfloattintt OverflowError(R1tnRowstnColst nullMarkert prioritiesREtcolttypeHeretrowRFtlocTypetmsgtltfD((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pyt TypeFinders@ +     #    cCsxtt|D]}tj|||||sddl}|jdG|GHdGt|GHqq7X|jq7Wn Xt|}|S(Niiisinsert failed:s ( Rt executemanytcommitRttupleR(R)ROR7(tconntsqlStrtblocktsilentRERZR(((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pyt _insertBlocks$      idc Cs| stj|||} n| j} y| jd|Wnd|GHnXy!d||f} | j| Wn,dG| GHdGHddl} | jdSX| jd} g} tjgt |d}d j |}d ||f} d}xR|D]J}dgt |}xt t |D]}||dk r|dks\|||kr||dt j krt||||>>sunequal lengthRVRjR6N(RdtreadlinetsplitRhR7ROtAssertionErrorRRRQR-R_RlRR~(RRtinFR=RRRfRjRVReRUR1tinLtsplitLttmpVectR3tvalRTRiRkRx((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pytTextFileToDatabase\s@!    !   R*c Csddl} | j} | jt||d|d|d|d|d|| jdt||| d|d|d| d | dS( s- FIX: at the moment this is a hack iNR<RR!RRRjRV(t cStringIOtStringIOR'RGtseekR( tfromDbtfromTblttoDbttoTblR<RR!RRRjRVRtio((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pytDatabaseToDatabases  $ !t__main__s foo,bar,baz s1,2,3 s1.1,4,5 s4,foo,6 tDbasesTEST.GDBtfromtext($t__doc__trdkitRtrdkit.Dbase.DbResultSetRRR t rdkit.DbaseR R%RHRcR R*RR5RGR_RhRltFalseRtR~RRt__name__RRRR'RtostpathRt RDCodeDirtdirLoc(((sU/public2/ftp/pub/ccp4/6.5/ccp4-6.5/lib/python2.7/site-packages/rdkit/Dbase/DbUtils.pytsD $! F 8 .   6F