Back ArrowExplore all kits

How to use the union() function using SQLAlchemy

share link

by vsasikalabe dot icon Updated: Nov 8, 2023

technology logo
technology logo

Solution Kit Solution Kit  

UNION eliminates duplicates from the resultset. We can do it while combining the results of two or more SELECT statements. The number of columns and datatype must be the same in both tables. 

 

UNION helps write two separate SELECT statements. So, we can have the results of one statement displayed in the same table. SQL has strict rules for adding data. The INTERSECT operator will take precedence over the UNION. EXCEPT operators is a combination of operators in the same query. SQLAlchemy has two distinct components. It is the Core and the ORM.  

 

The UNION operator helps combine the data from the result. This is of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results. Union can remove duplicate rows from the table, while Union All cannot. Union's performance could be faster due to the elimination of duplicate rows.


But Union All's performance is faster as it does not remove duplicate values. Unions can be handy because they talk to peripherals through some memory-mapped registers. C unions help save memory. The SQL UNION helps display the results of two or more SELECT statements. We can combine SQL UNION records into new rows.  

 

Normally, MySQL supports stacked queries. It's impossible to execute a query in PHP-MySQL applications because of the db layer. But, the MySQL client may support this. A primary purpose is to serve as an alternate within a SQL statement generated by the ORM. We can add limiting criteria to a certain entity in a query. We will apply it to the entity as it appears in the SELECT query.  

  

A query expression is in the form of a select list. It is a second query expression that follows the UNION, INTERSECT, or EXCEPT operator. A good way of using Unions is to combine multiple fields into one column. It is either from the same or different views or sources. We can build many applications strictly on Core. It uses the SQL expression system to make exact control over database interactions. The Union will be faster. It passes the first SELECT statement after it parses the SELECT statement. It adds the results to the end of the output table.  

Preview of the output that you will get on running this code from your IDE.

Code

In this solution, we used the SQLAlchemy library.

How to create a Union table inside the SQL database using SQLAlchemy?

PythondotLines of Code : 107dotLicense : Strong Copyleft (CC BY-SA 4.0)

from sqlalchemy import Column, String, create_engine, event, inspect, select, table, union
from sqlalchemy.ext import compiler
from sqlalchemy.orm import Session, declarative_base
from sqlalchemy.schema import DDLElement


# boilerplate from https://github.com/sqlalchemy/sqlalchemy/wiki/Views
class CreateView(DDLElement):
    def __init__(self, name, selectable):
        self.name = name
        self.selectable = selectable


class DropView(DDLElement):
    def __init__(self, name):
        self.name = name


@compiler.compiles(CreateView)
def _create_view(element, compiler, **kw):
    return "CREATE VIEW %s AS %s" % (
        element.name,
        compiler.sql_compiler.process(element.selectable, literal_binds=True),
    )


@compiler.compiles(DropView)
def _drop_view(element, compiler, **kw):
    return "DROP VIEW %s" % (element.name)


def view_exists(ddl, target, connection, **kw):
    return ddl.name in inspect(connection).get_view_names()


def view_doesnt_exist(ddl, target, connection, **kw):
    return not view_exists(ddl, target, connection, **kw)


def view(name, metadata, selectable):
    t = table(name)

    t._columns._populate_separate_keys(
        col._make_proxy(t) for col in selectable.selected_columns
    )

    event.listen(
        metadata,
        "after_create",
        CreateView(name, selectable).execute_if(callable_=view_doesnt_exist),
    )
    event.listen(
        metadata,
        "before_drop",
        DropView(name).execute_if(callable_=view_exists),
    )
    return t


# demo
Base = declarative_base()


class Equity(Base):
    __tablename__ = "equities"

    id = Column(String, primary_key=True)
    currency = Column(String, nullable=False)


class Bond(Base):
    __tablename__ = "bonds"

    id = Column(String, primary_key=True)
    currency = Column(String, nullable=False)


common_view = view(
    "bonds_equities_union_view",
    Base.metadata,
    union(
        select(Equity.id.label("id"), Equity.currency.label("currency")),
        select(Bond.id.label("id"), Bond.currency.label("currency")),
    ),
)


