corems.molecular_id.factory.EI_SQL 

  1__author__ = "Yuri E. Corilo"
  2__date__ = "Feb 12, 2020"
  3
  4import os
  5from dataclasses import dataclass
  6from typing import Optional
  7
  8from numpy import array, frombuffer
  9from sqlalchemy import (
 10    Column,
 11    Float,
 12    ForeignKey,
 13    Integer,
 14    LargeBinary,
 15    String,
 16    create_engine,
 17)
 18from sqlalchemy.exc import SQLAlchemyError
 19from sqlalchemy.orm import declarative_base
 20from sqlalchemy.orm import relationship, sessionmaker
 21from sqlalchemy.pool import QueuePool
 22
 23from corems.molecular_id.factory.molecularSQL import psycopg3_url
 24
 25Base = declarative_base()
 26
 27
 28class Metadatar(Base):
 29    """This class is used to store the metadata of the compounds in the database
 30
 31    Attributes
 32    -----------
 33    id : int
 34        The id of the compound.
 35    cas : str
 36        The CAS number of the compound.
 37    inchikey : str
 38        The InChiKey of the compound.
 39    inchi : str
 40        The InChi of the compound.
 41    chebi : str
 42        The ChEBI ID of the compound.
 43    smiles : str
 44        The SMILES of the compound.
 45    kegg : str
 46        The KEGG ID of the compound.
 47    iupac_name : str
 48        The IUPAC name of the compound.
 49    traditional_name : str
 50        The traditional name of the compound.
 51    common_name : str
 52        The common name of the compound.
 53    data_id : int
 54        The id of the compound in the molecularData table.
 55    data : LowResolutionEICompound
 56        The compound object.
 57    """
 58
 59    __tablename__ = "metaDataR"
 60
 61    id = Column(Integer, primary_key=True)
 62    cas = Column(String, nullable=True)
 63    inchikey = Column(String, nullable=False)
 64    inchi = Column(String, nullable=False)
 65    chebi = Column(String, nullable=True)
 66    smiles = Column(String, nullable=True)
 67    kegg = Column(String, nullable=True)
 68    iupac_name = Column(String, nullable=True)
 69    traditional_name = Column(String, nullable=True)
 70    common_name = Column(String, nullable=True)
 71
 72    data_id = Column(Integer, ForeignKey("molecularData.id"))
 73    data = relationship("LowResolutionEICompound", back_populates="metadatar")
 74
 75
 76class LowResolutionEICompound(Base):
 77    """This class is used to store the molecular and spectral data of the compounds in the low res EI database
 78
 79    Attributes
 80    -----------
 81    id : int
 82        The id of the compound.
 83    name : str
 84        The name of the compound.
 85    classify : str
 86        The classification of the compound.
 87    formula : str
 88        The formula of the compound.
 89    ri : float
 90        The retention index of the compound.
 91    retention_time : float
 92        The retention time of the compound.
 93    source : str
 94        The source of the compound.
 95    casno : str
 96        The CAS number of the compound.
 97    comment : str
 98        The comment of the compound.
 99    source_temp_c : float
100        The source temperature of the spectra.
101    ev : float
102        The electron volts of the spectra.
103    peaks_count : int
104        The number of peaks in the spectra.
105    mz : numpy.ndarray
106        The m/z values of the spectra.
107    abundance : numpy.ndarray
108        The abundance values of the spectra.
109    metadatar : Metadatar
110        The metadata object.
111    """
112
113    __tablename__ = "molecularData"
114
115    id = Column(Integer, primary_key=True)
116
117    name = Column(String, nullable=False)
118    classify = Column(String, nullable=True)
119    formula = Column(String, nullable=True)
120    ri = Column(Float, nullable=False)
121    retention_time = Column(Float, nullable=False)
122
123    source = Column(String, nullable=True)
124    casno = Column(String, nullable=False)
125    comment = Column(String, nullable=True)
126
127    derivativenum = Column(String, nullable=True)
128    derivatization = Column(String, nullable=True)
129
130    source_temp_c = Column(Float, nullable=True)
131    ev = Column(Float, nullable=True)
132
133    peaks_count = Column(Integer, nullable=False)
134
135    mz = Column(LargeBinary, nullable=False)
136    abundance = Column(LargeBinary, nullable=False)
137
138    metadatar = relationship("Metadatar", uselist=False, back_populates="data")
139
140    # metadatar = relationship('Metadatar', backref='smile', lazy='dynamic')
141
142    def __init__(self, **dict_data):
143        self.id = dict_data.get("id")
144
145        self.name = dict_data.get("NAME")
146        self.classify = dict_data.get("classify")
147        self.formula = dict_data.get("FORM")
148        self.ri = dict_data.get("RI")
149        self.retention_time = dict_data.get("RT")
150
151        self.source = dict_data.get("SOURCE")
152        self.casno = dict_data.get("CASNO")
153        self.comment = dict_data.get("COMMENT")
154
155        self.derivativenum = dict_data.get("derivativenum")
156        self.derivatization = dict_data.get("derivatization")
157
158        self.peaks_count = dict_data.get("NUM PEAKS")
159
160        # mz and abun are numpy arrays of 64 bits integer
161        # when using postgres array might be a better option
162
163        self.mz = array(dict_data.get("mz"), dtype="int32").tobytes()
164        self.abundance = array(dict_data.get("abundance"), dtype="int32").tobytes()
165
166        self.metadatar = dict_data.get("metadatar", None)
167
168    def __repr__(self):
169        return (
170            "<LowResolutionEICompound(name= %s , cas number = %s, formula = %s, Retention index= %.1f, Retention time= %.1f comment='%s')>"
171            % (
172                self.name,
173                self.casno,
174                self.formula,
175                self.ri,
176                self.retention_time,
177                self.comment,
178            )
179        )
180
181
182@dataclass
183class MetaboliteMetadata:
184    """Dataclass for the Metabolite Metadata
185
186    Attributes
187    -----------
188    id : int
189        The id of the compound.
190    cas : str
191        The CAS number of the compound.
192    inchikey : str
193        The InChiKey of the compound.
194    inchi : str
195        The InChi of the compound.
196    chebi : str
197        The ChEBI ID of the compound.
198    smiles : str
199        The SMILES of the compound.
200    kegg : str
201        The KEGG ID of the compound.
202    iupac_name : str
203        The IUPAC name of the compound.
204    traditional_name : str
205        The traditional name of the compound.
206    common_name : str
207        The common name of the compound, preferrably the RefMet name.
208    data_id : int
209        The id of the compound in the molecularData table.
210    name : Optional[str]
211        The name of the compound, preferably the same name as in PubChem,
212        Or if a lipid, name as in LipidMaps
213    formula : Optional[str]
214        The formula of the compound.
215    pubchem_id : Optional[str]
216        The PubChem ID of the compound.
217    refmet_id : Optional[str]
218        The RefMet ID of the compound.
219    """
220
221    id: int
222    cas: str
223    inchikey: str
224    inchi: str
225    chebi: str
226    smiles: str
227    kegg: str
228    data_id: int
229    iupac_name: str
230    traditional_name: str
231    common_name: str
232    name: Optional[str] = None
233    formula: Optional[str] = None
234    pubchem_id: Optional[str] = None
235    refmet_id: Optional[str] = None
236
237
238@dataclass
239class LowResCompoundRef:
240    """Dataclass for the Low Resolution Compound Reference
241
242    This class is used to store the molecular and spectral data of the compounds in the low res EI database
243
244    Parameters
245    -----------
246    compounds_dict : dict
247        A dictionary representing the compound.
248
249    Attributes
250    -----------
251    id : int
252        The id of the compound.
253    name : str
254        The name of the compound.
255    ri : str
256        The retention index of the compound.
257    retention_time : str
258        The retention time of the compound.
259    casno : str
260        The CAS number of the compound.
261    comment : str
262        The comment of the compound.
263    peaks_count : int
264        The number of peaks in the spectra.
265    classify : str
266        The classification of the compound.
267    derivativenum : str
268        The derivative number of the compound.
269    derivatization : str
270        The derivatization applied to the compound.
271    mz : numpy.ndarray
272        The m/z values of the spectra.
273    abundance : numpy.ndarray
274        The abundance values of the spectra.
275    source_temp_c : float
276        The source temperature of the spectra.
277    ev : float
278        The electron volts of the spectra.
279    formula : str
280        The formula of the compound.
281    source : str
282        The source of the spectra data.
283    classify : str
284        The classification of the compound.
285    metadata : MetaboliteMetadata
286        The metadata object.
287    similarity_score : float
288        The similarity score of the compound.
289    ri_score : float
290        The RI score of the compound.
291    spectral_similarity_score : float
292        The spectral similarity score of the compound.
293    spectral_similarity_scores : dict
294        The spectral similarity scores of the compound.
295
296    """
297
298    # this class is use to store the results inside the GCPeak class
299    def __init__(self, compounds_dict):
300        self.id = compounds_dict.get("id")
301        self.name = compounds_dict.get("name")
302        self.ri = compounds_dict.get("ri")
303        self.retention_time = compounds_dict.get("rt")
304        self.casno = compounds_dict.get("casno")
305        self.comment = compounds_dict.get("comment")
306        self.peaks_count = compounds_dict.get("peaks_count")
307
308        self.classify = compounds_dict.get("classify")
309        self.derivativenum = compounds_dict.get("derivativenum")
310        self.derivatization = compounds_dict.get("derivatization")
311
312        self.mz = compounds_dict.get("mz")
313        self.abundance = compounds_dict.get("abundance")
314
315        self.source_temp_c = compounds_dict.get("source_temp_c")
316        self.ev = compounds_dict.get("ev")
317        self.formula = compounds_dict.get("formula")
318        self.source = compounds_dict.get("source")
319
320        self.classify = compounds_dict.get("classify")
321
322        if compounds_dict.get("metadata"):
323            self.metadata = MetaboliteMetadata(**compounds_dict.get("metadata"))
324
325        else:
326            self.metadata = None
327
328        self.similarity_score = None
329        self.ri_score = None
330        self.spectral_similarity_score = None
331        self.spectral_similarity_scores = {}
332
333
334class EI_LowRes_SQLite:
335    """
336    A class for interacting with a SQLite database for low-resolution EI compounds.
337
338    Parameters
339    -----------
340    url : str, optional
341        The URL of the SQLite database. Default is 'sqlite://'.
342
343    Attributes
344    -----------
345    engine : sqlalchemy.engine.Engine
346        The SQLAlchemy engine for connecting to the database.
347    session : sqlalchemy.orm.Session
348        The SQLAlchemy session for executing database operations.
349
350    Methods
351    --------
352    * __init__(self, url='sqlite://').
353        Initializes the EI_LowRes_SQLite object.
354    * __exit__(self, exc_type, exc_val, exc_tb).
355        Closes the database connection.
356    * init_engine(self, url).
357        Initializes the SQLAlchemy engine.
358    * __enter__(self).
359        Returns the EI_LowRes_SQLite object.
360    * add_compound_list(self, data_dict_list).
361        Adds a list of compounds to the database.
362    * add_compound(self, data_dict).
363        Adds a single compound to the database.
364    * commit(self).
365        Commits the changes to the database.
366    * row_to_dict(self, row).
367        Converts a database row to a dictionary.
368    * get_all(self).
369        Retrieves all compounds from the database.
370    * query_min_max_rt(self, min_max_rt).
371        Queries compounds based on retention time range.
372    * query_min_max_ri(self, min_max_ri).
373        Queries compounds based on RI range.
374    * query_names_and_rt(self, min_max_rt, compound_names).
375        Queries compounds based on compound names and retention time range.
376    * query_min_max_ri_and_rt(self, min_max_ri, min_max_rt).
377        Queries compounds based on RI range and retention time range.
378    * delete_compound(self, compound).
379        Deletes a compound from the database.
380    * purge(self).
381        Deletes all compounds from the database table.
382    * clear_data(self).
383        Clears all tables in the database.
384    """
385
386    def __init__(self, url="sqlite://"):
387        self.engine = self.init_engine(url)
388
389        Base.metadata.create_all(self.engine)
390
391        Session = sessionmaker(bind=self.engine)
392
393        self.session = Session()
394
395    def __exit__(self, exc_type, exc_val, exc_tb):
396        """Closes the database connection."""
397        self.commit()
398        self.session.close()
399        self.engine.dispose()
400
401    def init_engine(self, url):
402        """Initializes the SQLAlchemy engine.
403
404        Parameters
405        -----------
406        url : str
407            The URL of the SQLite database.
408
409        Returns
410        --------
411        sqlalchemy.engine.Engine
412            The SQLAlchemy engine for connecting to the database.
413        """
414        directory = os.getcwd()
415        if not url:
416            if not os.path.isdir(directory + "/db"):
417                os.mkdir(directory + "/db")
418            url = "sqlite:///{DB}/db/pnnl_lowres_gcms_compounds.sqlite".format(
419                DB=directory
420            )
421        url = psycopg3_url(url)
422        return create_engine(url, poolclass=QueuePool)
423
424    def __enter__(self):
425        """Returns the EI_LowRes_SQLite object."""
426        return self
427
428    def add_compound_list(self, data_dict_list):
429        """Adds a list of compounds to the database.
430
431        Parameters
432        -----------
433        data_dict_list : list of dict
434            A list of dictionaries representing the compounds.
435        """
436        for data_dict in data_dict_list:
437            # print(data_dict.get('NUM PEAKS'))
438            if not data_dict.get("NUM PEAKS"):
439                data_dict["NUM PEAKS"] = len(data_dict.get("mz"))
440            if not data_dict.get("CASNO"):
441                data_dict["CASNO"] = data_dict.get("CAS")
442
443        self.session.add_all(
444            [LowResolutionEICompound(**data_dict) for data_dict in data_dict_list]
445        )
446
447    def add_compound(self, data_dict):
448        """Adds a single compound to the database.
449
450        Parameters
451        -----------
452        data_dict : dict
453            A dictionary representing the compound.
454
455        """
456        one_compound = LowResolutionEICompound(**data_dict)
457        self.session.add(one_compound)
458        self.commit()
459
460    def commit(self):
461        """Commits the changes to the database."""
462        try:
463            self.session.commit()
464        except SQLAlchemyError as e:
465            self.session.rollback()
466            print(str(e))
467
468    def row_to_dict(self, row):
469        """Converts a database row to a dictionary.
470
471        Parameters
472        -----------
473        row : sqlalchemy.engine.row.Row
474            A row from the database.
475
476        Returns
477        --------
478        dict
479            A dictionary representing the compound.
480        """
481        data_dict = {c.name: getattr(row, c.name) for c in row.__table__.columns}
482
483        data_dict["mz"] = frombuffer(data_dict.get("mz"), dtype="int32")
484        data_dict["abundance"] = frombuffer(data_dict.get("abundance"), dtype="int32")
485
486        if row.metadatar:
487            data_dict["metadata"] = {
488                c.name: getattr(row.metadatar, c.name)
489                for c in row.metadatar.__table__.columns
490            }
491
492        else:
493            data_dict["metadata"] = None
494
495        return data_dict
496
497    def get_all(
498        self,
499    ):
500        """Retrieves all compounds from the database.
501
502        Returns
503        --------
504        list
505            A list of dictionaries representing the compounds.
506        """
507        compounds = self.session.query(LowResolutionEICompound).all()
508
509        return [self.row_to_dict(compound) for compound in compounds]
510
511    def query_min_max_rt(
512        self,
513        min_max_rt,
514    ):
515        """Queries compounds based on retention time range.
516
517        Parameters
518        -----------
519        min_max_rt : tuple
520            A tuple containing the minimum and maximum retention time values.
521
522        Returns
523        --------
524        list
525            A list of dictionaries representing the compounds.
526        """
527        min_rt, max_rt = min_max_rt
528
529        compounds = self.session.query(LowResolutionEICompound).filter(
530            LowResolutionEICompound.retention_time.between(min_rt, max_rt)
531        )
532
533        return [self.row_to_dict(compound) for compound in compounds]
534
535    def query_min_max_ri(self, min_max_ri):
536        """Queries compounds based on RI range.
537
538        Parameters
539        -----------
540        min_max_ri : tuple
541            A tuple containing the minimum and maximum RI values.
542        """
543        min_ri, max_ri = min_max_ri
544
545        compounds = (
546            self.session.query(LowResolutionEICompound)
547            .filter(LowResolutionEICompound.ri.between(min_ri, max_ri))
548            .all()
549        )
550
551        return [self.row_to_dict(compound) for compound in compounds]
552
553    def query_names_and_rt(self, min_max_rt, compound_names):
554        """Queries compounds based on compound names and retention time range.
555
556        Parameters
557        -----------
558        min_max_rt : tuple
559            A tuple containing the minimum and maximum retention time values.
560        compound_names : list
561            A list of compound names.
562
563        Returns
564        --------
565        list
566            A list of dictionaries representing the compounds.
567
568        """
569        min_rt, max_rt = min_max_rt
570
571        compounds = (
572            self.session.query(LowResolutionEICompound)
573            .filter(LowResolutionEICompound.name.in_(compound_names))
574            .filter(
575                LowResolutionEICompound.retention_time >= min_rt,
576                LowResolutionEICompound.retention_time <= max_rt,
577            )
578        )
579
580        # self.session.query.select(LowResolutionEICompound).where(between(LowResolutionEICompound.ri, min_ri, max_ri))
581        # x = [self.row_to_dict(compound) for compound in compounds]
582
583        return [self.row_to_dict(compound) for compound in compounds]
584
585    def query_min_max_ri_and_rt(
586        self,
587        min_max_ri,
588        min_max_rt,
589    ):
590        """Queries compounds based on RI range and retention time range.
591
592        Parameters
593        -----------
594        min_max_ri : tuple
595            A tuple containing the minimum and maximum RI values.
596        min_max_rt : tuple
597            A tuple containing the minimum and maximum retention time values.
598
599        Returns
600        --------
601        list
602            A list of dictionaries representing the compounds.
603
604        """
605        min_ri, max_ri = min_max_ri
606
607        min_rt, max_rt = min_max_rt
608
609        compounds = self.session.query(LowResolutionEICompound).filter(
610            LowResolutionEICompound.ri <= max_ri,
611            LowResolutionEICompound.ri >= min_ri,
612            LowResolutionEICompound.ri >= min_rt,
613            LowResolutionEICompound.ri >= max_rt,
614        )
615
616        # self.session.query.select(LowResolutionEICompound).where(between(LowResolutionEICompound.ri, min_ri, max_ri))
617
618        return [self.row_to_dict(compound) for compound in compounds]
619
620    def delete_compound(self, compound):
621        """Deletes a compound from the database.
622
623        Parameters
624        -----------
625        compound : LowResolutionEICompound
626            A compound object.
627
628        """
629        try:
630            self.session.delete(compound)
631            self.session.commit()
632
633        except SQLAlchemyError as e:
634            self.session.rollback()
635            print(str(e))
636
637    def purge(self):
638        """Deletes all compounds from the database table.
639
640        Notes
641        ------
642        Careful, this will delete the entire database table.
643        """
644        self.session.query(LowResolutionEICompound).delete()
645        self.session.commit()
646
647    def clear_data(self):
648        """Clears all tables in the database."""
649        meta = Base.metadata
650        for table in reversed(meta.sorted_tables):
651            print("Clear table %s" % table)
652            self.session.execute(table.delete())
653        self.session.commit()
class Base(sqlalchemy.orm.decl_api._DynamicAttributesType, sqlalchemy.inspection.Inspectable[sqlalchemy.orm.mapper.Mapper[typing.Any]]):

