REST and RESTful APIs

REST stands for Representational State Transfer and defines a way to

construct networked interactions, such as the World Wide Web. There it is
based on HTTP and follows a few basic rules:

Client-Server Architecture
Stateless
Cacheable
Layered system
Code on demand (optional)
Uniform interface

REST achieves a number of important properties through its architectural

design, some of the most important ones being:

Performance (allowing for quick and stable networks)

Scalability (allows a large number of pieces to interact with each other;
this number can be continuously expanded)
System-agnosticism or interoperability (it allows to connect
completely different systems on different servers, as well as on clients
and servers, through a uniform interface)
 APIs - REST and RESTful APIs

REST is crazy important for how the web works. Taking an analogy from an
interesting conversational explanation of REST:

URLs are universally understandable nouns in that they allow the

entirety of machines to know what they are referring to.
And the HTTP methods are like universally
understandable verbs that allow all machines to communicate with
each other about what to do with the nouns.

REST is an architectural style that ties these concepts together in a

functional and effective way.

Here is a short description of each of the six guiding constraints taken

from Wikipedia (slightly edited):
Client-server architecture
The principle behind the client-server constraints is the separation of
concerns. Separating the user interface concerns from the data storage
concerns improves the portability of the user interface across multiple
platforms. It also improves scalability by simplifying the server components.
Perhaps most significant to the Web, however, is that the separation allows
the components to evolve independently, thus supporting the Internet-scale
requirement of multiple organizational domains.

Statelessness
The client-server communication is constrained by no client context being
stored on the server between requests. Each request from any client
contains all the information necessary to service the request, and session
state is held in the client. The session state can be transferred by the server
to another service such as a database to maintain a persistent state for a
period and allow authentication. The client begins sending requests when it
is ready to make the transition to a new state. While one or more requests
are outstanding, the client is considered to be in transition. The
representation of each application state contains links that may be used the
next time the client chooses to initiate a new state-transition.

Cacheability
As on the World Wide Web, clients and intermediaries can cache responses.
Responses must therefore, implicitly or explicitly, define themselves as
cacheable or not to prevent clients from getting stale or inappropriate data in
response to further requests. Well-managed caching partially or completely
eliminates some client-server interactions, further improving scalability and
performance.

Layered system
A client cannot ordinarily tell whether it is connected directly to the end
server, or to an intermediary along the way. Intermediary servers may
improve system scalability by enabling load balancing and by providing
shared caches. They may also enforce security policies.

Code on demand (optional)

Servers can temporarily extend or customize the functionality of a client by
transferring executable code. Examples of this may include compiled
components such as Java applets and client-side scripts such as JavaScript.

Uniform interface
The uniform interface constraint is fundamental to the design of any REST
service. It simplifies and decouples the architecture, which enables each
part to evolve independently. The four constraints for this uniform interface
are:

Resource identification in requests

Individual resources are identified in requests, for example using URIs in

Web-based REST systems. The resources themselves are conceptually
separate from the representations that are returned to the client. For
example, the server may send data from its database
as HTML, XML or JSON, none of which is the server's internal
representation.

Resource manipulation through representations

When a client holds a representation of a resource, including

any metadata attached, it has enough information to modify or delete the
resource.

Self-descriptive messages
Each message includes enough information to describe how to process the
message. For example, which parser to invoke may be specified by a media
type.

Hypermedia as the engine of application state (HATEOAS)

Having accessed an initial URI for the REST application—analogous to a

human Web user accessing the home page of a website—a REST client
should then be able to use server-provided links dynamically to discover all
the available actions and resources it needs. As access proceeds, the server
responds with text that includes hyperlinks to other actions that are currently
available. There is no need for the client to be hard-coded with information
regarding the structure or dynamics of the REST service.

REST Constraints

More Reading:
Informative Quora answer: https://www.quora.com/What-is-a-REST-API
Conversational explanation of the importance of
REST: http://web.archive.org/web/20130116005443/http://tomayko.com
/writings/rest-to-my-wife
Great description on
Wikipedia: https://en.wikipedia.org/wiki/Representational_state_transfer
Extensive answers on
SO: https://stackoverflow.com/questions/671118/what-exactly-is-
restful-programming
Roy Fielding's Thesis introducing
REST: https://www.ics.uci.edu/~fielding/pubs/dissertation/fielding_disse
rtation.pdf
Understanding the HTTP Protocol
As we mentioned in the previous section, RESTful API use (aka "piggy back")
the HTTP Protocol to go about their business. This is hugely useful. Since
RESTful APIs operate over the internet, and are always based in a
"Client/Server" relationship, we don't need to reinvent the wheel. We can just
piggyback on the HTTP Protocol that the internet already uses!

