Esempio n. 1
0
def _create_table(
    df: pd.DataFrame,
    cursor: pg8000.Cursor,
    table: str,
    schema: str,
    mode: str,
    index: bool,
    dtype: Optional[Dict[str, str]],
    varchar_lengths: Optional[Dict[str, int]],
) -> None:
    if mode == "overwrite":
        _drop_table(cursor=cursor, schema=schema, table=table)
    elif _does_table_exist(cursor=cursor, schema=schema, table=table):
        return
    postgresql_types: Dict[str, str] = _data_types.database_types_from_pandas(
        df=df,
        index=index,
        dtype=dtype,
        varchar_lengths_default="TEXT",
        varchar_lengths=varchar_lengths,
        converter_func=_data_types.pyarrow2postgresql,
    )
    cols_str: str = "".join(
        [f"{k} {v},\n" for k, v in postgresql_types.items()])[:-2]
    sql = f'CREATE TABLE IF NOT EXISTS "{schema}"."{table}" (\n{cols_str})'
    _logger.debug("Create table query:\n%s", sql)
    cursor.execute(sql)
Esempio n. 2
0
def _does_table_exist(cursor: pg8000.Cursor, schema: Optional[str],
                      table: str) -> bool:
    schema_str = f"TABLE_SCHEMA = '{schema}' AND" if schema else ""
    cursor.execute(f"SELECT true WHERE EXISTS ("
                   f"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE "
                   f"{schema_str} TABLE_NAME = '{table}'"
                   f");")
    return len(cursor.fetchall()) > 0
Esempio n. 3
0
 def get_all_nodes(cursor: pg8000.Cursor) -> Iterable[NdResp]:
     """
     :param cursor: Database cursor.
     :return: All nodes from database.
     """
     cursor.execute(f'''
         SELECT {NODE_COLUMN_ORDER}
         FROM nodes
         ORDER BY last_checked_at;
     ''')
     return cursor.fetchall()
Esempio n. 4
0
 def get_tweet(id_: int, cursor: pg8000.Cursor) -> TwResp:
     """
     Returns single tweet from database.
     :param id_: ID of tweet to get.
     :param cursor: Database cursor.
     :return: Tweet with provided ID.
     """
     cursor.execute(
         f'''
         SELECT {TWEET_COLUMN_ORDER}
         FROM tweets WHERE id=%s;
     ''', (id_, ))
     return cursor.fetchone()
Esempio n. 5
0
    def delete_all_nodes(cursor: pg8000.Cursor) -> bool:
        """
        Deletes all nodes from the database.
        Only used when joining a network for updating the stale node list.

        :param cursor: Database cursor.
        :return:
            Flag indicating if nodes were deleted or not. It is possible that
            nodes weren't deleted if the list was previously empty.
        """
        cursor.execute(f'''
        DELETE FROM nodes;''')
        return cursor.rowcount > 0
Esempio n. 6
0
    def get_all_tweets(cursor: pg8000.Cursor) -> Iterable[TwResp]:
        """
        Returns all tweets from database.

        :param cursor: Database cursor.
        :return: All tweets from database.
        """
        cursor.execute(f'''
            SELECT {TWEET_COLUMN_ORDER}
            FROM tweets
            ORDER BY created_at DESC
        ''')
        return cursor.fetchall()
Esempio n. 7
0
 def get_node(name: str, cursor: pg8000.Cursor) -> NdResp:
     """
     :param name: Name of the node to get.
     :param cursor: Database cursor.
     :return: Node with provided name.
     """
     cursor.execute(
         f'''
         SELECT {NODE_COLUMN_ORDER}
         FROM nodes
         WHERE name=%s;
     ''', (name, ))
     return cursor.fetchone()
Esempio n. 8
0
    def insert_tweet(tweet: str, cursor: pg8000.Cursor) -> TwResp:
        """
        Inserts new tweet and returns id of the created row

        :param tweet: Content of the tweet to add.
        :param cursor: Database cursor.
        :return: ID of the tweet that was created.
        """
        cursor.execute(
            f'''
            INSERT INTO tweets (tweet) VALUES (%s)
            RETURNING {TWEET_COLUMN_ORDER};
        ''', (tweet, ))
        return cursor.fetchone()
Esempio n. 9
0
    def delete_tweet(id_: int, cursor: pg8000.Cursor) -> bool:
        """
        Deletes tweet with provided ID from database.

        :param id_: ID of tweet to delete.
        :param cursor: Database cursor.
        :return:
            Boolean indicating if tweet with ID was deleted (False if tweetpyc
            does not exist).
        """
        cursor.execute(
            '''
            DELETE FROM tweets where id=%s
        ''', (id_, ))
        return cursor.rowcount > 0