The base class of the class hierarchy.

When called, it accepts no arguments and returns a new featureless instance that has no instance attributes and cannot be given any.

Base(**kwargs: Any) 
2275def _declarative_constructor(self: Any, **kwargs: Any) -> None:
2276    """A simple constructor that allows initialization from kwargs.
2277
2278    Sets attributes on the constructed instance using the names and
2279    values in ``kwargs``.
2280
2281    Only keys that are present as
2282    attributes of the instance's class are allowed. These could be,
2283    for example, any mapped columns or relationships.
2284    """
2285    cls_ = type(self)
2286    for k in kwargs:
2287        if not hasattr(cls_, k):
2288            raise TypeError(
2289                "%r is an invalid keyword argument for %s" % (k, cls_.__name__)
2290            )
2291        setattr(self, k, kwargs[k])

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

registry: sqlalchemy.orm.decl_api.registry = <sqlalchemy.orm.decl_api.registry object>
metadata: sqlalchemy.sql.schema.MetaData = MetaData()
class Metadatar(sqlalchemy.orm.decl_api._DynamicAttributesType, sqlalchemy.inspection.Inspectable[sqlalchemy.orm.mapper.Mapper[typing.Any]]): 
29class Metadatar(Base):
30    """This class is used to store the metadata of the compounds in the database
31
32    Attributes
33    -----------
34    id : int
35        The id of the compound.
36    cas : str
37        The CAS number of the compound.
38    inchikey : str
39        The InChiKey of the compound.
40    inchi : str
41        The InChi of the compound.
42    chebi : str
43        The ChEBI ID of the compound.
44    smiles : str
45        The SMILES of the compound.
46    kegg : str
47        The KEGG ID of the compound.
48    iupac_name : str
49        The IUPAC name of the compound.
50    traditional_name : str
51        The traditional name of the compound.
52    common_name : str
53        The common name of the compound.
54    data_id : int
55        The id of the compound in the molecularData table.
56    data : LowResolutionEICompound
57        The compound object.
58    """
59
60    __tablename__ = "metaDataR"
61
62    id = Column(Integer, primary_key=True)
63    cas = Column(String, nullable=True)
64    inchikey = Column(String, nullable=False)
65    inchi = Column(String, nullable=False)
66    chebi = Column(String, nullable=True)
67    smiles = Column(String, nullable=True)
68    kegg = Column(String, nullable=True)
69    iupac_name = Column(String, nullable=True)
70    traditional_name = Column(String, nullable=True)
71    common_name = Column(String, nullable=True)
72
73    data_id = Column(Integer, ForeignKey("molecularData.id"))
74    data = relationship("LowResolutionEICompound", back_populates="metadatar")