So what is the HTTP Protocol?

"HTTP means HyperText Transfer Protocol. HTTP is the

underlying protocol used by the World Wide Web and this protocol defines
how messages are formatted and transmitted, and what actions web servers
(also RESTful APIs) and web browsers (also API Clients) should take in
response to various commands." (link)

HTTP is a "Stateless" Protocol

This means that the client does not remember any "state" of the server. And
the server does not remember any "state" of the client. (This is the opposite
of what are known as "sessions" - where the client and server hold an open
connection to each other and immediately respond to changes in that
session. Sessions can be a huge headache for massive applications.) What
is great about "stateless" protocols is that the client sends all the
information needed for the server to complete the task at hand. Then, the
server completes said task and potentially returns some data. Then, the
server totally forgets that the client even exists. It maintains no "state" or
"memory" or "awareness" of any client. The server just sits there and listens
for incoming requests. When it receives a request, it completes the requests
as directed, then just goes back to sitting there waiting for incoming
requests.

HTTP Status Codes

The HTTP Protocol has a number of built-in status codes that we already
know and love. For instance, the dreaded 404 (NOT FOUND) or 401
(UNAUTHORIZED) and so on. Here's a quick pic that gives you the layout of
HTTP Status Codes (these codes are returned with all requests to give the
client a quick idea of the exact status of the response). This image comes
from here - very useful site.

The HTTPS Protocol

HTTPS means Hyper Text Transfer Protocol Secure. It is just the secure
version of the HTTP protocol. This means the requests and responses
between the server and client are encrypted by the Transport Layer Security
(TLS) or Secure Socket Layer (SSL).
HTTP Request Types
GET

The GET method requests a representation of the specified resource.

Requests using GET should only retrieve data. GET requests do not have
a "BODY".

POST

The POST method is used to submit an entity to the specified resource,

often causing a change in state or side effects on the server. The
changes to be made on the server are usually included in the request
body.

PUT

The PUT method replaces all current representations of the target

resource with the request payload. PUT and PATCH are often used
interchangeably out there in the real world. The changes to be made on
the server are usually included in the request body.

PATCH

The PATCH method is used to apply partial modifications to a

resource. PUT and PATCH are often used interchangeably out there in
the real-world. The changes to be made on the server are usually
included in the request body.

DELETE

The DELETE method deletes the specified resource.

The request types below are more of utility functions which are less
commonly used on a day-to-day basis.
 HEAD

The HEAD method asks for a response identical to that of a GET request,
but without the response body.

OPTIONS

The OPTIONS method is used to describe the communication options for

the target resource.

CONNECT

The CONNECT method establishes a tunnel to the server identified by the

target resource.

TRACE

The TRACE method performs a message loop-back test along the path
to the target resource.

Components of an HTTP Request:

Request URL: The endpoint to hit (the URL of the server to which you
are making the request)

HTTP Method: The type of request that you're making (GET, POST,
PUT, PATCH, DELETE)

Request Headers: These often include the type of data you're sending
(ie, "content-type: application/json") to tell the server how to read your
request. The headers also often include authorization and
authentication information.

Request Body: The body hold the information that you're asking the
 server to create (POST) or update (PUT/PATCH). The body is often
formatted in JSON and would appear like the block below if we were
trying to create a new User record on the server:

{
"first_name" : "Ryan",
"last_name" : "Desmond",
"email" : "ryan@codingnomads.co"
}
Intro to APIs with Python
You've probably heard about APIs before you knew you needed to or even
wanted to! Luckily we are about to uncrack the mysterious code of APIs and
find out how we can use the Python skills we have acquired thus far to
interact with the vast network of APIs.

Once you know the components of RESTful API request and responses as
learned in the API section, Python makes those interactions quite straight-
forward.

In this section we will take a look at how starting with a few lines of code:

import requests

base_url = "http://demo.codingnomads.co:8080/tasks_api/users"
request = requests.get(base_url)

We can start to interact with some of the most powerful applications around
the world.
Installing the requests package
In this section, we will be using one of the most common API packages, the
requests package.

