def _upsert(table: sqa.Table, engine: sqa.engine, clean_data: pd.DataFrame):
"""
insert data into a table, replacing any duplicate indices
postgres - see: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#insert-on-conflict-upsert
"""
# print(engine.dialect.dbapi.__name__)
with engine.begin() as con:
for index, row in clean_data.iterrows():
# check index uniqueness by attempting insert; if it fails, update
row = {**row.dropna().to_dict(), clean_data.index.name: index}
try:
if engine.dialect.dbapi.__name__ == 'psycopg2':
insert = pg_insert(table).values(
row).on_conflict_do_update(
index_elements=[clean_data.index.name], set_=row)
else:
insert = table.insert().values(row)
con.execute(insert)
except sqa.exc.IntegrityError:
upsert = table.update() \
.where(table.c[clean_data.index.name] == index) \
.values(row)
con.execute(upsert)
def add(self, secret: str, phrase: str, cipher: Fernet,
engine: sqlalchemy.engine, delete_date: None) -> str:
"""
Adding new secret to database
:param secret: secret's text
:param phrase: code phrase to unlock a secret
:param cipher: cryptography.fernet.Fernet
:param engine: sqlalchemy.Engine
Engine to use for connecting to database objects
:param delete_date: interval after which unlocked secret will be delete
may be None (secret will store unlimited time)
or between 1 second ana 6 days 23 hours 59 minutes 59 seconds
:return: unique secret key of stored message
"""
if self.is_empty:
self.event.set()
self.is_empty = False
with engine.begin() as conn:
query = sql.text(
"SELECT Secret.generate_secret(:phrase, :secret, :delete_date)"
)
result = conn \
.execute(query,
phrase=generate_password_hash(phrase),
secret=cipher.encrypt(str.encode(secret)),
delete_date=delete_date) \
.fetchone()[0]
secret_key = hashlib.sha256(str.encode(str(result))).hexdigest()
upd = sql.text(
"UPDATE Secret.Storage SET SecretKey = :skey WHERE StorageId = :id"
)
conn.execute(upd, skey=secret_key, id=result)
return secret_key
def _upsert(table: sqa.Table,
engine: sqa.engine,
cleaned_data: pd.DataFrame):
"""
insert data into a table, replacing any rows with duplicate indices
When upsert finds duplicate records, it overwrites ALL VALUES that are present in source DataFrame, including NaN.
postgres - see: https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#insert-on-conflict-upsert
"""
if isinstance(cleaned_data.index, pd.MultiIndex):
multi = True
names = cleaned_data.index.names
index_elements = names
else:
multi = False
names = cleaned_data.index.name
index_elements = [names]
cleaned_data = cleaned_data.astype('object')
def map2none(val):
"""sqlalchemy freaks out about NaNs, so replace them with None"""
if pd.notna(val):
return val
with engine.begin() as con:
for row in cleaned_data.reset_index(drop=False).itertuples(index=False):
# check index uniqueness by attempting insert; if it fails, update
row = {k: map2none(v) for k, v in row._asdict().items()}
try:
if engine.dialect.dbapi.__name__ == 'psycopg2':
insert = pg_insert(table).values(row).on_conflict_do_update(
index_elements=index_elements,
set_=row
)
else:
insert = table.insert().values(row)
con.execute(insert)
except sqa.exc.IntegrityError:
if multi:
upsert = table.update()
for n in names:
upsert = upsert.where(table.c[n] == row[n])
con.execute(upsert.values(row))
else:
upsert = table.update() \
.where(table.c[names] == row[names]) \
.values(row)
con.execute(upsert)
def _insert(table: sqa.Table, engine: sqa.engine, clean_data: pd.DataFrame,
auto_index: bool):
with engine.begin() as con:
rows = []
df = clean_data.dropna(axis=1, how='all')
if not auto_index:
for index, row in df.iterrows():
rows.append({**row.to_dict(), df.index.name: index})
con.execute(table.insert(), rows)
else:
for index, row in df.iterrows():
rows.append({**row.to_dict()})
con.execute(table.insert(), rows)
def _insert(table: sqa.Table, engine: sqa.engine, cleaned_data: pd.DataFrame,
auto_index: bool):
with engine.begin() as con:
rows = []
# remove completely null columns; convert to object due to bug inserting Int64
df = cleaned_data.dropna(axis=1, how='all').astype('object')
if not auto_index:
for row in df.reset_index(drop=False).itertuples(index=False):
rows.append(row._asdict())
else:
for row in df.reset_index(drop=True).itertuples(index=False):
rows.append(row._asdict())
con.execute(table.insert(), rows)
def to_sql_ignore(df: pandas.DataFrame, engine: sqlalchemy.engine,
tablename: str):
temp_table = tablename + '_temp'
df.to_sql(con=engine,
name=temp_table,
index=False,
if_exists='replace',
dtype={
'game_date': sqlalchemy.Date,
'pitchid': sqlalchemy.types.LargeBinary
})
"""
connection = engine.connect()
result = connection.execute(f'INSERT INTO {tablename} (SELECT * FROM {temp_table}) ON CONFLICT DO NOTHING')
connection.close()
"""
with engine.begin() as cn:
insert_sql = f'INSERT INTO {tablename} (SELECT * FROM {temp_table}) ON CONFLICT DO NOTHING'
cn.execute(insert_sql)
drop_sql = f'DROP TABLE {temp_table}'
cn.execute(drop_sql)
def _delete(engine: sqlalchemy.engine) -> None:
with engine.begin() as conn:
query = sql.text(
"DELETE FROM Secret.Storage WHERE DeleteDate <= CURRENT_TIMESTAMP"
)
conn.execute(query)
