B 3Rc8 @s"dZddlZddlmZddlmZddlmZmZddlm Z ddZ d1d d Z d2ddZ d3ddZ d4ddZddZd5ddZd6ddZd7d d!Zd8d#d$Zd9d&d'Zed(kre Zed)ed*ed+ed,eddd-lmZddlZejejd.d/Zeed0edS):z a set of functions for interacting with databases When possible, it's probably preferable to use a _DbConnection.DbConnect_ object N)DbInfo)DbModule) DbResultSetRandomAccessDbResultSet)StringIOcsfdd|DS)zl Given a list fromL, returns an iterator of the elements specified using their indices in the list what csg|] }|qSr).0x)fromLr2lib/python3.7/site-packages/rdkit/Dbase/DbUtils.py sz_take..r)r Zwhatr)r r _takesr sysdba masterkeyc Csb|st|||}|}d||f}|rP|ddkrDd|}|d|7}|||S)ax 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 zselect %s from %sjoinrzjoin %s )rconnectcursorstripfindexecutefetchall) dBasetable fieldStringuserpasswordrcnccmdrrr GetColumns s   r!*c Cs|r| dk rtd|r$| s$td|dkr0d}| sBt|||} | } d||f}|r|ddkrtd |}|d |7}|r|d dkrd |}|d |7}|ry | s| |n | || Wn6tk rtj d |ddl }| dSX| dk rtd| s(td| }|dkrt}xd|ddD].}|||krl||n|||qNWn$| rt}nt}|| | ||| | d}|S)a; 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 Nz4forceList and transform arguments are not compatiblez5when forceList is set, randomAccess must also be usedr#Tzselect %s from %srrzjoin %srwherezwhere %sz#the command "%s" generated errors: ) removeDups transformextras) ValueErrorrrrrrr Exceptionsysstderrwrite traceback print_excrsetremoveaddrr)rrrZ whereStringrrr&rZ forceListr'Z randomAccessr(rrr r.dataseenentryklassrrr GetDataBsX        r7,c Cs t|r"|ddkr"d|}t|rD|ddkrDd|}d||||f} |sft|||}|} | | g} g} xFtt| jD]4} | j| }|dt j kr| | | |dqWg}| | | | }x0|D](}t|| }| | d d |DqWd |S) ap 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%r#zwhere %srzjoin %szselect %s from %s %s %sr$rcSsg|] }t|qSr)str)rr rrr r sz"DatabaseToText.. )lenrrrrrrrangeZ descriptionrZ sqlBinTypesappendrrr )rrfieldsrr%rrdelimrZ sqlCommandrZheadersZ colsToTakeiitemlinesZresultsresdrrr DatabaseToTexts.     rEc Cstdtdtdddi}dg|}xt|D]t}ddg}x\t|D]N}|||} | dkr`qDt| } | tkr| tkrt} y t| } Wqtk r} z0td|d|ftdt| t| Wdd} ~ XYqXnt|dt t| |d<t | tr0|dks| |krtt | |d} t| g}qDytt| } Wnt k rZt} YnX| | krjt} t |dtsD|| ||dkrD| |d<qDW|||<q*W|S) z finds the types of the columns in _data_ if nullMarker is not None, elements of the data table which are equal to nullMarker will not count towards setting the type of their columns. r$r#Nz2cannot convert text from row %d col %d to a stringz >%sr) floatintr9r<type UnicodeErrorprintreprmaxr; isinstance OverflowError)r3nRowsnCols nullMarkerZ prioritiesrCcolZtypeHererowrDZlocTypemsglZfDrrr TypeFinders@        "  rXcCsxtt|D]}||||<||dd||<||dd||<||dd||<t|||kr||dd}|d|}td|||f|||<qW|S)z~ *For Internal Use* removes illegal characters from column headings and truncates those which are too long. r_-.rNz# Heading %s too long, changed to %s)r<r;rreplacerL) colHeadingsmaxColLabelLenr@ZnewHeadrrr _AdjustColHeadingss  r_cCsg}xtt|D]}||}|dtkr>|d||n:|dtkr^|d||n|d|||df|||krd|d|d<qW|S)z) returns a list of SQL type strings rz%s double precisionz %s integerz%s varchar(%d)r$z%s not null primary keyr#)r<r;rHr=rI)r]colTypeskeyColtypeStrsr@typrrr GetTypeStringss   rdFc Csy|||Wntk rd}|xv|D]n}y ||t||d7}Wn@tk r|sddl}|td|tdt |Yq6X|q6WYn Xt |}|S)Nrr$zinsert failed: ) rZ executemanyr*commitrtupler.r/rLrMr;)ZconnsqlStrblockZsilentrCrUr.rrr _insertBlock"s$   rjdc Cs | st|||} | } y| d|Wn tk rLtd|YnXyd||f} | | Wn6tk rtd| tdddl} | dSX| d} g} tj gt |d}d |}d ||f} d}x|D]}dgt |}xt t |D]}||dk r|dks0|||kr||dt krTt ||||<n4||dtkrxt||||<nt||||<nd||<qW| t|t | |kr|t| | | 7}t| d r| js| g} qWt | r|t| | | 7}t| d r| js| dS) zX *For Internal Use* (drops and) creates a table and then inserts the values z drop table %szcannot drop table %szcreate table %s (%s)zcreate table failed: zhere is the exception:rNr8zinsert into %s values (%s) autocommit)rrrrr*rLr.r/rfZ placeHolderr;rr<rHrIr9r=rgrjhasattrrl)rrrrcolDefsr`r3rSZ blockSizerrrhr.riZentryTxtZdStrZnDonerUentriesrTrrr _AddDataToDb9sX       rpc  Csb|dd|dd||} t| |t| } g} |} x| r| dd} | dd} | |} t| | krtdt| t| | kstdg}x^| D]V}y t|}Wn:t k ry t |}Wnt k r|}YnXYnX| |qW| ||} qFWt| }t | || |d }t | ||d }d |}t||||||| |d d S) aPloads the contents of the text file into a database. **Arguments** - dBase: the name of the DB to use - table: the name of the table to create/overwrite - inF: the file like object from which the data should be pulled (must support readline()) - delim: the delimiter used to separate fields - user: the user name to use in connecting to the DB - password: the password to use in connecting to the DB - maxColLabelLen: the maximum length a column label should be allowed to have (truncation otherwise) - keyCol: the column to be used as an index for the db **Notes** - if _table_ already exists, it is destroyed before we write the new data - we assume that the first row of the file contains the column names rZrYr rr:z>>>zunequal length)rS)rar8N)r\readlinesplitr_r;rLrMAssertionErrorrIr)rHr=rXrdrrp)rrZinFr?rrr^rarSr]rRr3ZinLZsplitLZtmpVectr5valrQr`rbrnrrr TextFileToDatabaseqs>             rwNonec CsFt} | t|||||||d| dt||| ||| | ddS)z. FIX: at the moment this is a hack )r>rr%rrr)rrrarSN)rr-rEseekrw) ZfromDbZfromTblZtoDbZtoTblr>rr%rrrarSsiorrr DatabaseToDatabases  r{__main__z foo,bar,baz z1,2,3 z1.1,4,5 z4,foo,6 )RDConfigZDbasezTEST.GDBZfromtext)rrrN) r"rrrr#rrNr$NN)r"rrrrr8N)N)N)F)NrkN)r8rrrqNN)r"rrrrNrx) __doc__r+Z rdkit.DbaserrZrdkit.Dbase.DbResultSetrriorr r!r7rErXr_rdrjrprwr{__name__rzr-ryZrdkitr}ospathrZ RDCodeDirZdirLocrrrr sB    " N 8 /   7 D