For this project, either create a new project with a new virtual or make sure
you are in a project with an activated virtual environment. Once the virtual
environment is activate we can install the requests package:

pip install requests

Note: There is no problem with installing this package to your computer,

however, for easy removal, we will use a virtual environment.

What is the requests package?

The requests package allows you to send HTTP compliant requests using
simple Python. It also allows you to easily access response data and work
with information that is returned.
Making a GET Request
GET Requests are for reading data from the server/API.
(Much like a SELECT query is for reading data from a
database.)

We will start by taking a look at the most simple RESTful request, the GET
request. We will be using an API provided by yours truly, The CodingNomads
Team. The first endpoint we will be looking at is:
http://demo.codingnomads.co:8080/tasks_api/users (go ahead and click
that link). If you remember, when you visit a page in a web browser, you are
making a GET request.

Note: We highly recommend installing a browser extension such as JSON

Formatter (or the equivalent for your preferred browser) so that you see the
JSON in a nice, formatted view rather than just a big blob of confusing text.

Copy and paste the link in a new tab and check out what it returns. JSON!
This is a response to the request you made. It should look something like
this:

{
"data": [
{
"id": 1,
"first_name": "Ryan",
"last_name": "Desmond",
"email": "ryan@codingnomads.co",
"created_at": 1549131644000,
"updated_at": 1549131644000
},
{
"id": 2,
"first_name": "Caden",
"last_name": "Mackenzie",
 "email": "caden@codingnomads.co",
"created_at": 1549131677000,
"updated_at": 1549131677000
}
],
"error": {
"message": "none"
},
"status": "200 OK"
}

Now how can we do this in Python?

We first need to import the requests package in our program. We can then
use the get() function. In order to verify our request, we will print out the
status code of the response:

You should see a status code of 200. If not, there is something wrong.

Now, change the base_url to something incorrect. For example, we will use
the path variable developers instead of users:
http://demo.codingnomads.co:8080/tasks_api/developers. You should
now get a status code of 404. We get this status code (Not Found) because
the API does not have a /developers endpoint.

In the next section, we will take a look at what else makes up the response.
Query Parameters
Query parameters can be passed as a part of the URL or as a param
argument with the URL. Query parameters are used to filter and/or narrow-
down the request you're making. Similar to the way a WHERE clause filters
the data you get back from a SQL query.

A question mark (?) always indicates the start of the query parameter list.
The query parameters are Key-Value pairs. For instance, in the example in
the REPL below the key is "email" and the value is
"ryan@codingnomads.co". Many API endpoints support multiple query
params. Query params are delimited by the "&" symbol. For instance:

http://demo.codingnomads.co:8080/tasks_api/tasks?userId=1&complete=true

In the example above we are passing two query parameters, "userId" and
"complete". The query params list starts with a question mark, and the query
params are separated by the "&" symbol. If you pop that URL in the example
above into your web browser URL bar and hit ENTER you'll see the response
we get back in JSON.

Using requests, we can attach the query params in two ways. Either in the
URL itself or as a key-value pair that we pass into the request. The following
two examples are functionally equivalent..
Response Formats: JSON and XML
When working with RESTful APIs, or interfacing with them, we will encounter
one of three possible formats: JSON, HTML or XML. When information is
returned to a user, this is often given in HTML. However, when we are
programmatically requesting data from an API for further processing, we will
mostly encounter JSON, sometimes also XML.

Let's take a look at these common formats that you should get familiar with
when working with APIs.

JSON
"JSON (JavaScript Object Notation) is a lightweight data-interchange
format. It is easy for humans to read and write. It is easy for machines to
parse and generate. It is based on a subset of the JavaScript Programming
Language. JSON is a text format that is completely language independent
but uses conventions that are familiar to programmers of the C-family of
languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many
others. These properties make JSON an ideal data-interchange language."

All of JSON is neatly explained in a one-pager here: https://www.json.org/.

Here's a look at an example JSON object:

{
"first_name" : "Ryan",
"last_name" : "Desmond",
 "email" : "ryan@codingnomads.co"
}

This JSON would map directly to the follow User class in Java:

public class User {

String first_name;
String last_name;
String email;
}

Or this class in Python:

class User:
def __init__(first_name, last_name, email):
self.first_name = first_name
self.last_name = last_name
self.email = email