This class is used to store the metadata of the compounds in the database

Attributes
  • id (int): The id of the compound.
  • cas (str): The CAS number of the compound.
  • inchikey (str): The InChiKey of the compound.
  • inchi (str): The InChi of the compound.
  • chebi (str): The ChEBI ID of the compound.
  • smiles (str): The SMILES of the compound.
  • kegg (str): The KEGG ID of the compound.
  • iupac_name (str): The IUPAC name of the compound.
  • traditional_name (str): The traditional name of the compound.
  • common_name (str): The common name of the compound.
  • data_id (int): The id of the compound in the molecularData table.
  • data (LowResolutionEICompound): The compound object.
Metadatar(**kwargs) 
2def __init__(self, **kwargs):
3    new_state = class_manager._new_state_if_none(self)
4    if new_state:
5        return new_state._initialize_instance(self, **kwargs)
6    else:
7        return original_init(self, **kwargs)

A simple constructor that allows initialization from kwargs.

Sets attributes on the constructed instance using the names and values in kwargs.

Only keys that are present as attributes of the instance's class are allowed. These could be, for example, any mapped columns or relationships.

id
cas
inchikey
inchi
chebi
smiles
kegg
iupac_name
traditional_name
common_name
data_id
data
class LowResolutionEICompound(sqlalchemy.orm.decl_api._DynamicAttributesType, sqlalchemy.inspection.Inspectable[sqlalchemy.orm.mapper.Mapper[typing.Any]]): 
 77class LowResolutionEICompound(Base):
 78    """This class is used to store the molecular and spectral data of the compounds in the low res EI database
 79
 80    Attributes
 81    -----------
 82    id : int
 83        The id of the compound.
 84    name : str
 85        The name of the compound.
 86    classify : str
 87        The classification of the compound.
 88    formula : str
 89        The formula of the compound.
 90    ri : float
 91        The retention index of the compound.
 92    retention_time : float
 93        The retention time of the compound.
 94    source : str
 95        The source of the compound.
 96    casno : str
 97        The CAS number of the compound.
 98    comment : str
 99        The comment of the compound.
100    source_temp_c : float
101        The source temperature of the spectra.
102    ev : float
103        The electron volts of the spectra.
104    peaks_count : int
105        The number of peaks in the spectra.
106    mz : numpy.ndarray
107        The m/z values of the spectra.
108    abundance : numpy.ndarray
109        The abundance values of the spectra.
110    metadatar : Metadatar
111        The metadata object.
112    """
113
114    __tablename__ = "molecularData"
115
116    id = Column(Integer, primary_key=True)
117
118    name = Column(String, nullable=False)
119    classify = Column(String, nullable=True)
120    formula = Column(String, nullable=True)
121    ri = Column(Float, nullable=False)
122    retention_time = Column(Float, nullable=False)
123
124    source = Column(String, nullable=True)
125    casno = Column(String, nullable=False)
126    comment = Column(String, nullable=True)
127
128    derivativenum = Column(String, nullable=True)
129    derivatization = Column(String, nullable=True)
130
131    source_temp_c = Column(Float, nullable=True)
132    ev = Column(Float, nullable=True)
133
134    peaks_count = Column(Integer, nullable=False)
135
136    mz = Column(LargeBinary, nullable=False)
137    abundance = Column(LargeBinary, nullable=False)
138
139    metadatar = relationship("Metadatar", uselist=False, back_populates="data")
140
141    # metadatar = relationship('Metadatar', backref='smile', lazy='dynamic')
142
143    def __init__(self, **dict_data):
144        self.id = dict_data.get("id")
145
146        self.name = dict_data.get("NAME")
147        self.classify = dict_data.get("classify")
148        self.formula = dict_data.get("FORM")
149        self.ri = dict_data.get("RI")
150        self.retention_time = dict_data.get("RT")
151
152        self.source = dict_data.get("SOURCE")
153        self.casno = dict_data.get("CASNO")
154        self.comment = dict_data.get("COMMENT")
155
156        self.derivativenum = dict_data.get("derivativenum")
157        self.derivatization = dict_data.get("derivatization")
158
159        self.peaks_count = dict_data.get("NUM PEAKS")
160
161        # mz and abun are numpy arrays of 64 bits integer
162        # when using postgres array might be a better option
163
164        self.mz = array(dict_data.get("mz"), dtype="int32").tobytes()
165        self.abundance = array(dict_data.get("abundance"), dtype="int32").tobytes()
166
167        self.metadatar = dict_data.get("metadatar", None)
168
169    def __repr__(self):
170        return (
171            "<LowResolutionEICompound(name= %s , cas number = %s, formula = %s, Retention index= %.1f, Retention time= %.1f comment='%s')>"
172            % (
173                self.name,
174                self.casno,
175                self.formula,
176                self.ri,
177                self.retention_time,
178                self.comment,
179            )
180        )

