Python postgresql.insert方法代码示例

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def process_entities(self, tlo: Any) -> None:
        rows = self._entities_to_rows(tlo)
        if not rows:
            return

        t = self.Entity.__table__
        ins = insert(t)
        upsert = ins.on_conflict_do_update(constraint=t.primary_key, set_={
            "hash": ins.excluded.hash,
            "username": ins.excluded.username,
            "phone": ins.excluded.phone,
            "name": ins.excluded.name,
        })
        with self.engine.begin() as conn:
            conn.execute(upsert, [dict(session_id=self.session_id, id=row[0], hash=row[1],
                                       username=row[2], phone=row[3], name=row[4])
                                  for row in rows]) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def store_scan_doc(self, scan):
        scan = scan.copy()
        if 'start' in scan:
            scan['start'] = datetime.datetime.utcfromtimestamp(
                int(scan['start'])
            )
        if 'scaninfos' in scan:
            scan["scaninfo"] = scan.pop('scaninfos')
        scan["sha256"] = utils.decode_hex(scan.pop('_id'))
        insrt = insert(self.tables.scanfile).values(
            **dict(
                (key, scan[key])
                for key in ['sha256', 'args', 'scaninfo', 'scanner', 'start',
                            'version', 'xmloutputversion']
                if key in scan
            )
        )
        if config.DEBUG:
            scanfileid = self.db.execute(
                insrt.returning(self.tables.scanfile.sha256)
            ).fetchone()[0]
            utils.LOGGER.debug("SCAN STORED: %r", utils.encode_hex(scanfileid))
        else:
            self.db.execute(insrt) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def set_show(self, string_id):
		"""
			Set current show.
		"""
		shows = self.metadata.tables["shows"]
		with self.engine.begin() as conn:
			# need to update to get the `id`
			query = insert(shows).returning(shows.c.id)
			query = query.on_conflict_do_update(
				index_elements=[shows.c.string_id],
				set_={
					'string_id': query.excluded.string_id,
				},
			)
			self.show_id, = conn.execute(query, {
				"name": string_id,
				"string_id": string_id,
			}).first() 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def update_fact_notification_status(data, process_day, notification_type):
    table = FactNotificationStatus.__table__
    FactNotificationStatus.query.filter(
        FactNotificationStatus.bst_date == process_day,
        FactNotificationStatus.notification_type == notification_type
    ).delete()

    for row in data:
        stmt = insert(table).values(
            bst_date=process_day,
            template_id=row.template_id,
            service_id=row.service_id,
            job_id=row.job_id,
            notification_type=notification_type,
            key_type=row.key_type,
            notification_status=row.status,
            notification_count=row.notification_count,
        )
        db.session.connection().execute(stmt) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def _insert_inbound_sms_history(subquery, query_limit=10000):
    offset = 0
    inbound_sms_query = db.session.query(
        *[x.name for x in InboundSmsHistory.__table__.c]
    ).filter(InboundSms.id.in_(subquery))
    inbound_sms_count = inbound_sms_query.count()

    while offset < inbound_sms_count:
        statement = insert(InboundSmsHistory).from_select(
            InboundSmsHistory.__table__.c,
            inbound_sms_query.limit(query_limit).offset(offset)
        )

        statement = statement.on_conflict_do_nothing(
            constraint="inbound_sms_history_pkey"
        )
        db.session.connection().execute(statement)

        offset += query_limit 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def insert_or_update_returned_letters(references):
    data = _get_notification_ids_for_references(references)
    for row in data:
        table = ReturnedLetter.__table__

        stmt = insert(table).values(
            reported_at=datetime.utcnow().date(),
            service_id=row.service_id,
            notification_id=row.id,
            created_at=datetime.utcnow()
        )

        stmt = stmt.on_conflict_do_update(
            index_elements=[table.c.notification_id],
            set_={
                'reported_at': datetime.utcnow().date(),
                'updated_at': datetime.utcnow()
            }
        )
        db.session.connection().execute(stmt) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def dao_create_or_update_daily_sorted_letter(new_daily_sorted_letter):
    '''
    This uses the Postgres upsert to avoid race conditions when two threads try and insert
    at the same row. The excluded object refers to values that we tried to insert but were
    rejected.
    http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#insert-on-conflict-upsert
    '''
    table = DailySortedLetter.__table__
    stmt = insert(table).values(
        billing_day=new_daily_sorted_letter.billing_day,
        file_name=new_daily_sorted_letter.file_name,
        unsorted_count=new_daily_sorted_letter.unsorted_count,
        sorted_count=new_daily_sorted_letter.sorted_count)
    stmt = stmt.on_conflict_do_update(
        index_elements=[table.c.billing_day, table.c.file_name],
        set_={
            'unsorted_count': stmt.excluded.unsorted_count,
            'sorted_count': stmt.excluded.sorted_count,
            'updated_at': datetime.utcnow()
        }
    )
    db.session.connection().execute(stmt) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def batch_upsert_records(
    session: Session, table: Table, records: List[Dict[str, Any]]
) -> None:
    """Batch upsert records into postgresql database."""
    if not records:
        return
    for record_batch in _batch_postgres_query(table, records):
        stmt = insert(table.__table__)
        stmt = stmt.on_conflict_do_update(
            constraint=table.__table__.primary_key,
            set_={
                "keys": stmt.excluded.get("keys"),
                "values": stmt.excluded.get("values"),
            },
        )
        session.execute(stmt, record_batch)
        session.commit() 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def __init__(self, db):
        self.db = db
        self.invalidated_pageids = set()

        wspc_sync = self.db.ws_parser_cache_sync
        wspc_sync_ins = insert(wspc_sync)

        self.sql_inserts = {
            "templatelinks": self.db.templatelinks.insert(),
            "pagelinks": self.db.pagelinks.insert(),
            "imagelinks": self.db.imagelinks.insert(),
            "categorylinks": self.db.categorylinks.insert(),
            "langlinks": self.db.langlinks.insert(),
            "iwlinks": self.db.iwlinks.insert(),
            "externallinks": self.db.externallinks.insert(),
            "redirect": self.db.redirect.insert(),
            "section": self.db.section.insert(),
            "ws_parser_cache_sync":
                wspc_sync_ins.on_conflict_do_update(
                    constraint=wspc_sync.primary_key,
                    set_={"wspc_rev_id": wspc_sync_ins.excluded.wspc_rev_id}
                )
        } 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def _set_sync_timestamp(self, timestamp, conn=None):
        """
        Set a last-sync timestamp for the grabber. Writes into the custom
        ``ws_sync`` table.

        :param datetime.datetime timestamp: the new timestamp
        :param conn: an existing :py:obj:`sqlalchemy.engine.Connection` or
            :py:obj:`sqlalchemy.engine.Transaction` object to be re-used for
            execution of the SQL query
        """
        ws_sync = self.db.ws_sync
        ins = insert(ws_sync)
        ins = ins.on_conflict_do_update(
                    constraint=ws_sync.primary_key,
                    set_={"wss_timestamp": ins.excluded.wss_timestamp}
                )
        entry = {
            "wss_key": self.__class__.__name__,
            "wss_timestamp": timestamp,
        }

        if conn is None:
            conn = self.db.engine.connect()
        conn.execute(ins, entry) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def create_upsert_postgres(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 PostgreSQL `on_conflict_do_update` (hence upsert), and that
    why the returned statement is just valid for PostgreSQL tables. Refer to
    this `SqlAlchemy PostgreSQL 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 PostgreSQL documentation:
       https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#insert-on-conflict-upsert
    """
    insert_stmt = postgres_insert(table).values(record)
    return insert_stmt.on_conflict_do_update(
        index_elements=[col for col in table.primary_key],
        set_=record
    ) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def test_bad_args(self):
        assert_raises(
            ValueError,
            insert(self.tables.users).on_conflict_do_nothing,
            constraint="id",
            index_elements=["id"],
        )
        assert_raises(
            ValueError,
            insert(self.tables.users).on_conflict_do_update,
            constraint="id",
            index_elements=["id"],
        )
        assert_raises(
            ValueError,
            insert(self.tables.users).on_conflict_do_update,
            constraint="id",
        )
        assert_raises(
            ValueError, insert(self.tables.users).on_conflict_do_update
        ) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def test_on_conflict_do_nothing(self):
        users = self.tables.users

        with testing.db.connect() as conn:
            result = conn.execute(
                insert(users).on_conflict_do_nothing(),
                dict(id=1, name="name1"),
            )
            eq_(result.inserted_primary_key, (1,))
            eq_(result.returned_defaults, None)

            result = conn.execute(
                insert(users).on_conflict_do_nothing(),
                dict(id=1, name="name2"),
            )
            eq_(result.inserted_primary_key, (1,))
            eq_(result.returned_defaults, None)

            eq_(
                conn.execute(users.select().where(users.c.id == 1)).fetchall(),
                [(1, "name1")],
            ) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def test_on_conflict_do_nothing_connectionless(self, connection):
        users = self.tables.users_xtra

        result = connection.execute(
            insert(users).on_conflict_do_nothing(constraint="uq_login_email"),
            dict(name="name1", login_email="email1"),
        )
        eq_(result.inserted_primary_key, (1,))
        eq_(result.returned_defaults, (1,))

        result = connection.execute(
            insert(users).on_conflict_do_nothing(constraint="uq_login_email"),
            dict(name="name2", login_email="email1"),
        )
        eq_(result.inserted_primary_key, None)
        eq_(result.returned_defaults, None)

        eq_(
            connection.execute(
                users.select().where(users.c.id == 1)
            ).fetchall(),
            [(1, "name1", "email1", None)],
        ) 

# 需要导入模块: from sqlalchemy.dialects import postgresql [as 别名]
# 或者: from sqlalchemy.dialects.postgresql import insert [as 别名]
def test_on_conflict_do_update_one(self):
        users = self.tables.users

        with testing.db.connect() as conn:
            conn.execute(users.insert(), dict(id=1, name="name1"))

            i = insert(users)
            i = i.on_conflict_do_update(
                index_elements=[users.c.id], set_=dict(name=i.excluded.name)
            )
            result = conn.execute(i, dict(id=1, name="name1"))

            eq_(result.inserted_primary_key, (1,))
            eq_(result.returned_defaults, None)

            eq_(
                conn.execute(users.select().where(users.c.id == 1)).fetchall(),
                [(1, "name1")],
            )