Sqlalchemy python update table

CRUD-операции с помощью SQLAlchemy Core

SQL Expression Language — это способ писать инструкции в приложениях Python вне зависимости от типа используемой базы данных.

Будем использовать таблицу созданную в предыдущей статье.

Вставка (добавление) записей

Есть несколько способов вставить записи в базу данных. Основной — метод insert() экземпляра Table . Его нужно вызвать, после чего использовать метод values() и передать значения для колонок в качестве аргументов-ключевых слов:

 
ins = customers.insert().values( first_name = 'Dmitriy', last_name = 'Yatsenko', username = 'Moseend', email = 'moseend@mail.com', address = 'Shemilovskiy 2-Y Per., bld. 8/10, appt. 23', town = ' Vladivostok' ) print(ins)
 
Чтобы увидеть, какой SQL код будет сгенерирован в результате, достаточно вывести ins :
 
INSERT INTO customers (first_name, last_name, username, email, address, town, created_on, updated_on) VALUES (:first_name, :last_name, :username, :email, :address, :town, :created_on, :updated_on)
 
Стоит обратить внимание на то, что внутри оператора VALUES находятся связанные параметры (параметры в формате :name), а не сами значения, переданные в метод values() .
 
И только при выполнении запроса в базе данных диалект заменит их на реальные значения. Они также будут экранированы, что исключает вероятность SQL-инъекций.
 
Посмотреть на то, какие значения будут на месте связанных параметров, можно с помощью такой инструкции: ins.compile().params .
 
Инструкция создана, но не отправлена в базу данных. Для ее вызова нужно вызвать метод execute() на объекте Connection :
 
 
ins = customers.insert().values( first_name = 'Dmitriy', last_name = 'Yatsenko', username = 'Moseend', email = 'moseend@mail.com', address = 'Shemilovskiy 2-Y Per., bld. 8/10, appt. 23', town = ' Vladivostok' ) conn = engine.connect() r = conn.execute(ins)
 
Этот код вставляет следующую запись в таблицу customers :
 
 
Метод execute() возвращает объект типа ResultProxy . Последний предоставляет несколько атрибутов, один из которых называется inserted_primary_key . Он возвращает первичный ключ вставленной записи.
 
Еще один способ создания инструкции для вставки — использование функции insert() из библиотеки sqlalchemy.
 
 
from sqlalchemy import insert ins = insert(customers).values( first_name = 'Valeriy', last_name = 'Golyshkin', username = 'Fortioneaks', email = 'fortioneaks@gmail.com', address = 'Narovchatova, bld. 8, appt. 37', town = 'Magadan' ) conn = engine.connect() r = conn.execute(ins) print(r.inserted_primary_key)
 
Вставка (добавление) нескольких записей
 
Вместо того чтобы передавать значения в метод values() в качестве аргументов-ключевых слов, их можно передать в метод execute() .
 
 
from sqlalchemy import insert conn = engine.connect() ins = insert(customers) r = conn.execute(ins, first_name = "Vadim", last_name = "Moiseenko", username = "Antence73", email = "antence73@mail.com", address = 'Partizanskiy Prospekt, bld. 28/А, appt. 51', town = ' Vladivostok' )
 
Метод execute() достаточно гибкий, потому что он позволяет вставить несколько записей, передав значения в качестве списка словарей, где каждый — значения для одной строки:
 
 
r = conn.execute(ins, [ < "first_name": "Vladimir", "last_name": "Belousov", "username": "Andescols", "email":"andescols@mail.com", "address": "Ul. Usmanova, bld. 70, appt. 223", "town": " Naberezhnye Chelny" >, < "first_name": "Tatyana", "last_name": "Khakimova", "username": "Caltin1962", "email":"caltin1962@mail.com", "address": "Rossiyskaya, bld. 153, appt. 509", "town": "Ufa" >, < "first_name": "Pavel", "last_name": "Arnautov", "username": "Lablen", "email":"lablen@mail.com", "address": "Krasnoyarskaya Ul., bld. 35, appt. 57", "town": "Irkutsk" >, ]) print(r.rowcount)
 