This class is used to store the molecular and spectral data of the compounds in the low res EI database

Attributes
  • id (int): The id of the compound.
  • name (str): The name of the compound.
  • classify (str): The classification of the compound.
  • formula (str): The formula of the compound.
  • ri (float): The retention index of the compound.
  • retention_time (float): The retention time of the compound.
  • source (str): The source of the compound.
  • casno (str): The CAS number of the compound.
  • comment (str): The comment of the compound.
  • source_temp_c (float): The source temperature of the spectra.
  • ev (float): The electron volts of the spectra.
  • peaks_count (int): The number of peaks in the spectra.
  • mz (numpy.ndarray): The m/z values of the spectra.
  • abundance (numpy.ndarray): The abundance values of the spectra.
  • metadatar (Metadatar): The metadata object.
LowResolutionEICompound(**dict_data) 
2def __init__(self, **dict_data):
3    new_state = class_manager._new_state_if_none(self)
4    if new_state:
5        return new_state._initialize_instance(self, **dict_data)
6    else:
7        return original_init(self, **dict_data)
id
name
classify
formula
ri
retention_time
source
casno
comment
derivativenum
derivatization
source_temp_c
ev
peaks_count
mz
abundance
metadatar
@dataclass
class MetaboliteMetadata: 
183@dataclass
184class MetaboliteMetadata:
185    """Dataclass for the Metabolite Metadata
186
187    Attributes
188    -----------
189    id : int
190        The id of the compound.
191    cas : str
192        The CAS number of the compound.
193    inchikey : str
194        The InChiKey of the compound.
195    inchi : str
196        The InChi of the compound.
197    chebi : str
198        The ChEBI ID of the compound.
199    smiles : str
200        The SMILES of the compound.
201    kegg : str
202        The KEGG ID of the compound.
203    iupac_name : str
204        The IUPAC name of the compound.
205    traditional_name : str
206        The traditional name of the compound.
207    common_name : str
208        The common name of the compound, preferrably the RefMet name.
209    data_id : int
210        The id of the compound in the molecularData table.
211    name : Optional[str]
212        The name of the compound, preferably the same name as in PubChem,
213        Or if a lipid, name as in LipidMaps
214    formula : Optional[str]
215        The formula of the compound.
216    pubchem_id : Optional[str]
217        The PubChem ID of the compound.
218    refmet_id : Optional[str]
219        The RefMet ID of the compound.
220    """
221
222    id: int
223    cas: str
224    inchikey: str
225    inchi: str
226    chebi: str
227    smiles: str
228    kegg: str
229    data_id: int
230    iupac_name: str
231    traditional_name: str
232    common_name: str
233    name: Optional[str] = None
234    formula: Optional[str] = None
235    pubchem_id: Optional[str] = None
236    refmet_id: Optional[str] = None

