Forums

I am building a APi with flask to connect MySQL. Do I need to close the database every time a request? will it consume more resources with current code?or flask will auto disconnect?

@app.route('/Kj', methods=['GET'])
def ABC():
    db = MySQLdb.connect(host='xxx.mysql.pythonanywhere-services.com',user='xxx',passwd='xxx',db='xxx$default',cursorclass=MySQLdb.cursors.DictCursor)
    curs = db.cursor()
    try:
        curs.execute("SELECT * FROM KJ")
        a = curs.fetchall()
    except Exception:
        return 'Error: unable to fetch items'
    return jsonify({'Stock': a})

If you're accessing MySQL directly, like you do in your example code above, then yes, you'll need to disconnect explicitly. The MySQL server will time out old connections after five minute of inactivity, but your account allows a limited number of connections so if your site is getting more than a few hits over the course of those five minutes, you'll have problems.

Have you considered using a database connection manager like SQLAlchemy? It makes handling DB connections much easier, removing a lot of error-prone boilerplate code like that. We have a tutorial on creating a simple Flask app using it, and while it's for a non-API website, it should have useful code that you could use for an API.

1.how many connections I can have per second? any differences for paid user? will sqlalchemy able to increase more connections per second? like aws rds, they can have like a base of 100 connections per second. From your tutorial, seems like, you have 300 seconds time out but not mention how many connections

