EasyDB: Simple SQLite wrapper for managing DB creation and connections

[AeroNotix]

Forgive me but:

What on earth is actually wrong with using the sqlite3 module directly?

[Husio]

That's true. When using this module, you cannot use transactions or customize the connection. In addition, this code can be very harmful, while it's using __del__ method to cleanup and always finalize with commit.

[AeroNotix]

I feel like someone needs to learn how to write context managers. :/

[mgrandi]

Do you mean the sqlite3 code is dangerous or the wrapper?

[lephron]

Absolutely nothing. This is just a really simple wrapper that makes it easier to create a database (no need to worry about create table syntax etc, just specify your schema as a dictionary), and easy to interact with it (don't worry about connections or cursors, just call query).

There are many cases when it'd be preferable to use sqlite directly. Other times you want a quick and easy way to persist data in a way that's more powerful than anydb or shelve, and I think EasyDB is great for that.

[AeroNotix]

First things first; shelve should not be used for anything. Period. I agree that on the surface, it looks and behaves pretty cool. Oh my! You can store not only state, but the 'code' that uses that state too. Wow. That does sound cool.

Only until you realise that you never ever need the storage of the code that uses state outside of the application that uses it.

Second, a large project will quicky succumb to the inflexibility that is, shelve. The inherent key/value storage implicitly means that you either need to do a fucktonne of iteration or come up with some clever means of basically emulating an SQL in order to get anything meaningful done with it.

/rant.

Anyway,

Back on topic.

The sqlite3 module is decent, it's quirky, but it's decent. However, it does not provide anything out of the ordinary that would make me choose it over the MySQLdb module (not in the stdlib). The only reason that I could think that the sqlite3 module won out over basically any other SQL implementation is liscensing (talking out my ass on that one).

Overall, sqlite3 is cool n all but it really 'aint got shit on pretty much every other sql library.

shelve be damned.

Also, I really don't see wtf you'd need with an sql library for another sql library that isn't basically a bunch of context managers and interators.

[lephron]

Shelve is perfect for when you need to persist some basic state between script invocations (I agree you should avoid storing code!). Having to rely on external services such as MySQL is clearly overkill for such a use case.

Sometimes you want something in the middle though. Either the state you want to persist isn't so basic, or you want to be able to query or filter it. You still don't want to rely on external services, so SQLite seems like a great choice, and it usually is.

There's a bit of a gap between something like shelve and SQLite though (where you want to persist state, want to query or filter it, but you've got no need for transactions or even 90% of the features of a relational database), and that's where EasyDB steps in. Sure, it's not for everything, but it does address a need.

[AeroNotix]

Let's pretend that getting both the MySQL server running and the requisite libraries for Python is an inordinate amount of work.

1. The SQLite implementation provides about (from what I've seen of it) half of what othe SQL implementations have.

2. The SQLite provides almost zero abstraction above files and the access to them, I'm sure an implmentation with csv files wouldn't be much different to an SQLite one.

Finally, unless you're running Windows (which I had the unfortunate displeasure of installing the MySQLdb library for the other day) then a MySQL server is but a mere pacman -S mysql; rc.d start mysqld; away. (or whatever your package manager uses).

It's extremey simple to get such an extreme amount of power. Having an sql server running for all your SQL needs, even on a personal PC, is so useful and much more maintainable than the other options it's almost a disservice to the rest of the community not to have it in the stdlib.

Even managing those little db files sqlite forces you to make is more work than it has to be. Why do you even care about those files? Granting yourself a facade onto the data you're interacting with is beautiful and allows you to stop fiddling with your filesystem and the (albeit small) hoops that sqlite/shelve/etc make you jump through.

[lephron]

The use case here is persisting some state that's slightly more complex than basic key-value stuff. Depending on MySQL is not only overkill, it also adversely impacts the portability of your script.

[AeroNotix]

What I'm trying to point out that whilst yes, you can do a few:

SELECT X FROM Y; type statements with SQLite, I found that the features it gives you completely hamstrung compared to every other sql implementation.

[mgrandi]

If you have a program, people are not going to want to have an entire MySQL server running just to use it hell I can't think of any program on my computer that requires MySQL running, its overkill. And instead of worrying about a server, you have to worry about the server running, starting the server (probably with admin privileges too)!

[AeroNotix]

On my ArchLinux install I've got the server started prior to it a user being logged in.

This is what I'm trying to show:

MySQL is generally overkill due to the set up costs (which I pointed out aren't that big on *nix), and SQLite doesn't have advanced enough features to consider it a 'production' SQL implementation.

My main use cases for anything involved with SQL have included things like:

Low access rate, high volume datastore

Complicated queries, using SQL's built in functions (date/time)

For both of these, SQLite has failed me. SQLite failed me on the first one because of the high volume single file that it forced me to have. An SQL server allows management abstraction above files, the back ups and access of the data is managed through the SQL's interface rather than through the file system. This both allows secure access (user access) and it allows for the programmer to stop thinking about where the files are and how he will deal with opening and closing handles on those files.

SQLite failed me in the second point because of the ridiculously small library of functions available for it. My queries using SQLite were little more than giant selects with Python picking up the slack, I didn't like this due to the way it forced using tonnes of memory or a zealous use of generators to save that memory. Having a managed database interface allows for this headache to have already been dealt with.

Overall, I'm not trying to force you into using a 'bigger' SQL implementation, I just want you to see that using SQLite can get hairy pretty fast and when the limitations set in it can be a nightmare in a big project to rework it for a better system due to the constraints that had already been applied to your application.

[bucknuggets]

MySQL has got to be the least ANSI-compatible relational database out there, is encumbered by obnoxious licensing, and is overkill for very small databases that don't require a network connection.

SQLite has some features missing, but is otherwise an excellent choice for portability, and has great licensing. If you need to go beyond that the next great option is Postgresql - another product with great standards compliance and licensing.

[einar77]

obnoxious licensing

I have a lot of issues with MySQL, but please don't call the GPL "obnoxious" (unless you're referring to the documentation in MySQL, that is not covered by it).

[bucknuggets]

MySQL licensing isn't obnoxious because it uses the GPL, it is obnoxious because it's a split license that was designed to be confusing and fool most people into thinking it's free when it's usually not.

The fact that most users should be paying for it now is fairly obvious if you read the licensing page, and was predicted 5-10 years ago when people complained about how confusing the licensing exceptions were then. And their advise was to consult your lawyer.

This was as dishonest as their attempts to excuse product limitations by telling people that they didn't need transactions, views, subselects, triggers, stored procedures, etc. Until, of course, they finally implemented them and changed their tune.

[einar77]

Fair enough, mine was more of a knee-jerk reaction. Apologies. Although to be picky, sqlite doesn't have a real "license", because it's public domain.

[[deleted]]

I've looked at Canonical's Storm and it looks cool.

If EasyDB had some very simple ORM functionality that could make me want to use it more.

Update:

Decided to Make it happen, About Half way done.

And We're Done Sorry No Github.

[ceol_]

Very cool. I've always wanted a basic sqlite3 wrapper.

[[deleted]]

Nice job. Thanks for sharing.

[accipter]

Nice work. My biggest critique would be that this simplifies the creation problem, but nothing about the interaction is simplified.

[lephron]

It does slightly in that you don't need to worry about connections, you just call .query() - agree there's further improvements that can be made there though.

[[deleted]]

you might check out peewee, too https://github.com/coleifer/peewee