corems.molecular_id.factory.molecularSQL

  1import json
  2import os
  3
  4import tqdm
  5from sqlalchemy import (
  6    Column,
  7    Float,
  8    ForeignKey,
  9    Integer,
 10    String,
 11    and_,
 12    create_engine,
 13    event,
 14    exc,
 15    func,
 16)
 17from sqlalchemy.exc import SQLAlchemyError
 18from sqlalchemy.ext.associationproxy import association_proxy
 19from sqlalchemy.orm import declarative_base
 20from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property
 21from sqlalchemy.orm import backref, relationship
 22from sqlalchemy.orm.scoping import scoped_session
 23from sqlalchemy.orm.session import sessionmaker
 24from sqlalchemy.engine.url import make_url
 25from sqlalchemy.sql.operators import exists
 26from sqlalchemy.sql.schema import UniqueConstraint
 27
 28from corems.encapsulation.constant import Atoms, Labels
 29
 30_PSYCOPG3_DRIVERS = {"postgresql", "postgres", "postgresql+psycopg2"}
 31
 32
 33def psycopg3_url(url):
 34    """Return ``url`` using the psycopg 3 dialect when it is a legacy Postgres URL.
 35
 36    SQLAlchemy's default Postgres driver is psycopg 3. CoreMS does not install
 37    psycopg2, so a bare ``postgresql://`` or ``postgres://`` URL and a legacy
 38    ``postgresql+psycopg2://`` URL are rewritten to ``postgresql+psycopg://``.
 39    sqlite and any other explicit driver are returned unchanged.
 40
 41    Parameters
 42    ----------
 43    url : str or None
 44        Database URL. ``None``, ``""``, ``"None"``, and ``"False"`` are
 45        returned as given.
 46
 47    Returns
 48    -------
 49    str or None
 50        Rewritten URL, or ``url`` when no rewrite applies.
 51    """
 52    if url is None or url == "" or url == "None" or url == "False":
 53        return url
 54    parsed = make_url(url)
 55    if parsed.drivername in _PSYCOPG3_DRIVERS:
 56        parsed = parsed.set(drivername="postgresql+psycopg")
 57    return parsed.render_as_string(hide_password=False)
 58
 59
 60Base = declarative_base()
 61
 62
 63class HeteroAtoms(Base):
 64    """HeteroAtoms class for the heteroAtoms table in the SQLite database.
 65
 66    Attributes
 67    ----------
 68    id : int
 69        The primary key for the table.
 70    name : str
 71        The name of the heteroAtoms class.
 72    halogensCount : int
 73        The number of halogens in the heteroAtoms class.
 74    carbonHydrogen : relationship
 75        The relationship to the carbonHydrogen table.
 76
 77    Methods
 78    -------
 79    * __repr__()
 80        Returns the string representation of the object.
 81    * to_dict()
 82        Returns the heteroAtoms class as a dictionary.
 83    * halogens_count()
 84        Returns the number of halogens as a float.
 85
 86
 87    """
 88
 89    __tablename__ = "heteroAtoms"
 90
 91    id = Column(Integer, primary_key=True, unique=True, nullable=False)
 92
 93    name = Column(String, unique=True, nullable=False)
 94
 95    halogensCount = Column(Integer, unique=False, nullable=False)
 96
 97    carbonHydrogen = relationship(
 98        "CarbonHydrogen", secondary="molecularformula", viewonly=True
 99    )
100
101    def __repr__(self):
102        return "<HeteroAtoms Model {} class {}>".format(self.id, self.name)
103
104    @hybrid_property
105    def halogens_count(cls):
106        """Returns the number of halogens as a float."""
107        return cls.halogensCount.cast(Float)
108
109    def to_dict(self):
110        """Returns the heteroAtoms class as a dictionary."""
111        return json.loads(self.name)
112
113
114class CarbonHydrogen(Base):
115    """CarbonHydrogen class for the carbonHydrogen table in the SQLite database.
116
117    Attributes
118    ----------
119    id : int
120        The primary key for the table.
121    C : int
122        The number of carbon atoms.
123    H : int
124        The number of hydrogen atoms.
125    heteroAtoms : relationship
126        The relationship to the heteroAtoms table.
127
128    Methods
129    -------
130    * __repr__()
131        Returns the string representation of the object.
132    * mass()
133        Returns the mass of the carbonHydrogen class as a float.
134    * c()
135        Returns the number of carbon atoms as a float.
136    * h()
137        Returns the number of hydrogen atoms as a float.
138    * dbe()
139        Returns the double bond equivalent as a float.
140
141    """
142
143    __tablename__ = "carbonHydrogen"
144    __table_args__ = (UniqueConstraint("C", "H", name="unique_c_h"),)
145
146    id = Column(Integer, primary_key=True, unique=True, nullable=False)
147
148    C = Column(Integer, nullable=False)
149
150    H = Column(Integer, nullable=False)
151
152    heteroAtoms = relationship(
153        "HeteroAtoms", secondary="molecularformula", viewonly=True
154    )
155
156    def __repr__(self):
157        """Returns the string representation of the object."""
158        return "<CarbonHydrogen Model {} C{} H{}>".format(self.id, self.C, self.H)
159
160    @property
161    def mass(self):
162        """Returns the mass of the carbonHydrogen class as a float."""
163        return (self.C * Atoms.atomic_masses.get("C")) + (
164            self.H * Atoms.atomic_masses.get("H")
165        )
166
167    @hybrid_property
168    def c(cls):
169        """Returns the number of carbon atoms as a float."""
170        return cls.C.cast(Float)
171
172    @hybrid_property
173    def h(cls):
174        """Returns the number of hydrogen atoms as a float."""
175        return cls.H.cast(Float)
176
177    @hybrid_property
178    def dbe(cls):
179        """Returns the double bond equivalent as a float."""
180        # return cls.C.cast(Float) - (cls.H.cast(Float) / 2) + 1
181        return float(cls.C) - float(cls.H / 2) + 1
182
183
184# 264888.88 ms
185class MolecularFormulaLink(Base):
186    """MolecularFormulaLink class for the molecularformula table in the SQLite database.
187
188    Attributes
189    ----------
190    heteroAtoms_id : int
191        The foreign key for the heteroAtoms table.
192    carbonHydrogen_id : int
193        The foreign key for the carbonHydrogen table.
194    mass : float
195        The mass of the molecular formula.
196    DBE : float
197        The double bond equivalent of the molecular formula.
198    carbonHydrogen : relationship
199        The relationship to the carbonHydrogen table.
200    heteroAtoms : relationship
201        The relationship to the heteroAtoms table.
202    C : association_proxy
203        The association proxy for the carbonHydrogen table.
204    H : association_proxy
205        The association proxy for the carbonHydrogen table.
206    classe : association_proxy
207        The association proxy for the heteroAtoms table.
208
209    Methods
210    -------
211    * __repr__()
212        Returns the string representation of the object.
213    * to_dict()
214        Returns the molecular formula as a dictionary.
215    * formula_string()
216        Returns the molecular formula as a string.
217    * classe_string()
218        Returns the heteroAtoms class as a string.
219    * _adduct_mz(ion_charge, adduct_atom)
220        Returns the m/z of the adduct ion as a float.
221    * _protonated_mz(ion_charge)
222        Returns the m/z of the protonated ion as a float.
223    * _radical_mz(ion_charge)
224        Returns the m/z of the radical ion as a float.
225
226
227
228    """
229
230    __tablename__ = "molecularformula"
231    __table_args__ = (
232        UniqueConstraint("heteroAtoms_id", "carbonHydrogen_id", name="unique_molform"),
233    )
234
235    # id = Column(Integer, primary_key=True,
236    #                    unique=True,
237    #                    nullable=False)
238
239    heteroAtoms_id = Column(Integer, ForeignKey("heteroAtoms.id"), primary_key=True)
240
241    carbonHydrogen_id = Column(
242        Integer, ForeignKey("carbonHydrogen.id"), primary_key=True
243    )
244
245    mass = Column(Float)
246
247    DBE = Column(Float)
248
249    carbonHydrogen = relationship(CarbonHydrogen, backref=backref("heteroAtoms_assoc"))
250
251    heteroAtoms = relationship(HeteroAtoms, backref=backref("carbonHydrogen_assoc"))
252
253    C = association_proxy("carbonHydrogen", "C")
254
255    H = association_proxy("carbonHydrogen", "H")
256
257    classe = association_proxy("heteroAtoms", "name")
258
259    def to_dict(self):
260        """Returns the molecular formula as a dictionary.
261
262        Returns
263        -------
264        dict
265            The molecular formula as a dictionary.
266        """
267        carbon = {"C": self.C, "H": self.H}
268        classe = json.loads(self.classe)
269        if self.classe == '{"HC": ""}':
270            return {**carbon}
271        else:
272            return {**carbon, **classe}
273
274    @property
275    def formula_string(self):
276        """Returns the molecular formula as a string."""
277        class_dict = self.to_dict()
278        class_str = " ".join(
279            [atom + str(class_dict[atom]) for atom in class_dict.keys()]
280        )
281        return class_str.strip()
282
283    @property
284    def classe_string(self):
285        """Returns the heteroAtoms class as a string."""
286        class_dict = json.loads(self.classe)
287        class_str = " ".join(
288            [atom + str(class_dict[atom]) for atom in class_dict.keys()]
289        )
290        return class_str.strip()
291
292    @hybrid_method
293    def _adduct_mz(self, ion_charge, adduct_atom):
294        """Returns the m/z of the adduct ion as a float."""
295        return (
296            self.mass
297            + (Atoms.atomic_masses.get(adduct_atom))
298            + (ion_charge * -1 * Atoms.electron_mass)
299        ) / abs(ion_charge)
300
301    @hybrid_method
302    def _protonated_mz(self, ion_charge):
303        """Returns the m/z of the protonated ion as a float."""
304        return (
305            self.mass
306            + (ion_charge * Atoms.atomic_masses.get("H"))
307            + (ion_charge * -1 * Atoms.electron_mass)
308        ) / abs(ion_charge)
309
310    @hybrid_method
311    def _radical_mz(self, ion_charge):
312        """Returns the m/z of the radical ion as a float."""
313        return (self.mass + (ion_charge * -1 * Atoms.electron_mass)) / abs(ion_charge)
314
315    def __repr__(self):
316        """Returns the string representation of the object."""
317        return "<MolecularFormulaLink Model {}>".format(self.formula_string)
318
319
320class MolForm_SQL:
321    """MolForm_SQL class for the SQLite database.
322
323    Attributes
324    ----------
325    engine : sqlalchemy.engine.base.Engine
326        The SQLAlchemy engine.
327    session : sqlalchemy.orm.session.Session
328        The SQLAlchemy session.
329    type : str
330        The type of database.
331    chunks_count : int
332        The number of chunks to use when querying the database.
333
334    Methods
335    -------
336    * __init__(url=None, echo=False)
337        Initializes the database.
338    * __exit__(exc_type, exc_val, exc_tb)
339        Closes the database.
340    * initiate_database(url, database_name)
341        Creates the database.
342    * commit()
343        Commits the session.
344    * init_engine(url)
345        Initializes the SQLAlchemy engine.
346    * __enter__()
347
348    * get_dict_by_classes(classes, ion_type, nominal_mzs, ion_charge, molecular_search_settings, adducts=None)
349        Returns a dictionary of molecular formulas.
350    * check_entry(classe, ion_type, molecular_search_settings)
351        Checks if a molecular formula is in the database.
352    * get_all_classes()
353        Returns a list of all classes in the database.
354    * get_all()
355        Returns a list of all molecular formulas in the database.
356    * delete_entry(row)
357        Deletes a molecular formula from the database.
358    * purge(cls)
359        Deletes all molecular formulas from the database.
360    * clear_data()
361        Clears the database.
362    * close(commit=True)
363        Closes the database.
364    * add_engine_pidguard(engine)
365        Adds multiprocessing guards.
366
367    """
368
369    def __init__(self, url=None, echo=False):
370        self.engine = self.init_engine(url)
371
372        self.add_engine_pidguard(self.engine)
373
374        session_factory = sessionmaker(bind=self.engine)
375
376        Session = scoped_session(session_factory)
377
378        self.session = session_factory()
379
380        Base.metadata.create_all(self.engine)
381
382        self.session.commit()
383
384    def __exit__(self, exc_type, exc_val, exc_tb):
385        """Closes the database.
386
387        Parameters
388        ----------
389        exc_type : str
390            The exception type.
391        exc_val : str
392            The exception value.
393        exc_tb : str
394            The exception traceback.
395        """
396        # make sure the dbconnection gets closed
397
398        self.commit()
399        self.session.close()
400        self.engine.dispose()
401
402    def initiate_database(self, url, database_name):  # CREATION
403        """Creates the database.
404
405        Parameters
406        ----------
407        url : str
408            The URL for the database.
409        database_name : str
410            The name of the database.
411        """
412        engine = create_engine(psycopg3_url(url))
413        conn = engine.connect()
414        conn.execute("commit")
415        conn.execute("create database " + database_name)
416        conn.close()
417
418    def commit(self):
419        """Commits the session."""
420        try:
421            self.session.commit()
422        except SQLAlchemyError as e:
423            self.session.rollback()
424            print(str(e))
425
426    def init_engine(self, url):
427        """Initializes the SQLAlchemy engine.
428
429        Parameters
430        ----------
431        url : str
432            The URL for the database.
433
434        Returns
435        -------
436        sqlalchemy.engine.base.Engine
437            The SQLAlchemy engine.
438
439        """
440        if not url or url == "None" or url == "False":
441            directory = os.getcwd()
442
443            if not os.path.isdir(directory + "/db"):
444                os.mkdir(directory + "/db")
445
446            url = "sqlite:///{DB}/db/molformulas.sqlite".format(DB=directory)
447
448        url = psycopg3_url(url)
449
450        if url[0:6] == "sqlite":
451            self.type = "sqlite"
452        else:
453            self.type = "normal"
454
455        if url[0:6] == "sqlite":
456            engine = create_engine(url, echo=False)
457            self.chunks_count = 50
458
459        elif url[0:10] == "postgresql" or url[0:8] == "postgres":
460            # postgresql
461            self.chunks_count = 50000
462            engine = create_engine(url, echo=False, isolation_level="AUTOCOMMIT")
463
464        return engine  # poolclass=NullPool
465
466    def __enter__(self):
467        """Returns the object."""
468        return self
469
470    def get_dict_by_classes(
471        self,
472        classes,
473        ion_type,
474        nominal_mzs,
475        ion_charge,
476        molecular_search_settings,
477        adducts=None,
478    ):
479        """Returns a dictionary of molecular formulas.
480
481        Parameters
482        ----------
483        classes : list
484            The list of classes.
485        ion_type : str
486            The ion type.
487        nominal_mzs : list
488            The list of nominal m/z values.
489        ion_charge : int
490            The ion charge.
491        molecular_search_settings : MolecularFormulaSearchSettings
492            The molecular formula search settings.
493        adducts : list, optional
494            The list of adducts. Default is None.
495
496        Returns
497        -------
498        dict
499            The dictionary of molecular formulas.
500
501        Notes
502        -----
503        Known issue, when using SQLite:
504        if the number of classes and nominal_m/zs are higher than 999 the query will fail
505        Solution: use postgres or split query
506        """
507        verbose = molecular_search_settings.verbose_processing
508
509        def query_normal(class_list, len_adduct):
510            """query for normal database
511
512            Parameters
513            ----------
514            class_list : list
515                The list of classes.
516            len_adduct : int
517                The length of the adduct.
518
519            Returns
520            -------
521            sqlalchemy.orm.query.Query
522                The query.
523            """
524            base_query = (
525                self.session.query(MolecularFormulaLink, CarbonHydrogen, HeteroAtoms)
526                .filter(MolecularFormulaLink.carbonHydrogen_id == CarbonHydrogen.id)
527                .filter(MolecularFormulaLink.heteroAtoms_id == HeteroAtoms.id)
528            )
529
530            return base_query.filter(
531                and_(
532                    HeteroAtoms.name.in_(class_list),
533                    and_(
534                        MolecularFormulaLink.DBE >= molecular_search_settings.min_dbe,
535                        MolecularFormulaLink.DBE <= molecular_search_settings.max_dbe,
536                        and_(
537                            (
538                                (
539                                    CarbonHydrogen.h
540                                    + HeteroAtoms.halogens_count
541                                    - len_adduct
542                                )
543                                / CarbonHydrogen.c
544                            )
545                            >= molecular_search_settings.min_hc_filter,
546                            (
547                                (
548                                    CarbonHydrogen.h
549                                    + HeteroAtoms.halogens_count
550                                    - len_adduct
551                                )
552                                / CarbonHydrogen.c
553                            )
554                            <= molecular_search_settings.max_hc_filter,
555                            CarbonHydrogen.C
556                            >= molecular_search_settings.usedAtoms.get("C")[0],
557                            CarbonHydrogen.c
558                            <= molecular_search_settings.usedAtoms.get("C")[1],
559                            CarbonHydrogen.h
560                            >= molecular_search_settings.usedAtoms.get("H")[0],
561                            CarbonHydrogen.h
562                            <= molecular_search_settings.usedAtoms.get("H")[1],
563                        ),
564                    ),
565                )
566            )
567
568        def add_dict_formula(formulas, ion_type, ion_charge, adduct_atom=None):
569            """add molecular formula to dict
570
571            Parameters
572            ----------
573            formulas : sqlalchemy.orm.query.Query
574                The query.
575            ion_type : str
576                The ion type.
577            ion_charge : int
578                The ion charge.
579            adduct_atom : str, optional
580                The adduct atom. Default is None.
581
582            Returns
583            -------
584            dict
585                The dictionary of molecular formulas.
586
587            """
588            "organize data by heteroatom classes"
589            dict_res = {}
590
591            def nominal_mass_by_ion_type(formula_obj):
592                if ion_type == Labels.protonated_de_ion:
593                    return int(formula_obj._protonated_mz(ion_charge))
594
595                elif ion_type == Labels.radical_ion:
596                    return int(formula_obj._radical_mz(ion_charge))
597
598                elif ion_type == Labels.adduct_ion and adduct_atom:
599                    return int(formula_obj._adduct_mz(ion_charge, adduct_atom))
600
601            for formula_obj, ch_obj, classe_obj in tqdm.tqdm(
602                formulas, desc="Loading molecular formula database", disable=not verbose
603            ):
604                nominal_mz = nominal_mass_by_ion_type(formula_obj)
605
606                if self.type != "normal":
607                    if nominal_mz not in nominal_mzs:
608                        continue
609                classe = classe_obj.name
610
611                # classe_str = formula.classe_string
612
613                # pbar.set_description_str(desc="Loading molecular formula database for class %s " % classe_str)
614
615                formula_dict = formula_obj.to_dict()
616
617                if formula_dict.get("O"):
618                    if (
619                        formula_dict.get("O") / formula_dict.get("C")
620                        >= molecular_search_settings.max_oc_filter
621                    ):
622                        # print(formula_dict.get("O") / formula_dict.get("C"), molecular_search_settings.max_oc_filter)
623                        continue
624                    elif (
625                        formula_dict.get("O") / formula_dict.get("C")
626                        <= molecular_search_settings.min_oc_filter
627                    ):
628                        # print(formula_dict.get("O") / formula_dict.get("C"), molecular_search_settings.min_oc_filter)
629                        continue
630                    # if formula_dict.get("P"):
631
632                    #    if  not (formula_dict.get("O") -2)/ formula_dict.get("P") >= molecular_search_settings.min_op_filter:
633
634                    #        continue
635
636                if classe in dict_res.keys():
637                    if nominal_mz in dict_res[classe].keys():
638                        dict_res.get(classe).get(nominal_mz).append(formula_obj)
639
640                    else:
641                        dict_res.get(classe)[nominal_mz] = [formula_obj]
642
643                else:
644                    dict_res[classe] = {nominal_mz: [formula_obj]}
645
646            return dict_res
647
648        len_adducts = 0
649        if ion_type == Labels.adduct_ion:
650            len_adducts = 1
651
652        query = query_normal(classes, len_adducts)
653
654        if ion_type == Labels.protonated_de_ion:
655            if self.type == "normal":
656                query = query.filter(
657                    func.floor(MolecularFormulaLink._protonated_mz(ion_charge)).in_(
658                        nominal_mzs
659                    )
660                )
661
662            return add_dict_formula(query, ion_type, ion_charge)
663
664        if ion_type == Labels.radical_ion:
665            if self.type == "normal":
666                query = query.filter(
667                    func.floor(MolecularFormulaLink._radical_mz(ion_charge)).in_(
668                        nominal_mzs
669                    )
670                )
671            return add_dict_formula(query, ion_type, ion_charge)
672
673        if ion_type == Labels.adduct_ion:
674            dict_res = {}
675            if adducts:
676                for atom in adducts:
677                    if self.type == "normal":
678                        query = query.filter(
679                            func.floor(
680                                MolecularFormulaLink._adduct_mz(ion_charge, atom)
681                            ).in_(nominal_mzs)
682                        )
683                    dict_res[atom] = add_dict_formula(
684                        query, ion_type, ion_charge, adduct_atom=atom
685                    )
686                return dict_res
687        # dump all objs to memory
688        self.session.expunge_all()
689
690    def check_entry(self, classe, ion_type, molecular_search_settings):
691        """Checks if a molecular formula is in the database.
692
693        Parameters
694        ----------
695        classe : str
696            The class of the molecular formula.
697        ion_type : str
698            The ion type.
699        molecular_search_settings : MolecularFormulaSearchSettings
700            The molecular formula search settings.
701
702        Returns
703        -------
704        sqlalchemy.orm.query.Query
705            The query.
706        """
707        #  get all classes, ion_type, ion charge as str add to a dict or list
708        #  then check if class in database
709        has_class = self.session.query(
710            exists().where((MolecularFormulaLink.classe == classe))
711        )
712
713        return has_class
714
715    def get_all_classes(self):
716        """Returns a list of all classes in the database."""
717        query = self.session.query(
718            MolecularFormulaLink.classe.distinct().label("classe")
719        )
720
721        return query.all()
722
723    def get_all(
724        self,
725    ):
726        """Returns a list of all molecular formulas in the database."""
727        mol_formulas = self.session.query(MolecularFormulaLink).all()
728
729        return mol_formulas
730
731    def delete_entry(self, row):
732        """Deletes a molecular formula from the database."""
733        try:
734            self.session.delete(row)
735            self.session.commit()
736
737        except SQLAlchemyError as e:
738            self.session.rollback()
739            print(str(e))
740
741    def purge(self, cls):
742        """Deletes all molecular formulas from the database.
743
744        Notes
745        -------
746        Careful, this will delete the entire database table
747
748        """
749        self.session.query(cls).delete()
750        self.session.commit()
751
752    def clear_data(self):
753        """Clears the database."""
754        meta = Base.metadata
755        for table in reversed(meta.sorted_tables):
756            print("Clear table %s" % table)
757            self.session.execute(table.delete())
758        self.session.commit()
759
760    def close(self, commit=True):
761        """Closes the database.
762
763        Parameters
764        ----------
765        commit : bool, optional
766            Whether to commit the session. Default is True.
767        """
768        # make sure the dbconnection gets closed
769
770        if commit:
771            self.commit()
772        self.session.close()
773        self.engine.dispose()
774
775    def add_engine_pidguard(self, engine):
776        """Adds multiprocessing guards.
777
778        Forces a connection to be reconnected if it is detected
779        as having been shared to a sub-process.
780
781        Parameters
782        ----------
783        engine : sqlalchemy.engine.base.Engine
784            The SQLAlchemy engine.
785
786        """
787        import os
788        import warnings
789
790        @event.listens_for(engine, "connect")
791        def connect(dbapi_connection, connection_record):
792            """Forces a connection to be reconnected if it is detected
793
794            Parameters
795            ----------
796            dbapi_connection : sqlalchemy.engine.base.Engine
797                The SQLAlchemy engine.
798            connection_record : sqlalchemy.engine.base.Engine
799                The SQLAlchemy engine.
800            """
801            connection_record.info["pid"] = os.getpid()
802
803        @event.listens_for(engine, "checkout")
804        def checkout(dbapi_connection, connection_record, connection_proxy):
805            """Forces a connection to be reconnected if it is detected
806
807            Parameters
808            ----------
809            dbapi_connection : sqlalchemy.engine.base.Engine
810                The SQLAlchemy engine.
811            connection_record : sqlalchemy.engine.base.Engine
812                The SQLAlchemy engine.
813            connection_proxy : sqlalchemy.engine.base.Engine
814                The SQLAlchemy engine.
815
816            Raises
817            ------
818            exc.DisconnectionError
819                If the connection record belongs to a different process.
820
821            """
822            pid = os.getpid()
823            if connection_record.info["pid"] != pid:
824                # substitute log.debug() or similar here as desired
825                warnings.warn(
826                    "Parent process %(orig)s forked (%(newproc)s) with an open "
827                    "database connection, "
828                    "which is being discarded and recreated."
829                    % {"newproc": pid, "orig": connection_record.info["pid"]}
830                )
831                connection_record.connection = connection_proxy.connection = None
832                raise exc.DisconnectionError(
833                    "Connection record belongs to pid %s, "
834                    "attempting to check out in pid %s"
835                    % (connection_record.info["pid"], pid)
836                )
837
838
839if __name__ == "__main__":
840    sql = MolForm_SQL(url="sqlite:///")
841
842    dict_data = {"name": '{"O": 12}'}
843    dict_data2 = {"name": '{"O": 13}'}
844    hetero_obj = HeteroAtoms(**dict_data)
845    hetero_obj2 = HeteroAtoms(**dict_data2)
846    sql.session.add(hetero_obj)
847    sql.session.add(hetero_obj2)
848
849    print(sql.session.query(HeteroAtoms).all())
850    # molecular_search_settings = MolecularFormulaSearchSettings()
851    # sql = MolForm_SQL()
852    # query = sql.session.query(MolecularFormulaLink).filter_by(classe = '{"O": 12}').filter(MolecularFormulaLink._adduct_mz(+2, "Na") < 250)
853    # query = sql.get_by_classe('{"O": 12}', molecular_search_settings).filter(MolecularFormulaLink._adduct_mz(+2, "Na") < 250)
854    # classes = ['{"O": 12}']*1
855    # for i, classe in enumerate(classes):
856    # query = sql.get_by_classe(classe, molecular_search_settings)
857    # query = sql.session.query(MolecularFormulaLink).filter_by(classe = '{"O": 12}').filter(MolecularFormulaLink._adduct_mz(+2, "Na") < 250)
858    # for i in query.filter(MolecularFormulaLink.mass < 250):
859
860    #    print(i._radical_mz(-1), i._protonated_mz(-1), i._adduct_mz(+2, "Na"), i.mass, i.to_dict(), i.formula_string)
861    #