自动打开和关闭数据库连接

发布于 2024-12-22 23:35:55 字数 663 浏览 4 评论 0原文

我正在我的应用程序中使用 SQLite3 编写一个用于数据库查询的类。该类的大多数方法与此非常相似：

def getPrice(self, symbol, date):
    date = dt.datetime.strptime(date, '%Y-%m-%d')
    conn = sqlite3.connect('stocks.db')
    curs =conn.cursor()
    curs.execute('''SELECT close FROM prices WHERE symbol = ? AND date = ?;''', (symbol, date))
    close = curs.fetchall()
    curs.close()
    return close

唯一的区别是数据库查询和参数数量。是否有可能抽象数据库连接的打开和关闭？

我知道使用像 SQLAlchemy 这样的 ORM 可能会更容易。但我想了解一般如何解决此类问题，而不仅仅是与数据库相关的问题。

感谢您的建议！

编辑：这篇文章基本上回答了我的问题。

原文

I am writing a class for database queries with SQLite3 in my application. Most of the methods of the class are very similar to this:

def getPrice(self, symbol, date):
    date = dt.datetime.strptime(date, '%Y-%m-%d')
    conn = sqlite3.connect('stocks.db')
    curs =conn.cursor()
    curs.execute('''SELECT close FROM prices WHERE symbol = ? AND date = ?;''', (symbol, date))
    close = curs.fetchall()
    curs.close()
    return close

The only difference is the database query and the number of arguments. Is there a possibility to abstract the opening and closing of the database connection away?

I know that it would be probably easier to use a ORM like SQLAlchemy. But I want to understand how I solve this kind of problem in general, not only in relation to databases.

Thanks for your suggestions!

EDIT: This post basically answers my question.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏有森光若流苏 2024-12-29 23:35:55

第一的。您会对一种（而且只有一种）全球联系感到非常非常高兴。如果您在一个地方执行此操作，配置更改会容易得多。

其次，使用 with 语句和上下文管理器库。

from contextlib import closing
from my_database_module import the_global_connection

def getPrice(
    with closing(the_global_connection.cursor())
        curs.execute('''SELECT close FROM prices WHERE symbol = ? AND date = ?;''', (symbol, date))
        close = curs.fetchall()
    return close

您的数据库模块如下所示：

import sqlite3
the_global_connection = sqlite3.connect( "stocks.db" )

这使您能够在一个地方更改数据库或数据库服务器技术。

First. You'll be much, much happier with one -- and only one -- global connection. Configuration changes are much easier if you do this in exactly one place.

Second, use the with statement and the context manager library.

from contextlib import closing
from my_database_module import the_global_connection

def getPrice(
    with closing(the_global_connection.cursor())
        curs.execute('''SELECT close FROM prices WHERE symbol = ? AND date = ?;''', (symbol, date))
        close = curs.fetchall()
    return close

Your database module looks like this:

import sqlite3
the_global_connection = sqlite3.connect( "stocks.db" )

This gives you the ability to change databases, or database server technology in exactly one place.

回复收藏 0 原文

溺渁∝ 2024-12-29 23:35:55

请注意，从 Python2.6 开始，sqlite.connect 返回上下文管理器：

连接对象可以用作上下文管理器，自动
提交或回滚事务。如果出现异常情况，
事务被回滚；否则，事务将被提交：

因此，不要用contextlib.fitting修饰连接——否则，您将失去提交/回滚行为，而只能获得< code>connection.close() 在退出 with-statement 时调用。

根据 PEP249：

... closing a connection without committing the changes first will cause
an implicit rollback to be performed.

因此提交/回滚行为比简单地有用得多呼叫关闭。

您可以使用上下文管理器：

import contextlib

def query(sql,args):
    with contextlib.closing(sqlite3.connect('stocks.db')) as conn:
        curs = conn.cursor()
        curs.execute(sql,args))
        close = curs.fetchall()
        return close

def getPrice(self, symbol, date):
    date = dt.datetime.strptime(date, '%Y-%m-%d')
    sql = '''SELECT close FROM prices WHERE symbol = ? AND date = ?'''
    args = (symbol, date)
    return query(sql, args)

因为您有许多函数（例如 getPrice）仅在 SQL 和参数上有所不同，您可以通过定义 query 函数来减少重复的样板代码。

您还可以定义一个上下文管理器来在出现错误时回滚连接并在退出 with 块时提交和关闭。可以在此处找到一个示例（对于 MySQL），将其调整为 sqlite3 应该不难..

参考：

contextlib.looking装饰器

Note that as of Python2.6, sqlite.connect returns a context manager:

Connection objects can be used as context managers that automatically
commit or rollback transactions. In the event of an exception, the
transaction is rolled back; otherwise, the transaction is committed:

Therefore, do not decorate the connection with contextlib.closing -- otherwise, you will lose the commit/rollback behavior and instead only get the connection.close() called upon exiting the with-statement.

Per PEP249:

... closing a connection without committing the changes first will cause
an implicit rollback to be performed.

So the commit/rollback behavior is much more useful than simply calling close.

You could use a context manager:

import contextlib

def query(sql,args):
    with contextlib.closing(sqlite3.connect('stocks.db')) as conn:
        curs = conn.cursor()
        curs.execute(sql,args))
        close = curs.fetchall()
        return close

def getPrice(self, symbol, date):
    date = dt.datetime.strptime(date, '%Y-%m-%d')
    sql = '''SELECT close FROM prices WHERE symbol = ? AND date = ?'''
    args = (symbol, date)
    return query(sql, args)

Since you have many functions like getPrice which differ only by the SQL and arguments, you could reduce the repetitious boiler-plate code by defining the query function.

You could also define a context manager to rollback the connection on errors and commit as well as close upon exiting the with block. An example of this (for MySQL) can be found here, adapting it to sqlite3 should not be difficult..

Reference: