1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110import psycopg2
from database.access import connection
from logger.config import logger
from database.access import connection
from config import SITEMAP_FREQUENCY
from datetime import datetime, timedelta
def execute_select(query, params=None, fetchone=True):
"""
Execute a query on the database and return the result.
Args:
query (str): The SQL query to execute.
params (tuple, optional): Parameters to pass to the query. Defaults to None.
fetchone (bool, optional): Whether to fetch only the first row of results. Defaults to True.
Returns:
Any: The result of the query.
"""
# Connect to the database
conn = connection()
conn.open()
# Create a cursor
cur = conn.conn.cursor()
# Execute the query
cur.execute(query, params)
# Fetch the results if requested
result = None
if fetchone:
result = cur.fetchone() if cur.rowcount > 0 else None
else:
result = cur.fetchall()
# Close the cursor and connection
cur.close()
conn.close()
return result
def next_sitemap_url():
query = """
SELECT url, domain_id
FROM meta.sitemaps
WHERE crawl_uuid = '' OR crawl_uuid IS NULL
ORDER BY
CASE WHEN crawl_uuid = '' OR crawl_uuid IS NULL
THEN created_at
ELSE (SELECT ended_at FROM events.crawls WHERE crawl_uuid = meta.sitemaps.crawl_uuid)
END ASC
LIMIT 1;
"""
result = execute_select(query)
# print(f"Result from query: {result}") # Add this line to see the result
if result:
url, domain_id = result
logger.info(f'Sitemap Selected for Domain {domain_id}: {url}')
return url, domain_id
else:
logger.error('Unable to select sitemap')
return None, None
def get_useragent():
query = """
SELECT user_agent, id as "user_agent_id"
FROM meta.user_agents
WHERE active=TRUE
ORDER BY RANDOM()
LIMIT 1;
"""
result = execute_select(query)
logger.debug
if result:
user_agent, user_agent_id = result
logger.info(f'Useragent Selected: {user_agent}')
return user_agent, user_agent_id
else:
logger.error('No Useragent Returned')
return None, None
def select_pending_sitemap_count():
query = """
SELECT count(*)
FROM meta.sitemaps
WHERE crawl_uuid is NULL OR events.crawls.ended_at < %s
"""
# calculate the datetime value SITEMAP_FREQUENCY hours ago
frequency = timedelta(hours=SITEMAP_FREQUENCY)
past_time = datetime.utcnow() - frequency
# Connect to the database
conn = connection()
conn.open()
# Create a cursor
cur = conn.conn.cursor()
# Execute the query
cur.execute(query, (past_time,))
# Fetch the result
count = cur.fetchone()[0]
# Close the cursor and connection
cur.close()
conn.close()
logger.info(f'{count} Sitemaps to Crawl ')
return count