Прежде чем переходить к следующему разделу, добавим также записи в таблицы items, orders и order_lines:
 
 
items_list = [ < "name":"Chair", "cost_price": 9.21, "selling_price": 10.81, "quantity": 6 >, < "name":"Pen", "cost_price": 3.45, "selling_price": 4.51, "quantity": 3 >, < "name":"Headphone", "cost_price": 15.52, "selling_price": 16.81, "quantity": 50 >, < "name":"Travel Bag", "cost_price": 20.1, "selling_price": 24.21, "quantity": 50 >, < "name":"Keyboard", "cost_price": 20.12, "selling_price": 22.11, "quantity": 50 >, < "name":"Monitor", "cost_price": 200.14, "selling_price": 212.89, "quantity": 50 >, < "name":"Watch", "cost_price": 100.58, "selling_price": 104.41, "quantity": 50 >, < "name":"Water Bottle", "cost_price": 20.89, "selling_price": 25.00, "quantity": 50 >, ] order_list = [ < "customer_id": 1 >, < "customer_id": 1 >] order_line_list = [ < "order_id": 1, "item_id": 1, "quantity": 5 >, < "order_id": 1, "item_id": 2, "quantity": 2 >, < "order_id": 1, "item_id": 3, "quantity": 1 >, < "order_id": 2, "item_id": 1, "quantity": 5 >, < "order_id": 2, "item_id": 2, "quantity": 5 >, ] r = conn.execute(insert(items), items_list) print(r.rowcount) r = conn.execute(insert(orders), order_list) print(r.rowcount) r = conn.execute(insert(order_lines), order_line_list) print(r.rowcount)
 
Получение записей
 
Для получения записей используется метод select() на экземпляре объекта Table :
 
 
s = customers.select() print(s)
 
SELECT customers.id, customers.first_name, customers.last_name, customers.username, customers.email, customers.address, customers.town, customers.created_on, customers.updated_on FROM customers
 
Такой запрос вернет все записи из таблицы customers. Вместо этого можно также использовать функцию select() . Она принимает список или колонок, из которых требуется получить данные.
 
 
from sqlalchemy import select s = select([customers]) print(s)
 
Для отправки запроса нужно выполнить метод execute() :
 
 
from sqlalchemy import select conn = engine.connect() s = select([customers]) r = conn.execute(s) print(r.fetchall())
 
[(1, 'Dmitriy', 'Yatsenko', 'Moseend', 'moseend@mail.com', 'Shemilovskiy 2-Y Per., bld. 8/10, appt. 23', ' Vladivostok', datetime.datetime(2021, 4, 21, 17, 33, 35, 172583), datetime.datetime(2021, 4, 21, 17, 33, 35, 172583)), (2, 'Valeriy', 'Golyshkin', 'Fortioneaks', 'fortioneaks@gmail.com', 'Narovchatova, bld. 8, appt. 37', 'Magadan', datetime.datetime(2021, 4, 21, 17, 54, 30, 209109), datetime.datetime(2021, 4, 21, 17, 54, 30, 209109)). )]
 
Метод fetchall() на объекте ResultProxy возвращает все записи, соответствующие запросу. Как только результаты будут исчерпаны, последующие запросы к fetchall() вернут пустой список.
 
Метод fetchall() загружает все результаты в память сразу. В случае большого количества данных это не очень эффективно. Как вариант, можно использовать цикл для перебора по результатам:
 
 
s = select([customers]) rs = conn.execute(s) for row in rs: print(row)
 
(1, 'Dmitriy', 'Yatsenko', 'Moseend', 'moseend@mail.com', 'Shemilovskiy 2-Y Per., bld. 8/10, appt. 23', ' Vladivostok', datetime.datetime(2021, 4, 21, 17, 33, 35, 172583), datetime.datetime(2021, 4, 21, 17, 33, 35, 172583)) . (7, 'Pavel', 'Arnautov', 'Lablen', 'lablen@mail.com', 'Krasnoyarskaya Ul., bld. 35, appt. 57', 'Irkutsk', datetime.datetime(2021, 4, 22, 10, 32, 45, 364619), datetime.datetime(2021, 4, 22, 10, 32, 45, 364619))
 
Дальше список часто используемых методов и атрибутов объекта ResultProxy :
 
 
 
 
Метод/Атрибут
 
Описание
 
 
 
fetchone()
 
Извлекает следующую запись из результата. Если других записей нет, то последующие вызовы вернут None
 
 
 
fetchmany(size=None)
 
Извлекает набор записей из результата. Если их нет, то последующие вызовы вернут None
 
 
 
fetchall()
 
Извлекает все записи из результата. Если записей нет, то вернется None
 
 
 
first()
 
Извлекает первую запись из результата и закрывает соединение. Это значит, что после вызова метода first() остальные записи в результате получить не выйдет, пока не будет отправлен новый запрос с помощью метода execute()
 
 
 
rowcount
 
Возвращает количество строк в результате
 
 
 
