Sqlite is getting rust extensions now, and datetimes make it totally worth if if they work well and and fast, two things that don’t always go together in datetime libraries
Posts tagged: sql
All posts with the tag "sql"
8 posts
latest post 2024-07-24
Publishing rhythm
Another interesting option for slow count queries in sqlite.
If you haven’t DELETEd any records, doing:
Very interesting article by Sylvain, suggested by Simon Willison.
Definitely some things that I want to come back and try later on.
Here is the TLDR of the whole post
PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA cache_size = 1000000000; PRAGMA foreign_keys = true; PRAGMA temp_store = memory;
This is interesting, and something I need to consider. I definitely have an application with slow count queries. I am not sure how to make it better as its not a full count(*) so a count table doesn’t work, nor does counting by index.
...
sqlite-utils is primarily a cli tool for sqlite operations such as enabling full text search, and executing searches, but it also has a nice python api that is exposed and pretty straightforward to use.
from sqlite_utils import Database db = Database("database.db") db["post"].enable_fts(["title", "message", "tags]) db["post"].search("water")
This returns a generator object that you can iterate over the row objects with.
How to sort results from a sqlalchemy based orm.
.order_by(model.Entry.amount.desc())
I needed this to enable paging on my thoughts api.
As the title states sqlite-utils now supports plugins. I dug in just a bit and Simon implemented this completely with entrypoints, no framework or library at all.
sqlalchemy server_defaults end up as defaults in the database when new values are inserted.
I want to like jq, but I think Simon is selling me on sqlite, maybe its just me but this looks readable, hackable, editable, memorizable. Everytime I try jq, and its 5 minutes fussing with it just to get the most basic thing to work. I know enough sql out of the gate to make this work off the top of my head
Stepping Up My SQL Game
In 2018 I transitioned from a Product Engineering (Mechanical) role to a Data Scientist Role. I entered this space with strong subject matter expertise with our products, our data, munging through data in pyhon, and data visualization in python. My sql skills were lacking to say the least. I had learned what I needed to know to get data from our relational databases, then use pandas to do any further analysis. Just run something like the following and you have data.
SELECT * FROM Table Where col_1 = 'col_1_filter'
This technique works great for small data sets that you only need to run once. There is no shame to pull in a big dataset and start munging with it in pandas to get some results, and make decisions. The problem becomes when your dataset becomes too big or you need to run the query on a frequent basis. Doing the aggregations on the server run much quicker, as it reduces the time spent in io. My longest running steps are currently io related....
...