Inserts, Updates, Deletes in SQLAlchemy 1.4/2.0 Core – News Couple
ANALYTICS

Inserts, Updates, Deletes in SQLAlchemy 1.4/2.0 Core


Introduction

SQLAlchemy is a library of Python or a Database Toolkit that consists of comprehensive tools to work with databases. It has two portions ie, The Core and The ORM. You can use Python code directly to perform operations such as Create, Read, Update and Delete. This helps in speeding up the development time and it can often make managing data less error-prone.

SQLAlchemy Core consists of SQL Expression Language. The SQL Expression Language allows us to create SQL expressions and further these expressions will be executed against a targeted database and finally returns a dataset.

Before performing any operation it is necessary to first connect to the database. So, to establish the connection, we will be creating an object called Engine. This object establishes a connection with the database. We use the method create_engine() to create the Engine object.

Creating an Engine Object

from sqlalchemy import create_engine

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

Creating a Table Using MetaData

We then create an object of MetaData() to create a Table. In this example, I am creating a user_account table that stores the ‘username’ and ’emailid’ and contains a primary key ‘id’.

from sqlalchemy import MetaData

from sqlalchemy import Table,Column

from sqlalchemy import Integer, String

metadata=MetaData()

user_table=Table(

“user_account”,

metadata,

Column(‘id’,Integer,primary_key=True),

Column(“username”,String(50),nullable=False),

Column(“emailid”, String(255)),

)

