Working with Data in SQLAlchemy 1.4 2.0 Core

SQLAlchemy is a large SQL toolkit with lots of different components. The two largest components are SQLAlchemy Core andSQLAlchemy ORM. Core is considered the foundation of SQLAlchemy, whereas ORM is built on the top of the Core. SQLAlchemy Core consists of SQL Expression Language. It consists of two primary objects i.e., the engine and the connection that allows us to connect to the database, execute queries, and generate results.

###Creating an Engine

Engine: An object that is used to establish a connection. Using this connection we can create queries on the database.

create_engine(): This method is used to create an engine object.

In this tutorial we are using a SQLite database that is stored in memory.

1
2
3
from sqlalchemy
import create_engine
engine = create_engine("sqlite+pysqlite:///:memory:", echo = True, future = True)

Creating a Table with Metadata:

MetaData:  An object which consists of table objects. One MetaData object is enough for the entire application. This MetaData object will hold all the information of our table.

Table: An object which represents a table that consists of column objects and constraints.

Column: An object which represents a column of a table which can be an integer, a string or a number, etc.

The following example shows the code for creating a MetaData object.

1
2
3
from sqlalchemy
import MetaData
metadata = MetaData()

Using the above-created MetaData object we can create a new table (named as student_ac), as below:

1
2
3
4
5
6
7
8
9
10
11
12
from sqlalchemy
import Table, Column
from sqlalchemy
import Integer, String
student_ac = Table(
  "student_account",
  metadata,
  Column('id', Integer, primary_key = True),
  Column('name', String(50), nullable = False),
  Column('age', Integer),
  Column('grade', String(90))
)

Emitting DDL to Database:

We can emit DDL to the database using the method metadata.create_all(conn) using the engine object created above.

1
2
with engine.begin() as conn:
  metadata.create_all(conn)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
2021 - 10 - 25 15: 46: 14, 920 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("student_account")
2021 - 10 - 25 15: 46: 14, 920 INFO sqlalchemy.engine.Engine[raw sql]()
2021 - 10 - 25 15: 46: 14, 920 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("student_account")
2021 - 10 - 25 15: 46: 14, 920 INFO sqlalchemy.engine.Engine[raw sql]()
2021 - 10 - 25 15: 46: 14, 921 INFO sqlalchemy.engine.Engine
CREATE TABLE student_account(
  id INTEGER NOT NULL,
  name VARCHAR(50) NOT NULL,
  age INTEGER,
  grade VARCHAR(90),
  PRIMARY KEY(id)
)
2021 - 10 - 25 15: 46: 14, 921 INFO sqlalchemy.engine.Engine[no key 0.00022 s]()
2021 - 10 - 25 15: 46: 14, 936 INFO sqlalchemy.engine.Engine COMMIT

Creating Another Table Using ForeignKey

We use the Foreign Key constraint when we want to relate two tables. A Foreign Key constraint can be used in a table using the object ForeignKey. In the below example we create another table address that relates to the table student_account.

1
2
3
4
5
6
7
8
9
10
11
12
13
from sqlalchemy
import ForeignKey
address = Table(
  "address",
  metadata,
  Column("id", Integer, primary_key = True),
  Column("address", String(100), nullable = False),
  Column("Student_id",
    ForeignKey("student_account.id"),
    nullable = False),
)
with engine.begin() as conn:
  address.create(conn)

1
2
3
4
5
6
7
8
9
10
11
12
2021 - 10 - 25 15: 50: 34, 795 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 15: 50: 34, 796 INFO sqlalchemy.engine.Engine
CREATE TABLE address(
  id INTEGER NOT NULL,
  address VARCHAR(100) NOT NULL,
  "Student_id"
  INTEGER NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY("Student_id") REFERENCES student_account(id)
)
2021 - 10 - 25 15: 50: 34, 796 INFO sqlalchemy.engine.Engine[no key 0.00018 s]()
2021 - 10 - 25 15: 50: 34, 797 INFO sqlalchemy.engine.Engine COMMIT

