Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!news.stack.nl!newsfeed.xs4all.nl!newsfeed3.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
Date: Thu, 05 Jun 2014 13:42:10 -0400
From: R Johnson <ps16thypresenceisfullnessofjoy@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: python-list@python.org
Subject: How to use SQLite (sqlite3) more efficiently
Content-Type: multipart/mixed; boundary="------------050902080507040401050606"
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.10754.1401990136.18130.python-list@python.org>
Lines: 134
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:72741

This is a multi-part message in MIME format.
--------------050902080507040401050606
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

Thank you all for your replies and suggestions.

To Chris's "two small points":
I saw that using the mailing list was recommended to several other 
people who posted here using Google Groups, so I thought it might be 
recommended to me as well sometime :). I'll try to use it from now on.
My code was tested on Python 2.7.6 on Windows 8.1 (and I just installed 
Python 2.7.7 yesterday).

 > There's a general principle in Python APIs that a "mode switch"
 > parameter isn't a good thing. Even more strongly, I would be very
 > surprised if attempting to set a blank description deleted the row
 > instead of setting the description to blank. My recommendation: Split
 > this into two functions, set_description and delete_language. In
 > delete_language, just unconditionally delete, don't bother checking
 > for the row's presence first.

I agree for the case of the sample code I showed here (which was really 
just a scaled-down version of some of the functions in my program). But 
in my actual program, I am using SQLite to load and save information 
from a wxPython GUI, where it's more practical to call a single save 
function. Below is the actual function (that's part of a class in my 
program):

def save_text(self):
     if not self.editor.IsModified():
         return
     if not self.editor.IsEmpty():
         stream = cStringIO.StringIO()
         self.editor.GetBuffer().SaveStream(stream,
             richtext.RICHTEXT_TYPE_XML)
         self.conn.execute("REPLACE INTO notes VALUES(?,?)",
             (self.db_key, stream.getvalue()))
         self.editor.SetModified(False)
     else:
         self.conn.execute("DELETE FROM notes WHERE topic=?",
             (self.db_key,))

(Even if you're not familiar with wxPython, it should be fairly easy to 
figure out what the code is doing. It's just saving some XML from a rich 
text editor to a StringIO object, and then to an SQLite database.)

 > > set_description(conn, "Assembly",
 > >     "Making Easy Things Very Hard & Hard Things Impossible")
 >
 > Hey, that's not fair! Assembly language makes some hard things really
 > easy, like segfaulting your process. Credit where it's due! :)

OK, I'll admit that I don't know Assembly :). How about the paradox 
"Making Easy Things Hard & Hard Things Easy"? Although that might make 
my description of C++ too unfair; suggestions for improvements to my 
language descriptions are welcome :).

 >        While /maybe/ not required for a SELECT operation, I'd put a
 >conn.commit() somewhere in there before the return(s). The standard for
 > Python DB-API interfaces is that auto-commit is turned off -- meaning the
 > SELECT has started a database transaction.

I don't exactly understand why conn.commit() should be called there. I 
thought it's only necessary to call it when the database has been 
changed, which a SELECT call doesn't do. Am I misunderstanding something 
here?

 > >        with conn:
 >
 >         This isn't really doing anything useful. You aren't opening a new
 > connection object, so there isn't really anything to close on block 
exit.

See 
https://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager. 
I removed it from my code, though, because it doesn't really seem necessary.

I've attached some new sample code in which I've attempted to correct 
various things that you mentioned. The links Peter pointed to were also 
helpful to show me some improvements I could make to my code. I'd be 
happy to hear any suggestions that anyone may have to improve the code 
further.

-- Timothy

--------------050902080507040401050606
Content-Type: text/plain; charset=windows-1252;
 name="sqlite_test.py"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="sqlite_test.py"

from __future__ import print_function 
import sqlite3


def get_description(conn, description):
    row = conn.execute("SELECT description FROM languages WHERE name=?",
        (description,)).fetchone()
    if row:
        return row[0]


def set_description(conn, name, description):
    conn.execute("REPLACE INTO languages VALUES(?,?)", (name, description))
    conn.commit()


def delete_language(conn, name):
    conn.execute("DELETE FROM languages WHERE name=?", (name,))
    conn.commit()


conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE IF NOT EXISTS languages(name TEXT PRIMARY KEY, " \
    "description TEXT NOT NULL)")
set_description(conn, "Perl", "Making Easy Things Easy & Hard Things Possible")
set_description(conn, "Python", "Making Easy Things Easier & Hard Things Easy")
set_description(conn, "C++", "Making Easy Things Hard & Hard Things Harder")
for language in ("Perl", "Python", "C++"):
    print("%s: %s" % (language, get_description(conn, language)))
set_description(conn, "Assembly", "Making Easy Things Very Hard & " \
    "Hard Things Impossible (Hey, that's not fair!)")
print("Assembly: %s" % get_description(conn, "Assembly"))
set_description(conn, "Assembly",
    "Making Easy Things Hard & Hard Things Easy")
print("Assembly: %s" % get_description(conn, "Assembly"))  # Should be changed
delete_language(conn, "Assembly")
print("Assembly: %s" % get_description(conn, "Assembly"))  # Should be None
conn.close()


--------------050902080507040401050606--