Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newspeer1.nac.net!newsfeed.xs4all.nl!newsfeed1.news.xs4all.nl!xs4all!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Dennis Lee Bieber <wlfraed@ix.netcom.com>
Subject: Re: How to use SQLite (sqlite3) more efficiently
Date: Wed, 04 Jun 2014 22:40:17 -0400
Organization: IISS Elusive Unicorn
References: <6e18a509-9471-4fe8-8664-3c58fc17536f@googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.10719.1401936028.18130.python-list@python.org>
Lines: 78
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:72669

On Wed, 4 Jun 2014 13:27:39 -0700 (PDT),
ps16thypresenceisfullnessofjoy@gmail.com declaimed the following:

>def get_description(conn, name):
>    cur = conn.cursor()
>    cur.execute("SELECT description FROM ProgrammingLanguages WHERE Name=?",
>        (name,))
>    row = cur.fetchone()
>    if row:
>        return row[0]
>    return None

	The last line is not really needed -- falling off the end of function
results in returning None.

	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.

>
>
>def set_description(conn, name, description):
>    cur = conn.cursor()
>    cur.execute("SELECT 1 FROM ProgrammingLanguages WHERE Name=?", (name,))
>    row = cur.fetchone()
>    if description:
>        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.

>            if not row:
>                conn.execute("INSERT INTO ProgrammingLanguages VALUES(?,?)",
>                    (name, description))

	And here you are using a "convenience" shortcut (execute from
connection rather than creating a cursor -- which you already have!).

>            else:
>                conn.execute("UPDATE ProgrammingLanguages SET Description=? " \
>                    "WHERE Name=?", (description, name))
>    elif row:
>        with conn:
>            conn.execute("DELETE FROM ProgrammingLanguages WHERE Name=?",
>                (name,))
>    conn.commit()

	Untested, but I'd probably end up with something like...

def set_description(conn, name, description=None):
	cur = conn.cursor()
	#only interested in knowing if any such exist; count() ensures a value
	cur.execute("select count(*) from ProgrammingLanguages where Name = ?",
				(name,))
	if cur.fetchone()[0] == 0:
		if description:
			cur.execute("insert into ProgrammingLanguages values (?, ?)",
						(name, description))
		else:
			#WARNING -- request to delete non-existant language records
	else:
		if description:
			#note use of triple quotes; avoids the line continuation \ need
			cur.execute("""update ProgrammingLanguages set
								Description = ?
							where Name = ?""",
						(description, name))
		else:
			cur.execute("delete from ProgrammingLanguages where Name = ?",
						(name,))
	conn.commit()
	cur.close()

-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/