Создадим две таблицы и добавим сотрудников (employee
).
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>
Функция sqlalchemy.create_engine()
создает новый экземпляр класса
sqlalchemy.engine.Engine
который предоставляет подключение к базе данных.
1 2 | from sqlalchemy import create_engine
engine = create_engine("sqlite:///some.db") |
Метод 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;
Объект класса 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' |
Объект класса 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')] |
Соединение закроется автоматически после выполнения 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.close()
- выполняет rollback
sqlalchemy.engine.Transaction.commit()
- подтверждает транзакциюsqlalchemy.engine.Transaction.rollback()
- отменяет транзакциюМетод 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") |
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.
# |