Inserting Values

The next step is to insert values into the tables we have created.

Insert(): This method is used to generate an insert SQL statement that inserts values to the existing table.

The basic insert statement is shown below, which has the insert statement and the values clause at once.

1
2
3
4
insert_stmt = student_ac.insert()
  .values(name = "John", age = 16, grade = "first")
with engine.begin() as connection:
  connection.execute(insert_stmt)

1
2
3
4
2021 - 10 - 25 15: 54: 44, 909 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 15: 54: 44, 911 INFO sqlalchemy.engine.Engine INSERT INTO student_account(name, age, grade) VALUES( ? , ? , ? )
2021 - 10 - 25 15: 54: 44, 911 INFO sqlalchemy.engine.Engine[generated in 0.00045 s]('John', 16, 'first')
2021 - 10 - 25 15: 54: 44, 912 INFO sqlalchemy.engine.Engine COMMIT

The second way of inserting values is as below:

1
2
3
4
5
6
7
8
with engine.begin() as connection:
  connection.execute(
    student_ac.insert(), {
      "name": "Mary",
      "age": 22,
      "grade": "fourth"
    }
  )

1
2
3
4
2021 - 10 - 25 15: 55: 57, 537 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 15: 55: 57, 538 INFO sqlalchemy.engine.Engine INSERT INTO student_account(name, age, grade) VALUES( ? , ? , ? )
2021 - 10 - 25 15: 55: 57, 538 INFO sqlalchemy.engine.Engine[generated in 0.00028 s]('Mary', 22, 'fourth')
2021 - 10 - 25 15: 55: 57, 538 INFO sqlalchemy.engine.Engine COMMIT

When we don’t give values to the insert statement, it will generate the values clause automatically based on the parameters passed while executing. We are inserting two rows in the below example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with engine.begin() as connection:
  connection.execute(
    student_ac.insert(), [
      {
        "name": "Harry",
        "age": 20,
        "grade": "second"
      },
      {
        "name": "Sunita",
        "age": 18,
        "grade": "third"
      }
            ]
  )

1
2
3
4
2021 - 10 - 25 15: 57: 15, 348 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 15: 57: 15, 349 INFO sqlalchemy.engine.Engine INSERT INTO student_account(name, age, grade) VALUES( ? , ? , ? )
2021 - 10 - 25 15: 57: 15, 349 INFO sqlalchemy.engine.Engine[generated in 0.00026 s](('Harry', 20, 'second'), ('Sunita', 18, 'third'))
2021 - 10 - 25 15: 57: 15, 349 INFO sqlalchemy.engine.Engine COMMIT

Select Statement

Using select to produce a select statement:

Select(): This method is used to create any select statement. To use select, import select from SQLAlchemy.

In the below example we are selecting the name, age, and grade from the first table we created(student_ac) using where clause (where the student name matches “John”). We then execute the statement using connection.execute() method which returns a result set. Further, we are iterating through the result using a for loop to print the rows.

1
2
3
4
5
6
7
8
9
from sqlalchemy
import select
with engine.connect() as connection:
  stmt = (select(student_ac.c.name, student_ac.c.age,
      student_ac.c.grade)
    .where(student_ac.c.name == "John"))