Dataclass for the Metabolite Metadata

Attributes
  • id (int): The id of the compound.
  • cas (str): The CAS number of the compound.
  • inchikey (str): The InChiKey of the compound.
  • inchi (str): The InChi of the compound.
  • chebi (str): The ChEBI ID of the compound.
  • smiles (str): The SMILES of the compound.
  • kegg (str): The KEGG ID of the compound.
  • iupac_name (str): The IUPAC name of the compound.
  • traditional_name (str): The traditional name of the compound.
  • common_name (str): The common name of the compound, preferrably the RefMet name.
  • data_id (int): The id of the compound in the molecularData table.
  • name (Optional[str]): The name of the compound, preferably the same name as in PubChem, Or if a lipid, name as in LipidMaps
  • formula (Optional[str]): The formula of the compound.
  • pubchem_id (Optional[str]): The PubChem ID of the compound.
  • refmet_id (Optional[str]): The RefMet ID of the compound.
MetaboliteMetadata( id: int, cas: str, inchikey: str, inchi: str, chebi: str, smiles: str, kegg: str, data_id: int, iupac_name: str, traditional_name: str, common_name: str, name: Optional[str] = None, formula: Optional[str] = None, pubchem_id: Optional[str] = None, refmet_id: Optional[str] = None)
id: int
cas: str
inchikey: str
inchi: str
chebi: str
smiles: str
kegg: str
data_id: int
iupac_name: str
traditional_name: str
common_name: str
name: Optional[str] = None
formula: Optional[str] = None
pubchem_id: Optional[str] = None
refmet_id: Optional[str] = None
@dataclass
class LowResCompoundRef: 
239@dataclass
240class LowResCompoundRef:
241    """Dataclass for the Low Resolution Compound Reference
242
243    This class is used to store the molecular and spectral data of the compounds in the low res EI database
244
245    Parameters
246    -----------
247    compounds_dict : dict
248        A dictionary representing the compound.
249
250    Attributes
251    -----------
252    id : int
253        The id of the compound.
254    name : str
255        The name of the compound.
256    ri : str
257        The retention index of the compound.
258    retention_time : str
259        The retention time of the compound.
260    casno : str
261        The CAS number of the compound.
262    comment : str
263        The comment of the compound.
264    peaks_count : int
265        The number of peaks in the spectra.
266    classify : str
267        The classification of the compound.
268    derivativenum : str
269        The derivative number of the compound.
270    derivatization : str
271        The derivatization applied to the compound.
272    mz : numpy.ndarray
273        The m/z values of the spectra.
274    abundance : numpy.ndarray
275        The abundance values of the spectra.
276    source_temp_c : float
277        The source temperature of the spectra.
278    ev : float
279        The electron volts of the spectra.
280    formula : str
281        The formula of the compound.
282    source : str
283        The source of the spectra data.
284    classify : str
285        The classification of the compound.
286    metadata : MetaboliteMetadata
287        The metadata object.
288    similarity_score : float
289        The similarity score of the compound.
290    ri_score : float
291        The RI score of the compound.
292    spectral_similarity_score : float
293        The spectral similarity score of the compound.
294    spectral_similarity_scores : dict
295        The spectral similarity scores of the compound.
296
297    """
298
299    # this class is use to store the results inside the GCPeak class
300    def __init__(self, compounds_dict):
301        self.id = compounds_dict.get("id")
302        self.name = compounds_dict.get("name")
303        self.ri = compounds_dict.get("ri")
304        self.retention_time = compounds_dict.get("rt")
305        self.casno = compounds_dict.get("casno")
306        self.comment = compounds_dict.get("comment")
307        self.peaks_count = compounds_dict.get("peaks_count")
308
309        self.classify = compounds_dict.get("classify")
310        self.derivativenum = compounds_dict.get("derivativenum")
311        self.derivatization = compounds_dict.get("derivatization")
312
313        self.mz = compounds_dict.get("mz")
314        self.abundance = compounds_dict.get("abundance")
315
316        self.source_temp_c = compounds_dict.get("source_temp_c")
317        self.ev = compounds_dict.get("ev")
318        self.formula = compounds_dict.get("formula")
319        self.source = compounds_dict.get("source")
320
321        self.classify = compounds_dict.get("classify")
322
323        if compounds_dict.get("metadata"):
324            self.metadata = MetaboliteMetadata(**compounds_dict.get("metadata"))
325
326        else:
327            self.metadata = None
328
329        self.similarity_score = None
330        self.ri_score = None
331        self.spectral_similarity_score = None
332        self.spectral_similarity_scores = {}

Dataclass for the Low Resolution Compound Reference

This class is used to store the molecular and spectral data of the compounds in the low res EI database

Parameters
  • compounds_dict (dict): A dictionary representing the compound.
Attributes
  • id (int): The id of the compound.
  • name (str): The name of the compound.
  • ri (str): The retention index of the compound.
  • retention_time (str): The retention time of the compound.
  • casno (str): The CAS number of the compound.
  • comment (str): The comment of the compound.
  • peaks_count (int): The number of peaks in the spectra.
  • classify (str): The classification of the compound.
  • derivativenum (str): The derivative number of the compound.
  • derivatization (str): The derivatization applied to the compound.
  • mz (numpy.ndarray): The m/z values of the spectra.
  • abundance (numpy.ndarray): The abundance values of the spectra.
  • source_temp_c (float): The source temperature of the spectra.
  • ev (float): The electron volts of the spectra.
  • formula (str): The formula of the compound.
  • source (str): The source of the spectra data.
  • classify (str): The classification of the compound.
  • metadata (MetaboliteMetadata): The metadata object.
  • similarity_score (float): The similarity score of the compound.
  • ri_score (float): The RI score of the compound.
  • spectral_similarity_score (float): The spectral similarity score of the compound.
  • spectral_similarity_scores (dict): The spectral similarity scores of the compound.
