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.u...@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@python.org
> http://mail.python.org/mailman/listinfo/db-sig
>
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
