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