Forums

Advising using SQLite

Hello all, I am hosting a Thinkful project called "SomeCode" on PythonAnywhere. It is a snippet service that is from two Thinkful projects (the Python/Flask class and the AngularJS class). The Flask class asked us to use the SQAlchemy ORM on the SQLite RDBMS as the backend DB. Not knowing much about the various SQL db's out there I just went with their suggestion. Turns out you can't host it on Heroku, since they don't support SQLite (only Postgres). SQLite actually works (since the db is just a file on the file system), but as soon as the Heroku Dyno is rebooted/refreshed, the db is gone.

I know that PythonAnywhere supports MySQL. So now I realize that although SQLite is a fine database, choosing a more advanced open-source db like MySQL or Postgres that works through a network socket rather than a file on the file system is the way to go.

My question is - can I still just use the SQLite db and trust that my db file will not get blown away (like it does on Heroku), or do I really need to go with a network oriented db like MySQL? If I can use SQLite, then are there backup schemes that I could use to save away the SQLite file? Or do I just need to get my ORM (SQAlchemy) to talk to MySQL rather than SQLite?

By the way, one of the reasons I like SQAlchemy on SQLite is that is has a nice Flask-WhooshAlchemy library that allows full-text searching on the db models. It's so easy to use. Unfortunately the Whoosh extension doesn't work with Postgres. I'm not sure if the Flask Whoosh extension works with MySQL, so that is why I haven't tried out SQAlchemy on the MySQL db.

You can definitely use sqlite on PythonAnywhere, and your data is safe -- we support a persistent filesystem, unlike Heroku.

The only downside is that it's slower (at least on PythonAnywhere) than MySQL or Postgres, but if that doesn't matter for you then it should be fine. (It's just slower, and won't work well for heavy-traffic sites -- it'll be fine for a low-traffic site.)

Re: backups -- we do have a replicated filesystem, so your data is safe against hardware failures. And we don't trash your files either. But if you want point-in-time backups (say, to protect yourself in case a bug somewhere loses data) then you can set up a scheduled task (check out the "Schedule" tab) to copy it once a day.

Giles, thank you so much for the super quick turnaround on my question. So far, I am really enjoying working with PythonAnywhere. Getting my Flask server running was so straight forward and simple.

So, my project is in an MVP phase, where I don't think it'll get much use. I am about 90% finished with it, but I wanted to figure out where I was going to host it before I completed version 1. Therefore, I don't think performance will be a big issue right now, but I would like to go with the more advanced support just to get better performance. Then, for the next version, I will port over to MySQL.

By the way, moving from my local machine to PythonAnywhere was very smooth. I just put my gulp build on github, and then in a Bash shell on PythonAnywhere I just did a 'git clone' - very nice. The only issue I have is that my "Twitter" OAuth sign-in is not working. I have three OAuth sign-ins (Facebook, Google+, Twitter). I'll start debugging the twitter now.

Thanks again for your help, John

Hi John -- thanks for the kind words! That sounds like a good plan. Start with sqlite, because you're familiar with it, then port to MySQL when you've got the basic functionality working :-)

Re: oauth -- let us know if and when you have more information. Problems like that can sometimes happen with free accounts if one of the servers involved in the oauth transaction isn't on our whitelist. I think we have all of the appropriate ones for Twitter on there, but it's possible we've missed something, so any detailed errors would be helpful.

Today, I have been unable to add or delete anything to or from my sqlite3 database file from the Bash console using SQLAlchemy's Database URI via Flask's app.config. Yesterday, everything was working just fine. Any clue on what could be wrong? There are no errors to be found in my logs. Thanks ahead of time.

What's the error message?

Conrad, there was no error message. And actually there was no error. It was all in my mind. Everything is working fine. I was failing to call "SQLAlchemy(App).session.commit()". I created and solved my own problem. Thanks again.

OK -- glad you got it working :-)

I want to ask something related to the use of SQlite3 in pythonanywhere. I´ve used Sqlite3 and love the way it works for my desktop programs. I´m starting to migrate my desktop programs to web pages with python and Flask. When I started reading these post I decided to change my data base to MySQL for my flask projects, then I realized I would lose many advantages of Sqlite and I want to be sure before start coding it. The webpage I have in mind is a very low traffic, the database is lightweight (my sqlite file is less than 1 MB) and there are not going to be many users connected at the same time. That why Sqlite3 sound perfect, I´m worried about the speed issues. Is that going to be a problem in a web app of those characteristics? To be honest I don´t expect more than 1000 hits a day and even less changes in the database, mostly reading it.

what advantages do you think you will lose?

you can always try sqlite and then change to mysql if you start seeing problems

Sorry for the delay. Considering my project will have few clients, or few groups of clients, the idea is that each group have their own database, that with sqlite are independent files. Once configured that way, databases will be stored and backup in different files. Moreover, in case of any mistake, sqlite bases can be downloaded, opened at my computer, corrected and saved back at the files folder of pythonanywhere without coding at all. And finally, considering I have already design a destock app that works with sqlite, I am configuring the web app so that they connect to the same database, so that if a client wants to change from desktop to web, I just have to copy the file from the desktop app to the web files. I am aware that most of these things can be done with MySql, but they are a lot easier with sqlite. I just love the idea that the database is a single file and I can do pretty much everything with it!

The problems we've seen with SQLite normally happen when the DB files are larger than 1MiB, and get particularly bad if you have different users of the site concurrently trying to make changes (because the way it handles locking, especially on networked filesystems, doesn't scale super-well).

So based on that, I think you should be fine -- your use case nearly avoids the areas where it's proven problematic in the past :-) That's not to say that there definitely won't be problems, and I'd always recommend using MySQL or Postgres if you can, but it certainly sounds like it's worth a try.

Thanks

Hi, how is it possible to use host on pythonanywhere SQLite DB that a pew web apps could communicate with it?

Just configure pew in the web app to use the sqlite file that you want it to use.

I have hosted my app on pythonanywhere but in my database, I'm not able to get the data through sqlite3 so what can I do?

What do you mean by "but in my database" and "I'm not able to get the data through sqlite3"?

hey, im trying to create a flask app to translate a message from one language to another, but whenever I am trying to access the languages in my sqlite database within the app, its saying there is no data in the database. I uploaded a python file that has the functions in need to call in it, and whenever I run the code in there, it accesses the database perfectly fine. in my flask app I am importing some of those functions and calling them, which then look in the database. whenever I try to translate through my actual website, it is able to call all the functions correctly, it just isn't able to find any data in the database. Why is this?

Have you checked if there are any errors related to database in (the bottom of) the error log of your web app?