Name: [116] Erik Thompson
Member: 87 months
Authored: 44 videos
Description: Hi! I am currently a full time student at California State University Long Beach. I recently began to do research in molecular dynamics. I have interests in Python programming, physics, chemistry, and 3D simulation. Also I like bird watching! Here are some of photos I've taken: http://ww ...

SQLite Database Class [ID:974] (26/35)

in series: Developing emol!

video tutorial by Erik Thompson, added 01/09

(Showmedo is undergoing major changes. To report any problems viewing the videos please email us and include browser and OS specifics. Cheers - Kyran.)

A python database class for use with SQLite databases.

"""
database.py

class to interface with a sqlite database

for python 2.4 or earlier download pysqlite from http://pysqlite.org/

"""

import os

#from pysqlite2 import dbapi2 as sqlite3 # if using python 2.4
import sqlite3  # if using python 2.5 or greater

class Database(object):
    """ class to handle all python communication with a sqlite database file """
    def __init__(self, db_file="data/data.db"):
        database_already_exists = os.path.exists(db_file)    
        self.db = sqlite3.connect(db_file)
        if not database_already_exists:
            self.setupDefaultData()
        
    def select(self,sql):
        """ select records from the database """
        print sql
        cursor = self.db.cursor()  
        cursor.execute(sql)
        records = cursor.fetchall()
        cursor.close()
        return records   
        
    def insert(self,sql):
        """ insert a new record to database and return the new primary key """
        print sql       
        newID = 0
        cursor = self.db.cursor()            
        cursor.execute(sql)
        newID = cursor.lastrowid
        self.db.commit()
        cursor.close()
        return newID
        
    def execute(self,sql):
        """ execute any SQL statement but no return value given """
        print sql 
        cursor = self.db.cursor()  
        cursor.execute(sql)
        self.db.commit()
        cursor.close()

    def executescript(self,sql):
        """ execute any SQL statement but no return value given """
        print sql 
        cursor = self.db.cursor()  
        cursor.executescript(sql)
        self.db.commit()
        cursor.close()

    def setupDefaultData(self):
        """ create the database tables and default values if it doesn't exist already """
        sql = """
        CREATE TABLE Settings
        (
             id         INTEGER     PRIMARY KEY     AUTOINCREMENT,
             setting    TEXT,
             value      TEXT
        ) ;

        INSERT INTO Settings (setting, value)
         VALUES ('slideshow_delay','20') ;


        INSERT INTO Settings (setting, value)
         VALUES ('display_molecule_name','True') ;
        """
        self.executescript(sql)

                            
if __name__ == '__main__':
    db = Database()
    sql = "SELECT id, setting, value FROM Settings"
    records = db.select(sql)
    print records  
    for record in records:
        print "%s = %s " % (record[1],record[2])

    #sql = "UPDATE Settings SET value = '15' WHERE setting = 'slideshow_delay'"
    #sql = "DELETE FROM Settings WHERE id > 2"
    #db.execute(sql)

Got any questions?

Get answers in the ShowMeDo Learners Google Group.

Video statistics:

  • Video's rank shown in the most popular listing
  • Video plays: 1338 (since July 30th)
  • Plays in last week: 4
  • Published: 64 months ago

Thank-yous, questions and comments

If this video tutorial was helpful please take some time to say thank-you to the authors for their hard work. Feel free to ask questions. Let the author know why their video tutorial was useful - what are you learning about? Did the video tutorial save you time? Would you like to see more?

You may also want to see our ShowMeDo Google Group to speak to our active users and authors.

Your email address will not be published.

Show some quick comments >>








All comments excluding tick-boxed quick-comments

good demonstration of use of class


Looks interesting. I will have to try it.


Wonderful. Great work


7. Jeff Kreh Tue, 01 Jun 2010 12:54

I'm enjoying your posts on sqlite3 and python. How would I go about taking a txt-file with a header-row and having it automatically write the db fields and insert the rows with an autoincremented primary key?


Clearly explained. Great stuff


5. Jeff Stocker Wed, 07 Apr 2010 13:12

I'm new to Python and found this video to be most helpful. The database code was simplistic enough for me to understand but yet also functional.


nice tut


3. anonymous Wed, 13 May 2009 04:52

Thanks for the tutorial. Saved some time on my current project.


2. anonymous Thu, 07 May 2009 16:07

This tutorial is awesome. I watched this on and the one before it. I can't quite see the whole name behind this survey window but they were the first and second tutorials on sqlite with python. Good Job Man! I look forward to seeing more of your work. Thank you very much.


video cuts, lets hope it is not recurrent.


Showmedo is a peer-produced video-tutorials and screencasts site for free and open-source software (FOSS)- with the exception of some club videos, the large majority are free to watch and download.

how to help » about » faq »

Educating the Open-source Community With Showmedo

Although as important as the software it supports, education and documentation are relatively neglected in the Open-source world. Coders love to code, and explaining how best to use or improve the software tends to be deferred or even sidelined.

At Showmedo we believe the community can play a vital role here and also say thanks for the tools and software that make our lives easier. If you have a piece of software you love or a programming langugage you are enthusiastic about, why not make a screencast showing others how to use it? All the stuff you wish you'd been told, the tips, tricks, insights that would have saved you time and frustration.

Screencasting is easier than you think, and we're happy to help you. You can emailus for advice or just use some of the how-to screencasts on the site. This screencasting learning-pathis a good place to start.

Kudos and Thanks for Erik

Content

Feedback

Showmedo's development is fairly rapid and bugs will inevitably creep in. If you have any problems please drop us a line using the contact address below. Likewise, any suggestions for improvements to the site are gratefully received.

feedback@showmedo.com