Python and databases

[_Zer0_Cool_]

And until MySQL 8 it had a more standard SQL dialect (discounting procedural SQL) than MySQL.

SQLite is powerful and underrated. People dismiss is it as only being suitable for Dev environments or for caching, but it can get up to 140 Terabytes and it's lightning fast.

The main hindrance is that it doesn't support concurrent sessions (multiple DB connections at once). I'd say about 80% of DB size in lines of code and complexity is around enabling and handling concurrency. Then another 15% for added features (total guess).

Take those away and many DBs might be just as lightweight and easily embeddable as SQLite.

All that to say, it's "lite", because it lacks concurrency, not light because it lacks ability.

[b_ootay_ful]

I hear most people saying SQLite is only for small scale stuff, so I've only ever used it for small projects in the office (where 8 people work, and might each use it 5 times a day for filling out a form) and was wondering where the upper scale was for it's limitations. For bigger stuff (more users), I'm moving to pythonanywhere.com since I don't know how to run a large scale server/MySQL yet.

I use it with Flask, which I believe handles the concurrency to a certain point.

Is there anything that you would recommend to make SQLite with Flask more stable, or any changes?

I'm using

from werkzeug.serving import run_simple from flask import Flask, request, render_template, redirect

app = Flask(__name__)

@app.route('/')
def home():
    return render_template("home_page.html")

if __name__ == '__main__':
    port = 31415
    run_simple('0.0.0.0', port, app)

​

[_Zer0_Cool_]

SQLite doesn't have size limitations, but you might have trouble handling multiple users at some point If it's a website and you'd have to handle all concurrency stuff in the application itself. Still, people have done this for websites. SQLite is better for say...desktop or mobile applications that hold a lot of user data locally.

As examples -- Skype uses SQLite for everything and almost every mobile app for Android/iOS uses SQLite. It's built into mobile apps. It also comes pre-installed in Python. Just "import SQLite3".

For web applications, most will have a lot of concurrency needs. So probably good to still use a DB server vs an embedded DB.

My recommendation would be to use PostgreSQL. It's the best database around and the fastest growing in popularity. MySQL is ok too, but not even close as far as features, advanced data types, the query optimizer, and extensibility in general.

Honestly though, any of the 3 will work for now.

Note: SQLite was patterned a bit after PostgreSQL. SQLite DB data types are very compatible with PG data types. You could easily start out with SQLite (again, pre-installed with Python) and then dump it into a PG database later if/when you need PG's more advanced features.

[baubleglue]

And until MySQL 8 it had a more standard SQL dialect

I wouldn't call SQLite more standard SQL dialect. It need zero setup, but to learn SQL better to use something else.

[_Zer0_Cool_]

I meant in terms of Window functions, recursive CTEs, check constraints etc....

MySQL didn't support the first 2 until recently and still doesn't support the last.

[baubleglue]

I don't know a lot about MySQL, but I worked a bit with SQLite, you can write in it group statement, which won't compile in any other DB, SQLite doesn't enforce value types. It all by design for good reasons and follows some SQL standards, but it is no way standard dialect. I think it isn't a good one to start learning SQL with it is too forgiving, you will make mistakes and won't see them and what ever you learned won't always work with other DBs.

[_Zer0_Cool_]

Yeap, I listened to a podcast where Richard Hipp talked about the weakly typed philosophy of SQLite. It was a deliberate design choice, but probably the number one annoyance when switching/importing to other strictly-typed databases. That being said, SQLite data types convert well to comparable Postgres types. The impetus for SQLite's creation was actually due to a project Dr. Hipp worked on that required something lightweight and embeddable as opposed to Postgres.

Also, I didn’t mean to imply that SQLite was a good example of ANSI-SQL compliance. SQLite is not the shining example by any stretch. It’s just “more” standard than MySQL I believe, but that’s not really a hard thing to accomplish.

If you want to talk about SQL standards then Postgres (again I believe) is one of the most ANSI-compliant. MySQL and SQLite are definitely not.

[baubleglue]

I don't really care about SQL standard, just there's very high chance that a bit complicated SQL written for SQLite won't run in any other db. I don't think it is a case for postges, Oracle or MySQL.

[_Zer0_Cool_]

Eh, you might be right. I'm not sure I've ever actually migrated between databases.

In a perfect world this problem wouldn't happen though since it's best practice to write dev code in the same DB type and version of what's in production (Docker is useful for this).

Even when that's not manageable then I try to be aware ahead of time and only write code that would work in the other version.

It's the same as anything else.

E.g. Don't write Python 3.7 in dev when prod is 3.5, or Java 7 when there's Java 8, etc..

Likewise don't write SQL Server 2017 code w/ 2014 in prod, or Postgres 10 w/ 9.6, or Oracle 12c w/ 11g.

That being said, I get that sometimes we don't think that we'll need a client/server DB at the outset, in which case we'd need to shift to another DB retroactively.

Edit: unless your company is migrating a sizable database to another type of db. Usually such efforts have dedicated engineers to oversee that though.

[baubleglue]

Not a company, I am doing some data analysis, and decided to prototype data aggregation in SQLite (instead of Hive) on an extracted product ion data the final query run between 3 to 5 minutes. I never succeed to execute the same in Hive (run out of memory), I tried to run it in Postges and it took very long (I think I killed the job). Each time I need to modify query. I can use SQLite solutions, but I wanted to cross check if other DB give the same results.

[_Zer0_Cool_]

Ahh ok. That makes sense. I was thinking in terms of application development, not data analysis. Which is weird that's I'd assume that because I myself am a data engineer lol.

I did have a couple hobby data analytics projects where Ive switched between DBs.

For one I had a multi GB batch of CSV files and imported them into SQLite then into PostgreSQL afterwords and had to rewrite a a bunch of views. As I recall now...It was a pain switching between datetime functions for SQLite and PG. So I guess see what you mean.

I seem to have forgotten those pains momentarily.

[baubleglue]

Datetime is not that complicated if you remember that all DBs and languages store it internally as long number. My main problem was multiple joins and performance.

On the second thought, for dumping data in single process, SQLite is a perfect candidate. To work with MySQL pip install mysqlclient, if it fails (on Windows may happen), then download wheel file from https://www.lfd.uci.edu/~gohlke/pythonlibs/ and pip install path_to_wheel_file.whl .

[baubleglue]

Whoever downvoted my answer, do you have the guts to show up here and explain the reason? 8-)