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() -- https://mail.python.org/mailman/listinfo/python-list