How to use SQLite (sqlite3) more efficiently
Thank you all for your replies and suggestions.
To Chris's "two small points":
I saw that using the mailing list was recommended to several other
people who posted here using Google Groups, so I thought it might be
recommended to me as well sometime :). I'll try to use it from now on.
My code was tested on Python 2.7.6 on Windows 8.1 (and I just installed
Python 2.7.7 yesterday).
> There's a general principle in Python APIs that a "mode switch"
> parameter isn't a good thing. Even more strongly, I would be very
> surprised if attempting to set a blank description deleted the row
> instead of setting the description to blank. My recommendation: Split
> this into two functions, set_description and delete_language. In
> delete_language, just unconditionally delete, don't bother checking
> for the row's presence first.
I agree for the case of the sample code I showed here (which was really
just a scaled-down version of some of the functions in my program). But
in my actual program, I am using SQLite to load and save information
from a wxPython GUI, where it's more practical to call a single save
function. Below is the actual function (that's part of a class in my
program):
def save_text(self):
if not self.editor.IsModified():
return
if not self.editor.IsEmpty():
stream = cStringIO.StringIO()
self.editor.GetBuffer().SaveStream(stream,
richtext.RICHTEXT_TYPE_XML)
self.conn.execute("REPLACE INTO notes VALUES(?,?)",
(self.db_key, stream.getvalue()))
self.editor.SetModified(False)
else:
self.conn.execute("DELETE FROM notes WHERE topic=?",
(self.db_key,))
(Even if you're not familiar with wxPython, it should be fairly easy to
figure out what the code is doing. It's just saving some XML from a rich
text editor to a StringIO object, and then to an SQLite database.)
> > set_description(conn, "Assembly",
> > "Making Easy Things Very Hard & Hard Things Impossible")
>
> Hey, that's not fair! Assembly language makes some hard things really
> easy, like segfaulting your process. Credit where it's due! :)
OK, I'll admit that I don't know Assembly :). How about the paradox
"Making Easy Things Hard & Hard Things Easy"? Although that might make
my description of C++ too unfair; suggestions for improvements to my
language descriptions are welcome :).
>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.
I don't exactly understand why conn.commit() should be called there. I
thought it's only necessary to call it when the database has been
changed, which a SELECT call doesn't do. Am I misunderstanding something
here?
> >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.
See
https://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager.
I removed it from my code, though, because it doesn't really seem necessary.
I've attached some new sample code in which I've attempted to correct
various things that you mentioned. The links Peter pointed to were also
helpful to show me some improvements I could make to my code. I'd be
happy to hear any suggestions that anyone may have to improve the code
further.
-- Timothy
from __future__ import print_function
import sqlite3
def get_description(conn, description):
row = conn.execute("SELECT description FROM languages WHERE name=?",
(description,)).fetchone()
if row:
return row[0]
def set_description(conn, name, description):
conn.execute("REPLACE INTO languages VALUES(?,?)", (name, description))
conn.commit()
def delete_language(conn, name):
conn.execute("DELETE FROM languages WHERE name=?", (name,))
conn.commit()
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE IF NOT EXISTS languages(name TEXT PRIMARY KEY, " \
"description TEXT NOT NULL)")
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 Very Hard & " \
"Hard Things Impossible (Hey, that's not fair!)")
print("Assembly: %s" % get_description(conn, "Assembly"))
set_description(conn, "Assembly",
"Making Easy Things Hard & Hard Things Easy")
print("Assembly: %s" % get_description(conn, "Assembly")) # Should be changed
delete_language(conn, "Assembly")
print("Assembly: %s" % get_description(conn, "Assembly")) # Should be None
conn.close
How to use SQLite (sqlite3) more efficiently
I forgot to mention that the scripts Peter pointed to used REPLACE instead of INSERT OR REPLACE. The SQLite documentation says that REPLACE is an alias for INSERT OR REPLACE provided for compatibility with other SQL database engines. Is there a preference for one or the other? I had changed my code from using INSERT OR REPLACE to using REPLACE (including my new sample), but since then changed it back. I don't care about compatibility with other database engines, and INSERT OR REPLACE seems more logical to me, since REPLACE sounds like a synonym for UPDATE. -- Timothy -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
Sorry for the attachment issue. I'm used to the wxPython-users Google
group, where posters are instructed to attach code to their post instead
of including it in the body of the message.
I placed the latest version of my sample code below, since I made a few
minor changes to it after posting it as that attachment. (I changed
REPLACE to INSERT OR REPLACE as I mentioned above, followed Chris'
suggestion to not use backslash continuation characters, and changed the
description of C++ as mentioned below.)
FYI, you can also view the version (now outdated) that I had attached on
the Google Groups website at:
https://groups.google.com/group/comp.lang.python/attach/bd64353c8dfd43ad/sqlite_test.py?part=0.1&view=1
> > OK, I'll admit that I don't know Assembly :). How about the paradox
> "Making
> > Easy Things Hard & Hard Things Easy"? Although that might make my
> > description of C++ too unfair; suggestions for improvements to my
> language
> > descriptions are welcome :).
>
> Hehe. As I'm sure you're aware, this has absolutely nothing to do with
> your SQL or Python code.
Of course :). It's just for fun. I changed the description for C++ to
"Making Easy Things Hard & Hard Things Hard" (without the -er on the end).
> (Suggestion: Always reply to an existing post if it's part of the same
> thread. Replying to your own post is fine, and it links the thread
> together nicely.)
Sorry about that. As you can probably tell, I'm relatively new to using
mailing lists. I'm not exactly sure why that occurred like it did. I'll
try adding "Re:" in front of the subject when I send this e-mail, and
see if it works right this time. If not, I guess you'll have to please
explain to me what I'm doing wrong.
Thank you again for your help.
-- Timothy
*** sqlite_test.py ***
from __future__ import print_function
import sqlite3
def get_description(conn, description):
row = conn.execute("SELECT description FROM languages WHERE name=?",
(description,)).fetchone()
if row:
return row[0]
def set_description(conn, name, description):
conn.execute("INSERT OR REPLACE INTO languages VALUES(?,?)",
(name, description))
conn.commit()
def delete_language(conn, name):
conn.execute("DELETE FROM languages WHERE name=?", (name,))
conn.commit()
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE IF NOT EXISTS languages(name TEXT PRIMARY KEY, "
"description TEXT NOT NULL)")
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 Hard")
for language in ("Perl", "Python", "C++"):
print("%s: %s" % (language, get_description(conn, language)))
set_description(conn, "Assembly", "Making Easy Things Very Hard & "
"Hard Things Impossible (Hey, that's not fair!)")
print("Assembly: %s" % get_description(conn, "Assembly"))
set_description(conn, "Assembly",
"Making Easy Things Hard & Hard Things Easy")
print("Assembly: %s" % get_description(conn, "Assembly")) # Should be
changed
delete_language(conn, "Assembly")
print("Assembly: %s" % get_description(conn, "Assembly")) # Should be None
conn.close()
--
https://mail.python.org/mailman/listinfo/python-list
How to use SQLite (sqlite3) more efficiently
> The subject line isn't as important as a header, carried invisibly > through, that says that you were replying to an existing post. :) Sorry for my ignorance, but I've never edited email headers before and didn't find any relevant help on Google. Could you please give some more details about how to do what you're referring to, or perhaps point me to a link that would explain more about it? (FYI, I read the Python mailing list on Google Groups, and reply to posts in Thunderbird, sending them to the Python-list email address.) I was thinking that since I've been manually calling conn.commit() in my wxPython program after executing each SQL transaction, it would make sense to use autocommit mode (by setting 'isolation_level' to None). Am I correct about this? I've read on StackOverflow that there can be disadvantages to using autocommit mode for SQL databases in general, but they weren't elaborated. The only one I could think of would be decreased performance if SQL transactions are committed automatically more than necessary (which wouldn't apply in my case). I guess that's also likely why PEP 249 specifies that Python database implementations must have autocommit mode turned off by default. Thank you. -- Timothy -- https://mail.python.org/mailman/listinfo/python-list
