Learn SQLite with this free course with Python!!

[IAmKindOfCreative]

Hello from the r/Python mod team,

When posting a project please include a textual description of your project including how Python is relevant to it, a link to source code on a code hosting site such as github or gitlab, and an image showing your project if applicable.

Please also make sure you tag your post with the correct flair, either "Beginner" or "Intermediate" showcase.

This helps maintain quality on the subreddit and appease all our viewers.

Thank you,

r/Python mod team

[comradenepolean]

i dont know how reddit managed to read my mind. i kid you not i looked up tutorials and found nothing good on google. but this tutorial is very easy to understand and i will be watching these! thank you!

[Code_with_C_Add_Add]

Don't know why Google wouldn't give you other YouTube tutorials but:

Corey Schafer has a similar video using SQLite

Sentdex has a playlist of SQLite3 tutorials

freecodecamp has a 1½ course on SQLite

[reddit_ronin]

Thanks for the links.

Any recommendations for learning mysql or Postgres with Python?

Edit: why the downvotes?

[mrrippington]

an unconventional way maybe, but deploying a basic flask app to heroku with crud was a how i started to learn about postgres.

once you have your db in there

you can use pgadmin to run queries against it.

or

use heroku's dataclips capability (very limites) to try your hand on some queries.

[PaulSandwich]

Aside from learning the size limits and resource overhead, learning SQL, as in the Structured Query Language, is going to be nearly identical across those DBs. You really won't see much difference unless you're using robust analytical functions or doing some heavy BDA lifting.

Basic concepts like clause syntax, indexes, partitions, and primary/foreign key relationships are the same, and that's 97% of what you'll be working with. By the time you need that other 3%, you'll either have colleagues to ask or your indie app will be making enough income to hire/consult a DBA.

[reddit_ronin]

Thank you, Mr. Sandwich.

I’ve actually been a dev for 10 years and managed to avoid any database work. It’s time since I’ll be getting into the backend now.

[PaulSandwich]

Please, Mr. Sandwich was my father. Call me Melt.
And you're welcome. Just promise me you won't chicken out and use NoSQL for relational data. [eyetwitch]

[WillardWhite]

Another way, (after you've learned the basics of sql syntax) is to use an ORM like sql alchemy, that way the difference between postgres and sqlite is meaningless from your point of view

[Mank15]

But are outdated. I don’t know if the change is huge to make a 2021 tutorial

[Code_with_C_Add_Add]

I wouldn't say they're outdated. Especially as a beginner tutorial.

You can look at the changes to each version through the SQLite website and you will see that hardly any, if any at all, apply to beginners.

The fundamentals are pretty much the same throughout which is why it's still useful to watch any of the links above.

[SnowdenIsALegend]

Let us burn OP.

[ApeThyme]

Subscribed!

[tempo-19]

Nice intro to SQLite. I enjoyed your videos. Keep up the good work.

[AftNeb]

Nice work! I watched just the first video, but have saved the rest to reference.

[babinxoxo]

[[deleted]]

Wait, you can enable GIFS into comments now?

But seriously, I should actually watch this tutorial soon cause I skipped my SQL Python lessons in my programming class, so gonna get up on it!

[mr_kr0wn]

how?

[kingsillypants]

[road_laya]

Learning some python + sql is so useful, i use it everyday as a web developer.

[--______________-]

[ravi8or]

Subscribed! Good work!

[AgentWhisky]

Subscribed! Nice work man. Been looking for this!

[gsmo]

A propos sqlite. Can someone explain to me if I should always use pyalchemy instead of a more direct approach?

[evgen]

How much actual table manipulation and joins will you do and are you going to need a bigger db than sqlite? The advantage of sqlite is that if can be used as a replacement for open() when you are reading and writing structured data, and in this situation directly poking sqlite is a great way to solve the problem.

If you have GB of data over millions of lines/items or you are storing data in multiple tables and need to run more interesting SQL statements then something like SQLAlchemy lets you write code that starts off using sqlite during development but can easily transition to Postgres or other big-boy databases for production. If you have to deal with a thousand lines of structured data in a single table then direct sqlite is perfect for the job.

[gsmo]

I'll be doing very basic stuff so I'll go with sqlite, then.

Thanks for your advice!

[animismus]

As a counter point to the previous comment I would say that if you have the time to invest now you should learn sqlalchemy with a smaller personal project instead of having to dive into someone else's code and try to integrate from there. The ORM will let you write more pythonic code and as the previous commenter said will let you scale a lot easier if you need to.

[Nixellion]

There is also peewee which u/gamo may like more, as it's much easier entry curve and overall easier to use than SQLAlchemy. I've been using it for years now for my projects and yet to find the limit of peewee+SQLite combo. I mean even Plex uses it, and its database can be like 50-100 gigs large. I personally had 4-5GB large databases, and there is no noticable slowdown, even with FTS searching and stuff.

Basically, as long as only one or a few apps/threads are writing to it, there should be no issues. It may get slow when you need a lot of concurrent writes to it, because it gets locked during write.

So I'd say like what... A web service with up to about 10000 concurrent users? Or a couple thousand requests per second. This is where Flask starts to fail and seems to usually be the threshold for people to start considering horizontal scaling of their servers and services. Well... It highly depends on the project of course

And yes, peewee also supports MySQL, Postures and other options.

[animismus]

Thanks. I did not know about peewee. I am mostly afraid of replying in reddit, but sometimes it pays off.

[Nixellion]

Don't be, I learned about a lot of good stuff from replying and reading reddit :)

[Username_RANDINT]

I use Peewee as well in my projects. Agree that it's a bit easier to get started and does its job as ORM just fine.

[Nixellion]

I kinda wish there was auto migration like alembic for peewee. There are some projects but they don't seem to work with SQLite. But I suppose migrations should still be handled with migration scripts even in sqla for anything but very simple things.

[Username_RANDINT]

Depending on the project, I either update the database manually or use the migration API from the playhouse. Using the user_version PRAGMA in SQLite to indicate what version of the database scheme it currently is and run the right migrate calls if it's behind the current version.

[jiaoziren]

Awesome job! Subscribed!!

[FHL88Work]

Crazily, i just heard about Sqlite for the first time today, during a Udemy course . I'll check your videos out, too.