Re: How to use SQLite (sqlite3) more efficiently
On 2014-06-06, Mark Lawrence breamore...@yahoo.co.uk wrote: On 06/06/2014 22:58, Dave Angel wrote: Chris Angelico ros...@gmail.com Wrote in message: On Sat, Jun 7, 2014 at 4:15 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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.) The simple answer is: You don't have to edit headers at all. If you want something to be part of the same thread, you hit Reply and don't change the subject line. If you want something to be a spin-off thread, you hit Reply and *do* change the subject. If you want it to be a brand new thread, you don't hit Reply, you start a fresh message. Any decent mailer will do the work for you. Replying is more than just quoting a bunch of text and copying in the subject line with Re: at the beginning. :) set up a newsgroup in Thunderbird from gmane.comp.python.general. That doesn't sound right to me. Surely you set up the newgroup news.gmane.org and then subscribe to the mailing lists, blog feeds or whatever it is that you want? In usenet parlance, news.gmane.org is a newsserver, and gmane.comp.python.general is a newsgroup. gmane runs a series of mail-news gateways for several mailing lists, but there are others as well: someone also bridges the list to the group comp.lang.python, which is where I'm reading this. -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
On Thu, 5 Jun 2014 17:17:19 -0500 (CDT), Dave Angel da...@davea.name wrote: R Johnson ps16thypresenceisfullnessof...@gmail.com Wrote in message: I've attached some new sample code in which I've attempted to correct various things that you mentioned. Attachments don't work well for many people using this list. I for one can't even see them. And for those reading it as a newsgroup they don't work at all. -- Steve Hayes from Tshwane, South Africa Web: http://www.khanya.org.za/stevesig.htm Blog: http://khanya.wordpress.com E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk -- 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
Re: How to use SQLite (sqlite3) more efficiently
On Sat, Jun 7, 2014 at 4:15 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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.) The simple answer is: You don't have to edit headers at all. If you want something to be part of the same thread, you hit Reply and don't change the subject line. If you want something to be a spin-off thread, you hit Reply and *do* change the subject. If you want it to be a brand new thread, you don't hit Reply, you start a fresh message. Any decent mailer will do the work for you. Replying is more than just quoting a bunch of text and copying in the subject line with Re: at the beginning. :) ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
Chris Angelico ros...@gmail.com Wrote in message: On Sat, Jun 7, 2014 at 4:15 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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.) The simple answer is: You don't have to edit headers at all. If you want something to be part of the same thread, you hit Reply and don't change the subject line. If you want something to be a spin-off thread, you hit Reply and *do* change the subject. If you want it to be a brand new thread, you don't hit Reply, you start a fresh message. Any decent mailer will do the work for you. Replying is more than just quoting a bunch of text and copying in the subject line with Re: at the beginning. :) The other half is that in order to be able to reply to a message you have to be reading that message in a mail program, or in a newsreader. Since you (R) are using Thunderbird, you should either subscribe to the mailing list (NOT in digest mode), or set up a newsgroup in Thunderbird from gmane.comp.python.general. If you choose the mailing list, you'll probably want to set up a rule in Thunderbird that moves all incoming messages from the inbox to a dedicated folder. Set that folder to show the threaded view, and you should be in good shape. There's a keystroke that gets you to the next unread message, but all the others are easily accessible. It interprets those headers mentioned above and groups all the replies together. And you use the Reply-list button so the message goes to the list and not the individual. -- DaveA -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
On 06/06/2014 22:58, Dave Angel wrote: Chris Angelico ros...@gmail.com Wrote in message: On Sat, Jun 7, 2014 at 4:15 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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.) The simple answer is: You don't have to edit headers at all. If you want something to be part of the same thread, you hit Reply and don't change the subject line. If you want something to be a spin-off thread, you hit Reply and *do* change the subject. If you want it to be a brand new thread, you don't hit Reply, you start a fresh message. Any decent mailer will do the work for you. Replying is more than just quoting a bunch of text and copying in the subject line with Re: at the beginning. :) set up a newsgroup in Thunderbird from gmane.comp.python.general. That doesn't sound right to me. Surely you set up the newgroup news.gmane.org and then subscribe to the mailing lists, blog feeds or whatever it is that you want? -- My fellow Pythonistas, ask not what our language can do for you, ask what you can do for our language. Mark Lawrence --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
On Thu, Jun 5, 2014 at 1:35 PM, Demian Brecht demianbre...@gmail.com wrote: On Thu, Jun 5, 2014 at 6:27 AM, ps16thypresence wrote: I'm completely new to SQL, and recently started using SQLite in one of my Python programs. 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 -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
ps16thypresenceisfullnessof...@gmail.com wrote: 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 -- https://mail.python.org/mailman/listinfo/python-list
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() -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
On Fri, Jun 6, 2014 at 3:42 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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). Thanks! Good to know. 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,)) I was actually going to consider saying don't wrap stuff up in functions like this at all, just do your SQL queries directly in whatever needs them, but apparently that's what you're already doing. The mode-switch is right where it should be, at the point where the switch happens. Your code says if the editor is empty, do something different, which is the easiest and clearest way to describe this. 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 :). Hehe. As I'm sure you're aware, this has absolutely nothing to do with your SQL or Python code. 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? I can't speak for SQLite3, but here's how it is with full-on databases like DB2 and PostgreSQL. (I suspect sqlite may be a little simplified from this, but it's likely to be the same. In any case, get into good habits now and you won't run into problems later.) Whenever you start working with a database, you open a unit of work, aka a transaction. This transaction continues until it is terminated, either by a commit or rollback, or by something breaking your connection to the db (if your program or the database shuts down abruptly, your transaction will be rolled back). While it's alive, it holds certain resources, mainly locks. It's fairly obvious that updating should acquire a lock - if some other program tries to update the same row of the same table, it should be blocked - but even reading will grab some locks. Depending on the database and the config, your reads might prevent anyone else from writing, or (as with PostgreSQL) they might simply prevent someone else from dropping that table altogether, but there'll still be locks. The most important thing to remember is that your commit/rollback points should match the logical unit of work that you're doing. Figure out what your program would do if it got shut down abruptly in the middle of the database work - if there's any way that something could be half-done and illogical, you should avoid committing in there, until you've brought the database back to a stable state. Often that means never committing until the very end of the program flow; sometimes it means committing every little piece you do; it could be anywhere in between. with conn: This isn't really doing anything useful. You aren't opening a new connection object, so there isn't
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
On Fri, Jun 6, 2014 at 6:12 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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. (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.) You can use either, and they're equally non-standard - in my opinion, equally wrong. Whatever feels right to you. ChrisA -- 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.1view=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
Re: How to use SQLite (sqlite3) more efficiently
On Fri, Jun 6, 2014 at 8:57 AM, R Johnson ps16thypresenceisfullnessof...@gmail.com wrote: 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. The subject line isn't as important as a header, carried invisibly through, that says that you were replying to an existing post. :) ChrisA -- https://mail.python.org/mailman/listinfo/python-list
How to use SQLite (sqlite3) more efficiently
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
Re: How to use SQLite (sqlite3) more efficiently
On Thu, Jun 5, 2014 at 6:27 AM, ps16thypresenceisfullnessof...@gmail.com wrote: 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'? 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: https://mail.python.org/mailman/listinfo/python-list Alternatively, you can read the comp.lang.python newsgroup in any good newsreader, or you can use Gmane. The second point that you may want to consider: If you think the code is that close to Python 3 compatible, and if your Python 2 is either version 2.6 or 2.7, just put this line at the top of your script: from __future__ import print_function Then you'll be using print() as a function, and your code may well work unchanged on both versions. But do still tell us what version you're running this under; Python 2 is a start, but it'd be useful to know which minor version, and which operating system. I don't think it makes any difference here, but it's easy enough to just say Tested on Python 2.6 on Debian Linux or Tested on Python 2.7.7 on Windows 7 or somesuch. On to the code! *plays Lord Phillip theme from 'The Beauty Stone'* 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 Since cur.fetchone() returns None if there's no row, you can simplify this down: return row and row[0] Not everyone likes that sort of style, so take your pick. 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() 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. The insert-or-update concept is a bit of a tricky one. With a simple database, you probably don't need to worry about the race condition of seeing there's no row, then going to insert, and finding that you can't; but still consider an EAFP model instead - attempt the update, and if nothing got updated, do the insert (or, conversely, attempt the insert, and if you get back an error, update instead). That still has a chance of a race (rows can still be inserted or deleted in the middle), but if you do the more likely case first (do you expect to mostly be updating or inserting?), you'll catch most of your work with a single query, which is that bit safer (not to mention faster). However, trying to use an SQL table as if it were a Python dict is fundamentally hard. No matter how you work it, it won't be perfect. 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() In theory, yes, this will probably achieve the same as your set_description. But since, as I said above, it's fundamentally hard to do the INSERT OR REPLACE operation, there's no guarantee it'll be any better than doing it manually; and this is something that not all database backends support, so if you ever change to a more high-end database (eg PostgreSQL), you'll have to recode this
Re: How to use SQLite (sqlite3) more efficiently
On Thursday, June 5, 2014 2:53:21 AM UTC+5:30, Chris Angelico wrote: On Thu, Jun 5, 2014 at 6:27 AM, ps16thypresence wrote: I'm completely new to SQL, and recently started using SQLite in one of my Python programs. : : 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: https://mail.python.org/mailman/listinfo/python-list 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 -- https://mail.python.org/mailman/listinfo/python-list
Re: How to use SQLite (sqlite3) more efficiently
On Thu, Jun 5, 2014 at 6:27 AM, ps16thypresence wrote: I'm completely new to SQL, and recently started using SQLite in one of my Python programs. 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. -- https://mail.python.org/mailman/listinfo/python-list
