🔒 Database is locked when using 2 processes

[molten lintel]

I have a sqlite databse with 3 tables. I use the multiprocessing lib to create 2 threads. One enters data into sensor_table and other process (process 2) enters data into recv_table. Once all the data has been entered, the data is concatenated on process 2. I can access the tables just fine, but once I'm done merging, Process 1 throws an error saying Error: database is locked. And I don't know why. Is there a better way to do this?

[hasty kite]

@molten lintel

Python help channel opened

Remember to:

• Ask your Python question, not if you can ask or if there's an expert who can help.
• Show a code sample as text (rather than a screenshot) and the error message, if you've got one.
• Explain what you expect to happen and what actually happens.

:warning: Do not pip install anything that isn't related to your question, especially if asked to over DMs.

Closes after a period of inactivity, or when you send !close.

[modern siren]

I doubt it's safe or possible to write to the same database from two different processes at once. SQLite databases are just basic files and don't have a server mediating access to it.

I'd try to do all writing from one process.

[molten lintel]

I don't know how to do that then with what I have set up.

lemme show you what I have

[modern siren]

!code

[hasty kite]

Formatting code on Discord

Here's how to format Python code on Discord:

```py
print('Hello world!')
```

These are backticks, not quotes. Check this out if you can't find the backtick key.

For long code samples, you can use our pastebin.

[molten lintel]

i keep going over the character lim to paste my code here 😦

    insert_recv = f'''
        INSERT INTO {recv_table_name} (date_table, time_table, location) VALUES (?, ? ,?)'''
    cursor.execute(insert_recv,(jsonString["date_table"],jsonString["time_table"], jsonString["location"]))
    conn.commit()            
    
def receive_data_from_device(client_bluetooth):
    while True:
        try:
            received_data = client_bluetooth.recv(1024).decode().strip()
            if received_data != "":
                jsonString = json.loads(received_data)
                enter_recv(jsonString=jsonString)
            elif jsonString["action"] == "Merge":
                try:
                    sql_query = f"""
                    INSERT INTO {merged_table_name} ( temp, date_table, time_table, location)
                    SELECT s.temp, s.date_table,s.time_table, r.location
                    FROM {sen_table_name} s
                    LEFT JOIN  {recv_table_name} r ON s.date_table = r.date_table AND s.time_table = r.time_table) """
                    cursor.execute(sql_query)
                except Exception as e:
                    print("merged: ", e)
        except Exception as e:
            print("Error recv:", str(e))
            break

def store_in_local_database(temp):
    try:
        cursor.execute(f''' INSERT INTO {sen_table_name} (value) VALUES (?)''', (temp,))
    except Exception as e:
        print("Error inserting data at sensor:", e)
    conn.commit()

def send_and_store(environment_data):
    while True:
        store_in_local_database(environment_data=environment_data)
def main():
    time.sleep(5)
    global received_data
    received_data = ""
    send_process = multiprocessing.Process(target=send_and_store, args=(environment_data))
    recv_process = multiprocessing.Process(target=receive_data_from_device, args=(client_bluetooth,))
    send_process.start()
    recv_process.start()```

this is the general setup I have

[modern siren]

I can't read that on my small phone screen unfortunately.

Do not use f-strings for database queries though. It's not a big deal here as long as the code only runs locally, but it allows for SQL injection, which is a massive vulnerability.

[molten lintel]

my table names are dynamic so thats why I have f strings, i think i need to find another database to do it in

this is the code I sent you:

so it ecevutes the merge tables, but then in process 1, where its storing in the database, it says database is locked for which I have not got a solution yet

[novel charm]

You can usually get away with multiple writes if you use WAL2 mode

[modern siren]

Look into multiprocessing queues. You can use them to send data out of the sub processes. Then you can do all the writes in the main process.

Or into what they mentioned if SQLite does support errors from multiple processes at once.

I didn't think it did support that though.

[novel charm]

Also rather than keeping a connection open the whole time you can open a connection for each insertion

[molten lintel]

it can read from multiple but not write from multiple, iirc? but I thought that didn't matter if I was writing to a different table in each process

[modern siren]

Writes and reads are often treated differently since writes change data. Multiple people can read a whiteboard at once, but multiple people attempting to blindly write to a whiteboard at once may lead to people overwriting other's work.

[molten lintel]

i tried this but it would say table name not found, so I just use one conn and cursor for all 3 tables and keep the connection open forever.

[novel charm]

That seems like a separate problem you need to fix

[molten lintel]

oh I fexed that one, just not the database locked

ill look into queues and wal2 modes

thank you!!

if there's another way to do this with another database im down to use it, but I see no other wayo to achieve what I want

[molten lintel]

omg it was becaue I forgot the conn.commit() and I didn't realise it

😭

im gonna close this now, thanks both for your help!!! 🙂

!close

[hasty kite]

Python help channel closed

This help channel has been closed and it's no longer possible to send messages here. If your question wasn't answered, feel free to create a new post in #1035199133436354600. To maximize your chances of getting a response, check out this guide on asking good questions.