The JSON below shows a collection of User objects (notice the square
brackets (which indicate "collection" or "array")

{ "users" : [
{

"first_name" : "Ryan",
"last_name" : "Desmond",
"email" : "ryan@codingnomads.co"
},

{
 "first_name" : "Kim",
"last_name" : "Desmond",
"email" : "kim@codingnomads.co"
},

{
"first_name" : "Martin",
"last_name" : "Breuss",
"email" : "martin@codingnomads.co"
} ]
}

Which would map to following Java class:

public class JsonExample {

User[] users;
}

JSON is just a collection of key-value pairs that can map directly to or from
Python/Java/JS Object.

Python's in-built json package is straightforward to use and allows

serializing, parsing, and File I/O for
JSON: https://docs.python.org/3.6/library/json.html

XML
XML stands for eXtensible Markup Language and has a longer history than
JSON. It bears close resemblance to HTML, but is (as the name implies)
more extensible than HTML, as well as stricter typed.

Working with XML in python can be more clunky than using JSON, therefore
if you have the choice I would suggest gathering the data in JSON. However,
sometimes XML it is, and a useful package to work with this is
python's ElementTree: https://docs.python.org/3/library/xml.etree.elementtre
e.html

Here is the same example as above (the single User) in XML:

<user>
<first_name>Ryan</first_name>
<last_name>Desmond</last_name>
<email>ryan@codingnomads.co</email>
</user>

And here is a collection of Users in XML:

<users>
<user>
<first_name>Ryan</first_name>
<last_name>Desmond</last_name>
<email>ryan@codingnomads.co</email>
</user>

<user>
<first_name>Kim</first_name>
<last_name>Desmond</last_name>
<email>kim@codingnomads.co</email>
</user>

<user>
<first_name>Martin</first_name>
<last_name>Breuss</last_name>
<email>martin@codingnomads.co</email>
</user>
</users>
HTTP Responses
As we have seen, when we make an API request, we get a response. The
response is made up of several pieces. We will take a look at the most
important ones.

The Content

The content is the meat of the response. It is where the data belongs. On the
previous page, when we made a request in our browser, it was the content
that was shown on the page.

The Headers

The response headers contain information about the response such as the
type of content in the response, the date and time of the response and the
encoding. This information varies based on what the API includes in its
response.

Other

A response can also include the encoding, the URL, the origin and many
other pieces of information.
Below is an example of how to access different parts of the response. Feel
free to explore and see if there is more information to be found.
Working with JSON
Now that we have looked what a response is made up of, we can look at how
to work with that information in our python programs.

Python Objects from JSON

Remember, JSON is simply an agreed-upon text format. On the previous

page, we looked at how to print out the response content. In raw JSON
format, the information in the string is not very accessible.

Luckily, Python makes it really easy to convert JSON to Python datatypes

such as dictionaries and lists.

The JSON method

Using the JSON method included in the requests package, we can convert
the content of the response to a Python dictionary and access the data. The
.json() method turns the JSON response into the appropriate python object.
In this example, we are using the pprint package which allows us to print
data structures in a readable way.

The result of line 8 is the creation of a dictionary. Play around with the
dictionary data. How can you access the values of the dictionary?
Making a POST Request
While all HTTP requests receive a response, not all requests can send data.
Unlike a GET request, which we have seen is used to retrieve information,
POST requests can send data to the server. To make a POST request we will
use the .post() method.

To pass information in a POST request, we can use the json parameter. Using
the same API as before, we are now going to create a new user by sending
the user information in the body of the POST request. For example:
Making a PUT Request
Making a PUT request is very similar to a POST request. PUT requests are
also used to send information to the server. As you might have guessed, you
will instead be using the put() method.

For this particular API, you also need to specify a user id as a path variable in
order to update the user record.

Replace YOUR_ID on line 12 with your user id.

Making a DELETE Request
The DELETE request is also capable of sending information to the server like
a POST or PUT request. To make a DELETE request to the API you have been
working on, you do not need to send any information in the body of the
request.

As with PUT, you can specify the user you want to delete by utilizing path
variables. For example, to delete the user with id 50 you will add the id to the
URL as a path variable:

import requests

base_url = "http://demo.codingnomads.co:8080/tasks_api/users"
response = requests.delete(base_url + "/50")
print(response.status_code)
Exploring the API
We have now seen how to make GET, POST, PUT and DELETE requests. The
API we have been using is set up for you to explore. All the examples we
have looked at only use the users endpoint. Take a look at the
documentation of this API and explore it by creating new tasks and users,
updating existing records and removing records. The documentation can be
found here.
Labs: APIs
Please clone the labs project
here: https://github.com/CodingNomads/python_apis_databases. After
cloning it, please push the project to your own GitHub. You can follow the
instructions here if you need a little help there.

Once you've got the project cloned to your machine, and pushed to your
GitHub. Please complete all labs in the APIs section. Then push your
completed work to your GitHub and share the link to the folder containing
your work here.

Submission status

Submission status No attempt

Mentor Review Review Pending (1-on-1 mentorship required for
Status assignment review)
Last modified -
Submission
comments

You have not made a submission yet

Installing SQLAlchemy
SQLALchemy is a tool that allows you to easily interact with
relational databases from your python programs. Unlike other python
database connectors, SQLALchemy can be used with a variety of SQL
databases such as MySQL, PostgreSQL and Oracle.

In this section, we will look at how to connect to a MySQL database.

Installing the sqlalchemy package

To start, we will need to install the sqlalchemy package. You can do this
locally on your machine or in a virtual environment. We recommend using a
virtual environment.

pip install sqlalchemy

Importing the Sakila Database
If you do not already have the sakila demo database imported to your local
MySQL installation, please refer to this page for directions on how to import
the sakila DB to your local machine before proceeding.
Connecting to a Database
Installing SQLAlchemy
In this section, we will be using the SQLAlchemy python package. If you have
not already done so, please instill the package to your machine locally or in a
virtual environment.

Making a Connection

In order to interact with a database, we need to first establish a connection

to the database. We can do this by first importing sqlalchemy and using the
create_engine() method. The following are examples of how to connect to
MySQL and PostgreSQL databases. The only difference is the database
URL.

PostgreSQL Connection

import sqlalchemy

engine = sqlalchemy.create_engine('postgresql://username:password@localhost/mydatabase')

MySQL Connection

import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/mydatabase')
Accessing Database Information
Now that we have made a connection, we can access information about the
database. For example, we can get the column names of a certain table, or
the entire Metadata of a table.

import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
actor = sqlalchemy.Table('actor', metadata, autoload_with=engine)

print(actor.columns.keys())
print(repr(metadata.tables['actor']))
Select Statement
Now that we have made a connection and looked at how to access
information about the database, let's look at how to query the data. The first
query we will look at is a SELECT query.

Select Query

We first set up the connect the same way we have seen in the past. From
there, we create a Select statement. The following is the equivalent of the
SQL statement

SELECT * FROM actor

query = sqlalchemy.select([actor])

Now, we can execute the query using the execute() method. The result is
saved in the variable result_proxy. In order to access the data returned by
the query, we fetch the results using the fetchall() method.
The data returned is stored in the variable result_set and can be used as any
other Python object. In the following example, we print out the entire result
set however, we could access the data in a variety of ways such as iterating
over the list, accessing an item at a specific index, etc.

import sqlalchemy
from pprint import pprint

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()
actor = sqlalchemy.Table('actor', metadata, autoload=True, autoload_with=engine)

query = sqlalchemy.select([actor])
result_proxy = connection.execute(query)

result_set = result_proxy.fetchall()
pprint(result_set)

If you are working with a large dataset, you can use the fetchmany() method
instead of fetchall() and specify how many rows you want to be returned. For
example:

result_set = result_proxy.fetchmany(5)
Filtering Data
Now that we are familiar with a SELECT statement, let us take a look at how
we can filter those results. The following are examples of SQL statements
and their matching SQLAlchemy statements.

WHERE

The where statement

SQL:

SELECT * FROM sakila.actor WHERE first_name = "PENELOPE";

SQLAlchemy:

query = sqlalchemy.select([actor]).where(actor.columns.first_name == 'PENELOPE')

IN

The in statement

SQL:

SELECT * FROM sakila.actor WHERE first_name IN ("PENELOPE", "JOHN", "UMA");

SQLAlchemy:

query = sqlalchemy.select([actor]).where(actor.columns.first_name.in_(["PENELOPE", "JOHN",

AND, OR, NOT

AND, OR, NOT statements

SQL:

SELECT * FROM sakila.film WHERE length > 60 AND rating = "PG";

OR

SELECT * FROM sakila.film WHERE length > 60 AND NOT rating = "PG";

SQLAlchemy:

query = sqlalchemy.select([film]).where(sqlalchemy.and_(film.columns.length > 60, film.columns.rati

OR

query = sqlalchemy.select([film]).where(sqlalchemy.and_(film.columns.length > 60, film.columns.rati

ORDER BY

ORDER BY Statements

SQL:

SELECT * FROM sakila.film ORDER BY replacement_cost ASC;

SQLAlchemy:

query = sqlalchemy.select([film]).order_by(sqlalchemy.asc(film.columns.replacement_cost))

SUM AND OTHER FUNCTIONS

SUM

SQL:

SELECT SUM(length) FROM sakila.film;

SQLAlchemy:

query = sqlalchemy.select([sqlalchemy.func.sum(film.columns.length)])
Using Joins
Join statements allow you to select data from more than one table. In the
following example, we will take a look at how to use SQLAlchemy joins to find
related data in the actor and film tables.

We first establish a connection the way we have done in the past and make
an object for each of the tables we need information from.

import sqlalchemy
from pprint import pprint

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()

actor = sqlalchemy.Table('actor', metadata, autoload=True, autoload_with=engine)

film = sqlalchemy.Table('film', metadata, autoload=True, autoload_with=engine)
film_actor = sqlalchemy.Table('film_actor', metadata, autoload=True, autoload_with=engine)

To make things more clear, we will operate the statement and query. First, we
create a statement join_statement by joining the three tables on the foreign
key conditions. We then create a SELECT statement using the join_statement
from the previous line to select the film_id, first_name, and last_name:

join_statement = actor.join(film_actor, film_actor.columns.actor_id == actor.columns.actor_id).

query = sqlalchemy.select([film.columns.film_id, film.columns.title,actor.columns.first_name, actor
Finally, we execute the query and print the results:

result_proxy = connection.execute(query)

result_set = result_proxy.fetchall()
pprint(result_set)
Creat!ng a Table
import sqlalchemy

engine =
sqlalchemy.create_engine('mysql+pymysql://username:password@localho
st/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()

newTable = sqlalchemy.Table('newTable', metadata,

sqlalchemy.Column('Id', sqlalchemy.Integer()),
sqlalchemy.Column('name', sqlalchemy.String(255), nullable=False),
sqlalchemy.Column('salary', sqlalchemy.Float(), default=100.0),
sqlalchemy.Column('active', sqlalchemy.Boolean(), default=True)
)

metadata.create_all(engine)
Insert Statement
Building on the previous page, we will now look at how to insert data into our
newly created table. We establish a connection as we have done in the past,
but now, we will use the insert() method and pass the key-value pairs. In the
following example, the keys are the names of the columns and the values are
the new values that are going to be inserted. For example:

import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()

newTable = sqlalchemy.Table('newTable', metadata, autoload=True, autoload_with=engine)

query = sqlalchemy.insert(newTable).values(Id=1, name='Software Ninjaneer', salary=60000.00

result_proxy = connection.execute(query)

In the example above, we have inserted one record, however, what if we

want to insert multiple records at once? We need to create a list of
dictionaries. For example:

query = sqlalchemy.insert(newTable)
new_records = [{'Id':'2', 'name':'record1', 'salary':80000, 'active':False},
{'Id':'3', 'name':'record2', 'salary':70000, 'active':True}]
result_proxy = connection.execute(query,new_records)
Update Statement
To update a record in our database we will use the update, values and where
methods. In the following example, we update the salary column in the
newTable table, where the column Id is equal to 1.

import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()

newTable = sqlalchemy.Table('newTable', metadata, autoload=True, autoload_with=engine)

query = sqlalchemy.update(newTable).values(salary=100000).where(newTable.columns.Id == 1)

result = connection.execute(query)
Delete Statement
Finally, the Delete statement! By now some of the connection set up should
look pretty familiar. In the following example, we will look at how to delete
one or more records from a table. Using the delete and where methods, we
delete all records from the table newTable, where, the column salary is less
than 100,000.

import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost/sakila')
connection = engine.connect()
metadata = sqlalchemy.MetaData()

newTable = sqlalchemy.Table('newTable', metadata, autoload=True, autoload_with=engine)

query = sqlalchemy.delete(newTable).where(newTable.columns.salary < 100000)

results = connection.execute(query)