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)
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
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()
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()
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
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()
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()
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()
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
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()
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()
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
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()
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]
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()
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())
]
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()
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)