Untitled attachment

Notices where this attachment appears

1. Embed this notice
   pwm (pwm@darkdork.dev)'s status on Tuesday, 01-Jul-2025 06:46:55 JST pwm

   in reply to

   @Pi_rat @p @SuperDicq

   I had read about autoincrement being slow but I thought alternative to it would be me generating unique ids myself. I will fix code to use rowids.

   I may have not made myself clear here, you need not fuss with rowids yoursefl, you may simply use integer primary key

   I thought it would be optimized. I will refactor around this. I was going for dicts since qmark style is going to be deprecated (and as furthur along you have said, I agree dicts are cleaner)

   This is a good intuition! In other database drivers (psycopg2 for example), executemany is optimized.

   As you have pointed out, Im thinking of upsert syntax to just ignore duplicate inserts. Originally I was trying to figure out a way to have "SELECT id FROM images WHERE image IN ?" and I tried every combination of qmark and dicts but it was no dice but could be done with string formatting, but I rather not use it since Im doing parametrized queries everywhere else, that would be bad.

   Good catch here, I don't know how I missed using upsert here but you're absolutely right, this is a good use case for it.

   Ah yes I have autocommit turned on as long as there are no exceptions, I will add explicit commit. I was not even closing db mistaking that context manager will handle closing it for me. Just learned today that I have to close cursor too.

   When you use the actual python context manager syntax,

   with sqlite3.connect("yourdb.sqlite") as db: with db.cursor() as cursor: do your query etc etc ... here the cursor is now closed db.commit() here the database connection is now closed

   In this case, the python context manager will automatically call the appropriate close function as you fall out of the indented block. If you do it in the imperative style you have been doing you must call close() on each cursor and database connection yourself. In a purely technical sense, all that is being cleaned up when the program exits, and this is not a long-running server process. But it's good practice to use either of these two methods to keep things tidy yourself. Many other objects use this protocol iirc you used it when opening and reading a file.

   Is it correct to use "sqlite3" directly and using functions and passing cursor around them, writing sql statements inside function. Im not sure if there is standard way to do this but personally it seems off.

   You may pass the connection or cursor object in a function call. However, things get hairier if you start to multithread or multiprocess. sqlite3 module objects are not threadsafe and you will get programming errors if you try to use e.g. a cursor outside of the thread it was created in. This is not really a relevant concern here, though.

   As for code style, it might be slightly "cleaner" to pass the database object to functions that use the database, create a cursor for the scope of the function, and close the cursor before you return. This will still alow the callee function control over, e.g. committing to the database in case of a DML statement.

   Since python is OOP shouldnt I be creating classes and adding methods? i.e (cursor.select_image(image_id) == cursor.execute("SELECT image FROM images WHERE id = ?", (image_id,))

   It is bad practice to attach methods to objects after the object is instantiated, which is what you would be doing were you to do as you stated in this example.

   I wouldn't get too caught up in trying to follow any particular paradigm here, your codebase is small and it's a for-fun project. Short of using an ORM to abstract away handling the database connection, carefully passing a connection object around is a-ok, and what the ORNM is doing under the hood, anyway.

   I LOVE sql(ite) and will read all the Docs you have linked with fervour. Thank you VERY much for giving such detailed review. I will follow you up when I implement queries.

   You're welcome! SQLite is one of my favorite pieces of software, too, if you couldn't tell! I'm a sql and python guy so I'm happy to help out and had fun reading through this and providing commentary today.