Insertion, updation, and deletion are the three basic operations that can be performed on a table in SQL.

1. Insertion: The INSERT INTO statement is used to insert new records into a table. The basic syntax for an INSERT INTO statement is:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

2. Updation: The UPDATE statement is used to modify existing records in a table. The basic syntax for an UPDATE statement is:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE some_column = some_value;

In this example, the UPDATE statement modifies the values of column1, column2, etc. in the table_name table. The WHERE clause is used to specify the conditions that must be met for the update to be performed.

3. Deletion: The DELETE statement is used to remove records from a table. The basic syntax for a DELETE statement is:

DELETE FROM table_name
WHERE some_column = some_value;

In this example, the DELETE statement removes the records from the table_name table where some_column is equal to some_value. The WHERE clause is used to specify the conditions that must be met for the deletion to be performed.

This was an overview of the insertion, updation, and deletion methods in SQL. We will see in the next section how to perform these methods in SQLAlchemy Table. 

Before starting the process let’s first create a sample table to perform operations. 

import sqlalchemy as db
import pandas as pd

engine = db.create_engine('sqlite:///user.sqlite') #Create test.sqlite automatically
connection = engine.connect()
metadata = db.MetaData()

user = db.Table('user', metadata,
              db.Column('U_id', db.Integer(), primary_key=True),
              db.Column('username', db.String(255), nullable=False),
              db.Column('password', db.String(100), nullable=False),
              db.Column('active', db.Boolean(), default=True)
              )

metadata.create_all(engine) #Creates the table

We created a user table with the primary key ‘U_id’. Other fields include username and password which are of string type. Another field is active which is boolean. 

How Do I Insert Data into a Table Using SQLAlchemy?

Insertion of data in a table means that to insert new records (rows) into an existing table in the database. There might be a possibility that you might add records one by one or multiple records at a time. We will see both possibilities here.

• Inserting records One by One

To add rows one by one into the table, you must follow these steps:

#Inserting record one by one
query = db.insert(user).values(U_id=1, username='Skhan0900', password='hi@8990', active=True)
Result = connection.execute(query)

#to view the results

results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

We converted the results to Pandas dataframe. That’s another way to view your records.

• Inserting Multiple Records at a time

To insert multiple records (rows) at a time in the table, follow this code:

#Inserting many records at ones
query = db.insert(user)
values_list = [{'U_id':'2', 'username':'JosephMuller', 'password':'Uop9099@3', 'active':False},
              {'U_id':'3','username':'Buttler879', 'password':'Butyu09@4', 'active':True}]
Result = connection.execute(query,values_list)

#to view the results

results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

The records are added successfully. We provided the values_list as the dictionary and provided it with the query in connection.execute() function.

How Do I Update Data in a Table Using SQLAlchemy?

Updating a record in the table means that modification of an already existing row provided by a specific condition.

For Example: change the username of the person to ‘XYZ’ provided that id==1.

To update a record in the table, the syntax is quite simple:

db.update(table_name).values(attribute = new_value).where(condition)

Scenario: Let’s Suppose we got a request to update the username of person with ‘U_id= 1’ to Muller0900 and another request to set the active status of a user with ‘U_id= 3’ to False.

Solution: Let’s see how the problem is solved

#import Libraries

import sqlalchemy as db
import pandas as pd

#load Already existing dataset

engine = db.create_engine('sqlite:///user.sqlite')
metadata = db.MetaData()
connection = engine.connect()
user = db.Table('user', metadata, autoload=True, autoload_with=engine)

# check how record looks

results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

The dataset has been loaded successfully.

# update query 1

query = db.update(user).values(username = 'Muller0900').where(user.columns.U_id == 1)
res = connection.execute(query)

# check results
results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

The username of the person with ‘U_id=1’ has been changed successfully.

#update query 2
query = db.update(user).values(active = False).where(user.columns.U_id == 3)
res = connection.execute(query)

# check results
results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

The active status of user with ‘U_id=3’ has been changed successfully to False.

How Do I Delete Data From a Table Using SQLAlchemy?

Deletion of data means dropping a row and deleting all elements of that row. Deleting a record from the SQLAlchemy table is simple. The syntax is given below:

db.delete(table_name).where(condition)

Scenario 1: 

Let’s Suppose you have to delete the users whose active status has been set to False.

Solution:

Let’s find out how to proceed with the solution.

#import Libraries

import sqlalchemy as db
import pandas as pd

#load Already existing dataset

engine = db.create_engine('sqlite:///user.sqlite')
metadata = db.MetaData()
connection = engine.connect()
user = db.Table('user', metadata, autoload=True, autoload_with=engine)
# check how record looks

results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

# delete query

query = db.delete(user).where(user.columns.active == False)
res = connection.execute(query)

# check results
results = connection.execute(db.select([user])).fetchall()
df = pd.DataFrame(results)
df.columns = results[0].keys()
df

The records with active==False have been deleted successfully.

Scenario 2: 

Suppose you want to drop a complete table. So what should you do?

Solution: 

The solution lies in a single command.

user.drop(engine) #drops the table