[DB-SIG] Python db programming conventions

[Vernon Cole]

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

1. transactions are controlled by the connection object, not the cursor
object.
so:
<code>
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
  c1.close()
  c2.close()
</code>

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:
<code>
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 > ?"""
    c.execute(sql,(rank,serviceLength))
    for s in c.fetchall():
        print(s[0],s[1])
</code>
--
Vernon

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>