sqlite is my favorite database (beginner - intermediate) anthony explains
HTML-код
- Опубликовано: 22 авг 2024
- today I talk about sqlite -- how to get started with it, where you should use it, and why I like it so much!
playlist: • anthony explains
==========
twitch: / anthonywritescode
dicsord: / discord
twitter: / codewithanthony
github: github.com/aso...
stream github: github.com/ant...
I won't ask for subscriptions / likes / comments in videos but it really helps the channel. If you have any suggestions or things you'd like to see please comment below!
I use sqlite3 as database for most of my projects (I mostly just need read-only data). One useful command to know is ".mode table" which makes query results much more readable, especially when you have multiline strings in one of the columns!
Except when the content texts or column names are so long, the table's format become distorted
Table mode works great until there is a really long named column or the content texts are really long. What do you do then?
I just use ".mode column" with headers turned on
SQLite is great! Love the in-memory feature as it allows you to write tests for a web app without having to spin up a whole database (like postgres) or you can also plot it to a file storage for backup and recovery and whatnot. Super super nice.
Woah, 500! Just 12 until a big round-number milestone!
thank you anthony i am a beginnre coder and i was told sqlite is the best fo rthe small pet project im working on
this is very helpful and easy to digest!
Sqlite is incredible. You can even embed it into application binaries.
Happy 500th
Happy 500th video!!
Regarding concurrency: WAL mode broaw.
Multiple writers works fine with Sqlite, just use 'PRAGMA busy_timeout' when you open the database. You can test it like this:
seq 1 10 | xargs -P 5 --replace sqlite3 db.db "PRAGMA busy_timeout = 5000" "insert into wat values ({}')"
if by "works fine" you mean busy loops and waits while the whole db is locked then sure -- but you might as well use a mutex in code then
@@anthonywritescode Yes you could, but this is build in and works the same on Windows and Linux.
Ah, this is the reason why Ive never seen concurrent write failures myself, python's default timeout value is 5s, so it just waits in a busy loop if I have 'concurrent writers'
#500 !!! congrats !
Interesting that you mention prototyping. What I found most frustrating with SQL are database migrations. I want a new column, different datatype, new table, change the key, but somehow keep the data. How do you deal with these things?
for me migrations are an extreme rarity so I haven't bothered with any complexity for them. when they come up I do a 3 stage rollout and forget that the old schema ever existed
I've been watching sqlite videos for quite some time and I can't wrap my head around this, how is the whole ecosystem so bullish on a db where migrations are such a pita
In-memory databases are great for high i/o apps with transient data, like multiplayer game states.
It's excellent for typical any single user desktop PC applications.
it would be so cool to see how you did the twitch chat database step by step. great cxontent. thank you!
it was all developed on twitch! there's vods originally on this channel but then on @anthonywritescode-vods
Would you use orm like sqlalchemy or do you like your queries raw?
I rarely reach for an ORM, mostly dealing with parametrized plain queries. then again I'm rarely at a scale where the complexity warrants an ORM
i only want to open an .exe file that is saved as sqlite
why is it so hard ?
i still cant figure it out
How can I use my own data?
Don't have a clue what I just watched. My phone downloads this torrent all by itself every night which consists of a jpg, an SQLite and an XML. I delete it and the next time I go to sleep I wake up to find the same 15K file. Anyone know what could be going on?
Do you have any good videos on sync between SQLite on android and a MySQL server?
Very nice video. Thanks! For a Flat File DB, I find SQLite amazingly fast and easy to interface with GAMBAS in Linux as my GUI. The ONLY issue I've been unable to solve is to have a copy on my NAS {RasPi running OpenMediaVault} or just a RasPi with a thumb drive so that 2 users, can read/write to it {but never at the same time, usually hours apart} Reads are fine but I can never write to it. on my NAS or standalone Pi and it's thumb drive. Every attempt to CHOWN or setting permissions to 777 fail.
As I run SSDs on everything I'm very interested in the in-memory feature to minimize writes especially during the development/testing/debugging phase.
File locking on network devices is buggy both on Linux and Windows. Maybe thats the issue.
You commented a year ago, but if you still have the same problem here is a solution:
Since the read/write operations are hours appart, and assuming your db is not huge, you can just copy the db to local disk, do whatever needed, and upload the file back to the network device
Any alternatives that are good at handling multiple writers ? I looking for an alternative with python binding.
Whats a wat
Great Video. Any chance you could share your terminal theme?
Isn't that just Ubuntu default, or maybe even gnome default.
Hi. What keyboard is that?
there's an faq playlist linked in the channel which has a video about the keyboards
Hi, do you know where to host django project with sqlite online?
duckdb has replaced sqllite for me.
i tried duckdb too. i guess it depends what use case you use it for. transactions i would keep in sqlite. duckdb still has no backwards compatibility in storage format, need to be careful with that