engine=create_engine(“sqlite+pysqlite:///:memory:”, echo=True, future=True)

with engine.begin() as conn:

metadata.create_all(conn)

Result

We can also use constraints on a Table. In the below example, I am creating a Foreign Key constraint which we will be using further in this guide. To create a Foreign Key constraint, we import ForeignKey from sqlalchemy as shown below.

from sqlalchemy import ForeignKey

address_table = Table(

“address”,

metadata,

Column(“id”, Integer, primary_key=True),

Column(“address”, String(100), nullable=False),

Column(“user_id”, ForeignKey(“user_account.id”), nullable=False),

)

with engine.begin() as conn:

address_table.create(conn)

with engine.begin() as conn:

metadata.create_all(conn)

Result

Result 2

Inserting Values ​​to the Table

The insert() function is used to create an insert object which can be further used to perform insert operations on the table.

syntax:

insert(table[, values, inline, bind, ...]**dialect_kw)

Example:

from sqlalchemy import insert
stmt = (insert(user_table).values(name="username", fullname="Full Username"))

Parameters

  • table: The database table where the values ​​are to be inserted.
  • values: Set of values ​​to be inserted
  • inline: If set to True, the SQL expressions will be rendered inline without the need for pre-execution.

Example

#creating an insert statement
insert_stmt=user_table.insert().values(
    username="spongebob", emailid="[email protected],ail.com")
#executing the insert_stmt
with engine.begin() as connection:
connection.execute(insert_stmt)
Example

In the above example, we are using the insert statement with the target table and VALUES clauses at once and then executing it using the engine object connection.

Another way of inserting values ​​is as below in which the VALUES clause is generated automatically.

with engine.begin() as connection:
    connection.execute(
        user_table.insert(),"username":"sandy","emailid":"[email protected]"
)

or

with engine.begin() as connection:

connection.execute(

user_table.insert(),[“username”:”patrick”,”emailid”:”[email protected]”,

)

So far, we have inserted 4 different rows into the Table.

Updating Values

The update() function is used to generate any Update statement which updates an existing table in the database.

Syntax:

update(table[, whereclause, values, inline, ...], **dialect_kw)

Example:

from sqlalchemy import update
stmt = (update(user_table).where(user_table.c.id == 3).values(name="User 3"))

Parameters

  • table: This refers to the database table that is to be updated
  • where clause: describes the where condition of the update statement
  • Values: Optional dictionary which specifies the SET condition of the Update.
  • inline: If True, the Columns with the default keyword will not be pre-executed and will be compiled inline.
  • preserve_parameter_order: If this is set to True, the parameters will be received only via the Update.values() method. The parameters must be passed as a Python list of 2-tuples.

A basic update statement looks like this in which we are updating the email id of a user where the username matches “sandy” and then executing the statement.

with engine.begin() as connection:

update_stmt=(user_table.update().

where(user_table.c.username==”sandy”))

result=connection.execute(update_stmt)

Parameters

We can also make use of column expressions in the update statement as below:

with engine.begin() as connection:
    update_stmt=(user_table.update().
                 values(emailid=user_table.c.username+"e:"+user_table.c.emailid))
result=connection.execute(update_stmt)
Parameters 2

Update using bindparam() where many parameter sets can be invoked using the same statement.

from sqlalchemy import update
from sqlalchemy import bindparam
with engine.begin() as connection:
    update_stmt = update(user_table).where(user_table.c.username == bindparam('oldname')).values(username=bindparam('newname'))
    connection.execute(update_stmt,
                   [
                       'oldname': 'Mary', 'newname': 'marie',
                       'oldname': 'patrick', 'newname': 'patryk',
                   ]
                   )

Results

Parameters 3

Correlated Updates

A correlated subquery can be used in the update statement by using rows of another table.

from sqlalchemy import select
scalar_subq=(
    select(address_table.c.address).
    where(address_table.c.user_id == user_table.c.id).
    order_by(address_table.c.id).
    limit(1).
    scalar_subquery())
update_stmt = update(user_table).values(username=scalar_subq)
print(update_stmt)
Correlated Updates

Update…FROM

When we use additional tables in the where clause, it automatically generates an UPDATE FROM clause as you can see in the result

update_stmt = (
    update(user_table).
    where(user_table.c.id == address_table.c.user_id).
    where(address_table.c.address == 'India').
    values(username="Patrik")
  )
print(update_stmt)
Update...FROM

Deleting Values

The delete() function is used to create any delete statement that deletes rows from the table.

Syntax:

Example:

from sqlalchemy import deletestmt = (delete(user_table).where(user_table.c.id == 3))

Parameters

  • table: The database table from
    where the rows are to be deleted
  • where clause: An optional SQL expression
    that describes the where condition of the DELETE statement.

A simple delete statement is like the below:

from sqlalchemy import delete
delete_stmt=delete(user_table).where(user_table.c.username=="marie")
print(delete_stmt)

Results

Result 3

Deleting from multiple tables

Correlated subqueries including the WHERE clause can be used with the delete() function to delete rows that are related to multiple tables.

delete_stmt = delete(user_table).where(user_table.c.id == address_table.c.user_id).where(address_table.c.address == "India")
print(delete_stmt)

Results

Results

Getting Row Count

We can get the row count which indicates the number of rows that matches the where clause of an update or a delete statement.

with engine.begin() as conn:
    result=conn.execute(
        update(user_table).
        values(username="Marie Sam").
        where(user_table.c.username=="marie"))
print(result.rowcount)

Results

Results
with engine.begin() as conn:
    result=conn.execute(
        update(user_table).
        values(username="spongebobby").
        where(user_table.c.username=="spongebob"))
print(result.rowcount)

Results

Results

Using Returning

The update() and delete() method also supports returning the selected columns from all rows that match the where criteria of a statement into the result object by using Update.Returning() and Delete.Returning() methods. The result object can further be iterated to get the rows matched.

update_stmt=(
    update(user_table).
       where(user_table.c.username == "sandy").
       values(username="Sandy Sun").
       returning(user_table.c.id,user_table.c.username)
)
print(update_stmt)

Results

Results
delete_stmt=(
    delete(user_table).
         where(user_table.c.username == "sandy").
         returning(user_table.c.id,user_table.c.username)
)
print(delete_stmt)

Results

Results

Conclusion

We have learned the basics of SQLAlchemy Core in this article. We’ve learned about basic SQLAlchemy concepts like

  • Engines that help us in establishing a connection to a database, and then we learned how to work with Database MetaData.
  • How to create a Table using MetaData object.
  • We’ve learned about how we can work with Data ie, perform operations such as Insert, Delete, and Update on Tables using an example for each.
  • In summary, we had the chance to learn and practice the most important pieces of SQLAlchemy (Core) 1.4/2.0.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.



Source link

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button