🔒 aiosqlite + discord.py

[ashen saffron]

Hello! I have a problem with that my program doesnt interrupt when I press CTRL+C. I figured out that it happens because I create a single connection for the whole time but I dont close the connection

(CTRL+C works if I open a new conn to db every time)

Here is my code:

app = {}


async def execute_query(query):
    if not 'db' in app:
        app['db'] = await aiosqlite.connect('...')
    await app['db'].execute(query)
    await app['db'].commit()

Thanks for help

[analog minnow]

@ashen saffron

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.

[sullen panther]

afaik aiosqlite uses non-daemonic threads so if you forget to close your connection(s), your program can't exit

[ashen saffron]

alright, but then it means I have to open a new connection every time which is probably not the best practice

should I use threads instead of async?

[sullen panther]

sqlite connections are relatively cheap compared to actual, network ones

from my previous experiments you can experience bottlenecks as a result of excessive cpu usage if you have a lot of connections, but in return each connection can have its own transaction and use sqlite's own locking to manage concurrent transactions, instead of you needing your own lock around it

[ashen saffron]

okay, Ill look in that direction, thanks

[sullen panther]

#databases message actually the result was that having 10 connections executing 100 queries was relatively slower than 1 connection executing 1000 queries, but 1000 connections executing 1 query (aiosqlite gives each conn one thread) was actually horribly slow

[ashen saffron]

yes, I that's probably the disadvantage of creating a new connection every time, but I dont need good perfomance so I think I will be good

[sullen panther]

ideal performance when using multiple connections is generally achieved by committing as soon as possible, since queries need to acquire a lock on the database which only gets released upon commit or rollback (implied if you close a connection without committing)

sqlite's locking mechanism also has cases where connections don't have to contend with each other, and is further improved if you set the journal mode to WAL

wal mode: https://sqlite.org/wal.html
rollback journal (the default): https://sqlite.org/lockingv3.html

[ashen saffron]

Alright, Ill look at WAL and the default mode, thanks

[analog minnow]

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.