[DB-SIG] Python db programming conventions

Vernon Cole vernondcole at gmail.com
Tue Oct 19 19:26:59 CEST 2010

This answer is based on the generic db-api, not specifically on sqlite3.

1. transactions are controlled by the connection object, not the cursor
import sqlite3 as db
conn = db.connect('someDatabase')
while someCondition:
  c1 = conn.cursor()
  c2 = conn.cursor()
  c1.execute('update something')
  c2.execute('update another thing')
  conn.commit() # commits both operations

2. Put the SQL code where ever it fits best in your application. Do remember
to use parameters for changeable data, rather than string substitution in
the SQL code. I find that my code is most readable when I keep the SQL near
the .execute() statement, like this:
def get_volunteers(rank,serviceLength=0):
    c = conn.cursor() # conn is a connection defined elsewhere
    sql = """SELECT name, serial_number from battalion
               where rank = ? and length_of_service > ?"""
    for s in c.fetchall():

On Sat, Oct 16, 2010 at 1:08 AM, python + sqlite3 <sqlite3.user at gmail.com>wrote:

> Hello all,
> As I'm new to both python and sqlite3, I would like to consult regarding
> python db programming conventions. After I finished to design my
> application
> and started coding it, I found myself dealing with the following questions:
> 1. Since the db contains more than one table, it seems natural to use
> transaction in order to create the it. The transaction contains more than
> one SQL statement and therefor can't be executed using cursor.execute(). I
> found the method cursor.executescript() that documented as non-standard, is
> that the method to be used? is there better way of creating the db?
> 2. In order to create and maintain the db tables, I'll need to use several
> long transactions (CREATE, EDIT, INSERT, DELETE etc.). Where should I place
> the SQL code for them? Should my class contain the SQL queries as private
> string properties? should I define the SQL queries as class member
> functions? Or is it better to store the SQL code in separate SQL files and
> execute the files somehow (is it possible to execute SQL file using
> sqlite3)?
> I'd appreciate your help,
> Cheers
> --
> View this message in context:
> http://old.nabble.com/Python-db-programming-conventions-tp29977345p29977345.html
> Sent from the Python - db-sig mailing list archive at Nabble.com.
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> http://mail.python.org/mailman/listinfo/db-sig
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/db-sig/attachments/20101019/86268310/attachment.html>

More information about the DB-SIG mailing list