I'm trying to download several game data over threads from a website(s). Whenever a list of data is filled after an inner loop then it's written to a sqlite3 database. However, when it gets to the line where it calls python sqlite3 function "executemany", this function throws an exception that states the following:
SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140681089709888 and this is thread id 140680470963968.
I kept looking up for the cause of this error, and came to the conclusion (idk if that really is the problem) that maybe a thread must have an unique connection object to the database and therefore an unique cursor. All the same, and excuse me for my ignorance (i'm a beginner as to this realms), before moving onwards i would like to be advised if there is any.
This is all the code, other custom modules just contain constants and user-defined exceptions. I think the main focus should be where i call the threads, in the end of the program.
CODE:
from bs4 import BeautifulSoup
from string import ascii_lowercase
from datetime import date
from vandal_constants import *
from PIL import Image
from functools import partial
from exceptions import NoTitleException
from rawgpy import RAWG
from io import BytesIO
import requests
import json
import threading
import sqlite3
import concurrent.futures
# GLOBALS #####
FROM_RAWG = False
INSERT_SQL = ''
# CONSTANTS #####
connection = sqlite3.connect('/media/l0new0lf/LocalStorage/data.db')
cursor = connection.cursor()
headers = {
'accept-language': 'es-ES'
}
rawg = RAWG('A Collector')
#################
def rindex(list_, item, limit=0):
i = len(list_) - 1
while i >= limit:
if list_[i] == item:
return i
i -= 1
return -1
def index(list_, item, limit=None, start=0):
limit = len(list_)-1 if not limit else limit
i = start
while i <= limit:
if list_[i] == item:
return i
i += 1
return -1
#def write_genres_to_file(site_genres):
# with open('/media/l0new0lf/LocalStorage/generos.txt', 'w') as file_:
# json.dump(site_genres, file_, indent=2,
# sort_keys=True, ensure_ascii=False)
def download_image(tag=None, game=None, rawg_game=None):
if tag:
return sqlite3.Binary(requests.get(url).content) if (url := tag['data-src']) else None
elif game:
global FROM_RAWG
img_tag = game.select_one(IMG_TAG_SELECTOR)
if img_tag and img_tag.get('data-src', None):
return sqlite3.Binary(requests.get(img_tag['data-src']).content)
FROM_RAWG = True
if rawg_game and getattr(rawg_game, 'background_image', None):
return sqlite3.Binary(requests.get(rawg_game.background_image).content)
return None
def construct_sql_insert(db_table='juegos', **kwargs):
return f'INSERT INTO {db_table} ({",".join(iter(kwargs.keys()))}) VALUES ({",".join(["?"] * len(kwargs))})'
def prepare_game_record(game):
global INSERT_SQL
title = game.select_one(TITLE_TAG_SELECTOR).text
if not title:
raise NoTitleException()
description = game.select_one(DESCRIPTION_TAG_SELECTOR)
rawg_game = rawg.search(title)[0]
game_data = {
'nombre': title,
'descripcion': description.text if description else rawg_game.description,
'genero': genres if (genres := translate_genres(game.select_one(GENRES_TAG_SELECTOR).contents[1].strip().split(' / '))) else '',
'fondo': resize_image(img) if (img := download_image(game=game, rawg_game=rawg_game)) and not FROM_RAWG else img,
'year': None,
}
if not INSERT_SQL:
INSERT_SQL = construct_sql_insert(**game_data)
if hasattr(rawg_game, 'released'):
game_data['year'] = date.fromisoformat(rawg_game.released).year
return tuple(game_data.values())
def resize_image(image, dimensions=300):
img = Image.open(BytesIO(image))
if not isinstance(dimensions, tuple):
if img.size[0] > img.size[1]:
dimensions = [dimensions, int(
float(dimensions / img.size[0]) * img.size[1])]
elif img.size[0] < img.size[1]:
dimensions = [int(float(dimensions / img.size[1])
* img.size[0]), dimensions]
else:
dimensions = [dimensions, dimensions]
temp = BytesIO()
img.resize(dimensions, Image.NEAREST).save(temp, format='jpeg')
return sqlite3.Binary(temp.getvalue())
def translate_genres(genres, check_translated=False):
"""Function that translates genres to another genre or a set of them or checks whether the passed in list is translated already, depending on check_translated boolean param."""
length = len(genres)
translation_keys = translations.keys()
if not check_translated:
# Make a set-like object from the keys of the translation dictionary in order that i'll be able to make sure whether a genre can be translated or not without performance issues.
for i in range(length):
# If this genre has its own translation then:
if genres[i] in translation_keys:
# If this genre is translated into a compound of 2 or more genres then
if isinstance(translations[genres[i]], list):
for genre in translations[genres[i]]:
# If at the start of translated genres in the genres list (right after the end of the original ones) this genre cannot be found, whether there is no translated genre or not.
if index(genres, genre, start=length) == -1:
genres.append(genre)
# else (Almost same logic) if its a one-to-one translation then just append the element at the end of the list, if not found.
elif index(genres, translations[genres[i]], start=length) == -1:
genres.append(translations[genres[i]])
return '/'.join(genres[length:])
if isinstance(genres, str):
genres = genres.split('/')
for genre in genres:
if genre not in translations_value:
return False
return True
def get_all_games_from_letter(letter):
global FROM_RAWG
counter = 1
hashes_set = set()
while counter < 2:
prepared_games = []
rq = requests.get(
f'https://vandal.elespanol.com/juegos/13/pc/letra/{letter}/inicio/{counter}')
if rq:
print('Request GET: from ' +
f'https://vandal.elespanol.com/juegos/13/pc/letra/{letter}/inicio/{counter}' + ' Got Workable HTML !')
else:
print('Request GET: from ' +
f'https://vandal.elespanol.com/juegos/13/pc/letra/{letter}/inicio/{counter}' + ' Not Working !!, getting next page!')
continue
if rq.status_code == 301 or rq.status_code == 302 or rq.status_code == 303 or rq.status_code == 304:
print(f'No more games in letter {letter}\n**REDIRECTING TO **')
break
counter += 1
soup = BeautifulSoup(rq.content, 'lxml')
main_table = soup.select_one(GAME_SEARCH_RESULTS_TABLE_SELECTOR)
if hash(main_table.get_text()) not in hashes_set:
hashes_set.add(hash(main_table.get_text()))
else:
print('Repeated page ! I\'m done with this letter.')
break
game_tables = main_table.find_all(
'table', {'class': GAME_TABLES_CLASS})
print('entering game_tables loop')
for game in game_tables:
FROM_RAWG = False
try:
game_record = prepare_game_record(game)
except NoTitleException:
continue
if not game_record:
continue
prepared_games.append(game_record)
print('Game successfully prepared !')
if prepared_games:
print(f'Thread, Writing to Database')
try:
cursor.executemany(INSERT_SQL, prepared_games)
connection.commit()
except Exception as err:
print(err)
print('done')
#get_all_games_from_letter('a')
with concurrent.futures.ThreadPoolExecutor(len(ascii_lowercase)) as executor:
for letter in ascii_lowercase:
executor.submit(get_all_games_from_letter, letter)
connection.close()
OUTPUT:
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Thread, Writing to Database
SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140681089709888 and this is thread id 140680479356672.
done
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Thread, Writing to Database
SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140681089709888 and this is thread id 140680470963968.
done
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Game successfully prepared !
Thread, Writing to Database
SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140681089709888 and this is thread id 140680996136704.
done
Game successfully prepared !
Game successfully prepared !
Thread, Writing to Database
SQLite objects created in a thread can only be used in that same thread. The object was created in thread id 140681089709888 and this is thread id 140681021314816.
done
And...a lot more like these ones. It is printed game successfully prepared from prepare_game_record function
NOTE: Roughly, what is the problem and what's a good approach to solve it. Also I would like to know in case my guess was right or mostly, why every thread must have an unique connection to a database.