Esempio n. 10
0
    def search_tweets(content: Optional[str], from_created: Optional[datetime],
                      to_created: Optional[datetime],
                      from_modified: Optional[datetime],
                      to_modified: Optional[datetime], retweet: Optional[bool],
                      cursor: pg8000.Cursor) -> Iterable[TwResp]:
        """
        :param content: Content to search in tweet.
        :param from_created: Start time for tweet creation.
        :param to_created: End time for tweet creation.
        :param from_modified:
            Start time for tweet modification.
        :param to_modified:
            End time for tweet modification.
        :param retweet:
            Flag indication if retweet or original tweets should be searched.
        :param cursor: Database cursor.
        """
        where: List[str] = []
        params: List[Union[str, datetime]] = []
        if content is not None:
            where.append('tweet ILIKE %s')
            params.append(f'%{content}%')
        if from_created is not None:
            where.append('created_at > %s')
            params.append(from_created)
        if to_created is not None:
            where.append('created_at < %s')
            params.append(to_created)
        if from_modified is not None:
            where.append('modified_at > %s')
            params.append(from_modified)
        if to_modified is not None:
            where.append('modified_at < %s')
            params.append(to_modified)
        if retweet is not None:
            where.append('type = %s')
            params.append('retweet')

        where_clause = 'WHERE ' + ' AND '.join(where) if len(where) > 0 else ''

        cursor.execute(
            f'''
            SELECT {TWEET_COLUMN_ORDER}
            FROM tweets
            {where_clause}
            ORDER BY created_at DESC
        ''', tuple(params))
        return cursor.fetchall()
Esempio n. 11
0
    def insert_node(name: str, address: str, cursor: pg8000.Cursor) -> NdResp:
        """
        Inserts new nodes to database.

        :param name: Name of new node.
        :param address: Address of new node.
        :param cursor: Database cursor.
        :return: Node that was inserted.
        """
        cursor.execute(
            f'''
            INSERT INTO nodes (name, address)
            VALUES (%s, %s)
            RETURNING {NODE_COLUMN_ORDER};
        ''', (name, address))
        return cursor.fetchone()
Esempio n. 12
0
    def delete_node(name: str, cursor: pg8000.Cursor) -> bool:
        """
        Deletes node from the database.

        :param name: Name of the node to delete.
        :param cursor: Database cursor.
        :return:
            Flag indicating if node was deleted or not. It is possible that
            node was not deleted if it was not found.
        """
        cursor.execute(
            f'''
            DELETE FROM nodes
            WHERE name = %s;
        ''', (name, ))
        return cursor.rowcount > 0
Esempio n. 13
0
    def create_retweet(server: str, ref: str, cursor: pg8000.Cursor) -> TwResp:
        """
        Creates retweet in database that references server and tweet ID
        provided in parameters.

        :param server: Server name of original tweet.
        :param ref: Tweet reference (ID) on original server.
        :param cursor: Database cursor.
        :return: Newly created tweet.
        """
        cursor.execute(
            f'''
            INSERT INTO tweets (type, reference)
            VALUES (%s, %s)
            RETURNING {TWEET_COLUMN_ORDER};
        ''', ('retweet', f'{server}#{ref}'))
        return cursor.fetchone()
Esempio n. 14
0
    def count_tweets(type_: str, cursor: pg8000.Cursor) -> int:
        """
        Returns number of tweets of specified type.

        :param type_: Type of tweet to count.
        :param cursor: Database cursor.
        """
        where = ''
        params = []
        if type_:
            where = 'WHERE type=%s'
            params.append(type_)
        cursor.execute(
            f'''
            SELECT count(*)
            FROM tweets
            {where}
        ''', tuple(params))
        return cursor.fetchone()[0]
Esempio n. 15
0
    def update_node(name: str, address: str, cursor: pg8000.Cursor) -> NdResp:
        """
        Updates existing node.

        :param name: Name of the node to update.
        :param address: New address to set for the node.
        :param cursor: Database cursor.
        :return: Node that was updated.
        """
        cursor.execute(
            f'''
            UPDATE nodes
            SET
                address = %s
            WHERE
                name = %s
            RETURNING {NODE_COLUMN_ORDER};
        ''', (address, name))
        return cursor.fetchone()
Esempio n. 16
0
def map_results_into_object(
        cursor: pg8000.Cursor,
        query: str,
        args: any,
        type_object: typing.Type
) -> list:
    """
    :param cursor:
    :param query:
    :param args:
    :param type_object:
    :return:
    """
    cursor.execute(query, args)
    columns = get_column_name(cursor)
    return [
        type_object(**{
            column[1]: row[column[0]]
            for column in enumerate(columns)
        }) for row in list(cursor.fetchall())
    ]
Esempio n. 17
0
    def modify_tweet(id_: int, new_content: str,
                     cursor: pg8000.Cursor) -> TwResp:
        """
        Updates tweet content.

        :param id_: ID of tweet to update.
        :param new_content: New tweet content.
        :param cursor: Database cursor.
        :return:
            Tweet that was update, if tweet with provided ID was found, None
            otherwise.
        """
        cursor.execute(
            f'''
            UPDATE tweets SET
            tweet=%s,
            modified_at=%s
            WHERE id=%s
            RETURNING {TWEET_COLUMN_ORDER};
        ''', (new_content, datetime.utcnow(), id_))
        return cursor.fetchone()
Esempio n. 18
0
def _drop_table(cursor: pg8000.Cursor, schema: Optional[str],
                table: str) -> None:
    schema_str = f'"{schema}".' if schema else ""
    sql = f'DROP TABLE IF EXISTS {schema_str}"{table}"'
    _logger.debug("Drop table query:\n%s", sql)
    cursor.execute(sql)