Looking For A More Pythonic Way To Access The Database

I have a bunch of python methods that follow this pattern: def delete_session(guid): conn = get_conn() cur = conn.cursor() cur.execute('delete from sessions where guid

Solution 1:

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

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:

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

Solution 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

def delete_session():
    withget_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:

@withcursordefdelete_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.

Solution 3:

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

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.

classSQLFunction( 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()

classDeleteSession( SQLFunction ):
    defrun( 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.

Bunch of Methods in One Class.

classSomeClass( object ):
    def__init__( self, connection ):
        self.connection= connection
    defsql_execute( self, statement, args= None)
        self.cursor= self.connection.cursor() 
        self.cursor.execute( statement, args if args isnotNoneelse [] )
        self.connection.commit()
        self.cursor.close()
    defdelete_session( self ):
        self.sql_execute( "statement" )

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

Solution 4:

A decorator?

classSqlExec: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()

@SqlExecdefdelete_session(guid):
      return"delete from sessions where guid=%s" % guid

Solution 5:

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

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

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"