result = connection.execute(stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
2021 - 10 - 25 16: 09: 22, 833 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 16: 09: 22, 834 INFO sqlalchemy.engine.Engine SELECT student_account.name, student_account.age, student_account.grade
FROM student_account
WHERE student_account.name = ?
  2021 - 10 - 25 16 : 09: 22, 834 INFO sqlalchemy.engine.Engine[generated in 0.00048 s]('John', )
  ('John', 16, 'first')
2021 - 10 - 25 16: 09: 22, 834 INFO sqlalchemy.engine.Engine ROLLBACK

Selecting using order by

In this example we are selecting all the rows from the table and ordering them by name using order_by clause. You can see the result which is ordered by name.

1
2
3
4
5
6
7
with engine.connect() as connection:
  result = connection.execute(
    select(student_ac)
    .order_by(student_ac.c.name)
  )
for name, age, grade in result.columns("name", "age", "grade"):
  print(f "{name}  {age}   {grade}")

1
2
3
4
5
6
7
8
9
10
2021 - 10 - 25 16: 12: 46, 198 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 16: 12: 46, 199 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account ORDER BY student_account.name
2021 - 10 - 25 16: 12: 46, 199 INFO sqlalchemy.engine.Engine[cached since 17.49 s ago]()
2021 - 10 - 25 16: 12: 46, 200 INFO sqlalchemy.engine.Engine ROLLBACK

Harry 20 second
John 16 first
Mary 22 fourth
Sunita 18 third

Using scalars: to select a particular column:

In order to select only a particular column, we use scalars method which selects a particular column from the result set.

1
2
3
4
5
6
7
with engine.connect() as connection:
  result = connection.execute(
    select(student_ac)
    .order_by(student_ac.c.name)
  )
for name in result.scalars("name"):
  print(name)

1
2
3
4
5
6
7
8
9
10
2021 - 10 - 25 16: 14: 35, 912 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 16: 14: 35, 912 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account ORDER BY student_account.name
2021 - 10 - 25 16: 14: 35, 912 INFO sqlalchemy.engine.Engine[cached since 127.2 s ago]()
2021 - 10 - 25 16: 14: 35, 912 INFO sqlalchemy.engine.Engine ROLLBACK

Harry
John
Mary
Sunita

Selecting from labeled SQL Expressions

1
2
3
4
5
6
7
8
9
10
11
12
from sqlalchemy
import func, cast
stmt = (
  select(
    ("name: " + student_ac.c.name)
    .label("name"),
  )
  .order_by(student_ac.c.name)
)
with engine.connect() as conn:
  for row in conn.execute(stmt):
  print(f "{row.name}")

1
2
3
4
5
6
7
8
9
2021 - 10 - 25 16: 20: 07, 277 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 16: 20: 07, 277 INFO sqlalchemy.engine.Engine SELECT ? || student_account.name AS name
FROM student_account ORDER BY student_account.name
2021 - 10 - 25 16: 20: 07, 277 INFO sqlalchemy.engine.Engine[generated in 0.00053 s]('name: ', )
name: Harry
name: John
name: Mary
name: Sunita
2021 - 10 - 25 16: 20: 07, 278 INFO sqlalchemy.engine.Engine ROLLBACK

Selecting with Textual Column Expressions:

The text() construct can be embedded in a select statement which can be used to display any text with the result as shown in the example below.

1
2
3
4
5
6
7
8
9
10
11
from sqlalchemy
import text
stmt = (
  select(
    text("'Student name is'"), student_ac.c.name
  )
  .order_by(student_ac.c.name)
)
with engine.begin() as conn:
  print(conn.execute(stmt)
    .all())

1
2
3
4
5
2021 - 10 - 25 16: 22: 49, 759 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 25 16: 22: 49, 760 INFO sqlalchemy.engine.Engine SELECT 'Student name is', student_account.name
FROM student_account ORDER BY student_account.name
2021 - 10 - 25 16: 22: 49, 760 INFO sqlalchemy.engine.Engine[cached since 15.77 s ago]()[('Student name is', 'Harry'), ('Student name is', 'John'), ('Student name is', 'Mary'), ('Student name is', 'Sunita')]
2021 - 10 - 25 16: 22: 49, 762 INFO sqlalchemy.engine.Engine COMMIT

The WHERE Clause:

Select.where(): This method is used to select a row by specifying an expression such as (name == ‘Mary’).

1
2
3
4
5
6
7
with engine.begin() as conn:
  stmt = select(student_ac)
  .where(student_ac.c.name == 'Mary')
print(stmt)
result = conn.execute(stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
8
9
2021 - 10 - 25 16: 27: 02, 184 INFO sqlalchemy.engine.Engine BEGIN(implicit)
SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account
WHERE student_account.name =: name_1
2021 - 10 - 25 16: 27: 02, 186 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account
WHERE student_account.name = ?
  2021 - 10 - 25 16 : 27: 02, 187 INFO sqlalchemy.engine.Engine[cached since 24.48 s ago]('Mary', )
2021 - 10 - 25 16: 27: 02, 219 INFO sqlalchemy.engine.Engine COMMIT(2, 'Mary', 22, 'fourth')

Multiple where using multiple tables:

We can use where with multiple tables as shown in the below example. The two where statements will be joined by ‘AND’.

1
2
3
4
5
6
7
8
with engine.begin() as conn:
  stmt = select(address.c.address)
  .where(student_ac.c.name == 'John')
  .where(address.c.Student_id == student_ac.c.id)
print(stmt)
result = conn.execute(stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
8
9
10
11
2021 - 10 - 25 16: 32: 45, 207 INFO sqlalchemy.engine.Engine BEGIN(implicit)
SELECT address.address
FROM address, student_account
WHERE student_account.name =: name_1 AND address.
"Student_id" = student_account.id
2021 - 10 - 25 16: 32: 45, 209 INFO sqlalchemy.engine.Engine SELECT address.address
FROM address, student_account
WHERE student_account.name = ? AND address.
"Student_id" = student_account.id
2021 - 10 - 25 16: 32: 45, 209 INFO sqlalchemy.engine.Engine[generated in 0.00030 s]('John', )
2021 - 10 - 25 16: 32: 45, 209 INFO sqlalchemy.engine.Engine COMMIT('Mumbai', )

Using Joins

We use joins when we have to select columns from two tables.

Select.join_from():
In this method, both the left and the right side are given explicitly.

1
2
3
4
from sqlalchemy
import select
print(select(student_ac.c.name, address.c.address)
  .join_from(student_ac, address))

1
2
3
SELECT student_account.name, address.address
FROM student_account JOIN address ON student_account.id = address.
"Student_id"

Select.join: In this method, only the right side is given whereas the left side is implicitly inferred.

1
2
print(select(student_ac.c.name, address.c.address)
  .join(address))

1
2
3
SELECT student_account.name, address.address
FROM student_account JOIN address ON student_account.id = address.
"Student_id"

ON clause

1
2
3
print(select(address.c.address)
  .select_from(student_ac)
  .join(address, student_ac.c.id == address.c.Student_id))

1
2
3
SELECT address.address
FROM student_account JOIN address ON student_account.id = address.
"Student_id"

Outer and full join

Outer join

1
2
3
4
5
6
7
stmt = select(student_ac)
  .join(address, isouter = True)
with engine.begin() as conn:
  result = conn.execute(stmt)
print(stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
8
9
10
11
12
13
2021 - 10 - 26 15: 57: 31, 127 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 26 15: 57: 31, 127 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account LEFT OUTER JOIN address ON student_account.id = address.
"Student_id"
2021 - 10 - 26 15: 57: 31, 128 INFO sqlalchemy.engine.Engine[cached since 75.16 s ago]()
2021 - 10 - 26 15: 57: 31, 128 INFO sqlalchemy.engine.Engine COMMIT
SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account LEFT OUTER JOIN address ON student_account.id = address.
"Student_id"
(1, 'John', 16, 'first')
(2, 'Mary', 22, 'fourth')
(3, 'Harry', 20, 'second')
(4, 'Sunita', 18, 'third')

Full join

1
2
print(select(student_ac)
  .join(address, full = True))

1
2
3
SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account FULL OUTER JOIN address ON student_account.id = address.
"Student_id"

ORDER BY

1
2
3
4
5
6
7
8
9
10
11
12
13
from sqlalchemy
import select
print(select(student_ac)
  .order_by(student_ac.c.name))
SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account ORDER BY student_account.name

stmt = select(student_ac)
  .order_by(student_ac.c.name)
with engine.begin() as conn:
  result = conn.execute(stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
8
2021 - 10 - 29 15: 40: 00, 607 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 15: 40: 00, 610 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account ORDER BY student_account.name
2021 - 10 - 29 15: 40: 00, 610 INFO sqlalchemy.engine.Engine[generated in 0.00049 s]()
2021 - 10 - 29 15: 40: 00, 656 INFO sqlalchemy.engine.Engine COMMIT(3, 'Harry', 20, 'second')
  (1, 'John', 16, 'first')
  (2, 'Mary', 22, 'fourth')
  (4, 'Sunita', 18, 'third')

Updating Values

The update construct is similar to insert which updates the rows of the existing table. However, it does not return any values. The basic update statement is shown in the example below.

1
2
3
4
5
with engine.begin() as connection:
  update_stmt = (student_ac.update()
    .values(grade = "second")
    .where(student_ac.c.name == "John"))
result = connection.execute(update_stmt)

1
2
3
4
2021 - 10 - 29 15: 49: 05, 069 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 15: 49: 05, 072 INFO sqlalchemy.engine.Engine UPDATE student_account SET grade = ? WHERE student_account.name = ?
  2021 - 10 - 29 15 : 49 : 05, 072 INFO sqlalchemy.engine.Engine[generated in 0.00042 s]('second', 'John')
2021 - 10 - 29 15: 49: 05, 073 INFO sqlalchemy.engine.Engine COMMIT

To check the updated table we use the following code:

1
2
3
4
5
with engine.begin() as connection:
  select_stmt = select(student_ac)
result = connection.execute(select_stmt)
for row in result:
  print(row)

1
2
3
4
(1, 'John', 16, 'second')
(2, 'Mary', 22, 'fourth')
(3, 'Sunita', 20, 'second')
(4, 'Bobby', 18, 'third')

Using bindparam:

We use bindparam when we need to pass a set of parameters in a single update statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
from sqlalchemy
import update
from sqlalchemy
import bindparam
with engine.begin() as connection:
  update_stmt = update(student_ac)
  .where(student_ac.c.name == bindparam('oldname'))
  .values(
    name = bindparam('newname'))
connection.execute(update_stmt,
                       [
    {
      'oldname': 'Mary',
      'newname': 'Miss Marie'
    },
    {
      'oldname': 'Bobby',
      'newname': 'Zoya'
    },
                       ]
)

1
2
3
4
2021 - 10 - 29 16: 06: 05, 982 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 16: 06: 05, 983 INFO sqlalchemy.engine.Engine UPDATE student_account SET name = ? WHERE student_account.name = ?
  2021 - 10 - 29 16 : 06 : 05, 983 INFO sqlalchemy.engine.Engine[generated in 0.00032 s](('Miss Marie', 'Mary'), ('Zoya', 'Bobby'))
2021 - 10 - 29 16: 06: 05, 984 INFO sqlalchemy.engine.Engine COMMIT

Let us look at the updated table:

1
2
3
4
5
with engine.begin() as connection:
  select_stmt = select(student_ac)
result = connection.execute(select_stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
8
2021 - 10 - 29 16: 06: 26, 665 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 16: 06: 26, 666 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account
2021 - 10 - 29 16: 06: 26, 666 INFO sqlalchemy.engine.Engine[cached since 710.3 s ago]()
2021 - 10 - 29 16: 06: 26, 666 INFO sqlalchemy.engine.Engine COMMIT(1, 'John', 16, 'second')
  (2, 'Miss Marie', 22, 'fourth')
  (3, 'Sunita', 20, 'second')
  (4, 'Zoya', 18, 'third')

Correlated updates

A correlated update statement consists of a correlated subquery when we need to use rows from another table.

1
2
3
4
5
6
7
8
9
10
11
from sqlalchemy
import select
scalar_subq = (
  select(address.c.address)
  .where(address.c.Student_id == student_ac.c.id)
  .order_by(address.c.id)
  .limit(1)
  .scalar_subquery())
update_stmt = update(student_ac)
  .values(name = scalar_subq)
print(update_stmt)

1
UPDATE student_account SET name = (SELECT address.address FROM address

Update…from

1
2
3
4
5
6
7
8
9
update(student_ac)
  .
where(student_ac.c.id == address.c.Student_id)
  .
where(address.c.address == 'India')
  .
values(name = 'Patrik')
)
print(update_stmt)

1
2
3
UPDATE student_account SET name =: name FROM address WHERE student_account.id = address.
"Student_id"
AND address.address =: address_1

Delete

The delete() function is used to create a new delete statement that deletes rows from the existing tables in the database.

1
2
3
4
5
from sqlalchemy
import delete
delete_stmt = delete(student_ac)
  .where(student_ac.c.name == "John")
print(delete_stmt)

DELETE FROM student_account WHERE student_account.name = :name_1

1
2
3
4
5
6
with engine.begin() as connection:
  connection.execute(delete_stmt)
select_stmt = select(student_ac)
result = connection.execute(select_stmt)
for row in result:
  print(row)

1
2
3
4
5
6
7
8
9
2021 - 10 - 29 16: 22: 20, 197 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 16: 22: 20, 198 INFO sqlalchemy.engine.Engine DELETE FROM student_account WHERE student_account.name = ?
  2021 - 10 - 29 16 : 22: 20, 198 INFO sqlalchemy.engine.Engine[generated in 0.00034 s]('John', )
2021 - 10 - 29 16: 22: 20, 199 INFO sqlalchemy.engine.Engine SELECT student_account.id, student_account.name, student_account.age, student_account.grade
FROM student_account
2021 - 10 - 29 16: 22: 20, 199 INFO sqlalchemy.engine.Engine[cached since 1664 s ago]()
2021 - 10 - 29 16: 22: 20, 199 INFO sqlalchemy.engine.Engine COMMIT(2, 'Miss Marie', 22, 'fourth')
  (3, 'Sunita', 20, 'second')
  (4, 'Zoya', 18, 'third')

Delete from multiple tables

1
2
3
4
delete_stmt = delete(student_ac)
  .where(student_ac.c.id == address.c.Student_id)
  .where(address.c.address == "India")
print(delete_stmt)

1
2
3
DELETE FROM student_account, address WHERE student_account.id = address.
"Student_id"
AND address.address =: address_1

Getting Affected Row Count from UPDATE, DELETE

The update and delete function returns the row count which indicates the number of rows affected.

1
2
3
4
5
6
with engine.begin() as conn:
  result = conn.execute(
    update(student_ac)
    .values(name = "Marie Sam")
    .where(student_ac.c.name == "Miss Marie"))
print(result.rowcount)

1
2
3
4
5
2021 - 10 - 29 16: 27: 37, 372 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 16: 27: 37, 374 INFO sqlalchemy.engine.Engine UPDATE student_account SET name = ? WHERE student_account.name = ?
  2021 - 10 - 29 16 : 27 : 37, 375 INFO sqlalchemy.engine.Engine[generated in 0.00040 s]('Marie Sam', 'Miss Marie')
2021 - 10 - 29 16: 27: 37, 375 INFO sqlalchemy.engine.Engine COMMIT
1

1
2
3
4
5
6
with engine.begin() as conn:
  result = conn.execute(
    update(student_ac)
    .values(name = "bobby")
    .where(student_ac.c.name == "john"))
print(result.rowcount)

1
2
3
4
5
2021 - 10 - 29 16: 29: 46, 466 INFO sqlalchemy.engine.Engine BEGIN(implicit)
2021 - 10 - 29 16: 29: 46, 467 INFO sqlalchemy.engine.Engine UPDATE student_account SET name = ? WHERE student_account.name = ?
  2021 - 10 - 29 16 : 29 : 46, 468 INFO sqlalchemy.engine.Engine[cached since 129.1 s ago]('bobby', 'john')
2021 - 10 - 29 16: 29: 46, 468 INFO sqlalchemy.engine.Engine COMMIT
0