寻找更加pythonic的方式来访问数据库

I have a bunch of python methods that follow this pattern:

我有一堆遵循这种模式的python方法:

def delete_session(guid):
    conn = get_conn()
    cur = conn.cursor()

    cur.execute("delete from sessions where guid=%s", guid)

    conn.commit()
    conn.close()

Is there a more pythonic way to execute raw sql. The 2 lines at the beginning and end of every method are starting to bother me.

是否有更pythonic方式来执行原始SQL。每种方法开始和结束时的2行开始困扰我。

I'm not looking for an orm, I want to stick with raw sql.

我不是在寻找一个orm,我想坚持使用原始sql。

6 个解决方案

#1

You could write a context manager and use the with statement. For example, see this blog post:

您可以编写上下文管理器并使用with语句。例如,请参阅此博客文章:

http://jessenoller.com/2009/02/03/get-with-the-program-as-contextmanager-completely-different/

Also the python documentation has a sample that pretty much matches your needs. See section 8.1 on this page, in particular the snippet that begins:

此外,python文档还有一个非常符合您需求的示例。请参阅本页8.1节,特别是开头的代码段:

db_connection = DatabaseConnection()
with db_connection as cursor:
    cursor.execute('insert into ...')
    cursor.execute('delete from ...')
    # ... more operations ...

https://docs.python.org/2.5/whatsnew/pep-343.html

#2

Careful about that execute, the second argument needs to be [guid] (a list with just one item). As for your question, I normally just use a class encapsulating connection and cursor, but it looks like you may prefer to use an execution context object whose __enter__ method gives you a cursor while __leave__ commits or rollbacks depending on whether the termination was normal or by exception; this would make your code

小心那个执行,第二个参数需要是[guid](只有一个项目的列表)。至于你的问题,我通常只使用一个类封装连接和游标,但看起来你可能更喜欢使用一个执行上下文对象,其__enter__方法在__leave__提交或回滚时为你提供一个游标,具体取决于终止是正常的还是通过例外;这会使你的代码

def delete_session():
    with get_cursor() as cur:
        cur.execute(etc etc)

If you like this style, let us know and I'll show you how to write get_cursor. Others will no doubt propose a decorator instead, so you'd write:

如果您喜欢这种风格,请告诉我们,我将向您展示如何编写get_cursor。其他人无疑会提出一个装饰师,所以你写道:

@withcursor
def delete_session(cur):
    cur.execute(etc etc)

but I think this makes commit/rollback, among other issues, a bit murkier. Still, if this is your preference, again let us know and I can show you how to write that form, too.

但我认为这使得提交/回滚,以及其他问题,有点模糊。不过,如果这是您的偏好,请再次告诉我们,我也可以告诉您如何编写该表单。

#3

"I have a bunch of python methods that follow this pattern:"

“我有一堆遵循这种模式的python方法:”

This is confusing.

这令人困惑。

Either you have a bunch of functions, or you have a bunch of methods of a class.

要么你有很多函数,要么你有一堆类的方法。

Bunch of Functions.

一堆功能。

Do this instead.

这样做。

class SQLFunction( object ):
    def __init__( self, connection ):
        self.connection = connection
    def __call__( self, args=None ):
        self.cursor= self.connection.cursor()
        self.run( args )
        self.cursor.commit()
        self.cursor.close()

class DeleteSession( SQLFunction ):
    def run( self, args ):
        self.cursor.execute( "statement" )

delete_session = DeleteSession( connection )

Your function declarations are two lines longer, but essentially the same. You can do func1( args ) because it's a callable object. The rest of your program should remain unchanged.

你的函数声明长两行,但基本相同。你可以做func1(args),因为它是一个可调用的对象。你的程序的其余部分应该保持不变。

Bunch of Methods in One Class.

一类方法。

class SomeClass( object ):
    def __init__( self, connection ):
        self.connection= connection
    def sql_execute( self, statement, args= None )
        self.cursor= self.connection.cursor() 
        self.cursor.execute( statement, args if args is not None else [] )
        self.connection.commit()
        self.cursor.close()
    def delete_session( self ):
        self.sql_execute( "statement" )

All your methods can look like delete_session and make use of a common sql_execute method.

您的所有方法都可以看起来像delete_session并使用常见的sql_execute方法。

#4

It doesn't have to be more pythonic, just more structured:

它不一定是pythonic,只是更有条理:

def execSql(statement):
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(statement)
    conn.commit()
    conn.close()

def delete_session(guid):
    execSql("delete from sessions where guid=%s"%(guid))

#5

A decorator?

class SqlExec:
   def __init__ (self, f):
      self.f = f
   def __call__ (self, *args):
      conn = get_conn() 
      cur = conn.cursor()
      cur.execute(self.f (*args))
      conn.commit()
      conn.close()

@SqlExec
def delete_session(guid):
      return "delete from sessions where guid=%s" % guid

#6

According to the docs, if you were using SQLite3, you wouldn't even need a Cursor which, as the docs say, is "often superfluous".

根据文档,如果您使用SQLite3,您甚至不需要Cursor,正如文档所说,它“通常是多余的”。

Instead you can use the shortcut methods execute executemany and executescript directly on the connection object:

相反,您可以直接在连接对象上使用快捷方法execute executemany和executioncript:

import sqlite3

persons = [
    ("Hugo", "Boss"),
    ("Calvin", "Klein")
    ]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table person(firstname, lastname)")

# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)", persons)

# Print the table contents
for row in con.execute("select firstname, lastname from person"):
    print row

print "I just deleted", con.execute("delete from person").rowcount, "rows"

#1