Домой Edit me on GitHub

2020-12-05

Каналы передачи данных | Сетевое программирование | Базы данных | Основы Веб-программирования

Соединение (engine)

Создадим две таблицы и добавим сотрудников (employee).

2.sqlalchemy/0.simple.example.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
from sqlalchemy import create_engine
import os

if os.path.exists("some.db"):
    os.remove("some.db")
e = create_engine("sqlite:///some.db")
e.execute("""
    create table employee (
        emp_id integer primary key,
        emp_name varchar
    )
""")

e.execute("""
    create table employee_of_month (
        emp_id integer primary key,
        emp_name varchar
    )
""")

e.execute("""insert into employee(emp_name) values ('ed')""")
e.execute("""insert into employee(emp_name) values ('jack')""")
e.execute("""insert into employee(emp_name) values ('fred')""")

SQL запрос:

 create table employee (
     emp_id integer primary key,
     emp_name varchar
 );

 create table employee_of_month (
     emp_id integer primary key,
     emp_name varchar
 );
insert into employee(emp_name) values ('ed');
insert into employee(emp_name) values ('jack');
insert into employee(emp_name) values ('fred');
$ sqlite3 some.db
sqlite> .tables
employee           employee_of_month
sqlite> SELECT * FROM employee;
1|ed
2|jack
3|fred
sqlite> SELECT * FROM employee_of_month;
sqlite>

create_engine

Функция sqlalchemy.create_engine() создает новый экземпляр класса sqlalchemy.engine.Engine который предоставляет подключение к базе данных.

1
2
from sqlalchemy import create_engine
engine = create_engine("sqlite:///some.db")

execute

Метод sqlalchemy.engine.Engine.execute() выполняет SQL запрос в нашем соединении и возвращает объект класса sqlalchemy.engine.ResultProxy.

1
2
3
4
result = engine.execute(
            "select emp_id, emp_name from "
            "employee where emp_id=:emp_id",
            emp_id=3)

В результате выполнится следующий SQL запрос:

select emp_id, emp_name from employee where emp_id=3;

fetchone

Объект класса sqlalchemy.engine.ResultProxy реализует некоторые методы из спецификации DB-API 2.0:

  • sqlalchemy.engine.ResultProxy.fetchone()
  • sqlalchemy.engine.ResultProxy.fetchmany()
  • sqlalchemy.engine.ResultProxy.fetchall()

Результат запроса похож на список.

1
2
row = result.fetchone()
print(row)  # (3, u'fred')

Но также выполняет функции словаря.

1
print(row['emp_name'])  # u'fred'

fetchall

Объект класса sqlalchemy.engine.ResultProxy является итератором, поэтому можно получить список всех строк в цикле.

1
2
3
4
5
6
result = engine.execute("select * from employee")
for row in result:
    print(row)
# (1, u'ed')
# (2, u'jack')
# (3, u'fred')

Тоже самое делает функция sqlalchemy.engine.ResultProxy.fetchall()

1
2
3
result = engine.execute("select * from employee")
print(result.fetchall())
# [(1, u'ed'), (2, u'jack'), (3, u'fred')]

close

Соединение закроется автоматически после выполнения SQL запроса, но можно это сделать и вручную, при помощи метода sqlalchemy.engine.ResultProxy.close()

Закрытие соединения вручную
1
result.close()

Транзакции

sqlalchemy.engine.Engine.execute() автоматически подтверждает транзакцию в текущем соединении (выполняет COMMIT)

1
2
engine.execute("insert into employee_of_month (emp_name) values (:emp_name)",
               emp_name='fred')

Мы можем контролировать соединение используя метод sqlalchemy.engine.Engine.connect()

1
2
3
4
conn = engine.connect()
result = conn.execute("select * from employee")
result.fetchall()
conn.close()

Он также дает возможность управлять транзакциями. Транзакция является объектом класса sqlalchemy.engine.Transaction и содержит в себе следующие методы:

Метод sqlalchemy.engine.Transaction.commit() позволяет вам вручную подтвердить транзакцию.

Подтверждение транзакции вручную
1
2
3
4
5
6
conn = engine.connect()
trans = conn.begin()
conn.execute("insert into employee (emp_name) values (:emp_name)", emp_name="wendy")
conn.execute("update employee_of_month set emp_name = :emp_name", emp_name="wendy")
trans.commit()
conn.close()

SQL запрос:

BEGIN;
insert into employee (emp_name) values 'wendy';
update employee_of_month set emp_name = 'wendy';
COMMIT;
Данные успешно записанны в БД
1
2
3
4
print(engine.execute("select * from employee").fetchall())
print(engine.execute("select * from employee_of_month").fetchall())
# [(1, u'ed'), (2, u'jack'), (3, u'fred'), (4, u'wendy')]
# [(1, u'wendy')]

Метод sqlalchemy.engine.Transaction.rollback() отменяет транзакцию, откатывая данные к начальному состоянию.

Отмена транзакции вручную
1
2
3
4
5
6
conn = engine.connect()
trans = conn.begin()
conn.execute("insert into employee (emp_name) values (:emp_name)", emp_name="wendy")
conn.execute("update employee_of_month set emp_name = :emp_name", emp_name="wendy")
trans.rollback()
conn.close()

SQL запрос:

BEGIN;
insert into employee (emp_name) values 'wendy';
update employee_of_month set emp_name = 'wendy';
ROLLBACK;
Данные не изменились
1
2
3
4
print(engine.execute("select * from employee").fetchall())
print(engine.execute("select * from employee_of_month").fetchall())
# [(1, u'ed'), (2, u'jack'), (3, u'fred')]
# [(1, u'fred')]

Контекстный менеджер немного упрощает это процесс:

1
2
3
with engine.begin() as conn:
    conn.execute("insert into employee (emp_name) values (:emp_name)", emp_name="mary")
    conn.execute("update employee_of_month set emp_name = :emp_name", emp_name="mary")

Полный пример

2.sqlalchemy/1.engine.py
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
# slide:: s
from sqlalchemy import create_engine
import os

if os.path.exists("some.db"):
    os.remove("some.db")
e = create_engine("sqlite:///some.db")
e.execute("""
    create table employee (
        emp_id integer primary key,
        emp_name varchar
    )
""")

e.execute("""
    create table employee_of_month (
        emp_id integer primary key,
        emp_name varchar
    )
""")

e.execute("""insert into employee(emp_name) values ('ed')""")
e.execute("""insert into employee(emp_name) values ('jack')""")
e.execute("""insert into employee(emp_name) values ('fred')""")

# # slide::
# ## title:: Engine Basics
# create_engine() builds a *factory* for database connections.

from sqlalchemy import create_engine

engine = create_engine("sqlite:///some.db")

# ## slide:: p
# Engine features an *execute()* method that will run a query on
# a connection for us.

result = engine.execute(
    "select emp_id, emp_name from "
    "employee where emp_id=:emp_id",
    emp_id=3)

# ## slide::
# the result object we get back features methods like fetchone(),
# fetchall()
row = result.fetchone()

# ## slide:: i
# the row looks like a tuple
row

# ## slide:: i
# but also acts like a dictionary
row['emp_name']

# ## slide::
# results close automatically when all rows are exhausted, but we can
# also close explicitly.
result.close()

# ## slide:: p
# result objects can also be iterated

result = engine.execute("select * from employee")
for row in result:
    print(row)

# ## slide:: p
# the fetchall() method is a shortcut to producing a list
# of all rows.
result = engine.execute("select * from employee")
print(result.fetchall())

# ## slide:: p
# The execute() method of Engine will *autocommit*
# statements like INSERT by default.

engine.execute("insert into employee_of_month (emp_name) values (:emp_name)",
               emp_name='fred')

# ## slide:: p
# We can control the scope of connection using connect().

conn = engine.connect()
result = conn.execute("select * from employee")
result.fetchall()
conn.close()

# ## slide:: p
# to run several statements inside a transaction, Connection
# features a begin() method that returns a Transaction.

conn = engine.connect()
trans = conn.begin()
conn.execute("insert into employee (emp_name) values (:emp_name)",
             emp_name="wendy")
conn.execute("update employee_of_month set emp_name = :emp_name",
             emp_name="wendy")
trans.commit()
conn.close()

# ## slide:: p
# a context manager is supplied to streamline this process.

with engine.begin() as conn:
    conn.execute("insert into employee (emp_name) values (:emp_name)",
                 emp_name="mary")
    conn.execute("update employee_of_month set emp_name = :emp_name",
                 emp_name="mary")


# ## slide::
# ## title:: Exercises
# Assuming this table:
#
#     CREATE TABLE employee (
#         emp_id INTEGER PRIMARY KEY,
#         emp_name VARCHAR(30)
#     }
#
# And using the "engine.execute()" method to invoke a statement:
#
# 1. Execute an INSERT statement that will insert
#    the row with emp_name='dilbert'.
#    The primary key column can be omitted so that it is
#    generated automatically.
#
# 2. SELECT all rows from the employee table.
#
Previous: SQLAlchemy ORM Next: Метаданные (metadata)