def upsert(model, data, update_field, engine):
"""
upsert实现
依据session.bind.dialect得到当前数据库类型
然后生成对应的upsert语句，当前支持mysql，postgresql，sqlite三种数据库
on_duplicate_key_update for mysql
on_conflict_do_nothing for postgresql
insert or ignore for sqlite
Parameters
----------
model : Base
orm model
data : list
update_field : list
engine
_engine.Engine instance
Returns
-------
insert
insert statement
"""
if engine.dialect.name == 'mysql':
stmt = mysql_insert(model).values(data)
d = {f: getattr(stmt.inserted, f) for f in update_field}
return stmt.on_duplicate_key_update(**d)
elif engine.dialect.name == 'postgresql':
stmt = postgres_insert(model).values(data)
return stmt.on_conflict_do_nothing(index_elements=[update_field[0]])
elif engine.dialect.name == 'sqlite':
stmt = insert(model).values(data).prefix_with('OR IGNORE')
return stmt
else:
raise Exception(f"can't support {engine.dialect.name} dialect")
def player_insert_on_conflict_update():
if Config.db_engine == "mysql":
query_insert = mysql_insert(scoreboard)
return query_insert.on_duplicate_key_update(
name=query_insert.inserted.name,
team=query_insert.inserted.team,
alive=query_insert.inserted.alive,
ping=query_insert.inserted.ping,
kills=query_insert.inserted.kills,
headshots=query_insert.inserted.headshots,
assists=query_insert.inserted.assists,
deaths=query_insert.inserted.deaths,
shots_fired=query_insert.inserted.shots_fired,
shots_hit=query_insert.inserted.shots_hit,
mvps=query_insert.inserted.mvps,
score=query_insert.inserted.score,
disconnected=query_insert.inserted.disconnected)
elif Config.db_engine == "psycopg2":
query_insert = postgresql_insert(scoreboard)
return postgresql_insert(scoreboard).on_conflict_do_update(
set_=dict(name=query_insert.inserted.name,
team=query_insert.inserted.team,
alive=query_insert.inserted.alive,
ping=query_insert.inserted.ping,
kills=query_insert.inserted.kills,
headshots=query_insert.inserted.headshots,
assists=query_insert.inserted.assists,
deaths=query_insert.inserted.deaths,
shots_fired=query_insert.inserted.shots_fired,
shots_hit=query_insert.inserted.shots_hit,
mvps=query_insert.inserted.mvps,
score=query_insert.inserted.score,
disconnected=query_insert.inserted.disconnected))
else:
return scoreboard.insert
async def bind_character_external_id(cls, character_id: int, external_type: ExternalType, external_id: str):
query = mysql_insert(cls.t).values(
character_id=character_id,
type=int(external_type),
external_id=external_id,
).on_duplicate_key_update(
external_id=external_id,
)
await table.execute(query)
async def bind_character_to_campaign(cls, character_id: int, name: str, campaign_id: int):
query = mysql_insert(cls.t).values(
character_id=character_id,
name=name,
campaign_id=campaign_id,
).on_duplicate_key_update(
name=name,
)
await table.execute(query)
def on_user_conflict() -> Any:
"""Used for updating a users on conflict.
"""
if Config.db_engine == "mysql":
query_insert = mysql_insert(user_table)
return query_insert.on_duplicate_key_update(
name=query_insert.inserted.name,
timestamp=query_insert.inserted.timestamp)
elif Config.db_engine == "psycopg2":
query_insert = postgresql_insert(user_table)
return query_insert.on_conflict_do_update(
set_=dict(name=query_insert.inserted.name,
timestamp=query_insert.inserted.timestamp))
else:
return user_table.insert
def on_scoreboard_conflict() -> Any:
"""Used for updating a player on a scoreboard on conflict.
"""
if Config.db_engine == "mysql":
query_insert = mysql_insert(scoreboard_table)
return query_insert.on_duplicate_key_update(
team=query_insert.inserted.team,
alive=query_insert.inserted.alive,
ping=query_insert.inserted.ping,
kills=scoreboard_table.c.kills + query_insert.inserted.kills,
headshots=scoreboard_table.c.headshots +
query_insert.inserted.headshots,
assists=scoreboard_table.c.assists + query_insert.inserted.assists,
deaths=scoreboard_table.c.deaths + query_insert.inserted.deaths,
shots_fired=scoreboard_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=scoreboard_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=scoreboard_table.c.mvps + query_insert.inserted.mvps,
score=scoreboard_table.c.score + query_insert.inserted.score,
disconnected=query_insert.inserted.disconnected)
elif Config.db_engine == "psycopg2":
query_insert = postgresql_insert(scoreboard_table)
return query_insert.on_conflict_do_update(
set_=dict(team=query_insert.inserted.team,
alive=query_insert.inserted.alive,
ping=query_insert.inserted.ping,
kills=scoreboard_table.c.kills +
query_insert.inserted.kills,
headshots=scoreboard_table.c.headshots +
query_insert.inserted.headshots,
assists=scoreboard_table.c.assists +
query_insert.inserted.assists,
deaths=scoreboard_table.c.deaths +
query_insert.inserted.deaths,
shots_fired=scoreboard_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=scoreboard_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=scoreboard_table.c.mvps +
query_insert.inserted.mvps,
score=scoreboard_table.c.score +
query_insert.inserted.score,
disconnected=query_insert.inserted.disconnected))
else:
return scoreboard_table.insert
def create_upsert_mysql(table, record):
"""Creates a statement for inserting the passed record to the passed
table; if the record already exists, the existing record will be updated.
This uses MySQL `on_duplicate_key_update` (hence upsert), and that
why the returned statement is valid only for MySQL tables. Refer to this
`SqlAlchemy MySQL documentation`_ for more information.
The created statement is not executed by this function.
Args:
table (sqlalchemy.sql.schema.Table): database table metadata.
record (dict): a data record, corresponding to one row, to be inserted.
Returns:
sqlalchemy.sql.dml.Insert: a statement for inserting the passed
record to the specified table.
.. _SqlAlchemy MySQL documentation:
https://docs.sqlalchemy.org/en/latest/dialects/mysql.html#mysql-inser-on-duplicate-key-update
"""
insert_stmt = mysql_insert(table).values(record)
return insert_stmt.on_duplicate_key_update(**record)
def put_data(self,
key: bytes,
value: bytes,
is_result: bool = False) -> None:
key = ensure_bytes(key)
if self.engine.name == "postgresql":
from sqlalchemy.dialects.postgresql import insert as pg_insert
insert_stmt = pg_insert(KV).values(queue=self.name,
key=key,
value=value)
self.engine.execute(
insert_stmt.on_conflict_do_update(
index_elements=[KV.c.queue, KV.c.key],
set_={"value": insert_stmt.excluded.value},
))
elif self.engine.name == "mysql":
from sqlalchemy.dialects.mysql import insert as mysql_insert
insert_stmt = mysql_insert(KV).values(queue=self.name,
key=key,
value=value)
self.engine.execute(
insert_stmt.on_conflict_do_update(
value=insert_stmt.inserted.value))
else:
with self.engine.begin() as conn:
exists = conn.execute(
self.kvs(select,
literal(True)).where(KV.c.key == key)).scalar()
if exists is None:
query = KV.insert().values(queue=self.name,
key=key,
value=value)
else:
query = self.kvs(update).where(KV.c.key == key).values(
value=value)
conn.execute(query)
def on_statistic_conflict() -> Any:
"""Used for updating a statistics on conflict.
"""
if Config.db_engine == "mysql":
query_insert = mysql_insert(statistic_table)
return query_insert.on_duplicate_key_update(
kills=statistic_table.c.kills + query_insert.inserted.kills,
headshots=statistic_table.c.headshots +
query_insert.inserted.headshots,
assists=statistic_table.c.assists + query_insert.inserted.assists,
deaths=statistic_table.c.deaths + query_insert.inserted.deaths,
shots_fired=statistic_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=statistic_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=statistic_table.c.mvps + query_insert.inserted.mvps)
elif Config.db_engine == "psycopg2":
query_insert = postgresql_insert(statistic_table)
return query_insert.on_conflict_do_update(
set_=dict(kills=statistic_table.c.kills +
query_insert.inserted.kills,
headshots=statistic_table.c.headshots +
query_insert.inserted.headshots,
assists=statistic_table.c.assists +
query_insert.inserted.assists,
deaths=statistic_table.c.deaths +
query_insert.inserted.deaths,
shots_fired=statistic_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=statistic_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=statistic_table.c.mvps +
query_insert.inserted.mvps))
else:
return statistic_table.insert