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

Groups > comp.lang.python > #72661

How to use SQLite (sqlite3) more efficiently

Show all headers | View raw

I'm completely new to SQL, and recently started using SQLite in one of my Python 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 and 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 should 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 = conn.cursor()
    cur.execute("SELECT description FROM ProgrammingLanguages WHERE Name=?",
        (name,))
    row = cur.fetchone()
    if row:
        return row[0]
    return None


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:
            if not row:
                conn.execute("INSERT INTO ProgrammingLanguages VALUES(?,?)",
                    (name, description))
            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()


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=?",
                (name,))
    conn.commit()


conn = 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 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 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 changed
set_description(conn, "Assembly", None)
print "Assembly: %s" % get_description(conn, "Assembly")  # Should be None
conn.close()

Back to comp.lang.python | Previous | Next — 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