LowResCompoundRef(compounds_dict) 
300    def __init__(self, compounds_dict):
301        self.id = compounds_dict.get("id")
302        self.name = compounds_dict.get("name")
303        self.ri = compounds_dict.get("ri")
304        self.retention_time = compounds_dict.get("rt")
305        self.casno = compounds_dict.get("casno")
306        self.comment = compounds_dict.get("comment")
307        self.peaks_count = compounds_dict.get("peaks_count")
308
309        self.classify = compounds_dict.get("classify")
310        self.derivativenum = compounds_dict.get("derivativenum")
311        self.derivatization = compounds_dict.get("derivatization")
312
313        self.mz = compounds_dict.get("mz")
314        self.abundance = compounds_dict.get("abundance")
315
316        self.source_temp_c = compounds_dict.get("source_temp_c")
317        self.ev = compounds_dict.get("ev")
318        self.formula = compounds_dict.get("formula")
319        self.source = compounds_dict.get("source")
320
321        self.classify = compounds_dict.get("classify")
322
323        if compounds_dict.get("metadata"):
324            self.metadata = MetaboliteMetadata(**compounds_dict.get("metadata"))
325
326        else:
327            self.metadata = None
328
329        self.similarity_score = None
330        self.ri_score = None
331        self.spectral_similarity_score = None
332        self.spectral_similarity_scores = {}
id
name
ri
retention_time
casno
comment
peaks_count
classify
derivativenum
derivatization
mz
abundance
source_temp_c
ev
formula
source
similarity_score
ri_score
spectral_similarity_score
spectral_similarity_scores
class EI_LowRes_SQLite: 
335class EI_LowRes_SQLite:
336    """
337    A class for interacting with a SQLite database for low-resolution EI compounds.
338
339    Parameters
340    -----------
341    url : str, optional
342        The URL of the SQLite database. Default is 'sqlite://'.
343
344    Attributes
345    -----------
346    engine : sqlalchemy.engine.Engine
347        The SQLAlchemy engine for connecting to the database.
348    session : sqlalchemy.orm.Session
349        The SQLAlchemy session for executing database operations.
350
351    Methods
352    --------
353    * __init__(self, url='sqlite://').
354        Initializes the EI_LowRes_SQLite object.
355    * __exit__(self, exc_type, exc_val, exc_tb).
356        Closes the database connection.
357    * init_engine(self, url).
358        Initializes the SQLAlchemy engine.
359    * __enter__(self).
360        Returns the EI_LowRes_SQLite object.
361    * add_compound_list(self, data_dict_list).
362        Adds a list of compounds to the database.
363    * add_compound(self, data_dict).
364        Adds a single compound to the database.
365    * commit(self).
366        Commits the changes to the database.
367    * row_to_dict(self, row).
368        Converts a database row to a dictionary.
369    * get_all(self).
370        Retrieves all compounds from the database.
371    * query_min_max_rt(self, min_max_rt).
372        Queries compounds based on retention time range.
373    * query_min_max_ri(self, min_max_ri).
374        Queries compounds based on RI range.
375    * query_names_and_rt(self, min_max_rt, compound_names).
376        Queries compounds based on compound names and retention time range.
377    * query_min_max_ri_and_rt(self, min_max_ri, min_max_rt).
378        Queries compounds based on RI range and retention time range.
379    * delete_compound(self, compound).
380        Deletes a compound from the database.
381    * purge(self).
382        Deletes all compounds from the database table.
383    * clear_data(self).
384        Clears all tables in the database.
385    """
386
387    def __init__(self, url="sqlite://"):
388        self.engine = self.init_engine(url)
389
390        Base.metadata.create_all(self.engine)
391
392        Session = sessionmaker(bind=self.engine)
393
394        self.session = Session()
395
396    def __exit__(self, exc_type, exc_val, exc_tb):
397        """Closes the database connection."""
398        self.commit()
399        self.session.close()
400        self.engine.dispose()
401
402    def init_engine(self, url):
403        """Initializes the SQLAlchemy engine.
404
405        Parameters
406        -----------
407        url : str
408            The URL of the SQLite database.
409
410        Returns
411        --------
412        sqlalchemy.engine.Engine
413            The SQLAlchemy engine for connecting to the database.
414        """
415        directory = os.getcwd()
416        if not url:
417            if not os.path.isdir(directory + "/db"):
418                os.mkdir(directory + "/db")
419            url = "sqlite:///{DB}/db/pnnl_lowres_gcms_compounds.sqlite".format(
420                DB=directory
421            )
422        url = psycopg3_url(url)
423        return create_engine(url, poolclass=QueuePool)
424
425    def __enter__(self):
426        """Returns the EI_LowRes_SQLite object."""
427        return self
428
429    def add_compound_list(self, data_dict_list):
430        """Adds a list of compounds to the database.
431
432        Parameters
433        -----------
434        data_dict_list : list of dict
435            A list of dictionaries representing the compounds.
436        """
437        for data_dict in data_dict_list:
438            # print(data_dict.get('NUM PEAKS'))
439            if not data_dict.get("NUM PEAKS"):
440                data_dict["NUM PEAKS"] = len(data_dict.get("mz"))
441            if not data_dict.get("CASNO"):
442                data_dict["CASNO"] = data_dict.get("CAS")
443
444        self.session.add_all(
445            [LowResolutionEICompound(**data_dict) for data_dict in data_dict_list]
446        )
447
448    def add_compound(self, data_dict):
449        """Adds a single compound to the database.
450
451        Parameters
452        -----------
453        data_dict : dict
454            A dictionary representing the compound.
455
456        """
457        one_compound = LowResolutionEICompound(**data_dict)
458        self.session.add(one_compound)
459        self.commit()
460
461    def commit(self):
462        """Commits the changes to the database."""
463        try:
464            self.session.commit()
465        except SQLAlchemyError as e:
466            self.session.rollback()
467            print(str(e))
468
469    def row_to_dict(self, row):
470        """Converts a database row to a dictionary.
471
472        Parameters
473        -----------
474        row : sqlalchemy.engine.row.Row
475            A row from the database.
476
477        Returns
478        --------
479        dict
480            A dictionary representing the compound.
481        """
482        data_dict = {c.name: getattr(row, c.name) for c in row.__table__.columns}
483
484        data_dict["mz"] = frombuffer(data_dict.get("mz"), dtype="int32")
485        data_dict["abundance"] = frombuffer(data_dict.get("abundance"), dtype="int32")
486
487        if row.metadatar:
488            data_dict["metadata"] = {
489                c.name: getattr(row.metadatar, c.name)
490                for c in row.metadatar.__table__.columns
491            }
492
493        else:
494            data_dict["metadata"] = None
495
496        return data_dict
497
498    def get_all(
499        self,
500    ):
501        """Retrieves all compounds from the database.
502
503        Returns
504        --------
505        list
506            A list of dictionaries representing the compounds.
507        """
508        compounds = self.session.query(LowResolutionEICompound).all()
509
510        return [self.row_to_dict(compound) for compound in compounds]
511
512    def query_min_max_rt(
513        self,
514        min_max_rt,
515    ):
516        """Queries compounds based on retention time range.
517
518        Parameters
519        -----------
520        min_max_rt : tuple
521            A tuple containing the minimum and maximum retention time values.
522
523        Returns
524        --------
525        list
526            A list of dictionaries representing the compounds.
527        """
528        min_rt, max_rt = min_max_rt
529
530        compounds = self.session.query(LowResolutionEICompound).filter(
531            LowResolutionEICompound.retention_time.between(min_rt, max_rt)
532        )
533
534        return [self.row_to_dict(compound) for compound in compounds]
535
536    def query_min_max_ri(self, min_max_ri):
537        """Queries compounds based on RI range.
538
539        Parameters
540        -----------
541        min_max_ri : tuple
542            A tuple containing the minimum and maximum RI values.
543        """
544        min_ri, max_ri = min_max_ri
545
546        compounds = (
547            self.session.query(LowResolutionEICompound)
548            .filter(LowResolutionEICompound.ri.between(min_ri, max_ri))
549            .all()
550        )
551
552        return [self.row_to_dict(compound) for compound in compounds]
553
554    def query_names_and_rt(self, min_max_rt, compound_names):
555        """Queries compounds based on compound names and retention time range.
556
557        Parameters
558        -----------
559        min_max_rt : tuple
560            A tuple containing the minimum and maximum retention time values.
561        compound_names : list
562            A list of compound names.
563
564        Returns
565        --------
566        list
567            A list of dictionaries representing the compounds.
568
569        """
570        min_rt, max_rt = min_max_rt
571
572        compounds = (
573            self.session.query(LowResolutionEICompound)
574            .filter(LowResolutionEICompound.name.in_(compound_names))
575            .filter(
576                LowResolutionEICompound.retention_time >= min_rt,
577                LowResolutionEICompound.retention_time <= max_rt,
578            )
579        )
580
581        # self.session.query.select(LowResolutionEICompound).where(between(LowResolutionEICompound.ri, min_ri, max_ri))
582        # x = [self.row_to_dict(compound) for compound in compounds]
583
584        return [self.row_to_dict(compound) for compound in compounds]
585
586    def query_min_max_ri_and_rt(
587        self,
588        min_max_ri,
589        min_max_rt,
590    ):
591        """Queries compounds based on RI range and retention time range.
592
593        Parameters
594        -----------
595        min_max_ri : tuple
596            A tuple containing the minimum and maximum RI values.
597        min_max_rt : tuple
598            A tuple containing the minimum and maximum retention time values.
599
600        Returns
601        --------
602        list
603            A list of dictionaries representing the compounds.
604
605        """
606        min_ri, max_ri = min_max_ri
607
608        min_rt, max_rt = min_max_rt
609
610        compounds = self.session.query(LowResolutionEICompound).filter(
611            LowResolutionEICompound.ri <= max_ri,
612            LowResolutionEICompound.ri >= min_ri,
613            LowResolutionEICompound.ri >= min_rt,
614            LowResolutionEICompound.ri >= max_rt,
615        )
616
617        # self.session.query.select(LowResolutionEICompound).where(between(LowResolutionEICompound.ri, min_ri, max_ri))
618
619        return [self.row_to_dict(compound) for compound in compounds]
620
621    def delete_compound(self, compound):
622        """Deletes a compound from the database.
623
624        Parameters
625        -----------
626        compound : LowResolutionEICompound
627            A compound object.
628
629        """
630        try:
631            self.session.delete(compound)
632            self.session.commit()
633
634        except SQLAlchemyError as e:
635            self.session.rollback()
636            print(str(e))
637
638    def purge(self):
639        """Deletes all compounds from the database table.
640
641        Notes
642        ------
643        Careful, this will delete the entire database table.
644        """
645        self.session.query(LowResolutionEICompound).delete()
646        self.session.commit()
647
648    def clear_data(self):
649        """Clears all tables in the database."""
650        meta = Base.metadata
651        for table in reversed(meta.sorted_tables):
652            print("Clear table %s" % table)
653            self.session.execute(table.delete())
654        self.session.commit()

A class for interacting with a SQLite database for low-resolution EI compounds.

Parameters
  • url (str, optional): The URL of the SQLite database. Default is 'sqlite://'.
Attributes
  • engine (sqlalchemy.engine.Engine): The SQLAlchemy engine for connecting to the database.
  • session (sqlalchemy.orm.Session): The SQLAlchemy session for executing database operations.
Methods
  • __init__(self, url='sqlite://'). Initializes the EI_LowRes_SQLite object.
  • __exit__(self, exc_type, exc_val, exc_tb). Closes the database connection.
  • init_engine(self, url). Initializes the SQLAlchemy engine.
  • __enter__(self). Returns the EI_LowRes_SQLite object.
  • add_compound_list(self, data_dict_list). Adds a list of compounds to the database.
  • add_compound(self, data_dict). Adds a single compound to the database.
  • commit(self). Commits the changes to the database.
  • row_to_dict(self, row). Converts a database row to a dictionary.
  • get_all(self). Retrieves all compounds from the database.
  • query_min_max_rt(self, min_max_rt). Queries compounds based on retention time range.
  • query_min_max_ri(self, min_max_ri). Queries compounds based on RI range.
  • query_names_and_rt(self, min_max_rt, compound_names). Queries compounds based on compound names and retention time range.
  • query_min_max_ri_and_rt(self, min_max_ri, min_max_rt). Queries compounds based on RI range and retention time range.
  • delete_compound(self, compound). Deletes a compound from the database.
  • purge(self). Deletes all compounds from the database table.
  • clear_data(self). Clears all tables in the database.
EI_LowRes_SQLite(url='sqlite://') 
387    def __init__(self, url="sqlite://"):
388        self.engine = self.init_engine(url)
389
390        Base.metadata.create_all(self.engine)
391
392        Session = sessionmaker(bind=self.engine)
393
394        self.session = Session()
engine
session
def init_engine(self, url): 
402    def init_engine(self, url):
403        """Initializes the SQLAlchemy engine.
404
405        Parameters
406        -----------
407        url : str
408            The URL of the SQLite database.
409
410        Returns
411        --------
412        sqlalchemy.engine.Engine
413            The SQLAlchemy engine for connecting to the database.
414        """
415        directory = os.getcwd()
416        if not url:
417            if not os.path.isdir(directory + "/db"):
418                os.mkdir(directory + "/db")
419            url = "sqlite:///{DB}/db/pnnl_lowres_gcms_compounds.sqlite".format(
420                DB=directory
421            )
422        url = psycopg3_url(url)
423        return create_engine(url, poolclass=QueuePool)

Initializes the SQLAlchemy engine.

Parameters
  • url (str): The URL of the SQLite database.
Returns
  • sqlalchemy.engine.Engine: The SQLAlchemy engine for connecting to the database.
def add_compound_list(self, data_dict_list): 
429    def add_compound_list(self, data_dict_list):
430        """Adds a list of compounds to the database.
431
432        Parameters
433        -----------
434        data_dict_list : list of dict
435            A list of dictionaries representing the compounds.
436        """
437        for data_dict in data_dict_list:
438            # print(data_dict.get('NUM PEAKS'))
439            if not data_dict.get("NUM PEAKS"):
440                data_dict["NUM PEAKS"] = len(data_dict.get("mz"))
441            if not data_dict.get("CASNO"):
442                data_dict["CASNO"] = data_dict.get("CAS")
443
444        self.session.add_all(
445            [LowResolutionEICompound(**data_dict) for data_dict in data_dict_list]
446        )

Adds a list of compounds to the database.

Parameters
  • data_dict_list (list of dict): A list of dictionaries representing the compounds.
def add_compound(self, data_dict): 
448    def add_compound(self, data_dict):
449        """Adds a single compound to the database.
450
451        Parameters
452        -----------
453        data_dict : dict
454            A dictionary representing the compound.
455
456        """
457        one_compound = LowResolutionEICompound(**data_dict)
458        self.session.add(one_compound)
459        self.commit()

