在Python中没有ORM的情况下,在http服务器(Flask)中处理sql连接的最佳方法是什么? [英] What is the best way to handle sql connection in http server (Flask) without ORM in Python?
问题描述
我正在将Flask与MySQL(MariaDB)数据库一起使用.为了处理sql连接和游标,我使用自制的上下文管理器.我在每个Flask http请求hadling函数中打开和关闭连接,因此可以确定到db的连接数不会超过特定数目,但这会增加开销.我确定其他用户可以使用相同的mysql连接,如果不使用ORM,可以使用什么其他方法来处理sql连接和游标?
I am using Flask with MySQL (MariaDB) database. To handle sql connection and cursor I use self-made context manager. I open and close connection inside each Flask http request hadling function, so I can be sure that number of connections to db will not exceed the certain number, but it creates overhead. I am sure that the same mysql connections can be used by other users, what other approach to handle sql connection and cursor I can use, if I do not use ORM ?
上下文管理器挂起光标和连接:
from contextlib import contextmanager
import mysql.connector
from mysql.connector.errors import Error
@contextmanager
def mysql_connection(user, password, host, database, auth_plugin):
_conn = mysql.connector.connect(user=user, password=password, host=host, database=database, auth_plugin=auth_plugin)
try:
yield _conn
except (Exception, Error) as ex:
# if error happened all made changes during the connection will be rolled back:
_conn.rollback()
# this statement re-raise error to let it be handled in outer scope:
raise
else:
# if everything is fine commit all changes to save them in db:
_conn.commit()
finally:
# close connection to db, do not wait for timeout release:
_conn.close()
@contextmanager
def mysql_curs(user, password, host, database, auth_plugin) -> "curs":
with mysql_connection(user=user, password=password, host=host, database=database, auth_plugin=auth_plugin) as _conn:
_curs = _conn.cursor()
try:
yield _curs
finally:
_curs.close() # close cursor when everything is done
一些Flask http处理程序随机函数:
@app.route('/admin_panel/repair', methods=["GET"])
def repair_show_all_menu_webpages():
"""The page exists to repair menu if not existent flask function was added"""
try:
with common_db_ops.mysql_curs() as curs:
left_side_menu = []
webpages = admin_ops.show_all_menu_webpages_to_repair(curs)
except (Exception, Error) as err:
app.logger.error(f"Failed to repair website: {err}")
abort(500)
return render_template('repair_menu.html', webpages=webpages, left_side_menu=left_side_menu)
我想补充一点,我发现以下文章讨论了如何在Flask和PostgreSQL中使用Flask并创建自定义的sql连接上下文管理器,但是我对在Flask中的哪个位置声明sql连接器Pool感到疑问.
推荐答案
如果有人对在不使用ORM的情况下处理sql连接的方法感兴趣,我可以按照以下步骤将MySQL Connections Pool,上下文管理器和Flask结合使用:
If anybody is interested in the approach of handling sql connection without ORM, I made the following steps to combine MySQL Connections Pool, context manager and Flask:
SQL_CONN_POOL = pooling.MySQLConnectionPool(
pool_name="mysqlpool",
pool_size=10,
user=DB_USER,
password=DB_PASS,
host=DB_HOST,
database=DATABASE,
auth_plugin=DB_PLUGIN
)
@contextmanager
def mysql_connection_from_pool() -> "conn":
conn_pool = SQL_CONN_POOL # get connection from the pool, all the rest is the same
# you can add print(conn_pool) here to be sure that pool
# is the same for each http request
_conn = conn_pool.get_connection()
try:
yield _conn
except (Exception, Error) as ex:
# if error happened all made changes during the connection will be rolled back:
_conn.rollback()
# this statement re-raise error to let it be handled in outer scope:
raise
else:
# if everything is fine commit all changes to save them in db:
_conn.commit()
finally:
# actually it returns cursor to the pool, rather than close it
_conn.close()
@contextmanager
def mysql_curs_from_pool() -> "curs":
with mysql_connection_from_pool() as _conn:
_curs = _conn.cursor()
try:
yield _curs
finally:
_curs.close()
我使用以下链接回答了这个问题:
这篇关于在Python中没有ORM的情况下,在http服务器(Flask)中处理sql连接的最佳方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!