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)