Adds a single compound to the database.

Parameters
  • data_dict (dict): A dictionary representing the compound.
def commit(self): 
461    def commit(self):
462        """Commits the changes to the database."""
463        try:
464            self.session.commit()
465        except SQLAlchemyError as e:
466            self.session.rollback()
467            print(str(e))

Commits the changes to the database.

def row_to_dict(self, row): 
469    def row_to_dict(self, row):
470        """Converts a database row to a dictionary.
471
472        Parameters
473        -----------
474        row : sqlalchemy.engine.row.Row
475            A row from the database.
476
477        Returns
478        --------
479        dict
480            A dictionary representing the compound.
481        """
482        data_dict = {c.name: getattr(row, c.name) for c in row.__table__.columns}
483
484        data_dict["mz"] = frombuffer(data_dict.get("mz"), dtype="int32")
485        data_dict["abundance"] = frombuffer(data_dict.get("abundance"), dtype="int32")
486
487        if row.metadatar:
488            data_dict["metadata"] = {
489                c.name: getattr(row.metadatar, c.name)
490                for c in row.metadatar.__table__.columns
491            }
492
493        else:
494            data_dict["metadata"] = None
495
496        return data_dict

Converts a database row to a dictionary.

Parameters
  • row (sqlalchemy.engine.row.Row): A row from the database.
Returns
  • dict: A dictionary representing the compound.
def get_all(self): 
498    def get_all(
499        self,
500    ):
501        """Retrieves all compounds from the database.
502
503        Returns
504        --------
505        list
506            A list of dictionaries representing the compounds.
507        """
508        compounds = self.session.query(LowResolutionEICompound).all()
509
510        return [self.row_to_dict(compound) for compound in compounds]

Retrieves all compounds from the database.

Returns
  • list: A list of dictionaries representing the compounds.
def query_min_max_rt(self, min_max_rt): 
512    def query_min_max_rt(
513        self,
514        min_max_rt,
515    ):
516        """Queries compounds based on retention time range.
517
518        Parameters
519        -----------
520        min_max_rt : tuple
521            A tuple containing the minimum and maximum retention time values.
522
523        Returns
524        --------
525        list
526            A list of dictionaries representing the compounds.
527        """
528        min_rt, max_rt = min_max_rt
529
530        compounds = self.session.query(LowResolutionEICompound).filter(
531            LowResolutionEICompound.retention_time.between(min_rt, max_rt)
532        )
533
534        return [self.row_to_dict(compound) for compound in compounds]

Queries compounds based on retention time range.

Parameters
  • min_max_rt (tuple): A tuple containing the minimum and maximum retention time values.
Returns
  • list: A list of dictionaries representing the compounds.
def query_min_max_ri(self, min_max_ri): 
536    def query_min_max_ri(self, min_max_ri):
537        """Queries compounds based on RI range.
538
539        Parameters
540        -----------
541        min_max_ri : tuple
542            A tuple containing the minimum and maximum RI values.
543        """
544        min_ri, max_ri = min_max_ri
545
546        compounds = (
547            self.session.query(LowResolutionEICompound)
548            .filter(LowResolutionEICompound.ri.between(min_ri, max_ri))
549            .all()
550        )
551
552        return [self.row_to_dict(compound) for compound in compounds]

Queries compounds based on RI range.

Parameters
  • min_max_ri (tuple): A tuple containing the minimum and maximum RI values.
def query_names_and_rt(self, min_max_rt, compound_names): 
554    def query_names_and_rt(self, min_max_rt, compound_names):
555        """Queries compounds based on compound names and retention time range.
556
557        Parameters
558        -----------
559        min_max_rt : tuple
560            A tuple containing the minimum and maximum retention time values.
561        compound_names : list
562            A list of compound names.
563
564        Returns
565        --------
566        list
567            A list of dictionaries representing the compounds.
568
569        """
570        min_rt, max_rt = min_max_rt
571
572        compounds = (
573            self.session.query(LowResolutionEICompound)
574            .filter(LowResolutionEICompound.name.in_(compound_names))
575            .filter(
576                LowResolutionEICompound.retention_time >= min_rt,
577                LowResolutionEICompound.retention_time <= max_rt,
578            )
579        )
580
581        # self.session.query.select(LowResolutionEICompound).where(between(LowResolutionEICompound.ri, min_ri, max_ri))
582        # x = [self.row_to_dict(compound) for compound in compounds]
583
584        return [self.row_to_dict(compound) for compound in compounds]

Queries compounds based on compound names and retention time range.

Parameters
  • min_max_rt (tuple): A tuple containing the minimum and maximum retention time values.
  • compound_names (list): A list of compound names.
Returns
  • list: A list of dictionaries representing the compounds.
def query_min_max_ri_and_rt(self, min_max_ri, min_max_rt): 
586    def query_min_max_ri_and_rt(
587        self,
588        min_max_ri,
589        min_max_rt,
590    ):
591        """Queries compounds based on RI range and retention time range.
592
593        Parameters
594        -----------
595        min_max_ri : tuple
596            A tuple containing the minimum and maximum RI values.
597        min_max_rt : tuple
598            A tuple containing the minimum and maximum retention time values.
599
600        Returns
601        --------
602        list
603            A list of dictionaries representing the compounds.
604
605        """
606        min_ri, max_ri = min_max_ri
607
608        min_rt, max_rt = min_max_rt
609
610        compounds = self.session.query(LowResolutionEICompound).filter(
611            LowResolutionEICompound.ri <= max_ri,
612            LowResolutionEICompound.ri >= min_ri,
613            LowResolutionEICompound.ri >= min_rt,
614            LowResolutionEICompound.ri >= max_rt,
615        )
616
617        # self.session.query.select(LowResolutionEICompound).where(between(LowResolutionEICompound.ri, min_ri, max_ri))
618
619        return [self.row_to_dict(compound) for compound in compounds]

Queries compounds based on RI range and retention time range.

Parameters
  • min_max_ri (tuple): A tuple containing the minimum and maximum RI values.
  • min_max_rt (tuple): A tuple containing the minimum and maximum retention time values.
Returns
  • list: A list of dictionaries representing the compounds.
def delete_compound(self, compound): 
621    def delete_compound(self, compound):
622        """Deletes a compound from the database.
623
624        Parameters
625        -----------
626        compound : LowResolutionEICompound
627            A compound object.
628
629        """
630        try:
631            self.session.delete(compound)
632            self.session.commit()
633
634        except SQLAlchemyError as e:
635            self.session.rollback()
636            print(str(e))

Deletes a compound from the database.

Parameters
  • compound (LowResolutionEICompound): A compound object.
def purge(self): 
638    def purge(self):
639        """Deletes all compounds from the database table.
640
641        Notes
642        ------
643        Careful, this will delete the entire database table.
644        """
645        self.session.query(LowResolutionEICompound).delete()
646        self.session.commit()

Deletes all compounds from the database table.

Notes

Careful, this will delete the entire database table.

def clear_data(self): 
648    def clear_data(self):
649        """Clears all tables in the database."""
650        meta = Base.metadata
651        for table in reversed(meta.sorted_tables):
652            print("Clear table %s" % table)
653            self.session.execute(table.delete())
654        self.session.commit()

Clears all tables in the database.