How to use SQLite (sqlite3) more efficiently
ps16thypresenceisfullnessofjoy at gmail.com
ps16thypresenceisfullnessofjoy at gmail.com
Wed Jun 4 16:27:39 EDT 2014
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()
More information about the Python-list
mailing list