Forums

I have a flask app hosted on my paid PythonAnywhere account which connects to my pythonanywhere hosted postgresql database via sqlalchemy (Flask-SQLAlchemy module).

This is the connection:

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://<user>:<pass>@snappiesticker-24.postgres.pythonanywhere-services.com:10024/snappie'
db = SQLAlchemy(app)
db.create_all()

Then when I try and run this, it times out on the db.create_all() line. Here is the full traceback with the error at the bottom:

Traceback (most recent call last):
  File "server.py", line 15, in <module>
    db.create_all()
  File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy/__init__.py", line 895, in create_all
    self._execute_for_all_tables(app, bind, 'create_all')
  File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy/__init__.py", line 887, in _execute_for_all_tables
    op(bind=self.get_engine(app, bind), **extra)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 3404, in create_all
    tables=tables)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1615, in _run_visitor
    with self._optional_conn_ctx_manager(connection) as conn:
  File "/usr/lib/python2.7/contextlib.py", line 17, in __enter__
    return self.gen.next()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1608, in _optional_conn_ctx_manager
    with self.contextual_connect() as conn:
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1798, in contextual_connect
    self.pool.connect(),
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 338, in connect
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 644, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 440, in checkout
    rec = pool._do_get()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 963, in _do_get
    return self._create_connection()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 285, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 411, in __init__
    self.connection = self.__connect()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line 538, in __connect
    connection = self.__pool._creator()
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 96, in connect
    connection_invalidated=invalidated
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 90, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 377, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
    connection_factory=connection_factory, async=async)
sqlalchemy.exc.OperationalError: (OperationalError) could not connect to server: Connection timed out
    Is the server running on host "snappiesticker-24.postgres.pythonanywhere-services.com" (10.169.154.47) and accepting
    TCP/IP connections on port 10024?
 None None

Not sure if this is an issue with my postgresql db or the way I am connecting to it. Why would it time out like this? Seems like it would give me an error with what I am doing wrong.

Just to get the basic stuff out of the way, are you on a custom plan with postgres enabled? See https://www.pythonanywhere.com/wiki/Postgres for details.

Second, is 10024 really the right port for PA? If it is that's great, but the normal port for Postgres is 5432 and I couldn't find any indications that wasn't the case on PA either.

Just checking the obvious stuff - if something more subtle is wrong, I'm sure one of the admins will chip in with some suggestions.

Yes, I am on a custom plan with postgresql enabled.

Second, I got 10024 from my postgresql settings page where the address is also listed. I agree it seems a bit strange to me too.

When I run the following script,

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://randomUser:randomPassword@snappiesticker-24.postgres.pythonanywhere-services.com:10024/snappie'

db = SQLAlchemy(app)
db.create_all()

I get the message

password authentication failed for user "randomUser"

Which should mean that the port is available. What happens when you run the same code by itself? (ie. Judging from the traceback you are running this from within a flask webapp instead of just as a standalone script right now?)

hmm no I get the same "could not connect" error. How exactly are you running that script? From where? Any where should I run it from? I am running it locally on my computer.

try running it from your pythonanywhere console

I ran it with %run on an Ipython console (the one given on my python anywhere account) and there was no output.

Hi Snappie, do not despair, for you have already arrived at nirvana early- there is no output because there were no errors!

Now if you had created some models before your run the db.create_all() command, you will be able to see that tables have been created in postgresql.

In fact, before you run that, open a postgresql terminal, and run the following commands:

• run any of the below to see what databases you currently have (make sure you have one called snappie- otherwise you need to create database snappie;)
  • \l;
  • \list;
  • SELECT * FROM pg_database;
• run \c snappie; to start using/working on/examining your particular database called snappie
• run any of the following to see what tables/data are in your database- see what the differences are between these!
  • \d;
  • \d+;
  • \dt;
  • \dt+;

Unless you have modified the tables from before, you will see nothing there. But after your run the db.create_all() command successfully, you should see that a new table has been created.

For reference, here is the python script being run now. Remember to substitute in your own username/password. You can definitely run it in ipython, as well as any bash console etc.

#!/usr/bin/env python
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql+psycopg2://randomUser:randomPassword@snappiesticker-24.postgres.pythonanywhere-services.com:10024/snappie'

