How to use SQLite (sqlite3) more efficiently

[ps16thypresenceisfullnessofjoy]

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 wayas 'set_description'?

Thank you.

-- Timothy

P.S. As some may recognize, the language descriptions in my sample code arebased 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()

 

[Rustom Mody]

:
:

Happy to help out! But before I look into the code itself, two small
points. Firstly, you're using Google Groups, which miswraps text and
double-spaces all quoted text. This is considered to be extremely
annoying on this list/newsgroup, and while it's not technically your
fault, it will make people less inclined to read and respond to your
posts. I advise signing up for the mailing list instead:

Alternatively, you can read the comp.lang.python newsgroup in any good
newsreader, or you can use Gmane.

What exactly are the irritations of GG and what are you may do about it:

https://wiki.python.org/moin/GoogleGroupsPython

 

[Dennis Lee Bieber]

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()

 

[Chris Angelico]

Unrelated to Python but as you're new to SQL I figured I'd ask: Do you have
an index on the name field? If you don't, you'll incur a full table scan
which will be more expensive than if you have an index in the field.

For more info about indexes see http://www.sqlite.org/lang_createindex.html.

It's the primary key, which I would normally assume is indexed
implicitly. This is another reason for not breaking the CREATE TABLE
statement at the point where the OP did; it's not obvious that that
field is indexed, this way.

ChrisA

 

[Peter Otten]

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'?

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()

Have a look at these to see how this can be wrapped into a dict-like object:

http://svn.python.org/view/sandbox/trunk/dbm_sqlite/alt/dbsqlite.py?view=markup
https://github.com/shish/sqliteshelf/blob/master/sqliteshelf.py