3

I need to create a simple project in Flask. I don't want to use SQLAlchemy. In the code snippet below, everyone that connects to the server uses the same connection object but for each request, a new cursor object is created. I am asking this because I have never used Python DB api before in this way. Is it correct? Should I create a new connection object for each request or use the same connection and cursor object for each request or the method below. Which one is correct?

import mysql.connector
from flask import Flask, request
app = Flask(__name__)

try:
    con = mysql.connector.connect(user='root',password='',host='localhost',database='pywork')
except mysql.connector.Error as err:
    print("Something went wrong")

@app.route('/')
def home():
    cursor = con.cursor()
    cursor.execute("INSERT INTO table_name VALUES(NULL,'test record')")
    con.commit()
    cursor.close()
    return ""
moooeeeep
  • 31,622
  • 22
  • 98
  • 187
amone
  • 3,712
  • 10
  • 36
  • 53
  • After reflecting about transactions (if available) being bound to the connection rather than the cursor, probably you should have a separate connection per request as soon as your database commands per request require a transaction to avoid your database being in an inconsistent state. – moooeeeep Dec 08 '17 at 18:20
  • @moooeeeep yes you're right. Sooner or later we use the connection object to execute the Sql query using the commit method. I hadn't thought about that. Thanks for your help. – amone Dec 08 '17 at 19:04

1 Answers1

0

WSGI applications may be served by several worker processes and threads. So you might end up having multiple threads using the same connection. So you need to find out whether your library's implementation of the connection is thread safe. Look up the documentation and see if they claim to provide Level 2 thread safety.

Then you should reflect about whether or not you need transactions during your requests. If you find you need transactions (e.g., requests issue multiple database commands with an inconsistent state in between or possible race conditions), you should use different connections, because transactions are always connection wide. Note that some database systems or configurations don't support transactions or don't isolate separate connections from each other.

So if you share a connection, you should assume that you work with autocommit turned on (or better: actually do that).

moooeeeep
  • 31,622
  • 22
  • 98
  • 187
  • I have been planning to deploy this app using apache. In this case the connection object will be used by everyone at the same time. – amone Dec 08 '17 at 14:26