db = SQLAlchemy(app)
db.create_all()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    nickname = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)

db.create_all()
print db
print 'done! yay! go check your postgresql database tables now'

Wait shouldn't it have given me an error? Seeing as I used randomUser:randomPassword instead of my own user and password?

oh. then yes it should have errored if you called db.create_all() using the wrong user/pw config.

It seems to run off of PythonAnywhere fine. In that I mean that the webserver works well enough to serve index.html (as you can see). However, The submit button is sending a

Bad Request

The browser (or proxy) sent a request that this server could not understand.

error. Although the server logs don't seem to be giving anything that looks like an error.

Additionally, when I try and run the server locally, I get the timeout error as above. When I remove the db specific part of my code on my pythonanywhere server file, it works just fine.

So clearly there is still a problem connecting to the db, but I can't find where to get the specific error. Any suggestions?

Is there anything in your web app's error log? that would show any python exceptions. The server log is just for tracking when the web app restarts...

The log I am referring to is the error log. It actually doesn't have anything wrong in it which is what's making this so hard to debug. If I could get a good error traceback out of python anywhere or connect to a db locally, I could figure out whats going on.

I really feel like this error: browser (or proxy) sent a request that this server could not understand. does not output any traceback message.

I say this because I have seen people online with the same problem. Most often it seems to occur because the request.form object is returning key empty and yet the server code is trying to use it. I really really don't think thats my issue though as I have checked many times.

Of course I could try this locally to see if the server gives a good error there, but I can't connect to the db after all....

Hey snappie- just to followup, were you able to get the script I detailed above working? If so, this should not be a db connection problem.

Also, have you tried the list of commands I showed you to look at the db etc after you have connected to it?

Final tip: I don't think you are looking at your error log correctly. This is what I see when I scroll to the bottom (notice it is chronological so you need to go to the bottom to see the most recent errors):

File "/home/snappiesticker/mysite/flask_app.py", line 68, in upload
    s = shortuuid.encode(u)
  File "/home/snappiesticker/.local/lib/python2.7/site-packages/shortuuid/main.py", line 41, in encode
    return self._num_to_string(uuid.int)
AttributeError: 'str' object has no attribute 'int'

ps: don't worry other users won't have access to your logs just because I post the link here.

Yeah the script above works great. I can see the db and the list of commands you gave works very well to navigate through the database. That error in the log file you are referencing is from a separate part of the code which is now fixed.

However, My main big Q still is: Why does the script only work when run from python anywhere. When I run your script locally, it seems to timeout. What gives? Shouldn't it be able to connect from anywhere if the credentials are correct?

P.S. Thanks for your persistence in trying to solve this.

We don't expose our user databases to the public internet. You'll need to use SSH tunneling to access your database

Hey guys, new to PA: could anyone show just the general outline for

1. Hosting a PostgreSQL DB on Pythonanywhere
2. Updating that same DB on a regular basis with a python script

I´m considering upgrading my account, but can't find info on how to connect to the PostgreSQL DB hosted on PA from PA itself.

I´m sure the answer is easy for you. Thanks!

It should be pretty simple :-) If you look at the "Databases" page right now, with your free account you'll see that there are tabs at the side for MySQL and Postgres. If you click on the "Postgres" tab, it will tell you that you need to upgrade in order to use it.

So if you upgrade (on the "Account" page) to an account with Postgres -- it's not included by default as part of a Hacker plan, but you can add on a Postgres DB with 1GiB for $7/month, and then extra storage at $0.20/month per GiB -- and then go back to the "Databases" page, you'll see that the "Postgres" option has changed so that you can create a server. You'll need to set an admin password, then it will spin up the new Postgres server and display all of the connection details you need -- the hostname, the port number, and so on. These details will all work when you're running code inside PythonAnywhere.

Regarding updating it on a regular basis, once you've got some code that connects to the DB and does the updates you require, you can schedule it to run periodically on the "Tasks" page.

Thank you Giles., works well inside Pythonanywhere.

Just wondering where this site has moved, in case I want to access dbs locally https://www.pythonanywhere.com/wiki/SSHTunnelling

Unable to connect to the db using an SQL client/ charting library...

Thank you!

That help page article that you referenced actually has a link to where it has moved to. You can also see it here.