&hzdZddlZddlmZddlmZddlmZdaddZGd d ZGd d Z Gd dZ Gdde Z e e dZ dddddddZ GddZy)zConnect with a BioSQL database and load Biopython like objects from it. This provides interfaces for loading biological objects from a relational database, and is compatible with the BioSQL standards. N)BioSeq)DBUtils)LoaderFc .|dk(r tdtjdk(r,ddlm}|}|dvrd}d|d zd z}n |d vrd }d |d zd z}nt |dg}|j }|j}|dvr2tjdk7rd|vr |d|d<|d=d|vr*|d|d<|d=nd|vr |d|d<|d=d|vr |d|d<|d=|dvr|jdsd|d<tjdk(rU|dvr%||jddz|d|d}nD|d vr@||jddzdz|d|d}n|dvr ||d}n|d$i|}tjdk(rt||} n t|} |dvr| jjd|dvr=d} | jj| rddl } ddlm} | j!d | d!a| S|d"k(r| jjd#| S)%aLoad an existing BioSQL-style database. This function is the easiest way to retrieve a connection to a database, doing something like:: from BioSQL import BioSeqDatabase server = BioSeqDatabase.open_database(user="root", db="minidb") Arguments: - driver - The name of the database driver to use for connecting. The driver should implement the python DB API. By default, the MySQLdb driver is used. - user -the username to connect to the database with. - password, passwd - the password to connect with - host - the hostname of the database - database or db - the name of the database psycopgzUUsing BioSQL with psycopg (version one) is no longer supported. Use psycopg2 instead.javar)zxJDBCMySQLdbzcom.mysql.jdbc.Driverz jdbc:mysql://host/)psycopg2zorg.postgresql.Driverzjdbc:postgresql://connect)fromlist)r mysql.connectordatabasedbpasswordpasswdrpgdb template1mysqluser postgresqlz?stringtype=unspecified)sqlite3zSET sql_mode='ANSI_QUOTES';zaSELECT ev_class FROM pg_rewrite WHERE rulename='rule_bioentry_i1' OR rulename='rule_bioentry_i2';N)BiopythonWarningaYour BioSQL PostgreSQL schema includes some rules currently required for bioperl-db but which maycause problems loading data using Biopython (see BioSQL's RedMine Bug 2839 aka GitHub Issue 4 https://github.com/biosql/biosql/issues/4). If you do not use BioPerl, please remove these rules. Biopython should cope with the rules present, but with a performance penalty when loading new records.TrzPRAGMA foreign_keys = ON) ValueErrorosnamecom.ziclix.python.sqlr __import__rcopygetDBServeradaptorexecuteexecute_and_fetchallwarningsBiorwarn_POSTGRES_RULES_PRESENT) driverkwargsr module jdbc_driverurl_prefrkwconnserversqlr+rs d/mounts/lovelace/software/anaconda3/envs/py312/lib/python3.12/site-packages/BioSQL/BioSeqDatabase.py open_databaser9s& 6   ww&0 [ 1K&7#=H | #1K+fVnBxL: 2:XBzN4 r>\BzN8  %%bffZ.@$: ww& [ 266*g666 : D | #266*l;;>WW6 : D ; r*~&}} ww&$/$'//<=%% + >> . .s 3  , MM'! '+ # M 9  9: McneZdZdZddZdZdZdZdZdZ d Z d Z d Z d Z dd ZdZdZdZdZy)r'zRepresents a BioSQL database containing namespaces (sub-databases). This acts like a Python dictionary, giving access to each namespace (defined by a row in the biodatabase table) as a BioSeqDatabase object. Nc||_| |j}|dk(rd}nd}tj|t}||t j |||_||_y)aCreate a DBServer object. Arguments: - conn - A database connection object - module - The module used to create the database connection - module_name - Optionally, the name of the module. Default: module.__name__ Normally you would not want to create a DBServer object yourself. Instead use the open_database function, which returns an instance of DBServer. NrTF) wrap_cursor) r1__name___interface_specific_adaptorsr&Adaptorr get_dbutilsr( module_name)selfr5r1rBr=Adapts r8__init__zDBServer.__init__sg   //K + +KK,00gF '%%k2   'r:cb|jjd|jjdS)zEReturn a short description of the class name and database connection.()) __class__r>r(r5rCs r8__repr__zDBServer.__repr__s+..))*!DLL,=,=+@BBr:c.t|j|S)zqReturn a BioSeqDatabase object. Arguments: - name - The name of the BioSeqDatabase )BioSeqDatabaser()rCr"s r8 __getitem__zDBServer.__getitem__sdllD11r:cTd}t|jj|dS)z=Return number of namespaces (sub-databases) in this database.z$SELECT COUNT(name) FROM biodatabase;r)intr(execute_and_fetch_col0rCr7s r8__len__zDBServer.__len__s&44<<66s;A>??r:cXd}t|jj||fdS)z5Check if a namespace (sub-database) in this database.z2SELECT COUNT(name) FROM biodatabase WHERE name=%s;r)boolr(rQ)rCvaluer7s r8 __contains__zDBServer.__contains__s*BDLL77eXFqIJJr:cHt|jjSz8Iterate over namespaces (sub-databases) in the database.)iterr(list_biodatabase_namesrJs r8__iter__zDBServer.__iter__sDLL779::r:ct|SrYrZrJs r8keysz DBServer.keys Dzr:c#(K|D] }|| yw)z4Iterate over BioSeqDatabase objects in the database.NrrCkeys r8valueszDBServer.values Cs)O c#,K|D] }|||f yw)z9Iterate over (namespace, BioSeqDatabase) in the database.Nrrbs r8itemszDBServer.items# !CtCy.  !c||vr t||jj|}tj|j|}|j y)z'Remove a namespace and all its entries.N)KeyErrorr(fetch_dbid_by_dbnamerDatabaseRemoverremove)rCr"db_idremovers r8 __delitem__zDBServer.__delitem__sH t 4.  11$7((u=r:cpd}|jj||||ft|j|S)z/Add a new database to the server and return it.zJINSERT INTO biodatabase (name, authority, description) VALUES (%s, %s, %s))r(r)rM)rCdb_name authority descriptionr7s r8 new_databasezDBServer.new_databases: #  S7I{"CDdllG44r:c$d}t|5}|D]M}|jdr|jdr'|js8||jdzz }O ddd|jdvr&|jj j |y|jdvrA|jd}|dd D]'}|jj j |)ytd |jd #1swYxYw) zLoad a database schema into the given database. This is used to create tables, etc when a database is first created. sql_file should specify the complete path to a file containing SQL entries for building the tables. z--# Nr)rr r;zModule z not supported by the loader.) open startswithstriprBr(cursorr)splitr )rCsql_filer7 sql_handleline sql_partssql_lines r8load_database_sqlzDBServer.load_database_sqls (^ .z" .??4(__S)ZZ\4::<#--C  . .   3 3 LL   ' ' ,  !J J #I%crN 6 ##++H5 6wt'7'7&88UVW W/ . .s:D DDc6|jjS)z/Commit the current transaction to the database.)r(commitrJs r8rzDBServer.commits||""$$r:c6|jjSz"Roll-back the current transaction.)r(rollbackrJs r8rzDBServer.rollback!s||$$&&r:c6|jjSz3Close the connection. No further activity possible.)r(closerJs r8rzDBServer.close%s||!!##r:N)NN)r> __module__ __qualname____doc__rErKrNrSrWr\r_rdrhrrrwrrrrrr:r8r'r'sW '2C2@ K ;  ! 5$XL%'$r:r'c<eZdZdZdZd dZdZdZdZdZ d Z y) _CursorWrapperzCA wrapper for mysql.connector resolving bytestring representations.c||_yr) real_cursor)rCrs r8rEz_CursorWrapper.__init__-s &r:Nc>|jj|||y)zExecute a sql statement.N)rr))rC operationparamsmultis r8r)z_CursorWrapper.execute0s   FE:r:c<|jj||y)zExecute many sql statements.N)r executemany)rCrrs r8rz_CursorWrapper.executemany4s $$Y7r:ct|}t|D]*\}}t|ts|j d||<,t |S)z4Decode any bytestrings present in the row (PRIVATE).zutf-8)list enumerate isinstancebytesdecodetuple)rCtuple_ tuple_listielems r8_convert_tuplez_CursorWrapper._convert_tuple8sL&\  , 5GAt$& $ G 4 1  5Z  r:c\g}|D]$}|j|}|j|&|Sr)rappend)rClstret_lstr new_tuples r8 _convert_listz_CursorWrapper._convert_list@s9 &F++F3I NN9 % &r:cX|jj}|j|Sr)rfetchallr)rCrvs r8rz_CursorWrapper.fetchallGs'    & & (!!"%%r:cX|jj}|j|Sr)rfetchoner)rCrs r8rz_CursorWrapper.fetchoneKs'!!**,""6**r:)NF) r>rrrrEr)rrrrrrr:r8rr*s(M';8!&+r:rceZdZdZddZdZddZdZdZdZ dZ d Z d Z d Z d Zd ZdZdZdZdZddZddZdZdZddZddZy)r@zHigh level wrapper for a database connection and cursor. Most database calls in BioSQL are done indirectly though this adaptor class. This provides helper methods for fetching data and executing sql. c||_|r&t|j|_||_y|j|_||_y)zCreate an Adaptor object. Arguments: - conn - A database connection - dbutils - A BioSQL.DBUtils object - wrap_cursor - Optional, whether to wrap the cursor object N)r5rrdbutils)rCr5rr=s r8rEzAdaptor.__init__Xs? (7DK ++-DK r:cN|jj|j|S)z.Return the last row id for the selected table.)rlast_idr)rCtables r8rzAdaptor.last_idhs||##DKK77r:cN|jj|j|S)zASet the autocommit mode. True values enable; False value disable.)r autocommitr5)rCys r8rzAdaptor.autocommitls||&&tyy!44r:c6|jjS)zCommit the current transaction.)r5rrJs r8rzAdaptor.commitpsyy!!r:c6|jjSr)r5rrJs r8rzAdaptor.rollbacktsyy!!##r:c6|jjSr)r5rrJs r8rz Adaptor.closexsyy  r:c|jd|f|jj}|std||ddS)z;Return the internal id for the sub-database using its name.z6select biodatabase_id from biodatabase where name = %sz"Cannot find biodatabase with name r)r)rrrl)rCdbnamers r8rmzAdaptor.fetch_dbid_by_dbname|sL Dvi [[ ! ! #?zJK K!uQxr:cd}|g}|r|dz }|j||j|||jj}|st d|t |dkDrt d||ddS)aReturn the internal id for a sequence using its display id. Arguments: - dbid - the internal id for the sub-database - name - the name of the sequence. Corresponds to the name column of the bioentry table of the SQL schema z0select bioentry_id from bioentry where name = %s and biodatabase_id = %sCannot find display id rz$More than one entry with display id rrr)rr IndexErrorlenrCdbidr"r7fieldsrs r8fetch_seqid_by_display_idz!Adaptor.fetch_seqid_by_display_idsA  - -C MM$  S&! [[ ! ! #6th?@ @ r7Q;CD8LM M!uQxr:cd}|g}|r|dz }|j||j|||jj}|st d|t |dkDrt d||ddS)aReturn the internal id for a sequence using its accession. Arguments: - dbid - the internal id for the sub-database - name - the accession of the sequence. Corresponds to the accession column of the bioentry table of the SQL schema 5select bioentry_id from bioentry where accession = %srzCannot find accession rz#More than one entry with accession rrrs r8fetch_seqid_by_accessionz Adaptor.fetch_seqid_by_accessionsF  - -C MM$  S&! [[ ! ! #5dX>? ? r7Q;B4(KL L!uQxr:c`d}|g}|r|dz }|j||j||S)a Return a list internal ids using an accession. Arguments: - dbid - the internal id for the sub-database - name - the accession of the sequence. Corresponds to the accession column of the bioentry table of the SQL schema rr)rrQ)rCrr"r7rs r8fetch_seqids_by_accessionz!Adaptor.fetch_seqids_by_accessions>F  - -C MM$ **377r:c|jd}t|dkDrtd||d}t|dk(r|d}nd}d}||g}|r|dz }|j||j |||j j }|std |t|dkDrtd ||ddS) aReturn the internal id for a sequence using its accession and version. Arguments: - dbid - the internal id for the sub-database - name - the accession of the sequence containing a version number. Must correspond to . .z Bad version rr0zFSELECT bioentry_id FROM bioentry WHERE accession = %s AND version = %srzCannot find version z!More than one entry with version )rrrrr)rr) rCrr" acc_versionaccversionr7rrs r8fetch_seqid_by_versionzAdaptor.fetch_seqid_by_versionsjjo { a |D845 5!n { q !!nGGVw  - -C MM$  S&! [[ ! ! #3D8<= = r7Q;@IJ J!uQxr:cd}|g}|r|dz }|j||j|||jj}|st d||ddS)a!Return the internal id for a sequence using its identifier. Arguments: - dbid - the internal id for the sub-database - identifier - the identifier of the sequence. Corresponds to the identifier column of the bioentry table in the SQL schema. z6SELECT bioentry_id FROM bioentry WHERE identifier = %srrr)rr)rrr)rCr identifierr7rrs r8fetch_seqid_by_identifierz!Adaptor.fetch_seqid_by_identifiersnG  - -C MM$  S&! [[ ! ! #6znEF F!uQxr:c$|jdS)z*Return a list of all of the sub-databases.zSELECT name FROM biodatabaserQrJs r8r[zAdaptor.list_biodatabase_namess**+IJJr:c(|jd|fS)zReturn a list of internal ids for all of the sequences in a sub-databae. Arguments: - dbid - The internal id for a sub-database z:SELECT bioentry_id FROM bioentry WHERE biodatabase_id = %srrCrs r8list_bioentry_idszAdaptor.list_bioentry_idss** H4'  r:c(|jd|fS)zReturn a list of all sequence names in a sub-databae. Arguments: - dbid - The internal id for a sub-database z3SELECT name FROM bioentry WHERE biodatabase_id = %srrs r8list_bioentry_display_idsz!Adaptor.list_bioentry_display_idss** AD7  r:c&|j||S)zReturn ids given a SQL statement to select for them. This assumes that the given SQL does a SELECT statement that returns a list of items. This parses them out of the 2D list they come as and just returns them in a list. rrCr7argss r8 list_any_idszAdaptor.list_any_idss**355r:Nc|j||xsd|jj}t|dk7rt dt|d|dS)9Execute sql that returns 1 record, and return the record.rrzExpected 1 response, got rr)r)rrrr )rCr7rrs r8 execute_onezAdaptor.execute_onesS S$*"% [[ ! ! # r7a<8R CD D!u r:ctjdk(r|jdd}|jj |j ||y)zJust execute an sql command.r %s?N)r!r"replacerr)rrs r8r)zAdaptor.execute s8 77f ++dC(C T[[#t4r:ctjdk(r|jdd}|jj |j ||y)zExecute many sql commands.r rrN)r!r"rrrrrs r8rzAdaptor.executemany&s8 77f ++dC(C   c48r:cB||z }|jd|dz||fdS)zReturn a substring of a sequence. Arguments: - seqid - The internal id for the sequence - start - The start position of the sequence; 0-indexed - end - The end position of the sequence zBSELECT SUBSTR(seq, %s, %s) FROM biosequence WHERE bioentry_id = %srr)r)rCseqidstartendlengths r8get_subseq_as_stringzAdaptor.get_subseq_as_string,s=u P QY &    r:c|j||xsd|jjDcgc]}|d c}Scc}w)9Return a list of values from the first column in the row.rrr)rr)rCr7rfields r8rQzAdaptor.execute_and_fetch_col0As9 S$*"%&*kk&:&:&<=Ua===s Acb|j||xsd|jjS)$Return a list of tuples of all rows.rrrs r8r*zAdaptor.execute_and_fetchallFs' S$*"%{{##%%r:F)Tr)r>rrrrErrrrrrmrrrrrr[rrrrr)rrrQr*rr:r8r@r@Psv 85"$!,,8 <*K    65 9  *> &r:r@cLeZdZdZedZdfd Zdfd Zdfd ZxZ S)MysqlConnectorAdaptoraA BioSQL Adaptor class with fixes for the MySQL interface. BioSQL was failing due to returns of bytearray objects from the mysql-connector-python database connector. This adaptor class scrubs returns of bytearrays and of byte strings converting them to string objects instead. This adaptor class was made in response to backwards incompatible changes added to mysql-connector-python in release 2.0.0 of the package. cRt|ttfr|jS|S)z:If s is bytes or bytearray, convert to a string (PRIVATE).)rr bytearrayr)ss r8_bytearray_to_strz'MysqlConnectorAdaptor._bytearray_to_strWs$ a%+ ,88: r:cNt||}tfd|DS)rc3@K|]}j|ywrr.0vrCs r8 z4MysqlConnectorAdaptor.execute_one..as<1T++A.<)superrr)rCr7routrIs` r8rz!MysqlConnectorAdaptor.execute_one^s%g!#t,<<<.ks;Ad,,Q/;r )r r*r)rCr7rr orIs` r8r*z*MysqlConnectorAdaptor.execute_and_fetchallhs3g*35EHI;;;IIIs5r) r>rrr staticmethodrrrQr* __classcell__)rIs@r8rrLs3 = B JJr:r)rr rrrr) primary_idgi display_idr" accessionrcreZdZdZdZdZdZdZdZdZ dZ d Z d Z d Z d Zd ZdZdZdZddZy)rMzRepresents a namespace (sub-database) within the BioSQL database. i.e. One row in the biodatabase table, and all all rows in the bioentry table associated with it. c`||_||_|jj||_y)zCreate a BioDatabase object. Arguments: - adaptor - A BioSQL.Adaptor object - name - The name of the sub-database (namespace) N)r(r"rmr)rCr(r"s r8rEzBioSeqDatabase.__init__s(  LL55d; r:c<d|jd|jdS)z-Return a short summary of the BioSeqDatabase.zBioSeqDatabase(z, rH)r(r"rJs r8rKzBioSeqDatabase.__repr__s   0499-qAAr:c|jj|j|}tj|j|S)zGet a DBSeqRecord object by its name. Example: seq_rec = db.get_Seq_by_id('ROA1_HUMAN') The name of this method is misleading since it returns a DBSeqRecord rather than a Seq object, and presumably was to mirror BioPerl. )r(rrr DBSeqRecordrCr"rs r8 get_Seq_by_idzBioSeqDatabase.get_Seq_by_ids5 66tyy$G!!$,,66r:c|jj|j|}tj|j|S)aGet a DBSeqRecord object by accession number. Example: seq_rec = db.get_Seq_by_acc('X77802') The name of this method is misleading since it returns a DBSeqRecord rather than a Seq object, and presumably was to mirror BioPerl. )r(rrrrrs r8get_Seq_by_acczBioSeqDatabase.get_Seq_by_accs5 55diiF!!$,,66r:c|jj|j|}tj|j|S)aGet a DBSeqRecord object by version number. Example: seq_rec = db.get_Seq_by_ver('X77802.1') The name of this method is misleading since it returns a DBSeqRecord rather than a Seq object, and presumably was to mirror BioPerl. )r(rrrrrs r8get_Seq_by_verzBioSeqDatabase.get_Seq_by_vers5 33DIItD!!$,,66r:c|jj|j|}|Dcgc]"}tj|j|$c}Scc}w)a/Get a list of DBSeqRecord objects by accession number. Example: seq_recs = db.get_Seq_by_acc('X77802') The name of this method is misleading since it returns a list of DBSeqRecord objects rather than a list of Seq objects, and presumably was to mirror BioPerl. )r(rrrr)rCr"seqidsrs r8get_Seqs_by_acczBioSeqDatabase.get_Seqs_by_accsC77 4HEKLE""4<<7LLLs'Actj|j|}|j|jk7rt d|d|S)zReturn a DBSeqRecord for one of the sequences in the sub-database. Arguments: - key - The internal id for the sequence Entry z* does exist, but not in current name space)rrr(_biodatabase_idrrl)rCrcrecords r8rNzBioSeqDatabase.__getitem__sF##DLL#6  ! !TYY .VC7*TUV V r:c~||vrtd|dd}|jj||j|fy)z'Remove an entry and all its annotation.r(z2 cannot be deleted. It was not found or is invalidz@DELETE FROM bioentry WHERE biodatabase_id=%s AND bioentry_id=%s;N)rlr(r)r)rCrcr7s r8rrzBioSeqDatabase.__delitem__sH d?QR Q S499c"23r:cld}t|jj||jfdS)z:Return number of records in this namespace (sub database).z@SELECT COUNT(bioentry_id) FROM bioentry WHERE biodatabase_id=%s;r)rPr(rQrrRs r8rSzBioSeqDatabase.__len__s.P4<<66sTYYLI!LMMr:cd} t|}t|jj ||j |fdS#t$rYywxYw)zBCheck if a primary (internal) id is this namespace (sub database).zSSELECT COUNT(bioentry_id) FROM bioentry WHERE biodatabase_id=%s AND bioentry_id=%s;Fr)rPr rUr(rQr)rCrVr7 bioentry_ids r8rWzBioSeqDatabase.__contains__s_ :  e*K LL / /dii5M Nq Q    s A AAc^t|jj|jSzEIterate over ids (which may not be meaningful outside this database).)rZr(rrrJs r8r\zBioSeqDatabase.__iter__s"DLL22499=>>r:ct|Sr0r^rJs r8r_zBioSeqDatabase.keysr`r:c#(K|D] }|| yw)zAIterate over DBSeqRecord objects in the namespace (sub database).Nrrbs r8rdzBioSeqDatabase.valuesrerfc#,K|D] }|||f yw)z@Iterate over (id, DBSeqRecord) for the namespace (sub database).Nrrbs r8rhzBioSeqDatabase.itemsrirjc ~t|dk7r tdt|jd\}}|tvr,tdttj d|t|}t |j|}||j|}tj|j|S)zReturn a DBSeqRecord using an acceptable identifier. Arguments: - kwargs - A single key-value pair where the key is one of primary_id, gi, display_id, name, accession, version rz#single key/value parameter expectedrzlookup() expects one of z, not ) r TypeErrorrrh_allowed_lookupsr_getattrr(rrr)rCr0kr  lookup_name lookup_funcrs r8lookupzBioSeqDatabase.lookups v;! AB BFLLN#A&1 $ $*40@0E0E0G+H*K6RSQVW 'q) dllK8 DIIq)!!$,,66r:c tj|j|j|}d}|D]}|dz }tr|j j ddk(r+|j jd\}} t|}n|j }d}|jjd}d} |jj| ||j|||jfz|jjjr%|jjj!d|j#||S#t$r|j }d}YwxYw)aLoad a set of SeqRecords into the BioSQL database. record_iterator is either a list of SeqRecord objects, or an Iterator object that returns SeqRecord objects (such as the output from the Bio.SeqIO.parse() function), which will be used to populate the database. fetch_NCBI_taxonomy is boolean flag allowing or preventing connection to the taxonomic database on the NCBI server (via Bio.Entrez) to fetch a detailed taxonomy for each SeqRecord. Example:: from Bio import SeqIO count = db.load(SeqIO.parse(open(filename), format)) Returns the number of records loaded. rrrrzSELECT bioentry_id FROM bioentry WHERE (identifier = '%s' AND biodatabase_id = '%s') OR (accession = '%s' AND version = '%s' AND biodatabase_id = '%s')z7Duplicate record detected: record has not been inserted)rDatabaseLoaderr(rr.idcountrrPr annotationsr&r)rrr5IntegrityErrorload_seqrecord) rCrecord_iteratorfetch_NCBI_taxonomy db_loader num_records cur_recordrrrr7s r8loadzBioSeqDatabase.loadsQ())$,, CVW  ) 1J 1 K'==&&s+q0)3)<)+&$$.MM "#$s< E  E#"E#Nr)r>rrrrErKrr!r#r&rNrrrSrWr\r_rdrhr;rHrr:r8rMrMsZ <B 7 7 7 M 4N  ?  ! 7(6r:rMr )rr!ryrrrr.r9r'rr@rr?r6rMrr:r8rIs  |~L$L$^#+#+Ly&y&xJGJH-$ . %- '+'LLr: