Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #72669

Re: How to use SQLite (sqlite3) more efficiently

Show all headers | View raw

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/

Back to comp.lang.python | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

How to use SQLite (sqlite3) more efficiently ps16thypresenceisfullnessofjoy@gmail.com - 2014-06-04 13:27 -0700
  Re: How to use SQLite (sqlite3) more efficiently Chris Angelico <rosuav@gmail.com> - 2014-06-05 07:23 +1000
    Re: How to use SQLite (sqlite3) more efficiently Rustom Mody <rustompmody@gmail.com> - 2014-06-04 19:15 -0700
      Re: How to use SQLite (sqlite3) more efficiently Demian Brecht <demianbrecht@gmail.com> - 2014-06-04 20:35 -0700
      Re: How to use SQLite (sqlite3) more efficiently Chris Angelico <rosuav@gmail.com> - 2014-06-05 17:08 +1000
  Re: How to use SQLite (sqlite3) more efficiently Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2014-06-04 22:40 -0400
  Re: How to use SQLite (sqlite3) more efficiently Peter Otten <__peter__@web.de> - 2014-06-05 09:48 +0200

csiph-web