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=%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.
I'm not looking for an orm, I want to stick with raw sql.
You could write a context manager and use the with statement. For example, see this blog post (archived)
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 ...
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():
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:
@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.
"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.
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.
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.
It doesn't have to be more pythonic, just more structured:
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))
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
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 execute executemany 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"