engine = create_engine("sqlite://", echo=True, future=True)

Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all(
        [
            Equity(id="AAA", currency="EUR"),
            Equity(id="AAB", currency="USD"),
            Bond(id="AAA", currency="EUR"),
            Equity(id="EEF", currency="GBP"),
        ]
    )
    session.commit()

with Session(engine) as session:
    results = session.execute(select(common_view)).all()

print(results)  # [('AAA', 'EUR'), ('AAB', 'USD'), ('EEF', 'GBP')]

Instructions

Follow the steps carefully to get the output easily.

  1. Download and Install the PyCharm Community Edition on your computer.
  2. Open the terminal and install the required libraries with the following commands.
  3. Install SQLAlchemy - pip install SQLAlchemy.
  4. Create a new Python file on your IDE.
  5. Copy the snippet using the 'copy' button and paste it into your python file.
  6. Run the current file to generate the output.


I hope you found this useful. I have added the link to dependent libraries, and version information in the following sections.


I found this code snippet by searching for ' How to create a Union table inside the SQL database using SQLAlchemy?' in Kandi. You can try any such use case!

Environment Tested

I tested this solution in the following versions. Be mindful of changes when working with other versions.

  1. The solution is created in PyCharm 2022.3.
  2. The solution is tested on Python 3.11.1
  3. SQLAlchemy version- 2.0.22


Using this solution, we are able to use the union() function using SQLAlchemy with simple steps. This process also facilitates an easy-way-to use hassle-free method to create a hands-on working version of code which would help us to use the union() function using SQLAlchemy.

Dependent Libraries

sqlalchemyby sqlalchemy

Python doticonstar image 7352 doticonVersion:rel_2_0_16doticon
License: Permissive (MIT)

The Database Toolkit for Python

Support
    Quality
      Security
        License
          Reuse

            sqlalchemyby sqlalchemy

            Python doticon star image 7352 doticonVersion:rel_2_0_16doticon License: Permissive (MIT)

            The Database Toolkit for Python
            Support
              Quality
                Security
                  License
                    Reuse

                      If you do not have the SQLAlchemy library that is required to run this code, you can install it by clicking on the above link.

                      You can search for any dependent library on Kandi like SQLAlchemy.

                      FAQ:    

                      1. What is SQLAlchemy ORM, and how does it relate to the Union select statement?   

                      The ORM loading of entities from SELECT statements. It comes from other sources. The main use case is that of a textual SELECT statement. We can represent the SQLAlchemy using the text() construct.  

                         

                      2. Can we combine multiple queries when using Union Select with SQLAlchemy?   

                      This metadata information helps query the table to get the Union of the three queries. We first prepare individual queries. Then, we can pass these queries as parameters in the sqlalchemy. union() method.  

                         

                      3. How can one optimize their queries utilizing unions selects and sqlalchemy ORM?  

                      • Load Fewer Fields.  
                      • Skip the ORM.  
                      • Lazy loading at Mapping.  
                      • Eager loading at Mapping.  
                      • Write Raw SQL.  
                      • Add indexes.  
                      • Load Fewer Fields.  
                      • Skip the ORM.  

                         

                      4. When is it most beneficial to use a left outer join versus a standard SQL join in conjunction with Union select?  

                      The INNER JOIN helps return only records having pairs on both sides. The LEFT JOIN helps when you need all records from the "left" table. No matter if they have a pair in the "right" table or not.  

                      Advantages:   

                      • Left joins are useful for combining the data from multiple tables. But it includes rows that have a match in the leftmost table.   
                      • This join type is useful for retrieving all rows from the leftmost table. It also helps even if no matching rows exist in the other table.  

                         

                      5. How do you define data types when using Union Select with SQLAlchemy?   

                      The UNION operator helps combine the data. This is from the result of two or more SELECT command queries into a single distinct result set. This operator removes any duplicates present in the results.  

                      Support

                      1. For any support on kandi solution kits, please use the chat
                      2. For further learning resources, visit the Open Weaver Community learning page.

                      See similar Kits and Libraries

                      Open Weaver – Develop Applications Faster with Open Source

                      kandi

                      Community and Support

                      Company

                      Follow

                      • © 2023 Open Weaver Inc.