X-Received: by 10.236.134.49 with SMTP id r37mr20603912yhi.14.1401913660220; Wed, 04 Jun 2014 13:27:40 -0700 (PDT)
X-Received: by 10.140.104.52 with SMTP id z49mr281105qge.12.1401913660204; Wed, 04 Jun 2014 13:27:40 -0700 (PDT)
Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!peer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!j5no1221142qaq.1!news-out.google.com!k18ni4648qav.1!nntp.google.com!j5no1221137qaq.1!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: comp.lang.python
Date: Wed, 4 Jun 2014 13:27:39 -0700 (PDT)
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=108.52.71.106; posting-account=5ZUofAoAAACSkMmYQkuJJonc3AuNVoNk
NNTP-Posting-Host: 108.52.71.106
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <6e18a509-9471-4fe8-8664-3c58fc17536f@googlegroups.com>
Subject: How to use SQLite (sqlite3) more efficiently
From: ps16thypresenceisfullnessofjoy@gmail.com
Injection-Date: Wed, 04 Jun 2014 20:27:40 +0000
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Received-Bytes: 4113
X-Received-Body-CRC: 894691532
Xref: csiph.com comp.lang.python:72661

I'm completely new to SQL, and recently started using SQLite in one of my P=
ython programs. I've gotten what I wanted to work, but I'm not sure if I'm =
doing it in the best/most efficient way. I have attached some sample code a=
nd would appreciate any (polite) comments about how the SQL (or Python) in =
it could be improved. The code is written in Python 2, but I think it shoul=
d work in Python 3 if the 4 print statements are changed to function calls.=
 Am I correct that the function 'set_description2' should work the same way=
 as 'set_description'?

Thank you.

-- Timothy

P.S. As some may recognize, the language descriptions in my sample code are=
 based on the subtitle of the book Learning Perl ("the llama").

*** sqlite_test.py ***
import sqlite3


def get_description(conn, name):
    cur =3D conn.cursor()
    cur.execute("SELECT description FROM ProgrammingLanguages WHERE Name=3D=
?",
        (name,))
    row =3D cur.fetchone()
    if row:
        return row[0]
    return None


def set_description(conn, name, description):
    cur =3D conn.cursor()
    cur.execute("SELECT 1 FROM ProgrammingLanguages WHERE Name=3D?", (name,=
))
    row =3D cur.fetchone()
    if description:
        with conn:
            if not row:
                conn.execute("INSERT INTO ProgrammingLanguages VALUES(?,?)"=
,
                    (name, description))
            else:
                conn.execute("UPDATE ProgrammingLanguages SET Description=
=3D? " \
                    "WHERE Name=3D?", (description, name))
    elif row:
        with conn:
            conn.execute("DELETE FROM ProgrammingLanguages WHERE Name=3D?",
                (name,))
    conn.commit()


def set_description2(conn, name, description):
    with conn:
        if description:
            conn.execute("INSERT OR REPLACE INTO ProgrammingLanguages " \
                "VALUES(?,?)", (name, description))
        else:
            conn.execute("DELETE FROM ProgrammingLanguages WHERE Name=3D?",
                (name,))
    conn.commit()


conn =3D sqlite3.connect(":memory:")
conn.execute("CREATE TABLE IF NOT EXISTS ProgrammingLanguages(name TEXT " \
            "PRIMARY KEY, description TEXT)")
set_description(conn, "Perl", "Making Easy Things Easy & Hard Things Possib=
le")
set_description(conn, "Python", "Making Easy Things Easier & Hard Things Ea=
sy")
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 Easy & Hard Things Possible?!")
print "Assembly: %s" % get_description(conn, "Assembly")
set_description(conn, "Assembly",
    "Making Easy Things Very Hard & Hard Things Impossible")
print "Assembly: %s" % get_description(conn, "Assembly")  # Should have cha=
nged
set_description(conn, "Assembly", None)
print "Assembly: %s" % get_description(conn, "Assembly")  # Should be None
conn.close()