Getting SQLite3 to work with multiple threads

user_7066668 0
Viewed 7973

I'm making a web crawler in Python that collects redirects/links, adds them to a database, and enters them as a new row if the link doesn't already exist. I want like to use multi-threading but having trouble because I have to check in real time if there is an entry with a given URL.

I was initially using sqlite3 but realised I can't use it simultaneously on different threads. I don't really want to use MySQL (or something similar) as it needs more disk space and runs as separate server. Is there anyway to make sqlite3 work with multiple threads?

multithreading sqlite multiprocessing python
3 Answers

This is how to make sqlite work in multiple threads.

Use BlockingCollection with ThreadPool.QueueUserWorkItem. Any database query are queued and executed in FIFO (First In First Out) order. Now the database is never locked while doing any SQL transaction from any thread. This is an example in C#.

public class DatabaseQueueBus
{
    private BlockingCollection<TransportBean> _dbQueueBus = new BlockingCollection<TransportBean>(new ConcurrentQueue<TransportBean>());
    private CancellationTokenSource __dbQueueBusCancelToken;

    public CancellationTokenSource _dbQueueBusCancelToken { get => __dbQueueBusCancelToken; set => __dbQueueBusCancelToken = value; }

    public DatabaseQueueBus()
    {
        _dbQueueBusCancelToken = new CancellationTokenSource();
        DatabaseQueue();

    }

    public void AddJob(TransportBean dto)
    {
        _dbQueueBus.Add(dto);
    }

    private void DatabaseQueue()
    {

        ThreadPool.QueueUserWorkItem((param) =>
        {
            try
            {
                do
                {
                    string job = "";
                    TransportBean dto = _dbQueueBus.Take(_dbQueueBusCancelToken.Token);
                    try
                    {
                        job = (string)dto.DictionaryTransBean["job"];
                        switch (job)
                        {
                            case "SaveClasse":
                                //Save to table here
                                break;
                            case "SaveRegistrant":
                                //Save Registrant here
                                break;
                          
                        }
                    }
                    catch (Exception ex)
                    {//TODO: Handle this exception or not

                    }

                } while (_dbQueueBusCancelToken.Token.IsCancellationRequested != true);
            }
            catch (OperationCanceledException)
            {

            }
            catch (Exception ex)
            {

            }
        });
    }
}
user_114562330
Related