1. If I do a db.close(() or cursor.close() and then db.close(), in mySQLdb, is it work for higher traffic?

2. I need to do some complicated select from MySQL, I need to replace all with sqlalchemy or sqlalchemy on top of MySQLdb?

Sqlalchemy can do connection pooling/automatic recycling/closing of your connections.

The more web workers you have in your PythonAnywhere plan the more db connections you will be allowed.

But in general you will have to setup connection pooling/recycling/disconnecting (because otherwise no matter what your limit is you will go over.

bfg, thanks Is sqlalchemy is something work on top of mySqLdb? which mean I still keep mysqldb accept the connections part?

You won't need mysqldb if you are using sqlalchemy. you will need to connect to the database using sqlalchemy instead of mysqldb.

Hi giles or glenn,

1.how many connections I can have per second? any differences for paid user? will sqlalchemy able to increase more connections per second? like aws rds, they can have like a base of 100 connections per second. From your tutorial, seems like, you have 300 seconds time out but not mention how many connections

2.If I do a db.close(() or cursor.close() and then db.close(), in mySQLdb, is it work for higher traffic?

3.I need to do some complicated select from MySQL, I need to replace all with sqlalchemy or sqlalchemy on top of MySQLdb?

1. 3 connections per worker. Free users have 1 worker. Paid users start with 2 but can customize the number of workers. You can make as many connections as you want every second, we only limit how many connections you can have open at a time.

2. Closing the connection will work, but make sure an exception doesn't prevent the connection from being closed.

3. You can execute raw queries using something like this StackOverflow post suggests.

Hi Glenn, still confuse of the first one. since I am using it for an app, which I expected it can have 50 request per seconds at peak time, will it supported? It is connected with API that I made. I think your connections explanation is different.

There is no time component to the connection limit. It doesn't matter how many requests you get per second or how many connections you make per second. You can have 3 connections open per worker at any one time.

Glenn, In this case, will my script as the above work for the purpose to get connection from the app? Or I need to add the db close or I still use the sqlalchemy? so if I have 10 APIs, only 3 APIs that connect to database. The fourth API, need to wait one of the 3 APIs to close before connecting?

Our advice is still the same: you need to either add a db.close() to your script as above, ideally in a finally: block, or use SQLAlchemy, to take advantage of its connection pooling.

Hi Glenn,

Will the above code perform as good as sqlalchemy? Let's assume I have 15 APIs, with a high traffic. I still dig into sqlalchemy to understand session.close() and connection close.

@app.route('/Kj', methods=['GET'])
        def ABC():
            db = MySQLdb.connect(host='xxx.mysql.pythonanywhere-services.com',user='xxx',passwd='xxx',db='xxx$default',cursorclass=MySQLdb.cursors.DictCursor)
            curs = db.cursor()
            try:
                curs.execute("SELECT * FROM KJ")
                a = curs.fetchall()
                curs.close()
            except Exception:
                return 'Error: unable to fetch items'
                curs.close()
            finally:
                 curs.close()
            return jsonify({'Stock': a})

That looks like it should work, but you may get issues because of trying to close the same connection repeatedly.

Hi Glenn, How will you suggest? shall I change to something like this? Actually I just make a cursors close, now I put a db.close() at the end of finally. Will this able to handle high traffic?

@app.route('/Kj', methods=['GET'])
        def ABC():
            db = MySQLdb.connect(host='xxx.mysql.pythonanywhere-services.com',user='xxx',passwd='xxx',db='xxx$default',cursorclass=MySQLdb.cursors.DictCursor)
            curs = db.cursor()
            try:
                curs.execute("SELECT * FROM KJ")
                a = curs.fetchall()
             except Exception:
                return 'Error: unable to fetch items'

            finally:
                 curs.close()
                 db.close()
            return jsonify({'Stock': a})

Also, it takes 0.28s from start to close the databse. Does it mean one connections can support 3 access per second and about 9 access per second for a free account?

That code looks better than the previous example.

The database connections have nothing to do with how many requests your app can handle. That's determined by the number of workers. If it takes your code .3 seconds to process a request, then you can handle 3 hits per second per worker.

Hi glenn,

In this case, using using sqlalchemy also will not increase the hits per second, correct?

Please note that closing connection used to have an impact on performances. I recommend to use pooling functions instead of closing connections.

agree. pooling = you don't repeatedly close the connections, and automatically reuse/recycle them for another request. not sure how much of a performance benefit until you try it though.

Hi glenn,

In this case, using using sqlalchemy also will not increase the hits per second, correct?

Hi bfg,

I am not familiar with sqlalchemy. However, which part of the code in my example can still remain unchanged? which command is related to the poolling? Is it session? An simple example will be good. Thanks a lot

Just answering on behalf of Glenn -- if you use SQLAlchemy, then you won't be opening and closing database connections on every hit, because it will handle your connects for you and keep them alive between hits if it can. Opening and closing DB connections is a relatively expensive operation, and takes time. So if you don't have to do that, your website does less processing on each request, which means that it can probably handle more hits.

I say "probably" because if your website is doing loads of stuff in addition to the database connection stuff, then that stuff might be a small proportion of the work it's doing, so the speedup might not be huge. In other words, if SELECT * FROM KJ runs quickly, then the connection overhead will be a large part of your current processing time, so SQLAlchemy will speed things up significantly. But if SELECT * FROM KJ runs slowly, then the connection overhead will be a small part of your current processing, so SQLAlchemy will only make a small improvement.

Regarding using SQLAlchemy in your app, I'd recommend you check out the tutorial I linked to earlier. However, it doesn't use raw SQL to connect to the database -- it uses SQLAlchemy's object-relational mapping, which provides a good way of using optimised queries that are likely to run faster than hand-crafted SQL.

If you really need raw SQL, then this modified code should do the job:

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

app = Flask(__name__)
SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:password}@{hostname}/{databasename}".format(
    username="xxx",
    password="xxx",
    hostname="xxx.mysql.pythonanywhere-services.com",
    databasename="xxx$default",
)
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299

db = SQLAlchemy(app)

@app.route('/Kj', methods=['GET'])
def ABC():
    a = db.session.execute("SELECT * FROM KJ")
    return jsonify({'Stock': a})

Thanks giles,

http://stackoverflow.com/questions/23185319/why-is-loading-sqlalchemy-objects-via-the-orm-5-8x-slower-than-rows-via-a-raw-my This SO said sqlalchemy ORM perform slower than MySQLdb. How ORM will help the query faster as it needs to fetch the whole data into memory, if I understand correctly.

It doesn't fetch all of the rows, but it does load all columns for each row -- which is normally what you'd want.

But either way, the code in my last post does not use the ORM, so if you're really worried about performance, then you can just use that and get the connection-pooling from SQLAlchemy but not have to worry about any performance hit from the ORM.

Hi Giles, Your code has an error when jsonify

RuntimeError: working outside of application context

I modify it by adding fetchone()

@app.route('/Kj', methods=['GET'])
def ABC():
    b = db.session.execute("SELECT * FROM KJ")
    a = b.fetchone()
    return jsonify({'Stock': a})

However still unable to jsonify. When I fetchone(), I notice the format is different with MySQldb.

(0, 1.75, 'B1', '02/23/2017 ')

While fetchone() in previous MySQLdb is as below:

{'ROI': 1.75, '50d': 'B1', 'Next_Report': ' 02/23/2017 ', }

maybe look at something like this?

Hi giles,

I added the column succesfully

a = b.fetchall()
col = ['index','ROI',50d','Next_Report']
d = [OrderedDict(zip(col,t)) for t in a]

However, the output still unable to jsonify. I think there should be easier way to json from sqlalchemy

That should jsonify -- what error do you get?

Hi Giles,

I got this error after jsonify

>>> jsonify({'Stock':d})
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.4/dist-packages/flask/json.py", line 234, in jsonify
    if current_app.config['JSONIFY_PRETTYPRINT_REGULAR'] \
  File "/usr/local/lib/python3.4/dist-packages/werkzeug/local.py", line 338, in __getattr__
    return getattr(self._get_current_object(), name)
  File "/usr/local/lib/python3.4/dist-packages/werkzeug/local.py", line 297, in _get_current_object
    return self.__local()
  File "/usr/local/lib/python3.4/dist-packages/flask/globals.py", line 34, in _find_app
    raise RuntimeError('working outside of application context')
RuntimeError: working outside of application context

Besides, db.session.execute only able to run in console command line(enter line by line) but not in script. Doing a simple experiment by comment out db.session.execute it return "hihi", but has internal server error if doesn't comment out db.session.execute

from flask import Flask,jsonify,abort,make_response,request,render_template
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
import MySQLdb
import MySQLdb.cursors
from collections import OrderedDict
import json


app = Flask(__name__)
SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
    username="xxx",
    password="xxx",
    hostname="xxx.mysql.pythonanywhere-services.com",
    databasename="xxx$default",
    )
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299

db = SQLAlchemy(app)

@app.route('/KJ', methods=['GET'])
def KLSE1():
    call = db.session.execute("SELECT * FROM KJ")
    #col = ['index','Stock','Name','MACD','STOCH','RSI','ATR','Bollinger','SMA','SMAcross','Momentum','Volume_changes_pc','Previous_close','Change','Change_pc','R1','R2','S1','S2','52w_High','52w_Low','52w','50d','Trend_date','Stockcode','Syariah','Trade','BUY_total','HOLD_total','SELL_total','Market_sentiment','gin','Time','Market','Vin_change','Grading','Yield','EPS','DPS','Net_Margin','EPS_1g','EPS_5g','ROE','ROA','ROI','divpayout','PER','Fix','PEG','PEGY','CF_per_share','BV_per_share','QQ','QoQ','value_BG','NAV','Next_Report','Last_Report']
        #a = call.fetchmany(5)
    #d = [OrderedDict(zip(col,t)) for t in a]
    #json.dumps(d, ensure_ascii=False)
    return "hihi"

session isn't it for ORM?

error log

2017-01-12 07:56:39,184 :Exception on /KJ [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1687, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1360, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1358, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1344, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/xxx/mysite/flask_app.py", line 33, in KJ
    call = db.engine.execute("SELECT * FROM KJ")
  File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy.py", line 746, in engine
    return self.get_engine(self.get_app())
  File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy.py", line 763, in get_engine
    return connector.get_engine()
  File "/usr/local/lib/python2.7/dist-packages/flask_sqlalchemy.py", line 443, in get_engine
    self._engine = rv = sqlalchemy.create_engine(info, **options)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/__init__.py", line 332, in create_engine
    return strategy.create(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/strategies.py", line 64, in create
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/dialects/mysql/mysqlconnector.py", line 77, in dbapi
    from mysql import connector
  File "./mysql.py", line 133, in <module>
    curs.close()
NameError: name 'curs' is not defined

I have a mysql.py in my /vinasia, but they error persist even I deleted mysql.py

Big lesson learnt, avoid naming MySQL.py in the folder

Hi giles, thanks for the guidance.

Do I need any session.close() and it will work fine? previously in mysqldb, using

curs.execute("SELECT * FROM KLSE WHERE Stock LIKE %s",(Stock,))

but sqlalchemy seems not recognised

db.session.execute("SELECT * FROM KLSE WHERE Stock LIKE %s",(Stock,))

has error below:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'Stock' is not defined

You shouldn't need a session.close, no.

I'm confused about the code you quote above -- if there's no variable called Stock defined when the code is executed, it shouldn't work in either raw mysqldb or in SQLAlchemy. Are you sure you didn't accidentally delete the line where you assigned a value to Stock?

I have many APIs that cerate previously need to change to sqlalchemy. Most of them using placeholder %s in MySQLdb but now need to change to sqlalchemy. I am struggle about sqlalchemy as the format is different from mysqldb

The code is below: I just comment out the part I previously use with mysqldb.

@app.route('/KJ/<Stock>', methods=['GET'])
def KJstock(Stock):
    #db = MySQLdb.connect(host='xxx.mysql.pythonanywhere-services.com',user='xxx',passwd='xxx',db='xxx$default',cursorclass=MySQLdb.cursors.DictCursor)
    #curs = db.cursor()
    try:
        call = db.session.execute("SELECT * FROM KJ WHERE Stock LIKE %s",(Stock,))
        col = ['index','Stock','Name','MACD','STOCH','RSI','ATR','Bollinger','SMA','SMAcross','Momentum','Volume_changes_pc','Previous_close','Change','Change_pc','R1','R2','S1','S2','52w_High','52w_Low','52w','50d','Trend_date','Stockcode','Syariah','Trade','BUY_total','HOLD_total','SELL_total','Market_sentiment','dex','Time','Market','Vin_change','Grading','Yield','EPS','DPS','Net_Margin','EPS_1g','EPS_5g','ROE','ROA','ROI','divpayout','PER','dex','PEG','PEGY','CF_per_share','BV_per_share','QQ','QoQ','value_BG','NAV','Next_Report','Last_Report']

        c = call.fetchall()
        d = [OrderedDict(zip(col,t)) for t in c]
    except Exception:
        return 'Error: unable to fetch items'
    return jsonify({'Stock': d})

I can't really see how that code could cause the stack trace you quoted earlier -- there definitely is a variable called Stock defined at the point where it's used.

However, looking again at the stack trace:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'Stock' is not defined

...it looks (from the <stdin>) like you might have been running it in a different context -- specifically, from a console -- when it generated that error. Are you sure it's generating that error when you run it in the web app?

Hi Giles,

I give assign a stock name, it shows the traceback

Stock ='SHELL'
>>> db.session.execute("SELECT * FROM KJ WHERE Stock LIKE %s",(Stock,))
>>>Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/scoping.py", line 149, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 978, in execute
    clause, params or {})
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 720, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 317, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 793, in _execute_clauseelement
    keys = distilled_params[0].keys()
AttributeError: 'tuple' object has no attribute 'keys'

when I am using mysqldb

curs.execute("SELECT * FROM KJ WHERE Stock LIKE %s",(Stock,))

give a correct output also,

db.engine.execute("SELECT * FROM KLSE WHERE Stock LIKE %s",(Stock,))

give correct output too. But I am not sure how db.engine.execute different from db.session.execute and I suppose db.session.execute is better for my application

You'll probably find that the way of specifying the query and it's argument is different for db.session.execute. I would suggest reading the sqlalchemy docs to see how to correctly specify the query.

I just wanted to post a "thank you" to everybody in this post because you have all answered my questions. I am definitely going to use SQLalchemy.

Glad to hear that!