How would I connect to a database with Flask? I can't find any info on how to do it on pythonanywhere and nobody else seems to ask so I'm asking here. Thanks in advance

Try sqlalchemy, also check out the PythonAnywhere wiki for tips about connecting

conrad the tips on connecting are useless as they are all about how to do it with Django. I clearly stated that I was using Flask.

Also, they don't say a thing about actually accessing the databases from inside my web app. Just how I can access them from the MySQL console. Which I obviously don't want to do. I want my app.py file to be able to draw information from the database which I can use for my site. I think it would all just be so much easier if PythonAnywhere just let us use .db files in the website's files section

1. you can use .db files with sqlite
2. in this link there is a section in the link about non-django access
3. this link is solely about how to use sqlalchemy (a database orm) with flask

1: with sqlite but not with pythonanywhere. When I imported the db it was wiped

1. with sqlite but not with pythonanywhere. When I imported the db it was wiped
2. it tells you how to install it and not about anything else

3. when i do ' pip3.4 install --user https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz' to install it it gives me this error:

   'Command "/usr/local/bin/python3.4 -c "import setuptools, tokenize;file='/tmp/pip-i71at5ro-build/setup.py';exec(compile(getattr(toke nize, 'open', open)(file).read().replace('\r\n', '\n'), file, 'exec'))" install --record /tmp/pip-qn_gm6nz-record/install-record.tx t --single-version-externally-managed --compile" failed with error code 1 in /tmp/pip-i71at5ro-build'

[edited by admin: formatting]

Hi there:

with sqlite but not with pythonanywhere. When I imported the db it was wiped

A common problem with sqlite databases is when you try to open them using a relative path as the database name -- for example, app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'``. This will try to open the database file in whatever directory your web app happens to be running in, so it will be empty, while the data is sitting safely in a different file somewhere else. Perhaps that's what happening here? Make sure you use a full path to the database, for exampleapp.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////home/SnakeMessages/test.db'`

when i do ' pip3.4 install --user https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz' to install it it gives me this error:

Can I take a look at your files? That would help me work out what's going on with that. The command works fine from both my account and a free one I created to check it out.

BTW I think I sent you an email message about SQLAlchemy with the link Conrad gave in his third point above, did it not get through?

What I meant was I uploaded the .db file to my pythonanywhere and tried to open it by clicking on it and it was empty. I also tried connecting to it with SQLite and it said no table 'details' (which was the name of one of the tables in the db)

That's odd. How large is the .db file? And can I take a look at your files to help debug the pip problem? I can see them from our side, but we never look without permission, so that your data remains private.

I've got nothing private on there. Look as much as you want ;). The db file is 4KB. Too small?

Sorry giles I just realised that I had wiped my site recently because it wasn't working. I'll re-upload everything Edit: re-uploaded stuff

OK, from the pip logs it looked like the install failed because of permission errors. Are you sure you ran the pip3.4 install command with the --user flag? I've just run it in a free account, copying and pasting it from your message above, and it worked.

Re. the DB file, 4KB is fine for a small database.

kk. I'm kinda confused as to how I would set it up as I thought pip came pre-installed for my site

It is pre-installed, and should work fine so long as you use the --user flag. Just copy and paste this command into a Bash console, and let us know what it prints out:

pip3.4 install --user https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz

If it prints out many lines of stuff with an error at the end, copy/paste all of the lines it prints and paste them into your reply in the forums -- often the important stuff for debugging purposes is halfway through the stuff that's printed out.

Requirement already satisfied (use --upgrade to upgrade): MySQL-python==1.2.3 from https://github.com/davispuh/MySQL-for-Python-3/archive /1.0.tar.gz in ./.local/lib/python3.4/site-packages

shall I do pip3.4 install --upgrade https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz ?

Yes, that's worth a try.

giles, I tried to do an update with the command above and it gave me this error:

Command "/usr/local/bin/python3.4 -c "import setuptools, tokenize;file='/tmp/pip-_r1ggayp-build/setup.py';exec(compile(getattr(toke nize, 'open', open)(file).read().replace('\r\n', '\n'), file, 'exec'))" install --record /tmp/pip-zu4w3giq-record/install-record.tx t --single-version-externally-managed --compile" failed with error code 1 in /tmp/pip-_r1ggayp-build

You need either the --user argument or to be installing into a virualenv.

glenn how would I upgrade then? Also, I assume 'Requirement already satisfied' means it's already installed and I'm baffled as to what is wrong with my code.

glenn how would I upgrade then? Also, I assume 'Requirement already satisfied' means it's already installed and I'm baffled as to what is wrong with my code.

You can use both the --user and --upgrade flags on the same command line, so:

pip3.4 install --user --upgrade https://github.com/davispuh/MySQL-for-Python-3/archive/1.0.tar.gz

Note to anyone viewing this: Only reply if you think you have an answer that can be done quickly as I've basically given up on the whole language of flask due to the fact that I cannot find a single free host other than pythonanywhere that is any good and I'm completely lost with the whole thing and don't seem to have got anywhere near an answer.

Hi there, I'm sure we can help, but I can't promise it will be done quickly. Programming can be tricky! And frustrating. But with a bit of patience, taking it one step at a time, any problem can be solved.

Looking back at the history of this forum post, I think the problem is that all the conversations have been a bit too vague -- "i need to use a database with flask", with responses like "here are some very general flask how-to guides".

I suggest starting back at the beginning, and simplifying things as much as possible. Exactly what is it that you're trying to achieve? Once that's clear, how can we simplify it down to the simplest possible example? So, if you want a web app with 12 different pages that uses 18 different tables in a database, can we start with one page and one database table, and get that working?

thanks harry. Here is what I want: to make a simple web app. I'm storing some info that I get from the user in my database. It worked offline but doesn't work on Pythonanywhere. In the version I have on my PC the database is a .db file. But .db files don't seem to work on Pythonanywhere. When I uploaded the .db file it was wiped and couldn't be written to and another one couldn't be created using the methods I had done before.

That's all still too vague I'm afraid. Can we try and build a single-page web app that stores some information in a database together? We can reuse some of the code you already have. Once we get that working, you should be able to re-introduce the rest of your code step by step...

What's the simplest possible page we can make that stores and retrieves information from a database?

Here's a very quick how-to guide, just to check that we can get a simple flask app that talks to a database working:

• Go to the Web tab and add a new web app (delete your old one first if necessary)
• Select Flask, Python 3.4
• Check it works by visiting it (It should say "hello from Flask")

Now open a Bash console and create your database:

$ cd mysite
$ touch things.db
$ sqlite3 things.db
sqlite> CREATE TABLE things (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO things(name) VALUES ('monkeys');
sqlite> INSERT INTO things(name) VALUES ('feathers');
sqlite> INSERT INTO things(name) VALUES ('plinth');
sqlite> SELECT * FROM things;
1|monkeys
2|feathers
3|plinth
sqlite>

Pres Ctrl+D to exit sqlite, then go back to the Web tab, click through to your web app code in the mysite folder, and go and edit the flask_app.py file that's serving your site. Change the main hello_world function to retrieve contents from the database:

@app.route('/')
def hello_world():
    database_contents = ''

    connection = sqlite3.connect('/home/harry/mysite/db.sqlite')
    try:
        c = connection.cursor()
        rows = c.execute('SELECT * FROM things;')
        for row in rows:
            thing_id, thing_name = row
            database_contents += '<p>{id}: {name}</p>\n'.format(id=thing_id, name=thing_name)
        return '<h1>Database contents:</h1>' + database_contents
    finally:
        connection.close()

Hi "Save", then hit "Reload" to reload your web app. If you now visit your site again, you should find it's displaying the database contents:

You can test it "really" works by going back to your database in the Bash console, running sqlite3 things.db and doing another INSERT INTO statement to add a new item, and verify it appears when you reload the page...

If you can run through those steps and confirm they all work, then we'll know for sure we've got at least a chance of getting this working...

Oops, that should read Ctrl+D to exit sqlite, not Ctrl+C... fixing it now...

Traceback (most recent call last): File "/home/SrinivasK/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1817, in wsgi_app response = self.full_dispatch_request() File "/home/SrinivasK/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1477, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/SrinivasK/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1381, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/SrinivasK/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise raise value File "/home/SrinivasK/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1475, in full_dispatch_request rv = self.dispatch_request() File "/home/SrinivasK/.virtualenvs/my-virtualenv/lib/python3.4/site-packages/flask/app.py", line 1461, in dispatch_request return self.view_functionsrule.endpoint File "/home/SrinivasK/mysite/flask_app.py", line 295, in debug rows = c.execute('SELECT * FROM things;')

sqlite3.OperationalError: no such table: things

This is the error I have got after following the steps above. Things.db works fine in bash.

Use the full path to the database file, not a relative path.

I had used it in the same format as this, connection = sqlite3.connect('/home/harry/mysite/db.sqlite')

i.e. connection = sqlite3.connect('/home/SrinivasK/mysite/db.sqlite')

when I changed it to connection = sqlite3.connect('/home/SrinivasK/mysite/things.db'), this worked fine.

Thanks!

(1045, "Access denied for user 'brandality1'@'ip-address.ec2.internal' (using password: YES)") I am getting this error..help?

I tried mysql -hmysql.server -ubrandality1 brandality1\$db -p --local-infile=1 at bash. Doesn't work. Thanks anyone. Or must I change to SQLite. Using Python 2.7..

That should definitely work. Are you sure you're using the password you set on the "Databases" tab (as opposed to, say, your normal PythonAnywhere login password)?

Yes Giles. I am sure. Should have mentioned I am trying to connect via a flask app.

Sure, I got that -- but you also mention that it doesn't work from a bash console -- or am I misunderstanding you? If it really isn't working, what error do you get on the bash console?

It works from bash. I thought that would fix the app.How do I fix that please?

What's the code you're using in the Flask app? (Obviously, remove the password from anything you post here :-)

import MySQLdb
def connection():
    conn = MySQLdb.connect(host="",
    user="",
    passwd="pass",db="$db")
    c = conn.cursor();
    return c, conn

do I need to edit my wsgi file? I am a complete noob. So please bear with me.

[edit by admin: formatting]

Further:

def register_page(): try: form = RegisterForm(request.form)

    if request.method == "POST" and form.validate():
        username  = form.username.data
        email = form.email.data
        name = form.name.data
        password = sha256_crypt.encrypt((str(form.password.data)))
        c, conn = connection()

        x = c.execute("SELECT * FROM users WHERE username = (%s)",
                      (thwart(username)))

        if int(x) > 0:
            flash("That username is already taken, please choose another")
            return render_template('register.html', form=form)

        else:
            c.execute("INSERT INTO users (username, password, name, email, permission) VALUES (%s, %s, %s, %s,%s)",
                      (thwart(username), thwart(password), thwart(name),thwart(email),thwart()))

            conn.commit()
            flash("Thanks for registering!")
            c.close()
            conn.close()
            gc.collect()

            session['logged_in'] = True
            session['username'] = username

            return redirect(url_for()

    return render_template(", form=form)

except Exception as e:
    return(str(e))

That looks OK, though I'd highly recommend that you use SQLAlchemy or some other system with a connection manager for a Flask app, as you'll either have to open a new connection for every DB transaction (which is expensive) or you'll need to monitor the connections and re-open them when necessary -- the database will automatically close connections that have been idle for more than five minutes.

Where are you seeing the DB connection error? I had a look at your logfiles and there's nothing in there to do with the database.

Well nothing gets written in the database...I checked with a Select * from users command and it's empty. The error comes after clicking the register button.

c.execute("INSERT INTO users (username, password, name, email, permission) VALUES (%s, %s, %s, %s,%s)", (thwart(username), thwart(password), thwart(name),thwart(email),thwart("cm")))

        conn.commit()
        flash("Thanks for registering!")
        c.close()
        conn.close()
        gc.collect()

But where do you see the error? Is it displayed on the Flask app? Is there a URL where I could see it? (Let me know via the "Send feedback" link above if the URL is private.)

(Discussion ongoing over email, we'll post back here if there's anything generally useful to share.)

app = Flask(name) app.secret_key = 'This is secret'

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+msqldb://brandality1:<password>@brandality1.mysql.pythonanywhere-services.com/brandality1$db'

db = SQLAlchemy(app, engine = create_engine("mysql+msqldb://brandality1:<password>@brandality1.mysql.pythonanywhere-services.com/brandality1$db", pool_recycle=280))

I got this error sqlalchemy.exc.ArgumentError: Can't load plugin: sqlalchemy.dialects:mysql.msqldb

You misspelled mysqldb as msqldb.

Hi, I'm a beginner and I'm trying to do the same thing - I have a Flask app with MySQLdb connection. My SELECT queries work, but not INSERT INTO. There are no error messages. When I print the sql insert into statement and feed it manually through the mysql console it works. I checked if the commit statements are there and they work. The code also works on my desktop. Totally out of ideas here.

from flask import Flask, request, render_template
import MySQLdb

username = "mbidelski"
passwd = "coderslab"
hostname = "mbidelski.mysql.pythonanywhere-services.com"
db_name = "mbidelski$cinemas_db"

app = Flask(__name__)
app.config["DEBUG"] = True

submit_types = {"cinema":"cinema", "movie":"film", "ticket":"ticket", "payment":"payment"}

@app.route('/', methods=['GET', 'POST'])

def aplikacja_www():

    def wykonajSQL():
        cursor.execute(sql)

    def zamknijSQL():
        # cursor.close()
        cnx.close()

    if request.method == 'GET':
        return render_template('B3.html')
    else:

        cnx = MySQLdb.connect(host=hostname, user=username, passwd=passwd, db=db_name)
        cnx.get_warnings = True
        cnx.autocommit = True
        cursor = cnx.cursor()

        show_sub = request.form.get('show_submit', None)
        # print(show_sub)
        if show_sub != None:
            # return "działa"
            # name = request.form.get('name', None)
            # rating = request.form.get('rating', None)
            # if name != None and name != "":
            #     sql="""SELECT * FROM {} WHERE UPPER(name) LIKE UPPER('%{}%');""".format(show_sub, name)
            #     print(sql)
            # elif rating != None and rating != "":
            #     sql="""SELECT * FROM {} WHERE rating={};""".format(show_sub, rating)
            #     print(sql)
            # else:
            sql="SELECT * FROM "+ show_sub
            wykonajSQL()
            text = ""
            for row in cursor:
                for x in row:
                    text = text + " " * 10 + str(x)
                text = text + "<br>"
            zamknijSQL()
            return text

        # table_name = request.form["del_submit"]
        table_name = request.form.get('del_submit', None)
        rec_id = request.form["id"]
        if table_name != None:
            if request.form["id"] != "":
                sql = """DELETE FROM {} WHERE id={}""".format(table_name, rec_id)
                try:
                    wykonajSQL()
                    zamknijSQL()
                    return "Usunałem z tablicy {} pole o id {}".format(table_name, rec_id)
                except Exception as ex:
                    zamknijSQL()
                    return "Brak rekordu o tym id lub błąd: <br>" + str(ex)
            else:
                zamknijSQL()
                return "Nie podałeś id"+str(rec_id)+"  "+str(table_name)

        add = request.form.get('submit', None)
        table_name = submit_types[add]

        if table_name =="cinema":
            name = request.form["name"]
            address = request.form["address"]
            sql = """INSERT INTO {} ({}, {}) VALUES ('{}', '{}');""" \
                .format(table_name, "name", "address", name, address)
            wykonajSQL()
            zamknijSQL()
            return "Dodałem kino"

        elif table_name == "film":
            name = request.form["name"]
            description = request.form["desc"]
            rating = float(request.form["rating"])
            print(name)
            print(description)
            print(rating)
            sql = """INSERT INTO {} ({}, {}, {}) VALUES ('{}', '{}', {});""" \
                .format(table_name, "name", "description", "rating", name, description, rating)
            if not 0.00<=rating<= 10.0:
                return "Rating musi być większy od 0 a mniejszy od 10"
            print(sql)
            cnx.commit()
            cursor.execute(sql)
            print(cursor.fetchwarnings())
            zamknijSQL()
            return "Dodałem film"
        elif table_name == "ticket":
            quantity = int(request.form["quantity"])
            price = float(request.form["price"])
            sql = "INSERT INTO {} ({}, {}) VALUES ({}, {});" \
                .format(table_name, "quantity", "price", quantity, price)
            if price <= 0:
                return "Cena biletu musi być większa od zera"
            wykonajSQL()
            zamknijSQL()
            return "Dodałem bilet"
        elif table_name == "payment":
            type = request.form["payment_type"]
            date = request.form["payment_date"]
            sql = """INSERT INTO {} ({}, {}) VALUES ('{}', '{}');""" \
                .format(table_name, "type", "date", type, date)
            if type not in ["transfer", "cash", "card"]:
                return "Podałeś błędny typ płatności"
            wykonajSQL()
            zamknijSQL()
            return "Dodałem płatność"


        # print(sql)
        return render_template('B3.html')

[edited by admin: formatting]

1. take out your password and change it. this is a public forum and anyone who sees this will be able to access your mysql database now
2. do you get any errors in your webapp error log?

1. don't mind, there's nothing there, just practicing
2. Nope, none, and my python code generates correct SQL commands (checked by pasting them in the console). The site is here: http://mbidelski.pythonanywhere.com/, only the SELECTs work (buttons named 'Pokaż ...'). The other buttons don't cause errors but nothing happens in the db. error log: https://www.pythonanywhere.com/user/mbidelski/files/var/log/mbidelski.pythonanywhere.com.error.log server log: https://www.pythonanywhere.com/user/mbidelski/files/var/log/mbidelski.pythonanywhere.com.server.log

It's hard to tell in all that, but I suspect that you're not committing your changes and/or closing your connections.

I have

cnx.autocommit = True

upfront and for one of the inserts i have this:

cnx.commit()

If there was a problem with the connections then shouldn't the SELECT not work either?

Do you know for sure (by experiment or side effect, not by looking at the code) that that code is actually running?

SELECT does not require a commit - commits are only for changes to the database.

You may be seeing the difference between PythonAnywhere and your desktop because you're running different versions of the libraries, or because you're running different code.

For anyone struggiling with this issue, you need:

cnx.autocommit(True)

instead of cnx.autocommit = True. Alternatively use cnx.commit() AFTER cursor.execute.

thanks for the correction!