SQLAlchemy中实现更新不存在即插入

项目高并发的时候很容易出现数据库插入相同的数据，虽然可以使用唯一索引避免插入相同数据，但是不断的程序报错也是我们要避免的。

MySQL中的插入更新

使用 insert ... on duplicate key update .. 语法可以避免上述情况，举个例子

drop table if exists `test`;
create table `test` (
	`id` int(11) not null AUTO_INCREMENT,
	`name` varchar(32) not null default '',
	`update_ts` timestamp not null default current_timestamp(),
	primary key (`id`)
) engine=InnoDB default charset=utf8mb4;

主键 id 是天然的唯一索引，我们插入重复数据时会报错

1
2
3

> INSERT INTO test (id, name) VALUES (1, 'wxnacy');
> INSERT INTO test (id, name) VALUES (1, 'wxnacy');
Error 1062: Duplicate entry '1' for key 'PRIMARY'

查看插入的数据

> SELECT * FROM `test`;
| id | name   | update_ts           |
|------------------------------------
| 1  | wxnacy | 2019-05-16 22:26:58 |

下面我们来换个语句

> insert into test (id, name) values (1, 'wxnacy') on duplicate key update update_ts = current_timestamp();
> SELECT * FROM `test`;

+----+--------+---------------------+
| id | name   | update_ts           |
+----+--------+---------------------+
|  1 | wxnacy | 2019-05-16 22:39:49 |
+----+--------+---------------------+

on duplicate key update 前面是正常的插入语句，其后跟着的是当唯一索引冲突时，想要更新的数据。

再换个使用场景，如果我想让数据库中用户名是唯一的，则可以先建立唯一索引，在使用该语法。

> alter table test add unique index_name (name);
> insert into test (name) values ('wenn') on duplicate key update update_ts = current_timestamp();
> SELECT * FROM `test`;
+----+--------+---------------------+
| id | name   | update_ts           |
+----+--------+---------------------+
|  1 | wxnacy | 2019-05-16 22:49:29 |
|  2 | wenn   | 2019-05-16 22:49:49 |
+----+--------+---------------------+

> insert into test (name) values ('wenn') on duplicate key update update_ts = current_timestamp();
> SELECT * FROM `test`;

+----+--------+---------------------+
| id | name   | update_ts           |
+----+--------+---------------------+
|  1 | wxnacy | 2019-05-16 23:09:12 |
|  2 | wenn   | 2019-05-16 23:11:42 |
+----+--------+---------------------+

这样及保证了避免插入重复数据，同时程序也没有报错，我还可以根据 update 的数据来分析问题的根源。

SQLAlchemy 中的存在即更新

如果你有兴趣可以看下官方文档 INSERT…ON DUPLICATE KEY UPDATE (Upsert)

我们看先下官方文档的实例代码

from sqlalchemy.dialects.mysql import insert

insert_stmt = insert(my_table).values(
    id='some_existing_id',
    data='inserted value')

on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
    data=insert_stmt.inserted.data,
    status='U'
)

conn.execute(on_duplicate_key_stmt)

上面代码的意思是当你有一个已经存在的主键some_existing_id的时候，你去执行上面的插入操作的时候将会执行下面的对应主键的更新操作。

官方代码总是很抽象，我们来个实际的例子吧。

from sqlalchemy.dialects.mysql import insert

db_client = get_db_client()

insert_stmt = insert(table_sa).values(**data) # 如果是批量数据 这里的 data应该是一个 列表不再是字典

update_data = {
  "some_data": insert_stmt.inserted.some_data,
  "some_value": getattr(insert_stmt.inserted, "some_value"),
  "update_time": func.current_timestamp(),
}

on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update(
    **update_data
)

await db_client.execute(on_duplicate_key_stmt)

上个代码其中的data是一个字典，其中的key是数据库对用的字段。意思是当我插入到数据中的时候，当存在重复的唯一键的时候，将会直接更新数据。需要更新的内容通过update_data指定，其中的的字段通过insert_stmt.inserted.some_data获取，也可以设置其他属性比如data中没有的update_time。

注意：想要使用上面的方法，我们需要创建一个唯一索引(即使是联合唯一索引也行)