keys()
 
Возвращает список колонок из источника данных
 
 
 
scalar()
 
Возвращает первую колонку первой записи и закрывает соединение. Если результата нет, то возвращает None
 
 
 
 
Следующие сессии терминала демонстрируют рассмотренные выше методы и атрибуты в действии, где s = select([customers]) .
 
Источник
 
How to update SQLAlchemy row entry?
 
If password matches, he proceeds further. What I would like to do is count how many times the user logged in. Thus whenever he successfully logs in, I would like to increment the no_of_logins field and store it back to the user table. I'm not sure how to run update query with SqlAlchemy.
 
7 Answers 7
 
There are several ways to UPDATE using sqlalchemy
 
1) user.no_of_logins += 1 session.commit() 2) session.query(User).\ filter(User.username == form.username.data).\ update() session.commit() 3) conn = engine.connect() stmt = User.update().\ values(no_of_logins=User.no_of_logins + 1).\ where(User.username == form.username.data) conn.execute(stmt) 4) setattr(user, 'no_of_logins', user.no_of_logins + 1) session.commit() 
 
I use setattr(query_result, key, value) for some updates in Flask SQLAlchemy followed by a commit. Any reason to discount this pattern?
 
@datamafia: You can use setattr(query_result, key, value) , which is exactly equivalent to writing query_result.key = value
 
@Hatshepsut One difference that I came across today between 4 and 1/2 is at: groups.google.com/forum/#!topic/sqlalchemy/wGUuAy27otM
 
point number 2 is failing if you won't specify class object inside query(). I mean, the final query is session.query(User).filter(User.username == form.username.data).update(<"no_of_logins": (User.no_of_logins +1)>)
 
user.no_of_logins += 1 session.commit() 
 
stackoverflow.com/a/2334917/125507 says this is a bad way to do it. Also what if the row doesn't exist yet?
 
As per endolith's link, user.no_of_logins += 1 can create race conditions. Instead use user.no_of_logins = user.no_of_logins + 1 . Translated into sql the latter correct way becomes: SET no_of_logins = no_of_logins + 1 .
 
@ChaimG I guess you meant user.no_of_logins = User.no_of_logins + 1 , or in other words use the instrumented attribute of the model to produce an SQL expression. As it is your comment displays 2 ways to produce the same race condition.
 
They are not. The former sets the attribute to an SQL expression, the latter performs an in-place addition in Python and again introduces the race.
 
@jayrizzo I'm not that familiar with SERIALIZABLE transaction isolation-level, but to my understanding it would allow performing the addition in Python using the in-place addition. If there is a race, one of the transactions will then succeed and the others will fail and must retry (with the new state of the DB). But I might've misunderstood SERIALIZABLE.
 
Examples to clarify the important issue in accepted answer's comments
 
I didn't understand it until I played around with it myself, so I figured there would be others who were confused as well. Say you are working on the user whose id == 6 and whose no_of_logins == 30 when you start.
 
# 1 (bad) user.no_of_logins += 1 # result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6 # 2 (bad) user.no_of_logins = user.no_of_logins + 1 # result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6 # 3 (bad) setattr(user, 'no_of_logins', user.no_of_logins + 1) # result: UPDATE user SET no_of_logins = 31 WHERE user.id = 6 # 4 (ok) user.no_of_logins = User.no_of_logins + 1 # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 # 5 (ok) setattr(user, 'no_of_logins', User.no_of_logins + 1) # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 
 
The point
 
By referencing the class instead of the instance, you can get SQLAlchemy to be smarter about incrementing, getting it to happen on the database side instead of the Python side. Doing it within the database is better since it's less vulnerable to data corruption (e.g. two clients attempt to increment at the same time with a net result of only one increment instead of two). I assume it's possible to do the incrementing in Python if you set locks or bump up the isolation level, but why bother if you don't have to?
 
A caveat
 
If you are going to increment twice via code that produces SQL like SET no_of_logins = no_of_logins + 1 , then you will need to commit or at least flush in between increments, or else you will only get one increment in total:
 
# 6 (bad) user.no_of_logins = User.no_of_logins + 1 user.no_of_logins = User.no_of_logins + 1 session.commit() # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 # 7 (ok) user.no_of_logins = User.no_of_logins + 1 session.flush() # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 user.no_of_logins = User.no_of_logins + 1 session.commit() # result: UPDATE user SET no_of_logins = no_of_logins + 1 WHERE user.id = 6 
 
Источник
 
Читайте также:  Python string byte encoding